Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 823
KLee1 Avatar asked Jun 28 '10 20:06

KLee1


People also ask

What is a PreparedStatement explain with suitable examples?

A PreparedStatement is a pre-compiled SQL statement. It is a subinterface of Statement. Prepared Statement objects have some useful additional features than Statement objects. Instead of hard coding queries, PreparedStatement object provides a feature to execute a parameterized query.

Can we create a table with PreparedStatement?

A JDBC PreparedStatement example to create a table in the database. A table 'employee' is created.

What does PreparedStatement setString do?

setString(1, usernameObject); setString(2, privilegeObject); The purpose of PreparedStatement is to reduce the difficulty and readability of the database connection code. when the developer has to use so many column values with Statement's instance it's so difficult to put semicolons, commas and plus (concat operator).

Can I use same PreparedStatement multiple times?

Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.


2 Answers

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.

like image 197
BalusC Avatar answered Sep 20 '22 07:09

BalusC


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.

like image 32
wgl Avatar answered Sep 18 '22 07:09

wgl