Variable column names using prepared statements

I was wondering if there was any way to specify returned column names using prepared statements.

I am using MySQL and Java.

When I try it:

String columnNames="d,e,f"; //Actually from the user...
String name = "some_table"; //From user...
String query = "SELECT a,b,c,? FROM " + name + " WHERE d=?";//...
stmt = conn.prepareStatement(query);
stmt.setString(1, columnNames);
stmt.setString(2, "x");

I get this type of statement (printing right before execution).

SELECT a,b,c,'d,e,f' FROM some_table WHERE d='x'

I would, however, like to see:

SELECT a,b,c,d,e,f FROM some_table WHERE d='x'

I know that I cannot do this for table names, as discussed here, but was wondering if there was some way to do it for column names.

If there is not, then I will just have to try and make sure that I sanitize the input so it doesn't lead to SQL injection vulnerabilities.

Solution 1:

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.

Solution 2:

Prepare a whitelist of allowed column names. Use the 'query' to look up in the whitelist to see if the column name is there. If not, reject the query.

Solution 3:

The accepted answer is not actually correct. While the OP approach indicated a bad DB design, it might be required by the business logic (for instance a MySQL IDE)

Anyway, for MySQL prepared statements, what you need to know is that ? is for values, but if you need to escape column names, table names etc, use ?? instead.

Something like this will work:

SELECT ??, ??, ?? FROM ?? WHERE ?? < ? 

Set values to ['id', 'name', 'address', 'user', 'id', 100]