Dynamic column name using prepared statement + sql

问题:

My query

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'

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

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.

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

回答1:

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

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

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

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

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.

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

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.

As a simple example:

  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 
  }

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.

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.

---

(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.)

回答2:

you have asked an interesting question.. solution is already there as YCF told you.. if you write

attributes.replace(" ' ", "");

then space will also count

so use

attributes.replace("'", "");

to make things work also i want to add

to Write a correct string escape utility, StringEscapeUtils.escapeSql(String) (note that this is so non-trivial that the method has been removed from more recent versions of StringEscapeUtils). –