How do I sanitize SQL without using prepared statements

Question

For some sql statements I can't use a prepared statment, for instance:

SELECT MAX(AGE) FROM ?

For instance when I want to vary the table. Is there a utility that sanitizes sql in Java? There is one in ruby.

Answer 1

Not possible. Best what you can do is to use String#format().

String sql = "SELECT MAX(AGE) FROM %s";
sql = String.format(sql, tablename);

Note that this doesn't avoid SQL injection risks. If the tablename is a user/client-controlled value, you'd need to sanitize it using String#replaceAll().

tablename = tablename.replaceAll("[^\\w]", "");

Hope this helps.

[Edit] I should add: do NOT use this for column values where you can use PreparedStatement for. Just continue using it the usual way for any column values.

[Edit2] Best would be to not let the user/client be able to enter the tablename the way it want, but better present a dropdown containing all valid tablenames (which you can obtain by DatabaseMetaData#getCatalogs()) in the UI so that the user/client can select it. Don't forget to check in the server side if the selection is valid because one could spoof the request parameters.

Answer 2

In this case you could validate the table name against the list of available tables, by getting the table listing from the DatabaseMetaData. In reality it would probably just be easier to use a regex to strip spaces, perhaps also some sql reserved words, ";", etc from the string prior to using something liek String.format to build your complete sql statement.

The reason you can't use preparedStatement is because it is probably encasing the table name in ''s and escaping it like a string.

Answer 3

Right, prepared statement query parameters can be used only where you would use a single literal value. You can't use a parameter for a table name, a column name, a list of values, or any other SQL syntax.

So you have to interpolate your application variable into the SQL string and quote the string appropriately. Do use quoting to delimit your table name identifier, and escape the quote string by doubling it:

java.sql.DatabaseMetaData md = conn.getMetadata();
String q = md.getIdentifierQuoteString();
String sql = "SELECT MAX(AGE) FROM %s%s%s";
sql = String.format(sql, q, tablename.replaceAll(q, q+q), q);

For example, if your table name is literally table"name, and your RDBMS identifier quote character is ", then sql should contain a string like:

SELECT MAX(AGE) FROM "table""name"

I also agree with @ChssPly76's comment -- it's best if your user input is actually not the literal table name, but a signifier that your code maps into a table name, which you then interpolate into the SQL query. This gives you more assurance that no SQL injection can occur.

HashMap h = new HashMap<String,String>();
/* user-friendly table name maps to actual, ugly table name */
h.put("accounts", "tbl_accounts123");

userTablename = ... /* user input */
if (h.containsKey(userTablename)) {
  tablename = h.get(userTablename);
} else {
  throw ... /* Exception that user input is invalid */
}
String sql = "SELECT MAX(AGE) FROM %s";
/* we know the table names are safe because we wrote them */
sql = String.format(sql, tablename);

Answer 4

So there isn't a library then is what I'm seeing.