使用预准备语句 + sql 查询的动态列名，变量包含 's [英] Dynamic column name using prepared statement + sql query with variable containing 's

问题描述

我的查询

attributes.replace(" ' ", "");
//also used SET "+attributes+" 
String sql;
sql = "UPDATE diseaseinfo"
        + " SET ?=?"
        + "WHERE companyname = 'mycom' && diseaseName =?";

PreparedStatement preparedStmt = connects.prepareStatement(sql);
preparedStmt.setString(1, attributes);
preparedStmt.setString(2, attrData);
preparedStmt.setString(3, medname);
System.out.println(preparedStmt);

它给了我一个错误，因为查询在字符串中设置了列名，所以它变成这样的原因

it is giving me an error because query set the column name in string so it become like this on causes

 UPDATE diseaseinfo SET 'causes'='abc' WHERE companyname = 'mycom'  and diseaseName ='fever'

通过这个问题我知道我不能通过准备好的语句添加动态列:https://stackoverflow.com/a/3136049/7794329

and through this question I get to know that I can't add dynamic column by prepared statement: https://stackoverflow.com/a/3136049/7794329

现在，真正的问题出现了:假设我是否会使用一个简单的更新查询，比如这个问题:jdbc dymanic sql 查询，变量包含变量

Now, the real question comes up: suppose if I will use a simple update query like in this question: jdbc dymanic sql query with variable containg 's

它说您不能在简单的 sql 查询中输入带有 ' 的值，因为它会再次使查询出现语法错误，例如:

It says you can't enter value with 's in your simple sql query because it will again make the query syntactical error for example :

SELECT * FROM diseaseinfo WHERE diseaseName = 'Adult Still's disease' AND name = 'add';

这里不会执行，因为 ' ' 在 'Adult Still's

Here it wont execute because of ' 's on 'Adult Still's

那么它就不能用于简单的查询.我现在该怎么办?用什么?在查询中设置动态列并处理 's.

Then it won't work with simple query. What should I do now? What to use? To set dynamic column with taking care of 's in the query.

我不担心 SQL 注入，因为我在本地工作.我只想执行我的查询.

I am not worried about SQL injection because i am working on local. And I just want my query to be executed.

推荐答案

对.我们不能提供标识符作为绑定参数.列的名称必须是 SQL 文本的一部分.

Right. We can't supply identifiers as bind parameters. The name of the column has to be part of the SQL text.

我们可以像这样动态地将列的名称合并到 SQL 文本中:

We can dynamically incorporate the name of the column into the SQL text with something like this:

  sql = "UPDATE diseaseinfo"
      + " SET `" + colname + "` = ?"
      + " WHERE companyname = 'mycom' AND diseaseName = ?";

并为剩余的两个绑定参数提供值

And supply values for the two remaining bind parameters

  preparedStmt.setString(1, attrData);
  preparedStmt.setString(2, medname);

您对 SQL 注入的担忧是完全正确的.

And you are absolutely correct about being concerned about SQL Injection.

作为绑定值提供，就 SQL 注入而言，attrData 和 medname 的值中的单引号不会成为问题.

Supplied as bind values, single quotes in the values of attrData and medname won't be an issue, in terms of SQL Injection.

但是我提供的示例通过将 colname 变量合并到 SQL 文本中而容易受到攻击，如果我们不能保证 colname 包含在语句中是安全的".

But the example I've provided is vulnerable through incorporating the colname variable into the SQL text, if we don't have some guaranteed that colname is "safe" to include in the statement.

所以我们需要给 colname 赋值安全".

So we need to make the assignment of a value to colname "safe".

我们可以使用几种方法来做到这一点.最安全的是白名单"方法.该代码可以确保在将 colname 包含到 SQL 文本之前，只将特定的允许安全"值分配给 colname.

Several approaches we can use do that. The most secure would be a "whitelist" approach. The code can ensure that only specific allowed "safe" values get assigned to colname, before colname gets included into the SQL text.

举个简单的例子:

  String colname;
  if (attributes.equals("someexpectedvalue") {
      colname = "columnname_to_be_used";
  } else if (attributes.equals("someothervalid") {
      colname = "valid_columname";
  } else {
     // unexpected/unsupported attributes value so
     // handle condition or throw an exception 
  }

更灵活的方法是确保colname 中不会出现反引号字符.在这个例子中，colname 的值被转义 用反引号括起来.因此，只要 colname 中没有出现反引号字符，我们就会防止将提供的值解释为标识符以外的任何内容.

A more flexible approach is to ensure that a backtick character doesn't appear in colname. In the example, the value of colname is being escaped by enclosing it in backticks. So, as long as a backtick character doesn't appear in colname, we will prevent a supplied value from being interpreted as anything other than as an identifier.

对于使用硬编码反引号字符的更通用(和复杂)的方法，我们可以考虑使用 java.sql 的 supportsQuotedIdentifiers 和 getIdentifierQuoteString 方法.DatabaseMetaData 类.

For a more generic (and complicated) approach to using hardcoded backtick characters, we could consider making use the supportsQuotedIdentifiers and getIdentifierQuoteString methods of java.sql.DatabaseMetaData class.

(在 OP 代码中，我们没有看到 attributes 内容的数据类型.我们看到对名为 replace 的方法的调用，以及提供给那个.假设 attributes 是一个字符串，并且它应该是一个列名，那么我们根本不清楚为什么我们会在字符串中有空格单引号空格"，或者为什么我们需要删除它.除此之外，这个答案没有解决这个问题.)

(In the OP code, we don't see the datatype of contents of attributes. We see a call to a method named replace, and the arguments that are supplied to that. Assuming that attributes is a String, and that's supposed to be a column name, it's not at all clear why we would have "space single quote space" in the string, or why we need to remove that. Other than this mention, this answer doesn't address that.)

这篇关于使用预准备语句 + sql 查询的动态列名，变量包含 's的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！