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