2
Copyright (C) 2002-2007 MySQL AB
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.
8
There are special exceptions to the terms and conditions of the GPL
9
as it is applied to this software. View the full text of the
10
exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
11
software distribution.
13
This program is distributed in the hope that it will be useful,
14
but WITHOUT ANY WARRANTY; without even the implied warranty of
15
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
GNU General Public License for more details.
18
You should have received a copy of the GNU General Public License
19
along with this program; if not, write to the Free Software
20
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27
* EscapeProcessor performs all escape code processing as outlined in the JDBC
30
package com.mysql.jdbc;
32
import java.sql.SQLException;
34
import java.sql.Timestamp;
36
import java.util.Calendar;
37
import java.util.Collections;
38
import java.util.GregorianCalendar;
39
import java.util.HashMap;
40
import java.util.Locale;
42
import java.util.StringTokenizer;
43
import java.util.TimeZone;
45
class EscapeProcessor {
46
private static Map JDBC_CONVERT_TO_MYSQL_TYPE_MAP;
48
private static Map JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP;
51
Map tempMap = new HashMap();
53
tempMap.put("BIGINT", "0 + ?");
54
tempMap.put("BINARY", "BINARY");
55
tempMap.put("BIT", "0 + ?");
56
tempMap.put("CHAR", "CHAR");
57
tempMap.put("DATE", "DATE");
58
tempMap.put("DECIMAL", "0.0 + ?");
59
tempMap.put("DOUBLE", "0.0 + ?");
60
tempMap.put("FLOAT", "0.0 + ?");
61
tempMap.put("INTEGER", "0 + ?");
62
tempMap.put("LONGVARBINARY", "BINARY");
63
tempMap.put("LONGVARCHAR", "CONCAT(?)");
64
tempMap.put("REAL", "0.0 + ?");
65
tempMap.put("SMALLINT", "CONCAT(?)");
66
tempMap.put("TIME", "TIME");
67
tempMap.put("TIMESTAMP", "DATETIME");
68
tempMap.put("TINYINT", "CONCAT(?)");
69
tempMap.put("VARBINARY", "BINARY");
70
tempMap.put("VARCHAR", "CONCAT(?)");
72
JDBC_CONVERT_TO_MYSQL_TYPE_MAP = Collections.unmodifiableMap(tempMap);
74
tempMap = new HashMap(JDBC_CONVERT_TO_MYSQL_TYPE_MAP);
76
tempMap.put("BINARY", "CONCAT(?)");
77
tempMap.put("CHAR", "CONCAT(?)");
78
tempMap.remove("DATE");
79
tempMap.put("LONGVARBINARY", "CONCAT(?)");
80
tempMap.remove("TIME");
81
tempMap.remove("TIMESTAMP");
82
tempMap.put("VARBINARY", "CONCAT(?)");
84
JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP = Collections
85
.unmodifiableMap(tempMap);
90
* Escape process one string
93
* the SQL to escape process.
95
* @return the SQL after it has been escape processed.
97
* @throws java.sql.SQLException
99
* @throws SQLException
102
public static final Object escapeSQL(String sql,
103
boolean serverSupportsConvertFn,
104
Connection conn) throws java.sql.SQLException {
105
boolean replaceEscapeSequence = false;
106
String escapeSequence = null;
113
* Short circuit this code if we don't have a matching pair of "{}". -
114
* Suggested by Ryan Gustafason
116
int beginBrace = sql.indexOf('{');
117
int nextEndBrace = (beginBrace == -1) ? (-1) : sql.indexOf('}',
120
if (nextEndBrace == -1) {
124
StringBuffer newSql = new StringBuffer();
126
EscapeTokenizer escapeTokenizer = new EscapeTokenizer(sql);
128
byte usesVariables = Statement.USES_VARIABLES_FALSE;
129
boolean callingStoredFunction = false;
131
while (escapeTokenizer.hasMoreTokens()) {
132
String token = escapeTokenizer.nextToken();
134
if (token.length() != 0) {
135
if (token.charAt(0) == '{') { // It's an escape code
137
if (!token.endsWith("}")) {
138
throw SQLError.createSQLException("Not a valid escape sequence: "
142
if (token.length() > 2) {
143
int nestedBrace = token.indexOf('{', 2);
145
if (nestedBrace != -1) {
146
StringBuffer buf = new StringBuffer(token
149
Object remainingResults = escapeSQL(token
150
.substring(1, token.length() - 1),
151
serverSupportsConvertFn, conn);
153
String remaining = null;
155
if (remainingResults instanceof String) {
156
remaining = (String) remainingResults;
158
remaining = ((EscapeProcessorResult) remainingResults).escapedSql;
160
if (usesVariables != Statement.USES_VARIABLES_TRUE) {
161
usesVariables = ((EscapeProcessorResult) remainingResults).usesVariables;
165
buf.append(remaining);
169
token = buf.toString();
173
// nested escape code
174
// Compare to tokens with _no_ whitespace
175
String collapsedToken = removeWhitespace(token);
178
* Process the escape code
180
if (StringUtils.startsWithIgnoreCase(collapsedToken,
183
StringTokenizer st = new StringTokenizer(token,
185
st.nextToken(); // eat the "escape" token
186
escapeSequence = st.nextToken();
188
if (escapeSequence.length() < 3) {
189
newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
193
escapeSequence = escapeSequence.substring(1,
194
escapeSequence.length() - 1);
195
replaceEscapeSequence = true;
197
} catch (java.util.NoSuchElementException e) {
198
newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
200
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
202
int startPos = token.toLowerCase().indexOf("fn ") + 3;
203
int endPos = token.length() - 1; // no }
205
String fnToken = token.substring(startPos, endPos);
207
// We need to handle 'convert' by ourselves
209
if (StringUtils.startsWithIgnoreCaseAndWs(fnToken,
211
newSql.append(processConvertToken(fnToken,
212
serverSupportsConvertFn));
214
// just pass functions right to the DB
215
newSql.append(fnToken);
217
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
219
int startPos = token.indexOf('\'') + 1;
220
int endPos = token.lastIndexOf('\''); // no }
222
if ((startPos == -1) || (endPos == -1)) {
223
newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
226
String argument = token.substring(startPos, endPos);
229
StringTokenizer st = new StringTokenizer(argument,
231
String year4 = st.nextToken();
232
String month2 = st.nextToken();
233
String day2 = st.nextToken();
234
String dateString = "'" + year4 + "-" + month2
236
newSql.append(dateString);
237
} catch (java.util.NoSuchElementException e) {
238
throw SQLError.createSQLException(
239
"Syntax error for DATE escape sequence '"
240
+ argument + "'", "42000");
243
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
245
int startPos = token.indexOf('\'') + 1;
246
int endPos = token.lastIndexOf('\''); // no }
248
if ((startPos == -1) || (endPos == -1)) {
249
newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
252
String argument = token.substring(startPos, endPos);
255
StringTokenizer st = new StringTokenizer(argument,
257
String year4 = st.nextToken();
258
String month2 = st.nextToken();
259
String day2 = st.nextToken();
260
String hour = st.nextToken();
261
String minute = st.nextToken();
262
String second = st.nextToken();
265
* For now, we get the fractional seconds part, but
266
* we don't use it, as MySQL doesn't support it in
267
* it's TIMESTAMP data type
269
* String fractionalSecond = "";
271
* if (st.hasMoreTokens()) { fractionalSecond =
275
* Use the full format because number format will
276
* not work for "between" clauses.
280
* You can specify DATETIME, DATE and TIMESTAMP
281
* values using any of a common set of formats:
283
* As a string in either 'YYYY-MM-DD HH:MM:SS' or
284
* 'YY-MM-DD HH:MM:SS' format.
286
* Thanks to Craig Longman for pointing out this bug
288
if (!conn.getUseTimezone() && !conn.getUseJDBCCompliantTimezoneShift()) {
289
newSql.append("'").append(year4).append("-")
290
.append(month2).append("-").append(day2)
291
.append(" ").append(hour).append(":")
292
.append(minute).append(":").append(second)
295
Calendar sessionCalendar;
298
sessionCalendar = conn.getCalendarInstanceForSessionOrNew();
300
sessionCalendar = new GregorianCalendar();
301
sessionCalendar.setTimeZone(TimeZone.getTimeZone("GMT"));
305
int year4Int = Integer.parseInt(year4);
306
int month2Int = Integer.parseInt(month2);
307
int day2Int = Integer.parseInt(day2);
308
int hourInt = Integer.parseInt(hour);
309
int minuteInt = Integer.parseInt(minute);
310
int secondInt = Integer.parseInt(second);
312
synchronized (sessionCalendar) {
313
boolean useGmtMillis = conn.getUseGmtMillisForDatetimes();
315
Timestamp toBeAdjusted = TimeUtil.fastTimestampCreate(useGmtMillis,
316
useGmtMillis ? Calendar.getInstance(TimeZone.getTimeZone("GMT")): null,
326
Timestamp inServerTimezone = TimeUtil.changeTimezone(
331
sessionCalendar.getTimeZone(),
332
conn.getServerTimezoneTZ(),
338
String timezoneLiteral = inServerTimezone.toString();
340
int indexOfDot = timezoneLiteral.indexOf(".");
342
if (indexOfDot != -1) {
343
timezoneLiteral = timezoneLiteral.substring(0, indexOfDot);
346
newSql.append(timezoneLiteral);
352
} catch (NumberFormatException nfe) {
353
throw SQLError.createSQLException("Syntax error in TIMESTAMP escape sequence '"
355
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
358
} catch (java.util.NoSuchElementException e) {
359
throw SQLError.createSQLException(
360
"Syntax error for TIMESTAMP escape sequence '"
361
+ argument + "'", "42000");
364
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
366
int startPos = token.indexOf('\'') + 1;
367
int endPos = token.lastIndexOf('\''); // no }
369
if ((startPos == -1) || (endPos == -1)) {
370
newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
373
String argument = token.substring(startPos, endPos);
376
StringTokenizer st = new StringTokenizer(argument,
378
String hour = st.nextToken();
379
String minute = st.nextToken();
380
String second = st.nextToken();
382
if (!conn.getUseTimezone()) {
383
String timeString = "'" + hour + ":" + minute + ":"
385
newSql.append(timeString);
387
Calendar sessionCalendar = null;
390
sessionCalendar = conn.getCalendarInstanceForSessionOrNew();
392
sessionCalendar = new GregorianCalendar();
396
int hourInt = Integer.parseInt(hour);
397
int minuteInt = Integer.parseInt(minute);
398
int secondInt = Integer.parseInt(second);
400
synchronized (sessionCalendar) {
401
Time toBeAdjusted = TimeUtil.fastTimeCreate(
407
Time inServerTimezone = TimeUtil.changeTimezone(
412
sessionCalendar.getTimeZone(),
413
conn.getServerTimezoneTZ(),
417
newSql.append(inServerTimezone.toString());
421
} catch (NumberFormatException nfe) {
422
throw SQLError.createSQLException("Syntax error in TIMESTAMP escape sequence '"
424
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
427
} catch (java.util.NoSuchElementException e) {
428
throw SQLError.createSQLException(
429
"Syntax error for escape sequence '"
430
+ argument + "'", "42000");
433
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
435
|| StringUtils.startsWithIgnoreCase(collapsedToken,
438
int startPos = StringUtils.indexOfIgnoreCase(token,
440
int endPos = token.length() - 1;
442
if (StringUtils.startsWithIgnoreCase(collapsedToken,
444
callingStoredFunction = true;
445
newSql.append("SELECT ");
446
newSql.append(token.substring(startPos, endPos));
448
callingStoredFunction = false;
449
newSql.append("CALL ");
450
newSql.append(token.substring(startPos, endPos));
453
for (int i = endPos - 1; i >= startPos; i--) {
454
char c = token.charAt(i);
456
if (Character.isWhitespace(c)) {
461
newSql.append("()"); // handle no-parenthesis no-arg call not supported
467
} else if (StringUtils.startsWithIgnoreCase(collapsedToken,
469
// MySQL already handles this escape sequence
470
// because of ODBC. Cool.
471
newSql.append(token);
474
newSql.append(token); // it's just part of the query
479
String escapedSql = newSql.toString();
482
// FIXME: Let MySQL do this, however requires
483
// lightweight parsing of statement
485
if (replaceEscapeSequence) {
486
String currentSql = escapedSql;
488
while (currentSql.indexOf(escapeSequence) != -1) {
489
int escapePos = currentSql.indexOf(escapeSequence);
490
String lhs = currentSql.substring(0, escapePos);
491
String rhs = currentSql.substring(escapePos + 1, currentSql
493
currentSql = lhs + "\\" + rhs;
496
escapedSql = currentSql;
499
EscapeProcessorResult epr = new EscapeProcessorResult();
500
epr.escapedSql = escapedSql;
501
epr.callingStoredFunction = callingStoredFunction;
503
if (usesVariables != Statement.USES_VARIABLES_TRUE) {
504
if (escapeTokenizer.sawVariableUse()) {
505
epr.usesVariables = Statement.USES_VARIABLES_TRUE;
507
epr.usesVariables = Statement.USES_VARIABLES_FALSE;
515
* Re-writes {fn convert (expr, type)} as cast(expr AS type)
517
* @param functionToken
519
* @throws SQLException
521
private static String processConvertToken(String functionToken,
522
boolean serverSupportsConvertFn) throws SQLException {
523
// The JDBC spec requires these types:
544
// MySQL supports these types:
551
// UNSIGNED (integer)
554
int firstIndexOfParen = functionToken.indexOf("(");
556
if (firstIndexOfParen == -1) {
557
throw SQLError.createSQLException(
558
"Syntax error while processing {fn convert (... , ...)} token, missing opening parenthesis in token '"
559
+ functionToken + "'.",
560
SQLError.SQL_STATE_SYNTAX_ERROR);
563
int tokenLength = functionToken.length();
565
int indexOfComma = functionToken.lastIndexOf(",");
567
if (indexOfComma == -1) {
568
throw SQLError.createSQLException(
569
"Syntax error while processing {fn convert (... , ...)} token, missing comma in token '"
570
+ functionToken + "'.",
571
SQLError.SQL_STATE_SYNTAX_ERROR);
574
int indexOfCloseParen = functionToken.indexOf(')', indexOfComma);
576
if (indexOfCloseParen == -1) {
577
throw SQLError.createSQLException(
578
"Syntax error while processing {fn convert (... , ...)} token, missing closing parenthesis in token '"
579
+ functionToken + "'.",
580
SQLError.SQL_STATE_SYNTAX_ERROR);
584
String expression = functionToken.substring(firstIndexOfParen + 1,
586
String type = functionToken.substring(indexOfComma + 1,
589
String newType = null;
591
String trimmedType = type.trim();
593
if (StringUtils.startsWithIgnoreCase(trimmedType, "SQL_")) {
594
trimmedType = trimmedType.substring(4, trimmedType.length());
597
if (serverSupportsConvertFn) {
598
newType = (String) JDBC_CONVERT_TO_MYSQL_TYPE_MAP.get(trimmedType
599
.toUpperCase(Locale.ENGLISH));
601
newType = (String) JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP
602
.get(trimmedType.toUpperCase(Locale.ENGLISH));
604
// We need a 'special' check here to give a better error message. If
606
// block, the version of MySQL we're connected to doesn't support
608
// so we can't re-write some data type conversions
609
// (date,time,timestamp, datetime)
611
if (newType == null) {
612
throw SQLError.createSQLException(
613
"Can't find conversion re-write for type '"
615
+ "' that is applicable for this server version while processing escape tokens.",
616
SQLError.SQL_STATE_GENERAL_ERROR);
620
if (newType == null) {
621
throw SQLError.createSQLException("Unsupported conversion type '"
622
+ type.trim() + "' found while processing escape token.",
623
SQLError.SQL_STATE_GENERAL_ERROR);
626
int replaceIndex = newType.indexOf("?");
628
if (replaceIndex != -1) {
629
StringBuffer convertRewrite = new StringBuffer(newType.substring(0,
631
convertRewrite.append(expression);
632
convertRewrite.append(newType.substring(replaceIndex + 1, newType
635
return convertRewrite.toString();
638
StringBuffer castRewrite = new StringBuffer("CAST(");
639
castRewrite.append(expression);
640
castRewrite.append(" AS ");
641
castRewrite.append(newType);
642
castRewrite.append(")");
644
return castRewrite.toString();
649
* Removes all whitespace from the given String. We use this to make escape
650
* token comparison white-space ignorant.
653
* the string to remove the whitespace from
655
* @return a string with _no_ whitespace.
657
private static String removeWhitespace(String toCollapse) {
658
if (toCollapse == null) {
662
int length = toCollapse.length();
664
StringBuffer collapsed = new StringBuffer(length);
666
for (int i = 0; i < length; i++) {
667
char c = toCollapse.charAt(i);
669
if (!Character.isWhitespace(c)) {
674
return collapsed.toString();