001 package hirondelle.web4j.database;
002
003 import hirondelle.web4j.util.Util;
004 import static hirondelle.web4j.util.Consts.NEW_LINE;
005
006 /**
007 Dynamic SQL statement fragment (usually WHERE and/or ORDER BY clauses) created in code, whose content is checked by this class
008 for <a href='http://en.wikipedia.org/wiki/SQL_injection'>SQL Injection attacks</a>.
009
010 <P>This class is intended for implementing filter/sort operations, where the criteria are built dynamically in code, according
011 to what the user has entered in a form.
012 <b>This class exists since enumerating all possible SQL statements in an <tt>.sql</tt> file,
013 corresponding to all possible combinations of filter/sort criteria that the user might enter in a form, is often impractical,
014 since the number of combinations can often become quite large.</b>
015
016 <P>This class is used to dynamically create a <i>validated</i> SQL fragment (usually a <tt>WHERE</tt> and/or an <tt>ORDER BY</tt>
017 clause), which is then <em>appended</em> to a base SQL statement already defined (as usual), in your <tt>.sql</tt> file.
018 Specifically, the return value of {@link #toString} is appended.
019 The framework then passes the resulting text to {@link java.sql.PreparedStatement}.
020 If needed, <tt>GROUP BY</tt> and <tt>HAVING</tt> clauses can be included as well.
021
022 <h3>Entries in .sql Files</h3>
023 The return value of {@link #toString()} is appended to an existing entry in an <tt>.sql</tt> file.
024 That entry can take several forms.
025 The general idea is that the .sql file contains a valid SQL statement which :
026 <ul>
027 <li>can be precompiled by WEB4J upon startup, if desired.
028 <li>contains all <i>static</i> elements of the final SQL statement, while this class is used to create the <i>dynamic</i>
029 part of the SQL statement.
030 </ul>
031
032 <P>Entries in an <tt>.sql</tt> file that are used with this class can take these forms :
033 <PRE>SELECT a,b,c
034 FROM d JOIN e on ...
035 <em>[WHERE / ORDER BY added dynamically here]</em></PRE>
036 or
037 <PRE>SELECT a,b,c
038 FROM d JOIN e on ...
039 WHERE f=g <em>[static criteria only]</em>
040 <em>[more WHERE criteria, and ORDER BY, added dynamically here]</em></PRE>
041
042 <P>You are encouraged implement joins between tables using the <tt>JOIN</tt> syntax in your <tt>.sql</tt> file entry.
043 The alternative is to implement joins using expressions in the <tt>WHERE</tt> clause.
044 This isn't desirable, since it mixes up two distinct items - joins and actual criteria.
045 Using <tt>JOIN</tt> allows these items to remain separate and distinct.
046
047
048 <h3>SQL Injection</h3>
049 <P>When creating SQL statements in code, there's a risk of
050 <a href='http://en.wikipedia.org/wiki/SQL_injection'>SQL Injection attacks</a>.
051 <i>When used correctly with '?' placeholders</i>, the {@link java.sql.PreparedStatement} class will protect against all
052 SQL injection attacks. However, <i>if used incorrectly</i>, by placing literal values where '?' placeholders should appear, the String passed to
053 <tt>PreparedStatement</tt> is still subject to SQL Injection attacks.
054
055 <P>An inexperienced or inattentive programmer needs protection from such errors.
056 This class provides some protection for such mistakes. Its {@link #toString()} method validates the SQL fragment
057 against SQL Injection flaws, by checking that a '?' appears in every place where it should.
058
059
060 <h3>Defects of this Class</h3>
061 The implmentation of this class is imperfect. It uses some relatively simple regular expressions, not a comprehensive
062 SQL parser. (Given the variations of SQL implementations from the ANSI standard, even a standards-compliant
063 SQL parser might still fail to correctly report all possible SQL Injection flaws.)
064
065 <P>In spite of the defects listed below, this class is still useful, since it will still prevent the programmer
066 from many simple careless errors - which is the intent of this class.
067
068 <P><b>Extra Spaces</b><br>
069 The following criteria will be falsely reported as having a SQL Injection flaw :
070 <pre>where x = some( select blah from whatever)
071 where x = to_date( ?)
072 where x IN ( ?)
073 where x IN (?, ?)</pre>
074
075 The workaround is simply to remove the 'extra' space, like so :
076 <pre>where x = some(select blah from whatever)
077 where x = to_date(?)
078 where x IN (?)
079 where x IN (?,?)</pre>
080
081 <P><b>Special Function Parameters</b><br>
082 Function calls that don't have a '?' immediately after the opening parenthesis are falsely reported by this class as SQL Injection flaws.
083 Example :
084 <PRE>
085 where x = to_date(interval ?)
086 where x = FROM_TZ(TIMESTAMP '..', ...)
087 where x = TO_CHAR(TO_DATE(...))
088 where x = TO_CHAR(SYSDATE,...)
089 where x = TZ_OFFSET(SESSIONTIMEZONE)
090 </PRE>
091
092 <P>There is no workaround for this issue.
093
094
095 <h3>Changing the Regular Behavior of This Class</h3>
096 Given the above mentioned defects, there are two ways to change the behavior of this class :
097 <ul>
098 <li>override the {@link #sqlInjectionRiskFor(String)} method.
099 <li>turn off checking for SQL Injection altogether, by calling {@link #turnOffCheckingForSqlInjection()}.
100 <b>This should be done only as a last resort, and only if you have confirmed that your
101 dynamically constructed SQL is not open to SQL Injection flaws</b>.
102 </ul>
103
104 <h3>See Also</h3>
105 Other items closely related to this class are :
106 <ul>
107 <li> {@link hirondelle.web4j.action.ActionImpl#getOrderBy(hirondelle.web4j.request.RequestParameter,hirondelle.web4j.request.RequestParameter, String)} -
108 convenience method for constructing an <tt>ORDER BY</tt> clause from request parameters.
109 <li> {@link hirondelle.web4j.database.Db#search(Class, SqlId, DynamicCriteria, Object[])}
110 <li> the {@link hirondelle.web4j.database.Report} class.
111 </ul>
112
113 <h3>Constants</h3>
114 The {@link #WHERE}, {@link #AND}, and other constants are included in this class as a simple
115 convenience. Note that each value includes a leading a trailing space, to avoid trivial spacing errors.
116 */
117 public class DynamicCriteria {
118
119 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause.*/
120 public static final String WHERE = " WHERE ";
121
122 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
123 public static final String AND = " AND ";
124
125 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
126 public static final String OR = " OR ";
127
128 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
129 public static final String ORDER_BY = " ORDER BY ";
130
131 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
132 public static final String ASC = " ASC ";
133
134 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
135 public static final String DESC = " DESC ";
136
137 /**
138 Represents the absence of any criteria. The value of this item is simply <tt>null</tt>.
139 <P>If a method allows a <tt>null</tt> object to indicate the absence of any criteria,
140 then it is recommended that this reference be used instead of <tt>null</tt>.
141 */
142 public static final DynamicCriteria NONE = null;
143
144 /**
145 Constructor.
146 <P>This constructor will slightly modify the given parameter: it will trim it, and prepend a new line to the result.
147 @param aSqlFragment must have content; it will be trimmed by this method.
148 */
149 public DynamicCriteria(String aSqlFragment){
150 if( ! Util.textHasContent(aSqlFragment) ){
151 throw new IllegalArgumentException("The SQL fragment has no content.");
152 }
153 fSqlFragment = NEW_LINE + aSqlFragment.trim();
154 }
155
156
157 /** Convenience constructor, forwards to {@link #DynamicCriteria(String)}. */
158 public DynamicCriteria(StringBuilder aSqlFragment){
159 this(aSqlFragment.toString());
160 }
161
162 /**
163 <b>Template</b> method which returns the String passed to the constructor, after validating it for SQL Injection flaws.
164
165 <P>The returned SQL fragment is not a complete SQL statement, and is appended by the framework
166 to an existing entry in an <tt>.sql</tt> file.
167 <P>This method calls {@link #sqlInjectionRiskFor(String)}, which is an overridable method. If that method returns a
168 non-empty value, then a runtime exception is thrown.
169 <P>If {@link #turnOffCheckingForSqlInjection()} has been called, then no checking for SQL Injection flaws is performed.
170 See class comment.
171 */
172 @Override final public String toString(){
173 if( fDoChecking ){
174 String sqlInjectionRisk = sqlInjectionRiskFor(fSqlFragment);
175 if( Util.textHasContent(sqlInjectionRisk) ){
176 throw new IllegalArgumentException(sqlInjectionRisk);
177 }
178 }
179 return fSqlFragment;
180 }
181
182 /**
183 Force this class to not perform any validation on the SQL fragment.
184
185 <P><b>Warning - this method should be called only if you are certain that your dynamic SQL always
186 uses '?' placeholders where it should, and doesn't place literal data from user input directly into the SQL fragment.</b>
187 <P>See class comment; consider overriding {@link #sqlInjectionRiskFor(String)} instead of calling this method.
188 */
189 public final void turnOffCheckingForSqlInjection(){
190 fDoChecking = false;
191 }
192
193 /**
194 Validate the SQL fragment.
195
196 <P>Return text with content only if the SQL fragment shows a risk of SQL injection.
197 The returned text describes the nature of the error. If there's no error, then an empty String, or null, is returned.
198 Some implementations may have a simple 'there is an error' message, while others may be more detailed, and
199 provide the exact position in the SQL fragment of the problems found.
200
201 <P>This method is overridable. Subclasses may supply their own validation, if desired. <b>Please exercise caution
202 when overriding this method.</b> See class comment.
203
204 <P>The default implementation searches for the text appearing after these operators (text is delimited by a blank space) :
205 <PRE>(=|<>|!=|<=|>=|<|>| IN | Like | Between )</PRE>
206
207 The text is then examined. If the text doesn't match any of the following allowed forms,
208 then it's flagged as an SQL Injection flaw:
209
210 <P>Fixed text :<PRE>?, ?), (?), (SELECT, ALL(SELECT, SOME(SELECT, ANY(SELECT, EXISTS(SELECT</PRE>
211 Function calls of the following forms : <pre>to_date(?), my_function(?,...), blah(? ...)</pre>
212 IN parameters : <pre>IN(?,?,...,?)</pre>
213 */
214 protected String sqlInjectionRiskFor(String aSqlFragment){
215 return defaultSqlInjectionRiskFor(aSqlFragment);
216 }
217
218 // PRIVATE
219
220 private String fSqlFragment = "";
221 private boolean fDoChecking = true;
222 private static final String NO_SQL_INJECTION_RISK_FOUND = "";
223
224 private String defaultSqlInjectionRiskFor(String aSqlFragment){
225 String result = NO_SQL_INJECTION_RISK_FOUND;
226 if(hasSqlInjectionFlaw(aSqlFragment)) {
227 result =
228 "SQL Injection flaw detected. Replace literals replace with '?'. Remove extraneous spaces. See javadoc for "
229 + this.getClass().getSimpleName() +
230 " for more information." + Util.quote(aSqlFragment)
231 ;
232 }
233 return result;
234 }
235
236 private boolean hasSqlInjectionFlaw(String aSqlFragment){
237 SqlInjectionDetector sqlInjection = new SqlInjectionDetector();
238 return sqlInjection.hasFlaw(aSqlFragment);
239 }
240 }