如何在不使用预准备语句的情况下清理SQL

本文介绍了如何在不使用预准备语句的情况下清理SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对于某些sql语句，我不能使用准备好的语句，例如：

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

SELECT MAX(AGE) FROM ?

例如，当我想改变表时。是否有一个实用程序可以在Java中清理sql？ ruby中有一个。

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

推荐答案

是的，准备好的语句查询参数只能在你使用单一的地方使用字面值。您不能将参数用于表名，列名，值列表或任何其他SQL语法。

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.

因此您必须插入应用程序变量进入SQL字符串并适当地引用字符串。使用引用来分隔您的表名称标识符，并通过加倍来转义引号字符串：

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

例如，如果您的表名字面上是表name ，并且您的RDBMS标识符引用字符是，然后 sql 应包含如下字符串：

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"

我同意@ChssPly76的评论 - 如果你的用户输入实际上不是文字表名，而是你的代码映射成表名的能指，那你最好然后插入到SQL查询中。这可以让您更加确信不会发生SQL注入。

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