hirondelle.web4j.database
Class DynamicCriteria

Object
  hirondelle.web4j.database.DynamicCriteria

public class DynamicCriteria
extends Object

Dynamic SQL statement fragment (usually WHERE and/or ORDER BY clauses) created in code, whose content is checked by this class for SQL Injection attacks.

This class is intended for implementing filter/sort operations, where the criteria are built dynamically in code, according to what the user has entered in a form. This class exists since enumerating all possible SQL statements in an .sql file, corresponding to all possible combinations of filter/sort criteria that the user might enter in a form, is often impractical, since the number of combinations can often become quite large.

This class is used to dynamically create a validated SQL fragment (usually a WHERE and/or an ORDER BY clause), which is then appended to a base SQL statement already defined (as usual), in your .sql file. Specifically, the return value of toString() is appended. The framework then passes the resulting text to PreparedStatement. If needed, GROUP BY and HAVING clauses can be included as well.

Entries in .sql Files

The return value of toString() is appended to an existing entry in an .sql file. That entry can take several forms. The general idea is that the .sql file contains a valid SQL statement which :

• can be precompiled by WEB4J upon startup, if desired.
• contains all static elements of the final SQL statement, while this class is used to create the dynamic part of the SQL statement.

Entries in an .sql file that are used with this class can take these forms :

SELECT a,b,c 
FROM d JOIN e on ...
[WHERE / ORDER BY added dynamically here]

or

SELECT a,b,c 
FROM d JOIN e on ...
WHERE f=g [static criteria only]
[more WHERE criteria, and ORDER BY, added dynamically here]

You are encouraged implement joins between tables using the JOIN syntax in your .sql file entry. The alternative is to implement joins using expressions in the WHERE clause. This isn't desirable, since it mixes up two distinct items - joins and actual criteria. Using JOIN allows these items to remain separate and distinct.

SQL Injection

When creating SQL statements in code, there's a risk of SQL Injection attacks. When used correctly with '?' placeholders, the PreparedStatement class will protect against all SQL injection attacks. However, if used incorrectly, by placing literal values where '?' placeholders should appear, the String passed to PreparedStatement is still subject to SQL Injection attacks.

An inexperienced or inattentive programmer needs protection from such errors. This class provides some protection for such mistakes. Its toString() method validates the SQL fragment against SQL Injection flaws, by checking that a '?' appears in every place where it should.

Defects of this Class

The implmentation of this class is imperfect. It uses some relatively simple regular expressions, not a comprehensive SQL parser. (Given the variations of SQL implementations from the ANSI standard, even a standards-compliant SQL parser might still fail to correctly report all possible SQL Injection flaws.)

In spite of the defects listed below, this class is still useful, since it will still prevent the programmer from many simple careless errors - which is the intent of this class.

Extra Spaces
The following criteria will be falsely reported as having a SQL Injection flaw :

where x = some( select blah from whatever) 
where x = to_date( ?) 
where x IN ( ?)
where x IN (?, ?)

The workaround is simply to remove the 'extra' space, like so :

where x = some(select blah from whatever) 
where x = to_date(?) 
where x IN (?)
where x IN (?,?)

Special Function Parameters
Function calls that don't have a '?' immediately after the opening parenthesis are falsely reported by this class as SQL Injection flaws. Example :

where x = to_date(interval ?)
where x = FROM_TZ(TIMESTAMP '..', ...)
where x = TO_CHAR(TO_DATE(...))   
where x = TO_CHAR(SYSDATE,...)   
where x = TZ_OFFSET(SESSIONTIMEZONE)   

There is no workaround for this issue.

Changing the Regular Behavior of This Class

Given the above mentioned defects, there are two ways to change the behavior of this class :

• override the sqlInjectionRiskFor(String) method.
• turn off checking for SQL Injection altogether, by calling turnOffCheckingForSqlInjection(). This should be done only as a last resort, and only if you have confirmed that your dynamically constructed SQL is not open to SQL Injection flaws.

See Also

Other items closely related to this class are :

• ActionImpl.getOrderBy(hirondelle.web4j.request.RequestParameter,hirondelle.web4j.request.RequestParameter, String) - convenience method for constructing an ORDER BY clause from request parameters.
• Db.search(Class, SqlId, DynamicCriteria, Object[])
• the Report class.

Constants

The WHERE, AND, and other constants are included in this class as a simple convenience. Note that each value includes a leading a trailing space, to avoid trivial spacing errors.

Field Summary

static String	AND Value - " AND ", convenience value for building a WHERE clause.
static String	ASC Value - " ASC ", convenience value for building an ORDER BY clause.
static String	DESC Value - " DESC ", convenience value for building an ORDER BY clause.
static DynamicCriteria	NONE Represents the absence of any criteria.
static String	OR Value - " OR ", convenience value for building a WHERE clause.
static String	ORDER_BY Value - " ORDER BY ", convenience value for building an ORDER BY clause.
static String	WHERE Value - " WHERE ", convenience value for building a WHERE clause.

Constructor Summary

DynamicCriteria(String aSqlFragment)
Constructor.

DynamicCriteria(StringBuilder aSqlFragment)
Convenience constructor, forwards to DynamicCriteria(String).

Method Summary

protected String	sqlInjectionRiskFor(String aSqlFragment) Validate the SQL fragment.
String	toString() Template method which returns the String passed to the constructor, after validating it for SQL Injection flaws.
void	turnOffCheckingForSqlInjection() Force this class to not perform any validation on the SQL fragment.

Methods inherited from class Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

WHERE

public static final String WHERE

Value - " WHERE ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

AND

public static final String AND

Value - " AND ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

OR

public static final String OR

Value - " OR ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

ORDER_BY

public static final String ORDER_BY

Value - " ORDER BY ", convenience value for building an ORDER BY clause.

See Also:

Constant Field Values

ASC

public static final String ASC

Value - " ASC ", convenience value for building an ORDER BY clause.

See Also:

Constant Field Values

DESC

public static final String DESC

Value - " DESC ", convenience value for building an ORDER BY clause.

See Also:

Constant Field Values

NONE

public static final DynamicCriteria NONE

Represents the absence of any criteria. The value of this item is simply null.

If a method allows a null object to indicate the absence of any criteria, then it is recommended that this reference be used instead of null.

Constructor Detail

DynamicCriteria

public DynamicCriteria(String aSqlFragment)

Constructor.

This constructor will slightly modify the given parameter: it will trim it, and prepend a new line to the result.

Parameters:

aSqlFragment - must have content; it will be trimmed by this method.

DynamicCriteria

public DynamicCriteria(StringBuilder aSqlFragment)

Convenience constructor, forwards to DynamicCriteria(String).

Method Detail

toString

public final String toString()

Template method which returns the String passed to the constructor, after validating it for SQL Injection flaws.

The returned SQL fragment is not a complete SQL statement, and is appended by the framework to an existing entry in an .sql file.

This method calls sqlInjectionRiskFor(String), which is an overridable method. If that method returns a non-empty value, then a runtime exception is thrown.

If turnOffCheckingForSqlInjection() has been called, then no checking for SQL Injection flaws is performed. See class comment.

Overrides:

toString in class Object

turnOffCheckingForSqlInjection

public final void turnOffCheckingForSqlInjection()

Force this class to not perform any validation on the SQL fragment.

Warning - this method should be called only if you are certain that your dynamic SQL always uses '?' placeholders where it should, and doesn't place literal data from user input directly into the SQL fragment.

See class comment; consider overriding sqlInjectionRiskFor(String) instead of calling this method.

sqlInjectionRiskFor

protected String sqlInjectionRiskFor(String aSqlFragment)

Validate the SQL fragment.

Return text with content only if the SQL fragment shows a risk of SQL injection. The returned text describes the nature of the error. If there's no error, then an empty String, or null, is returned. Some implementations may have a simple 'there is an error' message, while others may be more detailed, and provide the exact position in the SQL fragment of the problems found.

This method is overridable. Subclasses may supply their own validation, if desired. Please exercise caution when overriding this method. See class comment.

The default implementation searches for the text appearing after these operators (text is delimited by a blank space) :

(=|<>|!=|<=|>=|<|>| IN | Like | Between )

The text is then examined. If the text doesn't match any of the following allowed forms, then it's flagged as an SQL Injection flaw:

Fixed text :

?, ?), (?), (SELECT,  ALL(SELECT, SOME(SELECT, ANY(SELECT, EXISTS(SELECT

Function calls of the following forms :

to_date(?), my_function(?,...), blah(? ...)

IN parameters :

IN(?,?,...,?)