PreparedStatement set Where clause

Question

I'm using PreparedStatement for a DELETE query.

My ps is configured as this

config.sql.statement.delete=DELETE FROM ? WHERE ?

Then in my Java code, I set values like this

ps.setString(1, schemaName == "F" ? "FUNDS" : "MANDATE" + "." + tableName);
ps.setString(2, whereClause);

The whereClause is set up as below

String whereClause = " ";
for (int m = 0; m < columns.size(); m++) {
    String columnData = jsonObj.getString(columns.get(m));
    log.info("Column Data for column " + columns.get(m) + " Value: " + columnData);

    if (m == 0) {
        whereClause = whereClause + columns.get(m) + " = " +  "'" + columnData + "'";
    } else {
        whereClause = whereClause + " AND " + columns.get(m) + " = " + "'" + columnData + "'";
    }

}
log.info("WHERE CLAUSE: " + whereClause);

whereClause is being logged as this:

WHERE CLAUSE:  CLIENT_END_DT = '9998-12-31' AND CLIENT_START_DT = '2017-04-06' AND FUND_CODE = 'TEST_CODE'

ERROR that I got:

com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near '@P1'.

After google a bit, I noticed that it might be related to how I configure WHERE clause. Any exact problem with the way I use this ps?

score 0 · Answer 1 · 2018-05-09T03:43:25.470

With PreparedStatement you cannot use database object names i.e. table, columns as parameter.

In your sql query, use specific names for both table and columns, in order to avoid SQL injections.

DELETE FROM table_name WHERE column1 = ? and column2 = ?

Perhaps, you may use your custom placeholder for the same as work around, beware of SQL attacks !

config.sql.statement.delete=DELETE FROM $table WHERE $whereClause

Later build your sql as:

String sql = ...; /* your logic */
sql = sql.replace("$table",(schemaName == "F" ? "FUNDS" : "MANDATE" + "." + tableName));
sql = sql.replace("$whereClause",whereClause);
ps=conn.prepareStatement(sql);

PreparedStatement set Where clause

1 Answers1