001: /*
002: Copyright (C) 2002-2007 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025:
026: /**
027: * EscapeProcessor performs all escape code processing as outlined in the JDBC
028: * spec by JavaSoft.
029: */package com.mysql.jdbc;
030:
031: import java.sql.SQLException;
032: import java.sql.Time;
033: import java.sql.Timestamp;
034:
035: import java.util.Calendar;
036: import java.util.Collections;
037: import java.util.GregorianCalendar;
038: import java.util.HashMap;
039: import java.util.Locale;
040: import java.util.Map;
041: import java.util.StringTokenizer;
042: import java.util.TimeZone;
043:
044: class EscapeProcessor {
045: private static Map JDBC_CONVERT_TO_MYSQL_TYPE_MAP;
046:
047: private static Map JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP;
048:
049: static {
050: Map tempMap = new HashMap();
051:
052: tempMap.put("BIGINT", "0 + ?");
053: tempMap.put("BINARY", "BINARY");
054: tempMap.put("BIT", "0 + ?");
055: tempMap.put("CHAR", "CHAR");
056: tempMap.put("DATE", "DATE");
057: tempMap.put("DECIMAL", "0.0 + ?");
058: tempMap.put("DOUBLE", "0.0 + ?");
059: tempMap.put("FLOAT", "0.0 + ?");
060: tempMap.put("INTEGER", "0 + ?");
061: tempMap.put("LONGVARBINARY", "BINARY");
062: tempMap.put("LONGVARCHAR", "CONCAT(?)");
063: tempMap.put("REAL", "0.0 + ?");
064: tempMap.put("SMALLINT", "CONCAT(?)");
065: tempMap.put("TIME", "TIME");
066: tempMap.put("TIMESTAMP", "DATETIME");
067: tempMap.put("TINYINT", "CONCAT(?)");
068: tempMap.put("VARBINARY", "BINARY");
069: tempMap.put("VARCHAR", "CONCAT(?)");
070:
071: JDBC_CONVERT_TO_MYSQL_TYPE_MAP = Collections
072: .unmodifiableMap(tempMap);
073:
074: tempMap = new HashMap(JDBC_CONVERT_TO_MYSQL_TYPE_MAP);
075:
076: tempMap.put("BINARY", "CONCAT(?)");
077: tempMap.put("CHAR", "CONCAT(?)");
078: tempMap.remove("DATE");
079: tempMap.put("LONGVARBINARY", "CONCAT(?)");
080: tempMap.remove("TIME");
081: tempMap.remove("TIMESTAMP");
082: tempMap.put("VARBINARY", "CONCAT(?)");
083:
084: JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP = Collections
085: .unmodifiableMap(tempMap);
086:
087: }
088:
089: /**
090: * Escape process one string
091: *
092: * @param sql
093: * the SQL to escape process.
094: *
095: * @return the SQL after it has been escape processed.
096: *
097: * @throws java.sql.SQLException
098: * DOCUMENT ME!
099: * @throws SQLException
100: * DOCUMENT ME!
101: */
102: public static final Object escapeSQL(String sql,
103: boolean serverSupportsConvertFn, ConnectionImpl conn)
104: throws java.sql.SQLException {
105: boolean replaceEscapeSequence = false;
106: String escapeSequence = null;
107:
108: if (sql == null) {
109: return null;
110: }
111:
112: /*
113: * Short circuit this code if we don't have a matching pair of "{}". -
114: * Suggested by Ryan Gustafason
115: */
116: int beginBrace = sql.indexOf('{');
117: int nextEndBrace = (beginBrace == -1) ? (-1) : sql.indexOf('}',
118: beginBrace);
119:
120: if (nextEndBrace == -1) {
121: return sql;
122: }
123:
124: StringBuffer newSql = new StringBuffer();
125:
126: EscapeTokenizer escapeTokenizer = new EscapeTokenizer(sql);
127:
128: byte usesVariables = StatementImpl.USES_VARIABLES_FALSE;
129: boolean callingStoredFunction = false;
130:
131: while (escapeTokenizer.hasMoreTokens()) {
132: String token = escapeTokenizer.nextToken();
133:
134: if (token.length() != 0) {
135: if (token.charAt(0) == '{') { // It's an escape code
136:
137: if (!token.endsWith("}")) {
138: throw SQLError
139: .createSQLException("Not a valid escape sequence: "
140: + token);
141: }
142:
143: if (token.length() > 2) {
144: int nestedBrace = token.indexOf('{', 2);
145:
146: if (nestedBrace != -1) {
147: StringBuffer buf = new StringBuffer(token
148: .substring(0, 1));
149:
150: Object remainingResults = escapeSQL(token
151: .substring(1, token.length() - 1),
152: serverSupportsConvertFn, conn);
153:
154: String remaining = null;
155:
156: if (remainingResults instanceof String) {
157: remaining = (String) remainingResults;
158: } else {
159: remaining = ((EscapeProcessorResult) remainingResults).escapedSql;
160:
161: if (usesVariables != StatementImpl.USES_VARIABLES_TRUE) {
162: usesVariables = ((EscapeProcessorResult) remainingResults).usesVariables;
163: }
164: }
165:
166: buf.append(remaining);
167:
168: buf.append('}');
169:
170: token = buf.toString();
171: }
172: }
173:
174: // nested escape code
175: // Compare to tokens with _no_ whitespace
176: String collapsedToken = removeWhitespace(token);
177:
178: /*
179: * Process the escape code
180: */
181: if (StringUtils.startsWithIgnoreCase(
182: collapsedToken, "{escape")) {
183: try {
184: StringTokenizer st = new StringTokenizer(
185: token, " '");
186: st.nextToken(); // eat the "escape" token
187: escapeSequence = st.nextToken();
188:
189: if (escapeSequence.length() < 3) {
190: newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
191: } else {
192:
193: escapeSequence = escapeSequence
194: .substring(1, escapeSequence
195: .length() - 1);
196: replaceEscapeSequence = true;
197: }
198: } catch (java.util.NoSuchElementException e) {
199: newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
200: }
201: } else if (StringUtils.startsWithIgnoreCase(
202: collapsedToken, "{fn")) {
203: int startPos = token.toLowerCase().indexOf(
204: "fn ") + 3;
205: int endPos = token.length() - 1; // no }
206:
207: String fnToken = token.substring(startPos,
208: endPos);
209:
210: // We need to handle 'convert' by ourselves
211:
212: if (StringUtils.startsWithIgnoreCaseAndWs(
213: fnToken, "convert")) {
214: newSql.append(processConvertToken(fnToken,
215: serverSupportsConvertFn));
216: } else {
217: // just pass functions right to the DB
218: newSql.append(fnToken);
219: }
220: } else if (StringUtils.startsWithIgnoreCase(
221: collapsedToken, "{d")) {
222: int startPos = token.indexOf('\'') + 1;
223: int endPos = token.lastIndexOf('\''); // no }
224:
225: if ((startPos == -1) || (endPos == -1)) {
226: newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
227: } else {
228:
229: String argument = token.substring(startPos,
230: endPos);
231:
232: try {
233: StringTokenizer st = new StringTokenizer(
234: argument, " -");
235: String year4 = st.nextToken();
236: String month2 = st.nextToken();
237: String day2 = st.nextToken();
238: String dateString = "'" + year4 + "-"
239: + month2 + "-" + day2 + "'";
240: newSql.append(dateString);
241: } catch (java.util.NoSuchElementException e) {
242: throw SQLError.createSQLException(
243: "Syntax error for DATE escape sequence '"
244: + argument + "'",
245: "42000");
246: }
247: }
248: } else if (StringUtils.startsWithIgnoreCase(
249: collapsedToken, "{ts")) {
250: int startPos = token.indexOf('\'') + 1;
251: int endPos = token.lastIndexOf('\''); // no }
252:
253: if ((startPos == -1) || (endPos == -1)) {
254: newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
255: } else {
256:
257: String argument = token.substring(startPos,
258: endPos);
259:
260: try {
261: StringTokenizer st = new StringTokenizer(
262: argument, " .-:");
263: String year4 = st.nextToken();
264: String month2 = st.nextToken();
265: String day2 = st.nextToken();
266: String hour = st.nextToken();
267: String minute = st.nextToken();
268: String second = st.nextToken();
269:
270: /*
271: * For now, we get the fractional seconds part, but
272: * we don't use it, as MySQL doesn't support it in
273: * it's TIMESTAMP data type
274: *
275: * String fractionalSecond = "";
276: *
277: * if (st.hasMoreTokens()) { fractionalSecond =
278: * st.nextToken(); }
279: */
280: /*
281: * Use the full format because number format will
282: * not work for "between" clauses.
283: *
284: * Ref. Mysql Docs
285: *
286: * You can specify DATETIME, DATE and TIMESTAMP
287: * values using any of a common set of formats:
288: *
289: * As a string in either 'YYYY-MM-DD HH:MM:SS' or
290: * 'YY-MM-DD HH:MM:SS' format.
291: *
292: * Thanks to Craig Longman for pointing out this bug
293: */
294: if (!conn.getUseTimezone()
295: && !conn
296: .getUseJDBCCompliantTimezoneShift()) {
297: newSql.append("'").append(year4)
298: .append("-").append(month2)
299: .append("-").append(day2)
300: .append(" ").append(hour)
301: .append(":").append(minute)
302: .append(":").append(second)
303: .append("'");
304: } else {
305: Calendar sessionCalendar;
306:
307: if (conn != null) {
308: sessionCalendar = conn
309: .getCalendarInstanceForSessionOrNew();
310: } else {
311: sessionCalendar = new GregorianCalendar();
312: sessionCalendar
313: .setTimeZone(TimeZone
314: .getTimeZone("GMT"));
315: }
316:
317: try {
318: int year4Int = Integer
319: .parseInt(year4);
320: int month2Int = Integer
321: .parseInt(month2);
322: int day2Int = Integer
323: .parseInt(day2);
324: int hourInt = Integer
325: .parseInt(hour);
326: int minuteInt = Integer
327: .parseInt(minute);
328: int secondInt = Integer
329: .parseInt(second);
330:
331: synchronized (sessionCalendar) {
332: boolean useGmtMillis = conn
333: .getUseGmtMillisForDatetimes();
334:
335: Timestamp toBeAdjusted = TimeUtil
336: .fastTimestampCreate(
337: useGmtMillis,
338: useGmtMillis ? Calendar
339: .getInstance(TimeZone
340: .getTimeZone("GMT"))
341: : null,
342: sessionCalendar,
343: year4Int,
344: month2Int,
345: day2Int,
346: hourInt,
347: minuteInt,
348: secondInt,
349: 0);
350:
351: Timestamp inServerTimezone = TimeUtil
352: .changeTimezone(
353: conn,
354: sessionCalendar,
355: null,
356: toBeAdjusted,
357: sessionCalendar
358: .getTimeZone(),
359: conn
360: .getServerTimezoneTZ(),
361: false);
362:
363: newSql.append("'");
364:
365: String timezoneLiteral = inServerTimezone
366: .toString();
367:
368: int indexOfDot = timezoneLiteral
369: .indexOf(".");
370:
371: if (indexOfDot != -1) {
372: timezoneLiteral = timezoneLiteral
373: .substring(0,
374: indexOfDot);
375: }
376:
377: newSql
378: .append(timezoneLiteral);
379: }
380:
381: newSql.append("'");
382:
383: } catch (NumberFormatException nfe) {
384: throw SQLError
385: .createSQLException(
386: "Syntax error in TIMESTAMP escape sequence '"
387: + token
388: + "'.",
389: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
390: }
391: }
392: } catch (java.util.NoSuchElementException e) {
393: throw SQLError.createSQLException(
394: "Syntax error for TIMESTAMP escape sequence '"
395: + argument + "'",
396: "42000");
397: }
398: }
399: } else if (StringUtils.startsWithIgnoreCase(
400: collapsedToken, "{t")) {
401: int startPos = token.indexOf('\'') + 1;
402: int endPos = token.lastIndexOf('\''); // no }
403:
404: if ((startPos == -1) || (endPos == -1)) {
405: newSql.append(token); // it's just part of the query, push possible syntax errors onto server's shoulders
406: } else {
407:
408: String argument = token.substring(startPos,
409: endPos);
410:
411: try {
412: StringTokenizer st = new StringTokenizer(
413: argument, " :");
414: String hour = st.nextToken();
415: String minute = st.nextToken();
416: String second = st.nextToken();
417:
418: if (!conn.getUseTimezone()) {
419: String timeString = "'" + hour
420: + ":" + minute + ":"
421: + second + "'";
422: newSql.append(timeString);
423: } else {
424: Calendar sessionCalendar = null;
425:
426: if (conn != null) {
427: sessionCalendar = conn
428: .getCalendarInstanceForSessionOrNew();
429: } else {
430: sessionCalendar = new GregorianCalendar();
431: }
432:
433: try {
434: int hourInt = Integer
435: .parseInt(hour);
436: int minuteInt = Integer
437: .parseInt(minute);
438: int secondInt = Integer
439: .parseInt(second);
440:
441: synchronized (sessionCalendar) {
442: Time toBeAdjusted = TimeUtil
443: .fastTimeCreate(
444: sessionCalendar,
445: hourInt,
446: minuteInt,
447: secondInt);
448:
449: Time inServerTimezone = TimeUtil
450: .changeTimezone(
451: conn,
452: sessionCalendar,
453: null,
454: toBeAdjusted,
455: sessionCalendar
456: .getTimeZone(),
457: conn
458: .getServerTimezoneTZ(),
459: false);
460:
461: newSql.append("'");
462: newSql
463: .append(inServerTimezone
464: .toString());
465: newSql.append("'");
466: }
467:
468: } catch (NumberFormatException nfe) {
469: throw SQLError
470: .createSQLException(
471: "Syntax error in TIMESTAMP escape sequence '"
472: + token
473: + "'.",
474: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
475: }
476: }
477: } catch (java.util.NoSuchElementException e) {
478: throw SQLError.createSQLException(
479: "Syntax error for escape sequence '"
480: + argument + "'",
481: "42000");
482: }
483: }
484: } else if (StringUtils.startsWithIgnoreCase(
485: collapsedToken, "{call")
486: || StringUtils.startsWithIgnoreCase(
487: collapsedToken, "{?=call")) {
488:
489: int startPos = StringUtils.indexOfIgnoreCase(
490: token, "CALL") + 5;
491: int endPos = token.length() - 1;
492:
493: if (StringUtils.startsWithIgnoreCase(
494: collapsedToken, "{?=call")) {
495: callingStoredFunction = true;
496: newSql.append("SELECT ");
497: newSql.append(token.substring(startPos,
498: endPos));
499: } else {
500: callingStoredFunction = false;
501: newSql.append("CALL ");
502: newSql.append(token.substring(startPos,
503: endPos));
504: }
505:
506: for (int i = endPos - 1; i >= startPos; i--) {
507: char c = token.charAt(i);
508:
509: if (Character.isWhitespace(c)) {
510: continue;
511: }
512:
513: if (c != ')') {
514: newSql.append("()"); // handle no-parenthesis no-arg call not supported
515: // by MySQL parser
516: }
517:
518: break;
519: }
520: } else if (StringUtils.startsWithIgnoreCase(
521: collapsedToken, "{oj")) {
522: // MySQL already handles this escape sequence
523: // because of ODBC. Cool.
524: newSql.append(token);
525: }
526: } else {
527: newSql.append(token); // it's just part of the query
528: }
529: }
530: }
531:
532: String escapedSql = newSql.toString();
533:
534: //
535: // FIXME: Let MySQL do this, however requires
536: // lightweight parsing of statement
537: //
538: if (replaceEscapeSequence) {
539: String currentSql = escapedSql;
540:
541: while (currentSql.indexOf(escapeSequence) != -1) {
542: int escapePos = currentSql.indexOf(escapeSequence);
543: String lhs = currentSql.substring(0, escapePos);
544: String rhs = currentSql.substring(escapePos + 1,
545: currentSql.length());
546: currentSql = lhs + "\\" + rhs;
547: }
548:
549: escapedSql = currentSql;
550: }
551:
552: EscapeProcessorResult epr = new EscapeProcessorResult();
553: epr.escapedSql = escapedSql;
554: epr.callingStoredFunction = callingStoredFunction;
555:
556: if (usesVariables != StatementImpl.USES_VARIABLES_TRUE) {
557: if (escapeTokenizer.sawVariableUse()) {
558: epr.usesVariables = StatementImpl.USES_VARIABLES_TRUE;
559: } else {
560: epr.usesVariables = StatementImpl.USES_VARIABLES_FALSE;
561: }
562: }
563:
564: return epr;
565: }
566:
567: /**
568: * Re-writes {fn convert (expr, type)} as cast(expr AS type)
569: *
570: * @param functionToken
571: * @return
572: * @throws SQLException
573: */
574: private static String processConvertToken(String functionToken,
575: boolean serverSupportsConvertFn) throws SQLException {
576: // The JDBC spec requires these types:
577: //
578: // BIGINT
579: // BINARY
580: // BIT
581: // CHAR
582: // DATE
583: // DECIMAL
584: // DOUBLE
585: // FLOAT
586: // INTEGER
587: // LONGVARBINARY
588: // LONGVARCHAR
589: // REAL
590: // SMALLINT
591: // TIME
592: // TIMESTAMP
593: // TINYINT
594: // VARBINARY
595: // VARCHAR
596:
597: // MySQL supports these types:
598: //
599: // BINARY
600: // CHAR
601: // DATE
602: // DATETIME
603: // SIGNED (integer)
604: // UNSIGNED (integer)
605: // TIME
606:
607: int firstIndexOfParen = functionToken.indexOf("(");
608:
609: if (firstIndexOfParen == -1) {
610: throw SQLError
611: .createSQLException(
612: "Syntax error while processing {fn convert (... , ...)} token, missing opening parenthesis in token '"
613: + functionToken + "'.",
614: SQLError.SQL_STATE_SYNTAX_ERROR);
615: }
616:
617: int tokenLength = functionToken.length();
618:
619: int indexOfComma = functionToken.lastIndexOf(",");
620:
621: if (indexOfComma == -1) {
622: throw SQLError
623: .createSQLException(
624: "Syntax error while processing {fn convert (... , ...)} token, missing comma in token '"
625: + functionToken + "'.",
626: SQLError.SQL_STATE_SYNTAX_ERROR);
627: }
628:
629: int indexOfCloseParen = functionToken
630: .indexOf(')', indexOfComma);
631:
632: if (indexOfCloseParen == -1) {
633: throw SQLError
634: .createSQLException(
635: "Syntax error while processing {fn convert (... , ...)} token, missing closing parenthesis in token '"
636: + functionToken + "'.",
637: SQLError.SQL_STATE_SYNTAX_ERROR);
638:
639: }
640:
641: String expression = functionToken.substring(
642: firstIndexOfParen + 1, indexOfComma);
643: String type = functionToken.substring(indexOfComma + 1,
644: indexOfCloseParen);
645:
646: String newType = null;
647:
648: String trimmedType = type.trim();
649:
650: if (StringUtils.startsWithIgnoreCase(trimmedType, "SQL_")) {
651: trimmedType = trimmedType
652: .substring(4, trimmedType.length());
653: }
654:
655: if (serverSupportsConvertFn) {
656: newType = (String) JDBC_CONVERT_TO_MYSQL_TYPE_MAP
657: .get(trimmedType.toUpperCase(Locale.ENGLISH));
658: } else {
659: newType = (String) JDBC_NO_CONVERT_TO_MYSQL_EXPRESSION_MAP
660: .get(trimmedType.toUpperCase(Locale.ENGLISH));
661:
662: // We need a 'special' check here to give a better error message. If
663: // we're in this
664: // block, the version of MySQL we're connected to doesn't support
665: // CAST/CONVERT,
666: // so we can't re-write some data type conversions
667: // (date,time,timestamp, datetime)
668:
669: if (newType == null) {
670: throw SQLError
671: .createSQLException(
672: "Can't find conversion re-write for type '"
673: + type
674: + "' that is applicable for this server version while processing escape tokens.",
675: SQLError.SQL_STATE_GENERAL_ERROR);
676: }
677: }
678:
679: if (newType == null) {
680: throw SQLError.createSQLException(
681: "Unsupported conversion type '" + type.trim()
682: + "' found while processing escape token.",
683: SQLError.SQL_STATE_GENERAL_ERROR);
684: }
685:
686: int replaceIndex = newType.indexOf("?");
687:
688: if (replaceIndex != -1) {
689: StringBuffer convertRewrite = new StringBuffer(newType
690: .substring(0, replaceIndex));
691: convertRewrite.append(expression);
692: convertRewrite.append(newType.substring(replaceIndex + 1,
693: newType.length()));
694:
695: return convertRewrite.toString();
696: } else {
697:
698: StringBuffer castRewrite = new StringBuffer("CAST(");
699: castRewrite.append(expression);
700: castRewrite.append(" AS ");
701: castRewrite.append(newType);
702: castRewrite.append(")");
703:
704: return castRewrite.toString();
705: }
706: }
707:
708: /**
709: * Removes all whitespace from the given String. We use this to make escape
710: * token comparison white-space ignorant.
711: *
712: * @param toCollapse
713: * the string to remove the whitespace from
714: *
715: * @return a string with _no_ whitespace.
716: */
717: private static String removeWhitespace(String toCollapse) {
718: if (toCollapse == null) {
719: return null;
720: }
721:
722: int length = toCollapse.length();
723:
724: StringBuffer collapsed = new StringBuffer(length);
725:
726: for (int i = 0; i < length; i++) {
727: char c = toCollapse.charAt(i);
728:
729: if (!Character.isWhitespace(c)) {
730: collapsed.append(c);
731: }
732: }
733:
734: return collapsed.toString();
735: }
736: }
|