changeset 425:95d15f1d750d

Restructured code (incl removal of duplicate code) for utility functions dq() and sq() which add double or double quotes and add escapes to string values.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 03 Feb 2021 17:22:03 +0100 (2021-02-03)
parents 709300b32284
children 3da79315783b
files src/main/java/org/monetdb/client/JdbcClient.java src/main/java/org/monetdb/jdbc/MonetConnection.java src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java src/main/java/org/monetdb/jdbc/MonetResultSet.java src/main/java/org/monetdb/jdbc/MonetWrapper.java src/main/java/org/monetdb/util/Exporter.java src/main/java/org/monetdb/util/SQLExporter.java
diffstat 8 files changed, 74 insertions(+), 57 deletions(-) [+]
line wrap: on
line diff
--- a/src/main/java/org/monetdb/client/JdbcClient.java
+++ b/src/main/java/org/monetdb/client/JdbcClient.java
@@ -707,7 +707,7 @@ public class JdbcClient {	/* cannot (yet
 									if (tableType != null && tableType.startsWith("SYSTEM ")) {
 										String tableNm = tbl.getString(3);	// 3 = "TABLE_NAME"
 										if (tableNm.contains(" ") || tableNm.contains("\t"))
-											tableNm = dq(tableNm);
+											tableNm = Exporter.dq(tableNm);
 										out.println(tableType + "\t" +
 											tbl.getString(2) + "." +	// 2 = "TABLE_SCHEM"
 											tableNm);
@@ -727,7 +727,7 @@ public class JdbcClient {	/* cannot (yet
 										if (tableType != null && !tableType.startsWith("SYSTEM ")) {
 											String tableNm = tbl.getString(3);	// 3 = "TABLE_NAME"
 											if (tableNm.contains(" ") || tableNm.contains("\t"))
-												tableNm = dq(tableNm);
+												tableNm = Exporter.dq(tableNm);
 											out.println(tableType + "\t" +
 												tbl.getString(2) + "." +	// 2 = "TABLE_SCHEM"
 												tableNm);
@@ -1162,10 +1162,6 @@ public class JdbcClient {	/* cannot (yet
 			}
 		}
 	}
-
-	static final String dq(final String in) {
-		return "\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
-	}
 }
 
 /**
@@ -1294,7 +1290,7 @@ final class Table {
 	}
 
 	final String getFqnameQ() {
-		return JdbcClient.dq(schem) + "." + JdbcClient.dq(name);
+		return Exporter.dq(schem) + "." + Exporter.dq(name);
 	}
 
 	public final String toString() {
--- a/src/main/java/org/monetdb/jdbc/MonetConnection.java
+++ b/src/main/java/org/monetdb/jdbc/MonetConnection.java
@@ -1729,22 +1729,6 @@ public class MonetConnection
 		return sb.toString();
 	}
 
-	/**
-	 * Utility method to escape all ocurrences of special characters
-	 * (double slashes and single quotes) in a string literal
-	 * It is called from: MonetDatabaseMetaData and MonetPreparedStatement
-	 */
-	final String escapeSpecialChars(final String in) {
-		String ret = in;
-		if (ret.contains("\\\\"))
-			// all double slashes in input need to be escaped.
-			ret = ret.replaceAll("\\\\", "\\\\\\\\");
-		if (ret.contains("'"))
-			// all single quotes in input need to be escaped.
-			ret = ret.replaceAll("'", "\\\\'");
-		return ret;
-	}
-
 
 	// Internal caches for 3 static mserver environment values, so they aren't queried from mserver again and again
 	private String env_current_user;
--- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -4099,25 +4099,13 @@ public class MonetDatabaseMetaData
 		if (in == null)
 			return "IS NULL";
 
-		String cmp = "= '";
+		String cmp = "= ";
 		// check if SQL wildcards are used in the input, if so use LIKE
 		if (in.contains("%") || in.contains("_"))
-			cmp = "LIKE '";
-
-		return cmp + con.escapeSpecialChars(in) + "'";
+			cmp = "LIKE ";
+
+		return cmp + MonetWrapper.sq(in);
 	}
 
-	/**
-	 * Returns the given string between two double quotes for usage as
-	 * exact column or table name in SQL queries.
-	 *
-	 * @param in the string to quote
-	 * @return the quoted string
-	 */
-//	@SuppressWarnings("unused")
-//	private static final String dq(String in) {
-//		return "\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
-//	}
-
 	//== end helper methods
 }
--- a/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
+++ b/src/main/java/org/monetdb/jdbc/MonetPreparedStatement.java
@@ -2019,7 +2019,7 @@ public class MonetPreparedStatement
 					// representation is given, but we need to prefix it
 					// with the actual sqltype the server expects, or we
 					// will get an error back
-					setValue(paramnr, sqltype + " '" + connection.escapeSpecialChars(x) + "'");
+					setValue(paramnr, sqltype + " " + MonetWrapper.sq(x));
 				}
 
 				@Override
@@ -2293,7 +2293,7 @@ public class MonetPreparedStatement
 						break;
 				}
 				/* in specific cases prefix the string with: inet or json or url or uuid */
-				setValue(parameterIndex, castprefix + " '" + connection.escapeSpecialChars(x) + "'");
+				setValue(parameterIndex, castprefix + " " + MonetWrapper.sq(x));
 
 				break;
 			}
@@ -2579,7 +2579,7 @@ public class MonetPreparedStatement
 			return;
 		}
 
-		setValue(parameterIndex, "url '" + connection.escapeSpecialChars(x.toString()) + "'");
+		setValue(parameterIndex, "url " + MonetWrapper.sq(x.toString()));
 	}
 
 	/**
--- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java
+++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java
@@ -1323,9 +1323,9 @@ public class MonetResultSet
 				"JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " +
 				"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
 
-				query.append("WHERE s.\"name\" = '").append(conn.escapeSpecialChars(schemaName)).append("'");
-				query.append(" AND t.\"name\" = '").append(conn.escapeSpecialChars(tableName)).append("'");
-				query.append(" AND c.\"name\" = '").append(conn.escapeSpecialChars(columnName)).append("'");
+				query.append("WHERE s.\"name\" = ").append(MonetWrapper.sq(schemaName));
+				query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(tableName));
+				query.append(" AND c.\"name\" = ").append(MonetWrapper.sq(columnName));
 				// query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\"");
 
 				ResultSet rs = null;
--- a/src/main/java/org/monetdb/jdbc/MonetWrapper.java
+++ b/src/main/java/org/monetdb/jdbc/MonetWrapper.java
@@ -23,7 +23,7 @@ import java.sql.SQLFeatureNotSupportedEx
  * represented by their proxy, to permit direct access to the resource delegates.
  *
  * @author Fabian Groffen, Martin van Dinther
- * @version 1.1
+ * @version 1.2
  */
 public class MonetWrapper implements java.sql.Wrapper {
 	/**
@@ -77,6 +77,7 @@ public class MonetWrapper implements jav
 		throw new SQLException("Cannot unwrap to interface: " + (iface != null ? iface.getName() : ""), "0A000");
 	}
 
+
 	/**
 	 * Small helper method that formats the "Method ... not implemented" message
 	 * and creates a new SQLFeatureNotSupportedException object
@@ -88,4 +89,45 @@ public class MonetWrapper implements jav
 	static final SQLFeatureNotSupportedException newSQLFeatureNotSupportedException(final String name) {
 		return new SQLFeatureNotSupportedException("Method " + name + " not implemented", "0A000");
 	}
+
+	/**
+	 * General utility function to add double quotes around an SQL Indentifier
+	 * such as column or table or schema name in SQL queries.
+	 * It also adds escapes for special characters: double quotes and the escape character
+	 *
+	 * FYI: it is made public as it is also called from util/Exporter.java
+	 *
+	 * @param in the string to quote
+	 * @return the double quoted string
+	 */
+	public static final String dq(final String in) {
+		String ret = in;
+		if (ret.contains("\\\\"))
+			// all double slashes in input need to be escaped.
+			ret = ret.replaceAll("\\\\", "\\\\\\\\");
+		if (ret.contains("\""))
+			// all double quotes in input need to be escaped.
+			ret = ret.replaceAll("\"", "\\\\\"");
+		return "\"" + ret + "\"";
+	}
+
+	/**
+	 * General utility function to add single quotes around string literals as used in SQL queries.
+	 * It also adds escapes for special characters: single quotes and the escape character
+	 *
+	 * FYI: it is made public as it is also called from util/Exporter.java
+	 *
+	 * @param in the string to quote
+	 * @return the single quoted string
+	 */
+	public static final String sq(final String in) {
+		String ret = in;
+		if (ret.contains("\\\\"))
+			// all double slashes in input need to be escaped.
+			ret = ret.replaceAll("\\\\", "\\\\\\\\");
+		if (ret.contains("'"))
+			// all single quotes in input need to be escaped.
+			ret = ret.replaceAll("'", "\\\\'");
+		return "'" + ret + "'";
+	}
 }
--- a/src/main/java/org/monetdb/util/Exporter.java
+++ b/src/main/java/org/monetdb/util/Exporter.java
@@ -8,6 +8,8 @@
 
 package org.monetdb.util;
 
+import org.monetdb.jdbc.MonetWrapper;	// for dq() and sq()
+
 import java.io.PrintWriter;
 import java.sql.ResultSet;
 import java.sql.SQLException;
@@ -39,25 +41,30 @@ public abstract class Exporter {
 	}
 
 	/**
-	 * returns the given string between two double quotes for usage as
-	 * identifier such as column or table or schema name in SQL queries.
+	 * Convenience function to call the general utility function MonetWrapper.dq()
+	 * to add double quotes around an SQL Indentifier such as column or
+	 * table or schema name in SQL queries.
+	 * It also adds escapes for special characters: double quotes and the escape character
+	 *
+	 * FYI: it is made public as it is also called from client/JdbcClient.java
 	 *
 	 * @param in the string to quote
 	 * @return the quoted string
 	 */
-	protected static final String dq(final String in) {
-		return "\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
+	public static final String dq(final String in) {
+		return MonetWrapper.dq(in);
 	}
 
 	/**
-	 * returns the given string between two single quotes for usage as
-	 * string literal in SQL queries.
+	 * Convenience function to call the general utility function MonetWrapper.sq()
+	 * to add single quotes around string literals in SQL queries.
+	 * It also adds escapes for special characters: single quotes and the escape character
 	 *
 	 * @param in the string to quote
 	 * @return the quoted string
 	 */
-	protected static final String q(final String in) {
-		return "'" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'") + "'";
+	protected static final String sq(final String in) {
+		return MonetWrapper.sq(in);
 	}
 
 	/**
@@ -92,8 +99,8 @@ public abstract class Exporter {
 		String val = null;
 		try {
 			stmt = con.createStatement();
-			final String cmd = "SELECT query FROM sys.tables WHERE name = '" + name
-				+ "' and schema_id IN (SELECT id FROM sys.schemas WHERE name = '" + schema + "')";
+			final String cmd = "SELECT query FROM sys.tables WHERE name = " + sq(name)
+				+ " and schema_id IN (SELECT id FROM sys.schemas WHERE name = " + sq(schema) + ")";
 			rs = stmt.executeQuery(cmd);
 			if (rs != null) {
 				if (rs.next()) {
--- a/src/main/java/org/monetdb/util/SQLExporter.java
+++ b/src/main/java/org/monetdb/util/SQLExporter.java
@@ -486,7 +486,7 @@ public final class SQLExporter extends E
 				if (val == null || rs.wasNull()) {
 					strbuf.append("NULL");
 				} else {
-					strbuf.append((types[i] == QUOTE) ? q(val) : val);
+					strbuf.append((types[i] == QUOTE) ? sq(val) : val);
 				}
 			}
 			strbuf.append(");");