Examples of hirondelle.web4j.database.DynamicCriteria

kipedia.org/wiki/SQL_injection'>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 {@link #toString} is appended. The framework then passes the resulting text to {@link java.sql.PreparedStatement}. If needed, GROUP BY and HAVING clauses can be included as well.

Entries in .sql Files

The return value of {@link #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]

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 {@link java.sql.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 {@link #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 {@link #sqlInjectionRiskFor(String)} method.
turn off checking for SQL Injection altogether, by calling {@link #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.

Constants

The {@link #WHERE}, {@link #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.