postgresql-jdbc.git / summary / log / commit / refs
commit 00a847879f313105de124776b7edaab928bf5b4c
Author: Sebastian Utz <su@rtme.net>
Commit: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Date: Wed Oct 05 13:36:32 2016 +0000
fix: makes escape processing strict according to JDBC spec
before, escape code parsing was very lenient and
invalid escape code formats were accidentally processed.
closes #657
.../src/main/java/org/postgresql/core/Parser.java | 115 ++++++++++++++-------
.../test/java/org/postgresql/core/ParserTest.java | 25 +++++
.../org/postgresql/test/jdbc2/ConnectionTest.java | 2 +-
.../benchmark/escaping/EscapeProcessing.java | 33 ++++++
4 files changed, 138 insertions(+), 37 deletions(-)
diff --git a/pgjdbc/src/main/java/org/postgresql/core/Parser.java b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
index ec084591..7e88be09 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/Parser.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
@@ -22,6 +22,7 @@ import java.util.List;
/**
* Basic query parser infrastructure.
+ * Note: This class should not be considered as pgjdbc public API.
*
* @author Michael Paesold (mpaesold@gmx.at)
* @author Christopher Deckers (chrriis@gmail.com)
@@ -961,7 +962,7 @@ public class Parser {
* @param standardConformingStrings whether standard_conforming_strings is on
* @return PostgreSQL-compatible SQL
*/
- static String replaceProcessing(String p_sql, boolean replaceProcessingEnabled,
+ public static String replaceProcessing(String p_sql, boolean replaceProcessingEnabled,
boolean standardConformingStrings) throws SQLException {
if (replaceProcessingEnabled) {
// Since escape codes can only appear in SQL CODE, we keep track
@@ -1012,6 +1013,8 @@ public class Parser {
i--;
while (!endOfNested && ++i < len) {
char c = p_sql[i];
+
+ state_switch:
switch (state) {
case IN_SQLCODE:
if (c == '$') {
@@ -1054,37 +1057,20 @@ public class Parser {
break;
} else if (c == '{') { // start of an escape code?
if (i + 1 < len) {
- char next = p_sql[i + 1];
- char nextnext = (i + 2 < len) ? p_sql[i + 2] : '\0';
- if (next == 'd' || next == 'D') {
- state = SqlParseState.ESC_TIMEDATE;
- i++;
- newsql.append("DATE ");
- break;
- } else if (next == 't' || next == 'T') {
- state = SqlParseState.ESC_TIMEDATE;
- if (nextnext == 's' || nextnext == 'S') {
- // timestamp constant
- i += 2;
- newsql.append("TIMESTAMP ");
- } else {
- // time constant
- i++;
- newsql.append("TIME ");
+ SqlParseState[] availableStates = SqlParseState.values();
+ // skip first state, it's not a escape code state
+ for (int j = 1; j < availableStates.length; j++) {
+ SqlParseState availableState = availableStates[j];
+ int matchedPosition = availableState.getMatchedPosition(p_sql, i + 1);
+ if (matchedPosition == 0) {
+ continue;
+ }
+ i += matchedPosition;
+ if (availableState.replacementKeyword != null) {
+ newsql.append(availableState.replacementKeyword);
}
- break;
- } else if (next == 'f' || next == 'F') {
- state = SqlParseState.ESC_FUNCTION;
- i += (nextnext == 'n' || nextnext == 'N') ? 2 : 1;
- break;
- } else if (next == 'o' || next == 'O') {
- state = SqlParseState.ESC_OUTERJOIN;
- i += (nextnext == 'j' || nextnext == 'J') ? 2 : 1;
- break;
- } else if (next == 'e' || next == 'E') {
- // we assume that escape is the only escape sequence beginning with e
- state = SqlParseState.ESC_ESCAPECHAR;
- break;
+ state = availableState;
+ break state_switch;
}
}
}
@@ -1114,7 +1100,9 @@ public class Parser {
}
state = SqlParseState.IN_SQLCODE; // end of escaped function (or query)
break;
- case ESC_TIMEDATE:
+ case ESC_DATE:
+ case ESC_TIME:
+ case ESC_TIMESTAMP:
case ESC_OUTERJOIN:
case ESC_ESCAPECHAR:
if (c == '}') {
@@ -1178,12 +1166,67 @@ public class Parser {
}
}
+ private final static char[] QUOTE_OR_ALPHABETIC_MARKER = new char[]{'\"', '0'};
+ private final static char[] SINGLE_QUOTE = new char[]{'\''};
+
// Static variables for parsing SQL when replaceProcessing is true.
private enum SqlParseState {
IN_SQLCODE,
- ESC_TIMEDATE,
- ESC_FUNCTION,
- ESC_OUTERJOIN,
- ESC_ESCAPECHAR;
+ ESC_DATE("d", SINGLE_QUOTE, "DATE "),
+ ESC_TIME("t", SINGLE_QUOTE, "TIME "),
+
+ ESC_TIMESTAMP("ts", SINGLE_QUOTE, "TIMESTAMP "),
+ ESC_FUNCTION("fn", QUOTE_OR_ALPHABETIC_MARKER, null),
+ ESC_OUTERJOIN("oj", QUOTE_OR_ALPHABETIC_MARKER, null),
+ ESC_ESCAPECHAR("escape", SINGLE_QUOTE, "ESCAPE ");
+
+ private final char[] escapeKeyword;
+ private final char[] allowedValues;
+ private final String replacementKeyword;
+
+ SqlParseState() {
+ this("", new char[0], null);
+ }
+
+ SqlParseState(String escapeKeyword, char[] allowedValues, String replacementKeyword) {
+ this.escapeKeyword = escapeKeyword.toCharArray();
+ this.allowedValues = allowedValues;
+ this.replacementKeyword = replacementKeyword;
+ }
+
+ private int getMatchedPosition(char[] p_sql, int pos) {
+ int newPos = pos;
+
+ // check for the keyword
+ for (char c : escapeKeyword) {
+ if (newPos >= p_sql.length) {
+ return 0;
+ }
+ char curr = p_sql[newPos++];
+ if (curr != c && curr != Character.toUpperCase(c)) {
+ return 0;
+ }
+ }
+ if (newPos >= p_sql.length) {
+ return 0;
+ }
+
+ // check for the beginning of the value
+ char curr = p_sql[newPos];
+ // ignore any in-between whitespace
+ while (curr == ' ') {
+ newPos++;
+ if (newPos >= p_sql.length) {
+ return 0;
+ }
+ curr = p_sql[newPos];
+ }
+ for (char c : allowedValues) {
+ if (curr == c || (c == '0' && Character.isLetter(curr))) {
+ return newPos - pos;
+ }
+ }
+ return 0;
+ }
}
}
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
index 3edb18f7..68bdf427 100644
--- a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
@@ -103,4 +103,29 @@ public class ParserTest extends TestCase {
"select".getChars(0, 6, command, 0);
Assert.assertTrue("Failed to correctly parse lower case command.", Parser.parseSelectKeyword(command, 0));
}
+
+ public void testEscapeProcessing() throws Exception {
+ Assert.assertEquals("DATE '1999-01-09'", Parser.replaceProcessing("{d '1999-01-09'}", true, false));
+ Assert.assertEquals("DATE '1999-01-09'", Parser.replaceProcessing("{D '1999-01-09'}", true, false));
+ Assert.assertEquals("TIME '20:00:03'", Parser.replaceProcessing("{t '20:00:03'}", true, false));
+ Assert.assertEquals("TIME '20:00:03'", Parser.replaceProcessing("{T '20:00:03'}", true, false));
+ Assert.assertEquals("TIMESTAMP '1999-01-09 20:11:11.123455'", Parser.replaceProcessing("{ts '1999-01-09 20:11:11.123455'}", true, false));
+ Assert.assertEquals("TIMESTAMP '1999-01-09 20:11:11.123455'", Parser.replaceProcessing("{Ts '1999-01-09 20:11:11.123455'}", true, false));
+
+ Assert.assertEquals("user", Parser.replaceProcessing("{fn user()}", true, false));
+ Assert.assertEquals("cos(1)", Parser.replaceProcessing("{fn cos(1)}", true, false));
+ Assert.assertEquals("extract(week from DATE '2005-01-24')", Parser.replaceProcessing("{fn week({d '2005-01-24'})}", true, false));
+
+ Assert.assertEquals("\"T1\" LEFT OUTER JOIN t2 ON \"T1\".id = t2.id",
+ Parser.replaceProcessing("{oj \"T1\" LEFT OUTER JOIN t2 ON \"T1\".id = t2.id}", true, false));
+
+ Assert.assertEquals("ESCAPE '_'", Parser.replaceProcessing("{escape '_'}", true, false));
+
+ // nothing should be changed in that case, no valid escape code
+ Assert.assertEquals("{obj : 1}", Parser.replaceProcessing("{obj : 1}", true, false));
+ }
+
+ public void testUnterminatedEscape() throws Exception {
+ Assert.assertEquals("{oj ", Parser.replaceProcessing("{oj ", true, false));
+ }
}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConnectionTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConnectionTest.java
index 44d42931..00b69508 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConnectionTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConnectionTest.java
@@ -109,7 +109,7 @@ public class ConnectionTest extends TestCase {
public void testNativeSQL() throws Exception {
// test a simple escape
con = TestUtil.openDB();
- assertEquals("DATE '2005-01-24'", con.nativeSQL("{d '2005-01-24'}"));
+ assertEquals("DATE '2005-01-24'", con.nativeSQL("{d '2005-01-24'}"));
}
/*
diff --git a/ubenchmark/src/main/java/org/postgresql/benchmark/escaping/EscapeProcessing.java b/ubenchmark/src/main/java/org/postgresql/benchmark/escaping/EscapeProcessing.java
new file mode 100644
index 00000000..7b6a5146
--- /dev/null
+++ b/ubenchmark/src/main/java/org/postgresql/benchmark/escaping/EscapeProcessing.java
@@ -0,0 +1,33 @@
+package org.postgresql.benchmark.escaping;
+
+import org.postgresql.core.Parser;
+
+import org.openjdk.jmh.annotations.Benchmark;
+import org.openjdk.jmh.annotations.BenchmarkMode;
+import org.openjdk.jmh.annotations.Measurement;
+import org.openjdk.jmh.annotations.Mode;
+import org.openjdk.jmh.annotations.OutputTimeUnit;
+import org.openjdk.jmh.annotations.Scope;
+import org.openjdk.jmh.annotations.State;
+import org.openjdk.jmh.annotations.Threads;
+import org.openjdk.jmh.annotations.Warmup;
+
+import java.util.concurrent.TimeUnit;
+
+@Measurement(iterations = 10, time = 1, timeUnit = TimeUnit.SECONDS)
+@Warmup(iterations = 10, time = 1, timeUnit = TimeUnit.SECONDS)
+@State(Scope.Thread)
+@Threads(1)
+@BenchmarkMode(Mode.AverageTime)
+@OutputTimeUnit(TimeUnit.NANOSECONDS)
+public class EscapeProcessing {
+
+ private String fnEscapeSQL = "{fn week({d '2005-01-24'})}";
+ private boolean replaceProcessingEnabled = true;
+ private boolean standardConformingStrings = false;
+
+ @Benchmark
+ public String escapeFunctionWithDate() throws Exception {
+ return Parser.replaceProcessing(fnEscapeSQL, replaceProcessingEnabled, standardConformingStrings);
+ }
+}
[parent: 7270435aebc2]