EscapeProcessor


1   /*
2      Copyright (C) 2002 MySQL AB
3   
4         This program is free software; you can redistribute it and/or modify
5         it under the terms of the GNU General Public License as published by
6         the Free Software Foundation; either version 2 of the License, or
7         (at your option) any later version.
8   
9         This program is distributed in the hope that it will be useful,
10        but WITHOUT ANY WARRANTY; without even the implied warranty of
11        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12        GNU General Public License for more details.
13  
14        You should have received a copy of the GNU General Public License
15        along with this program; if not, write to the Free Software
16        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
17  
18   */
19  
20  /**
21   * EscapeProcessor performs all escape code processing as outlined
22   * in the JDBC spec by JavaSoft.
23   *
24   * @author Mark Matthews
25   * @version $Id: EscapeProcessor.java,v 1.9.2.8 2003/12/24 05:16:25 mmatthew Exp $
26   */
27  package com.mysql.jdbc;
28  
29  import java.sql.SQLException  ;
30  
31  import java.util.Collections  ;
32  import java.util.HashMap  ;
33  import java.util.Map  ;
34  import java.util.StringTokenizer  ;
35  
36  
37  class EscapeProcessor {
38      /**
39       * Escape process one string
40       *
41       * @param SQL the SQL to escape process.
42       * @param serverSupportsConvertFn does the server support CONVERT() or CAST()?
43       * 
44       * @return the SQL after it has been escape processed.
45       */
46      public static final String   escapeSQL(String   sql, boolean serverSupportsConvertFn)
47          throws java.sql.SQLException   {
48          boolean replaceEscapeSequence = false;
49          String   escapeSequence = null;
50          StringBuffer   newSql = new StringBuffer  ();
51  
52          if (sql == null) {
53              return null;
54          }
55  
56          /*
57           * Short circuit this code if we don't have a matching pair of
58           * "{}". - Suggested by Ryan Gustafason
59           */
60          int beginBrace = sql.indexOf('{');
61          int nextEndBrace = (beginBrace == -1) ? (-1)
62                                                : sql.indexOf('}', beginBrace);
63  
64          if (nextEndBrace == -1) {
65              return sql;
66          }
67  
68          EscapeTokenizer escapeTokenizer = new EscapeTokenizer(sql);
69  
70          while (escapeTokenizer.hasMoreTokens()) {
71              String   token = escapeTokenizer.nextToken();
72  
73              if (token.startsWith("{")) { // It's an escape code
74  
75                  if (!token.endsWith("}")) {
76                      throw new java.sql.SQLException  (
77                          "Not a valid escape sequence: " + token);
78                  }
79  
80                  if (token.length() > 2) {
81                      int nestedBrace = token.indexOf('{', 2);
82  
83                      if (nestedBrace != -1) {
84                          StringBuffer   buf = new StringBuffer  (token.substring(0, 1));
85  
86                          String   remaining = escapeSQL(token.substring(1,
87                                      token.length() - 1), serverSupportsConvertFn);
88  
89                          buf.append(remaining);
90  
91                          buf.append('}');
92  
93                          token = buf.toString();
94                      }
95                  }
96                   // nested escape code
97  
98                  // Compare to tokens with _no_ whitespace
99                  String   collapsedToken = removeWhitespace(token);
100                 
101                 /*
102                  * Process the escape code
103                  */
104                 if (StringUtils.startsWithIgnoreCase(collapsedToken, "{escape")) {
105                     try {
106                         StringTokenizer   st = new StringTokenizer  (token, " '");
107                         st.nextToken(); // eat the "escape" token
108                         escapeSequence = st.nextToken();
109 
110                         if (escapeSequence.length() < 3) {
111                             throw new java.sql.SQLException  (
112                                 "Syntax error for escape sequence '" + token
113                                 + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
114                         }
115 
116                         escapeSequence = escapeSequence.substring(1,
117                                 escapeSequence.length() - 1);
118                         replaceEscapeSequence = true;
119                     } catch (java.util.NoSuchElementException   e) {
120                         throw new java.sql.SQLException  (
121                             "Syntax error for escape sequence '" + token + "'",
122                             SQLError.SQL_STATE_SYNTAX_ERROR);
123                     }
124                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{fn")) {
125                     
126                     
127                     
128                     int startPos = token.toLowerCase().indexOf("fn ") + 3;
129                     int endPos = token.length() - 1; // no }
130                     
131                     String   fnToken = token.substring(startPos, endPos);
132                     
133                     // We need to handle 'convert' by ourselves
134                     
135                     if (StringUtils.startsWithIgnoreCaseAndWs(fnToken, "convert")) {
136                         newSql.append(processConvertToken(fnToken, serverSupportsConvertFn));
137                     } else {
138                         // just pass functions right to the DB
139                         newSql.append(fnToken);
140                     }
141                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{d")) {
142                     int startPos = token.indexOf('\'') + 1;
143                     int endPos = token.lastIndexOf('\''); // no }
144 
145                     if ((startPos == -1) || (endPos == -1)) {
146                         throw new java.sql.SQLException  (
147                             "Syntax error for DATE escape sequence '" + token
148                             + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
149                     }
150 
151                     String   argument = token.substring(startPos, endPos);
152 
153                     try {
154                         StringTokenizer   st = new StringTokenizer  (argument, " -");
155                         String   year4 = st.nextToken();
156                         String   month2 = st.nextToken();
157                         String   day2 = st.nextToken();
158                         String   dateString = "'" + year4 + "-" + month2 + "-"
159                             + day2 + "'";
160                         newSql.append(dateString);
161                     } catch (java.util.NoSuchElementException   e) {
162                         throw new java.sql.SQLException  (
163                             "Syntax error for DATE escape sequence '"
164                             + argument + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
165                     }
166                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{ts")) {
167                     int startPos = token.indexOf('\'') + 1;
168                     int endPos = token.lastIndexOf('\''); // no }
169 
170                     if ((startPos == -1) || (endPos == -1)) {
171                         throw new java.sql.SQLException  (
172                             "Syntax error for TIMESTAMP escape sequence '"
173                             + token + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
174                     }
175 
176                     String   argument = token.substring(startPos, endPos);
177 
178                     try {
179                         StringTokenizer   st = new StringTokenizer  (argument,
180                                 " .-:");
181                         String   year4 = st.nextToken();
182                         String   month2 = st.nextToken();
183                         String   day2 = st.nextToken();
184                         String   hour = st.nextToken();
185                         String   minute = st.nextToken();
186                         String   second = st.nextToken();
187 
188                         /*
189                          * For now, we get the fractional seconds
190                          * part, but we don't use it, as MySQL doesn't
191                          * support it in it's TIMESTAMP data type
192                          *
193                         String fractionalSecond = "";
194 
195                         if (st.hasMoreTokens()) {
196                             fractionalSecond = st.nextToken();
197                         }
198                         */
199                         /*
200                          * Use the full format because number format
201                          * will not work for "between" clauses.
202                          *
203                          * Ref. Mysql Docs
204                          *
205                          * You can specify DATETIME, DATE and TIMESTAMP values
206                          * using any of a common set of formats:
207                          *
208                          * As a string in either 'YYYY-MM-DD HH:MM:SS' or
209                          * 'YY-MM-DD HH:MM:SS' format.
210                          *
211                          * Thanks to Craig Longman for pointing out this bug
212                          */
213                         newSql.append("'").append(year4).append("-")
214                               .append(month2).append("-").append(day2)
215                               .append(" ").append(hour).append(":")
216                               .append(minute).append(":").append(second).append("'");
217                     } catch (java.util.NoSuchElementException   e) {
218                         throw new java.sql.SQLException  (
219                             "Syntax error for TIMESTAMP escape sequence '"
220                             + argument + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
221                     }
222                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{t")) {
223                     int startPos = token.indexOf('\'') + 1;
224                     int endPos = token.lastIndexOf('\''); // no }
225 
226                     if ((startPos == -1) || (endPos == -1)) {
227                         throw new java.sql.SQLException  (
228                             "Syntax error for TIME escape sequence '" + token
229                             + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
230                     }
231 
232                     String   argument = token.substring(startPos, endPos);
233 
234                     try {
235                         StringTokenizer   st = new StringTokenizer  (argument, " :");
236                         String   hour = st.nextToken();
237                         String   minute = st.nextToken();
238                         String   second = st.nextToken();
239                         String   timeString = "'" + hour + ":" + minute + ":"
240                             + second + "'";
241                         newSql.append(timeString);
242                     } catch (java.util.NoSuchElementException   e) {
243                         throw new java.sql.SQLException  (
244                             "Syntax error for escape sequence '" + argument
245                             + "'", SQLError.SQL_STATE_SYNTAX_ERROR);
246                     }
247                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{call")
248                         || StringUtils.startsWithIgnoreCase(collapsedToken, "{?=call")) {
249                     throw new java.sql.SQLException  (
250                         "Stored procedures not supported: " + token, "S1C00");
251                 } else if (StringUtils.startsWithIgnoreCase(collapsedToken, "{oj")) {
252                     // MySQL already handles this escape sequence
253                     // because of ODBC. Cool.
254                     newSql.append(token);
255                 }
256             } else {
257                 newSql.append(token); // it's just part of the query
258             }
259         }
260 
261         String   escapedSql = newSql.toString();
262 
263         //
264         // FIXME: Let MySQL do this, however requires
265         //        lightweight parsing of statement
266         //
267         if (replaceEscapeSequence) {
268             String   currentSql = escapedSql;
269 
270             while (currentSql.indexOf(escapeSequence) != -1) {
271                 int escapePos = currentSql.indexOf(escapeSequence);
272                 String   lhs = currentSql.substring(0, escapePos);
273                 String   rhs = currentSql.substring(escapePos + 1,
274                         currentSql.length());
275                 currentSql = lhs + "\\" + rhs;
276             }
277 
278             escapedSql = currentSql;
279         }
280 
281         return escapedSql;
282     }
283     
284     /**
285      * Removes all whitespace from the given String. We use
286      * this to make escape token comparison white-space ignorant.
287      *  
288      * @param toCollapse the string to remove the whitespace from
289      * @return a string with _no_ whitespace.
290      */
291     private static String   removeWhitespace(String   toCollapse) {
292         if (toCollapse == null) {
293             return null;
294         }
295         
296         int length = toCollapse.length();
297         
298         StringBuffer   collapsed = new StringBuffer  (length);
299         
300         for (int i = 0; i < length; i++) {
301             char c = toCollapse.charAt(i);
302             
303             if (!Character.isWhitespace(c)) {
304                 collapsed.append(c);
305             }
306         }
307         
308         return collapsed.toString();
309     }
310     
311     private static Map   JDBC_CONVERT_TO_MYSQL_TYPE_MAP;
312     private static Map   JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP;
313     
314     static {
315         Map   tempMap = new HashMap  ();
316         
317         tempMap.put("BIGINT", "0 + ?");
318         tempMap.put("BINARY", "BINARY");
319         tempMap.put("BIT", "0 + ?");
320         tempMap.put("CHAR", "CHAR");
321         tempMap.put("DATE", "DATE");
322         tempMap.put("DECIMAL", "0.0 + ?");
323         tempMap.put("DOUBLE", "0.0 + ?");
324         tempMap.put("FLOAT", "0.0 + ?");
325         tempMap.put("INTEGER", "0 + ?");
326         tempMap.put("LONGVARBINARY", "BINARY");
327         tempMap.put("LONGVARCHAR", "CONCAT(?)");
328         tempMap.put("REAL", "0.0 + ?");
329         tempMap.put("SMALLINT", "CONCAT(?)");
330         tempMap.put("TIME", "TIME");
331         tempMap.put("TIMESTAMP", "DATETIME");
332         tempMap.put("TINYINT", "CONCAT(?)");
333         tempMap.put("VARBINARY", "BINARY");
334         tempMap.put("VARCHAR", "CONCAT(?)");
335         
336         JDBC_CONVERT_TO_MYSQL_TYPE_MAP = Collections.unmodifiableMap(tempMap);
337         
338         tempMap = new HashMap  (JDBC_CONVERT_TO_MYSQL_TYPE_MAP);
339         
340         tempMap.put("BINARY", "CONCAT(?)"); 
341         tempMap.put("CHAR", "CONCAT(?)");
342         tempMap.remove("DATE"); 
343         tempMap.put("LONGVARBINARY", "CONCAT(?)");  
344         tempMap.remove("TIME");
345         tempMap.remove("TIMESTAMP");
346         tempMap.put("VARBINARY", "CONCAT(?)");
347         
348         JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP = Collections.unmodifiableMap(tempMap);
349                 
350     }
351     
352     /**
353      * Re-writes {fn convert (expr, type)} as cast(expr AS type)
354      * @param functionToken
355      * @return
356      * @throws SQLException
357      */
358     private static String   processConvertToken(String   functionToken, boolean serverSupportsConvertFn) throws SQLException   {
359         // The JDBC spec requires these types:
360         //
361         // BIGINT
362         // BINARY
363         // BIT
364         // CHAR
365         // DATE
366         // DECIMAL
367         // DOUBLE
368         // FLOAT
369         // INTEGER
370         // LONGVARBINARY
371         // LONGVARCHAR
372         // REAL
373         // SMALLINT
374         // TIME
375         // TIMESTAMP
376         // TINYINT
377         // VARBINARY
378         // VARCHAR
379         
380         // MySQL supports these types:
381         //
382         // BINARY
383         // CHAR
384         // DATE
385         // DATETIME
386         // SIGNED (integer)
387         // UNSIGNED (integer)
388         // TIME
389         
390         int firstIndexOfParen = functionToken.indexOf("(");
391         
392         if (firstIndexOfParen == -1) {
393             throw new SQLException  ("Syntax error while processing {fn convert (... , ...)} token, missing opening parenthesis in token '" + functionToken + "'.", SQLError.SQL_STATE_SYNTAX_ERROR);
394         }
395         
396         int tokenLength = functionToken.length();
397         
398         int indexOfComma = functionToken.lastIndexOf(",");
399     
400         if (indexOfComma == -1) {
401             throw new SQLException  ("Syntax error while processing {fn convert (... , ...)} token, missing comma in token '" + functionToken + "'.", SQLError.SQL_STATE_SYNTAX_ERROR);
402         }
403  
404         int indexOfCloseParen = functionToken.indexOf(')', indexOfComma);
405         
406         if (indexOfCloseParen == -1) {
407             throw new SQLException  ("Syntax error while processing {fn convert (... , ...)} token, missing closing parenthesis in token '" + functionToken + "'.", SQLError.SQL_STATE_SYNTAX_ERROR);
408             
409         }
410     
411         String   expression = functionToken.substring(firstIndexOfParen + 1, indexOfComma);
412         String   type = functionToken.substring(indexOfComma + 1, indexOfCloseParen);
413         
414         String   newType = null;
415         
416         if (serverSupportsConvertFn) {
417             newType = (String  )JDBC_CONVERT_TO_MYSQL_TYPE_MAP.get(type.trim().toUpperCase());
418         } else {
419             newType = (String  )JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP.get(type.trim().toUpperCase());
420             
421             // We need a 'special' check here to give a better error message. If we're in this
422             // block, the version of MySQL we're connected to doesn't support CAST/CONVERT,
423             // so we can't re-write some data type conversions (date,time,timestamp, datetime)
424             
425             if (newType == null) {
426                 throw new SQLException  ("Can't find conversion re-write for type '" + type + "' that is applicable for this server version while processing escape tokens.", SQLError.SQL_STATE_GENERAL_ERROR);
427             }
428         }
429         
430         if (newType == null) {
431             throw new SQLException  ("Unsupported conversion type '" + type.trim() + "' found while processing escape token.", SQLError.SQL_STATE_GENERAL_ERROR);
432         }
433         
434         int replaceIndex = newType.indexOf("?");
435         
436         if (replaceIndex != -1) {
437             StringBuffer   convertRewrite = new StringBuffer  (newType.substring(0, replaceIndex));
438             convertRewrite.append(expression);
439             convertRewrite.append(newType.substring(replaceIndex + 1, newType.length()));
440             
441             return convertRewrite.toString();
442         } else {
443         
444             StringBuffer   castRewrite = new StringBuffer  ("CAST(");
445             castRewrite.append(expression);
446             castRewrite.append(" AS ");
447             castRewrite.append(newType);
448             castRewrite.append(")");
449         
450             return castRewrite.toString();
451         }
452     }
453 }
454
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags