使用准备好的语句+带有包含的变量的sql查询的动态列名 [英] 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

现在，真正的问题浮出水面:假设我是否将使用一个简单的更新查询，例如以下问题:

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

在这里，由于成人静止"上的"，它不会执行

Here it wont execute because of ' 's on 'Adult Still'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.DatabaseMetaData类的supportsQuotedIdentifiers和getIdentifierQuoteString方法.

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查询的动态列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！