如何在不使用准备好的语句的情况下清理 SQL [英] How do I sanitize SQL without using prepared statements

问题描述

对于某些 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 字符串并适当地引用该字符串.请使用 quoting 来分隔您的表名标识符，并通过将引号字符串加倍来转义:

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

例如，如果您的表名字面上是 table"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); 

这篇关于如何在不使用准备好的语句的情况下清理 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！