Changeset: b32946647c77 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=b32946647c77
Modified Files:
src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
tests/JDBC_API_Tester.java
Branch: default
Log Message:
Extended PreparedStatement.setString() json string validity test for json
parameters to now also accept: numbers (with optional fractions and exponent),
string literals between double quotes, and keywords: true, false and null.
Also extended and migrated test Bug_PrepStmtSetString_6382 to JDBC_API_Tester
with no output on success.
diffs (truncated from 363 to 300 lines):
diff --git a/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
b/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
--- a/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
+++ b/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
@@ -2251,26 +2251,120 @@ public class MonetPreparedStatement
castprefix = "inet ";
break;
case "json":
- // There is no support for JSON
in standard java class libraries.
- // Possibly we could use
org.json.simple.JSONObject or other/faster libs
- // javax.json.Json is not
released yet (see https://json-processing-spec.java.net/)
- // see also
https://github.com/fabienrenaud/java-json-benchmark
- // Note that it would make our
JDBC driver dependent of an external jar
- // and we don't want that.
+ {
+ // do a quick JSON string
validity check to prevent failing exec #(..., ...) calls
+ // which destroy the prepared
statement(s), see bug 6351 and 6832
+ String conversionIssue = null;
+ if (x.isEmpty()) {
+ conversionIssue = "It
may not be empty";
+ } else {
+ // scan for first and
last non-whitespace character
+ int start = 0;
+ int end = x.length() -1;
+ // find first non white
space character
+ char firstc =
x.charAt(start);
+ while ((firstc == ' '
|| firstc == '\t' || firstc == '\n' || firstc == '\r')
+ && (start < end)) {
+ start++;
+ firstc =
x.charAt(start);
+ }
+ // find last non white
space character
+ char lastc =
x.charAt(end);
+ while ((lastc == ' ' ||
lastc == '\t' || lastc == '\n' || lastc == '\r')
+ && (start < end)) {
+ end--;
+ lastc =
x.charAt(end);
+ }
- // do simplistic check if x
represents a valid json string to prevent
- // failing exec #(..., ...)
calls which destroy the prepared statement, see bug 6351
- if (x.isEmpty() ||
- (x.startsWith("{") &&
!x.endsWith("}")) ||
- (x.startsWith("[") &&
!x.endsWith("]"))
- // TODO check
completely if x represents a valid json string
- )
- throw new
SQLDataException("Invalid json string. It does not start with { or [ and end
with } or ]", "22M32");
-
- // TODO check completely if x
represents a valid json string
+ if (end - start <= 0) {
+ conversionIssue
= "It may not be empty";
+ } else {
+ switch (firstc)
{
+ case '{':
// start of an object
+ if
(lastc != '}')
+
conversionIssue = "It does not end with }";
+ break;
+ case '[':
// start of an array
+ if
(lastc != ']')
+
conversionIssue = "It does not end with ]";
+ break;
+ case '"':
// start of a string
+ if
(lastc != '"')
+
conversionIssue = "It does not end with \"";
+ break;
+ case 'n':
// start of literal: null
+ if
(lastc != 'l' || (end - start != 3) || !"null".equals(x.trim()))
+
conversionIssue = "It does not match: null";
+ break;
+ case 'f':
// start of literal: false
+ if
(lastc != 'e' || (end - start != 4) || !"false".equals(x.trim()))
+
conversionIssue = "It does not match: false";
+ break;
+ case 't':
// start of literal: true
+ if
(lastc != 'e' || (end - start != 3) || !"true".equals(x.trim()))
+
conversionIssue = "It does not match: true";
+ break;
+ case '-':
// start of a number with a negative sign
+ case '+':
// start of a number with a positive sign
+ case '0':
+ case '1':
+ case '2':
+ case '3':
+ case '4':
+ case '5':
+ case '6':
+ case '7':
+ case '8':
+ case '9':
+ //
start of a number in format: [ - | + ] int [ frac ] [ exp ]
+ //
check if it continues with more digits
+ if
(start < end)
+
start++;
+ firstc
= x.charAt(start);
+ while
(firstc >= '0' && firstc <= '9' && start < end) {
+
start++;
+
firstc = x.charAt(start);
+ }
+ //
check if it continues with optional fractions
+ if
(firstc == '.' && start < end) {
+
// yes, consume the digits of the fraction
+
start++;
+
firstc = x.charAt(start);
+
while (firstc >= '0' && firstc <= '9' && start < end) {
+
start++;
+
firstc = x.charAt(start);
+
}
+ }
+ //
check if it continues with optional exponent
+ if
((firstc == 'E' || firstc == 'e') && start < end) {
+
// yes, consume the optional sign
+
start++;
+
firstc = x.charAt(start);
+
if ((firstc == '+' || firstc == '-') && start < end) {
+
start++;
+
firstc = x.charAt(start);
+
}
+
// yes, consume the digits of the exponnent
+
while ((firstc >= '0' && firstc <= '9') && start < end) {
+
start++;
+
firstc = x.charAt(start);
+
}
+ }
+ if
(start != end)
+
conversionIssue = "It does not represent a valid number";
+ break;
+ default:
+
conversionIssue = "Invalid first character: " + firstc;
+ break;
+ }
+ }
+ }
+ if (conversionIssue != null)
+ throw new
SQLDataException("Invalid json string. " + conversionIssue, "22M32");
castprefix = "json ";
break;
+ }
case "url":
try {
// also check if x
represents a valid url string to prevent
diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java
--- a/tests/JDBC_API_Tester.java
+++ b/tests/JDBC_API_Tester.java
@@ -78,6 +78,7 @@ final public class JDBC_API_Tester {
jt.Test_Sbatching();
jt.Test_Smoreresults();
jt.Test_Wrapper();
+ jt.Bug_PrepStmtSetString_6382();
jt.closeConx(jt.con);
}
@@ -3029,6 +3030,211 @@ final public class JDBC_API_Tester {
}
}
+ private void Bug_PrepStmtSetString_6382() {
+ sb.setLength(0); // clear the output log buffer
+
+ Statement stmt = null;
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ final String tableName = "PrepStmtSetString_6382";
+ try {
+ // >> true: auto commit should be on by default
+ sb.append("0.
true\t").append(con.getAutoCommit()).append("\n");
+
+ stmt = con.createStatement();
+ sb.append("1. Creating table " + tableName);
+ if (stmt.executeUpdate("CREATE TABLE " + tableName + "
(myint INT, myvarchar VARCHAR(15), myjson JSON, myuuid UUID, myurl URL, myinet
INET)") != Statement.SUCCESS_NO_INFO)
+ sb.append("Wrong return status\n");
+
+ sb.append("\n2. Insert row 1, ");
+ stmt.executeUpdate("INSERT INTO " + tableName + "
VALUES (1, 'row 1', '{}', uuid '34c8deb5-e608-406b-beda-6a951f73d455',
'https://www.monetdb.org/', '128.0.0.1')");
+ sb.append("2, ");
+ stmt.executeUpdate("INSERT INTO " + tableName + "
VALUES (2, 'row 2', '[]', NULL, NULL, NULL)");
+ sb.append("3, ");
+ stmt.executeUpdate("INSERT INTO " + tableName + "
VALUES (3, 'row 3', '\"abc\"', NULL, NULL, NULL)");
+ sb.append("4, ");
+ stmt.executeUpdate("INSERT INTO " + tableName + "
VALUES (4, 'row 4', 'true', NULL, NULL, NULL)");
+ sb.append("5\n");
+ stmt.executeUpdate("INSERT INTO " + tableName + "
VALUES (5, 'row 5', '-0.123', NULL, NULL, NULL)");
+
+ sb.append("Creating a prepared statement with 6
parameters and inserting rows using setInt(), setString(), setNull(),
setNString(), setURL(), setObject().\n");
+ pstmt = con.prepareStatement("INSERT INTO " + tableName
+ " VALUES (?,?, ? ,?,? , ?)");
+ ParameterMetaData pmd = pstmt.getParameterMetaData();
+ int pcount = pmd.getParameterCount();
+ sb.append("Prepared Statement has " + pcount + "
parameters:" + (pcount != 6 ? " ERROR: Expected 6 parameters!" :
"")).append("\n");
+ for (int p = 1; p <= pcount; p++) {
+ sb.append(" Parameter " + p + " type is: " +
pmd.getParameterTypeName(p) + ". JDBC SQL type: " +
pmd.getParameterType(p)).append("\n");
+ }
+
+ int row = 6;
+ pstmt.setInt(1, row);
+ pstmt.setString(2, "row " + row);
+ pstmt.setString(3, "{\"menu\": {\n \"id\": \"file\",\n
\"value\": \"File\",\n \"popup\": {\n \"menuitem\": [\n {\"value\":
\"New\", \"onclick\": \"CreateNewDoc()\"},\n {\"value\": \"Open\",
\"onclick\": \"OpenDoc()\"},\n {\"value\": \"Close\", \"onclick\":
\"CloseDoc()\"}\n ]\n }\n}}");
+ pstmt.setNull(4, 0);
+ pstmt.setNull(5, 0);
+ pstmt.setNull(6, 0);
+ sb.append("Inserting row " + row).append("\n");
+ int inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ row++; // row 7
+ pstmt.setShort(1, (short)row);
+ pstmt.setNString(2, "row " + row);
+ pstmt.setNull(3, 0);
+ pstmt.setString(4,
"4a148b7d-8d47-4e1e-a21e-09a71abf2215");
+ sb.append("Inserting row " + row).append("\n");
+ inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ row++; // row 8
+ pstmt.setLong(1, (long)row);
+ pstmt.setString(2, "row " + row);
+ pstmt.setObject(3, "+3.1415E-06");
+ pstmt.setNull(4, 0);
+ try {
+ pstmt.setURL(5, new
java.net.URL("https://www.cwi.nl/";));
+ } catch (java.net.MalformedURLException mfe) {
+ sb.append(mfe).append("\n");
+ }
+ sb.append("Inserting row " + row).append("\n");
+ inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ row++; // row 9
+ pstmt.setBigDecimal(1, new java.math.BigDecimal(row));
+ pstmt.setNString(2, "row " + row);
+ pstmt.setNull(5, 0);
+ pstmt.setString(6, "127.255.255.255");
+ sb.append("Inserting row " + row).append("\n");
+ inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ /* also test generic setObject(int, String) */
+ row++; // row 10
+ pstmt.setObject(1, Integer.valueOf(row));
+ pstmt.setObject(2, "row " + row);
+ pstmt.setObject(3, "[{\"menu\": {\n \"header\":
\"SVG Viewer\",\n \"items\": [\n {\"id\": \"Open\"},\n
{\"id\": \"OpenNew\", \"label\": \"Open New\"},\n null,\n
{\"id\": \"ZoomIn\", \"label\": \"Zoom In\"},\n {\"id\": \"ZoomOut\",
\"label\": \"Zoom Out\"},\n {\"id\": \"OriginalView\", \"label\":
\"Original View\"},\n null,\n {\"id\": \"Quality\"},\n
{\"id\": \"Pause\"},\n {\"id\": \"Mute\"},\n null,\n
{\"id\": \"Help\"},\n {\"id\": \"About\", \"label\": \"About Adobe CVG
Viewer...\"}\n ]\n}}]");
+ pstmt.setObject(4,
"b39dc76e-4faf-4fd9-bc1e-17df48acf764");
+ pstmt.setObject(5,
"https://en.wikipedia.org/wiki/IP_address";);
+ pstmt.setObject(6, "223.255.255.255");
+ sb.append("Inserting row " + row).append("\n");
+ inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ row++; // row 11
+ pstmt.setObject(1, new java.math.BigDecimal(row));
+ pstmt.setObject(2, "row " + row);
+ pstmt.setObject(3, "null");
+ pstmt.setObject(4,
java.util.UUID.fromString("ff125769-b63c-4c3c-859f-5b84a9349e24"));
+ URL myURL = new URL();
+ try {
+
myURL.fromString("https://en.wikipedia.org/wiki/IP_address";);
+ pstmt.setObject(5, myURL);
+ } catch (Exception mfe) {
+ sb.append(mfe).append("\n");
+ }
+ INET myINET = new INET();
+ myINET.fromString("223.234.245.255");
+ pstmt.setObject(6, myINET);
+ sb.append("Inserting row " + row).append("\n");
+ inserted = pstmt.executeUpdate();
+ sb.append("Inserted " + inserted + " row").append("\n");
+
+ sb.append("List contents of TABLE " + tableName + "
after " + row + " rows inserted").append("\n");
+ rs = stmt.executeQuery("SELECT * FROM " + tableName + "
ORDER BY 1");
+ ResultSetMetaData rsmd = rs.getMetaData();
+ int colcount = rsmd.getColumnCount();
+ sb.append("Query has " + colcount + " output columns."
+ (colcount != 6 ? " ERROR: Expected 6 columns!" : "")).append("\n");
+ row = 0;
+ while (rs.next()) {
+ sb.append("row " + ++row);
+ for (int c = 1; c <= colcount; c++) {
+ sb.append("\t" + rs.getString(c));
+ }
+ sb.append("\n");
+ }
+ } catch (SQLException e) {
+ sb.append("FAILED:
").append(e.getMessage()).append("\n");
+ }
+
+ try {
+ sb.append("Cleanup TABLE " + tableName).append("\n");
+ stmt.executeUpdate("DROP TABLE " + tableName);
+ } catch (SQLException e) {
+ sb.append("FAILED:
").append(e.getMessage()).append("\n");
+ }
+
+ closeStmtResSet(stmt, rs;
+ closeStmtResSet(pstmt, null);
+
+ compareExpectedOutput("Bug_PrepStmtSetString_6382",
+ "0. true true\n" +
+ "1. Creating table PrepStmtSetString_6382\n" +
+ "2. Insert row 1, 2, 3, 4, 5\n" +
+ "Creating a prepared statement with 6
parameters and inserting rows using setInt(), setString(), setNull(),
setNString(), setURL(), setObject().\n" +
+ "Prepared Statement has 6 parameters:\n" +
+ " Parameter 1 type is: int. JDBC SQL type: 4\n"
+
+ " Parameter 2 type is: varchar. JDBC SQL type:
12\n" +
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
