Variable Column Names Using Prepared Statements

Variable column names using prepared statements

This indicates a bad DB design. The user shouldn't need to know about the column names. Create a real DB column which holds those "column names" and store the data along it instead.

And any way, no, you cannot set column names as PreparedStatement values. You can only set column values as PreparedStatement values

If you'd like to continue in this direction, you need to sanitize the column names (to avoid SQL Injection) and concatenate/build the SQL string yourself. Quote the separate column names and use String#replace() to escape the same quote inside the column name.

Dynamic column name using prepared statement + sql query with variable containing 's

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

Mysqli prepared statement column with variable

It's impossible to use a parameter for a column or a table name. Instead, they must be explicitly filtered out before use, using a white list approach.

// define a "white list"
$allowed = ['Node5', 'Node4'];

// Check the input variable against it 
if (!in_array($server, $allowed)) {
    throw new Exception("Invalid column name");
}

// now $server could be used in the SQL string
$sqlString = "UPDATE staff_members SET $server=? WHERE Username=?";
$stmt = $connection->prepare($sqlString);
$stmt->bind_param("ss", $rank, $username);
$stmt->execute();

Getting column name instead of values in prepared statement

This is not possible, with your way so to solve your problem.

Change your code like this :

String att = "FIRST_NAME";

string q="select " + att + " from EMPLOYEE where salary>?";

Preparedstatement pst=connectionobject.preparedstatement(q);
pst.setint(1,10000); 

IF YOU AFRAID THAT THE USER CAN MAKE AN SQL Injection than use this solution

You should to check if your column exist in your table or not :

SELECT * 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

Like so :

public boolean column_exist(String att) {
    boolean succes = false;
    CreerConnection con = new CreerConnection();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultat = null;
    try {
        connection = con.getConnection();

        statement = connection.prepareStatement("SELECT * \n"
                + "FROM information_schema.COLUMNS "
                + " WHERE"
                + " TABLE_SCHEMA = 'db_name'"
                + " AND TABLE_NAME = 'table_name'"
                + " AND COLUMN_NAME = ?");
        statement.setString(1, att);
        resultat = statement.executeQuery();

        if (resultat.next()) {
            succes = true;
        }

    } catch (SQLException e) {
        System.out.println("Exception = " + e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
            }
        }
    }
    return succes;
}

If the column exist then you can continue like so:

if(column_exist(att)){
   string q="select " + att + " from EMPLOYEE where salary>?";

   Preparedstatement pst=connectionobject.preparedstatement(q);
   pst.setint(1,10000); 
}

Learn more here :

MySQL, Check if a column exists in a table with SQL

Hope this can help you.

Should non-variable column names be prepared in a prepared query?

SQL parameters are used only for values, hence cannot be used for column or table names. Behind the scene, when preparing a statement, the RDBMS needs to fully understand the query that will later on be executed. Changing a column or table name does change the meaning of a query, so this is not supported.

Bottom line, you mentionned that the column and table names do not come from user input, so that removes the risk of SQL injection anyway. If they were coming from outside the program, then a solution would be to programmatically verify them (eg against predefined list of values).

PreparedStatement: Can I supply the column name as parameter?

you could code up a a set of sql queries and store them in a map, then grab one based on the column in question.

enum column { a, b, c}

Map<column, string> str;

static {
 str.put(a, "select * from tbl where a = ? ");
 ...
}

then just grab one out of the map later based on the enum. String appends in sql statements have a way of becoming security problems in the future.

JDBC PreparedStatement with ? for Columnname won't work

Just found the answer here, it´s not possible to do that. The "?" is just for values. To have variable columnnames it´s inevitable to do it with an own string manipulation.

---

Related Topics