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 created in code. The SQL statement can be either:
008 <ul>
009 <li>a complete statement
010 <li>a fragment of a statement, to be appended to the end of a static base statement
011 </ul>
012
013 <P>This class is intended for two use cases:
014 <ul>
015 <li>creating a statement entirely in code
016 <li>creating <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses dynamically, by building sort and filter criteria from
017 user input
018 </ul>
019
020 <P><b>The creation of SQL in code is dangerous.
021 You have to exercise care that your code will not be subject to
022 <a href='http://en.wikipedia.org/wiki/SQL_injection'>SQL Injection attacks</a>.
023 If you don't know what such attacks are all about, then you are in danger of creating
024 very large, dangerous security flaws in your application.</b>
025
026 <P>The main means of protecting yourself from these attacks is to ensure that the sql strings you pass to
027 this class never contain data that has come directly from the user, in an unescaped form.
028 You achieve this by <tt>parameterizing</tt> user input, and proceeding in 2 steps:
029 <ol>
030 <li>create an SQL statement that always uses a <tt>?</tt> placeholder for data entered by the user
031 <li>pass all user-entered data as parameters to the above statement
032 </ol>
033
034 The above corresponds to the correct use of a <tt>PreparedStatement</tt>.
035 After you have built your dynamic SQL, you will usually pass it to
036 {@link hirondelle.web4j.database.Db#search(Class, SqlId, DynamicSql, Object[])} to retrieve the data.
037
038 <h3>Entries in .sql Files</h3>
039 <P>The SQL string you pass to this class is always <em>appended</em> (using {@link #toString}) to
040 a (possibly-empty) base SQL statement already defined (as usual), in your <tt>.sql</tt> file.
041 That entry can take several forms. The criteria on the entry are:
042 <ul>
043 <li>it can be precompiled by WEB4J upon startup, if desired.
044 <li>it contains only <i>static</i> elements of the final SQL statement
045 </ul>
046
047 <em>It's important to note that the static base SQL can be completely empty.</em>
048 For example, the entry in your <tt>.sql file</tt> can look something like this:
049 <pre>MY_DYNAMIC_REPORT {
050 -- this sql is generated in code
051 }</pre>
052 As you can see, there's only a comment here; there's no real SQL.
053 In this case, you will need to build the entire SQL statement in code.
054 (Even though the above entry is empty, it's still necessary, since it's where you specify any
055 non-default target database name. It also ensures that the same mechanism web4j applies
056 to processing <tt>SqlId</tt> objects will remain in effect, which is useful.)
057
058 <P>You are encouraged to implement joins between tables using the <tt>JOIN</tt> syntax.
059 The alternative is to implement joins using expressions in the <tt>WHERE</tt> clause.
060 This usually isn't desirable, since it mixes up two distinct items - joins and actual criteria.
061 Using <tt>JOIN</tt> allows these items to remain separate and distinct.
062
063 <h3>See Also</h3>
064 Other items closely related to this class are :
065 <ul>
066 <li> {@link hirondelle.web4j.action.ActionImpl#getOrderBy(hirondelle.web4j.request.RequestParameter,hirondelle.web4j.request.RequestParameter, String)} -
067 convenience method for constructing an <tt>ORDER BY</tt> clause from request parameters.
068 <li> {@link hirondelle.web4j.database.Db#search(Class, SqlId, DynamicSql, Object[])}
069 <li> the {@link hirondelle.web4j.database.Report} class.
070 </ul>
071
072 <h3>Constants</h3>
073 The {@link #WHERE}, {@link #AND}, and other constants are included in this class as a simple
074 convenience. Note that each value includes a leading a trailing space, to avoid trivial spacing errors.
075
076 <P>This class is non-final, and can be overridden, if desired. The reason is that some applications may wish to try to
077 validate that the SQL passed to this class has been properly parameterized.
078 */
079 public class DynamicSql {;
080
081 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause.*/
082 public static final String WHERE = " WHERE ";
083
084 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
085 public static final String AND = " AND ";
086
087 /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
088 public static final String OR = " OR ";
089
090 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
091 public static final String ORDER_BY = " ORDER BY ";
092
093 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
094 public static final String ASC = " ASC ";
095
096 /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
097 public static final String DESC = " DESC ";
098
099 /**
100 Represents the absence of any criteria. The value of this item is simply <tt>null</tt>.
101 <P>If a method allows a <tt>null</tt> object to indicate the absence of any criteria,
102 then it is recommended that this reference be used instead of <tt>null</tt>.
103 */
104 public static final DynamicSql NONE = null;
105
106 /**
107 Constructor.
108 <P>This constructor will slightly modify the given parameter: it will trim it, and prepend a new line to the result.
109 @param aSql must have content; it will be trimmed by this method.
110 */
111 public DynamicSql(String aSql){
112 if( ! Util.textHasContent(aSql) ){
113 throw new IllegalArgumentException("The SQL text has no content.");
114 }
115 fSql = NEW_LINE + aSql.trim();
116 }
117
118
119 /** Convenience constructor, forwards to {@link #DynamicSql(String)}. */
120 public DynamicSql(StringBuilder aSql){
121 this(aSql.toString());
122 }
123
124 /**
125 Return the String passed to the constructor, trimmed.
126
127 <P>The returned value is appended by the framework to an existing (possibly empty) entry in an <tt>.sql</tt> file.
128 */
129 @Override final public String toString(){
130 return fSql;
131 }
132
133 // PRIVATE
134
135 private String fSql = "";
136
137 }