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

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);