52

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.

Josh Correia
  • 3,807
  • 3
  • 33
  • 50
KLee1
  • 6,080
  • 4
  • 30
  • 41

7 Answers7

47

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • 1
    Well, the user doesn't actually need to know the column names, but the column names needed are deduced based on forms submitted by the user. This is handled on the client side, though, so I wanted to see if there was some way to ensure the data is safe. Should I then just move the whole lot to the server-side, thereby ensuring the column data is untainted? – KLee1 Jun 28 '10 at 20:59
  • 3
    Handle it on the server side instead. Don't do business stuff in client side. – BalusC Jun 28 '10 at 21:03
  • 1
    @BalusC : _"you cannot set column names as PreparedStatement values"_ - thats completely made up. Using column names inside of prepared statement value-lists is of course possible - but that doesnt mean it should be used that way, it still is bad design. – specializt May 23 '15 at 12:55
  • 1
    I'd like to see you dynamically limit fields for a given resource using REST with JSON. Not everybody wants the whole resource returned, and not everybody wants to create 1,000 queries for every permutation of said columns. – npn_or_pnp Mar 23 '16 at 21:51
  • 1
    What about dynamic ordering for a query utilizing variable column name? Would that also be considered bad design? –  Oct 26 '17 at 09:14
20

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.

wgl
  • 301
  • 2
  • 3
10

For MySQL prepared statements with NodeJS (mysqljs/mysql), 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]

DraganescuValentin
  • 842
  • 2
  • 10
  • 16
  • 3
    It's a very interesting comment. I couldn't find any reference to that escaped parameter value syntax (??). Could you, possibly, point me to one? – where_ Mar 09 '20 at 08:34
  • I don't remember where I've seen this, definitely not in the official docs. However, I'm using this successfully in a toy project. – DraganescuValentin Mar 12 '20 at 20:30
  • I think this is a specified usage for Node.js packaged named [mysqljs/mysql](https://stackoverflow.com/questions/30457086/what-is-the-difference-between-single-and-double-question-mark-in-n) – Yami Odymel Oct 21 '21 at 04:10
  • 2
    @YamiOdymel Indeed this might only work on the mysqljs/mysql Node.js package. It looks like the select is not valid when run from MySQLWorkbench This statements do not work because `??` is not recognised `PREPARE stmt1 FROM 'SELECT ??, ?? FROM ?? WHERE ?? = ?'; SET @a = 'foo'; SET @b = 'bar'; SET @c = 'Foos'; SET @d = 'foo'; SET @e = 'fooooo'; EXECUTE stmt1 USING @a, @b, @c, @d, @e;` – DraganescuValentin Oct 21 '21 at 08:42
3

I think this case can't work because the whole point of the prepared statement is to prevent the user from putting in unescaped query bits - so you're always going to have the text quoted or escaped.

You'll need to sanitize this input in Java if you want to affect the query structure safely.

G__
  • 7,003
  • 5
  • 36
  • 54
  • 1
    You're right on the "it can't work". However the first reason for PreparedStatement was because of resource efficiency, allowing to keep a statement cached and submitting it multiple times just changing the values (great especially for OLTP). Its resilience to SQL Injection attempts is a very desiderable side effect. – Insac Dec 20 '16 at 20:51
-1

Use sql injection disadvantage of Statement Interface as advantage. Ex:

st=conn.createStatement();
String columnName="name";
rs=st.executeQuery("select "+ columnName+" from ad_org ");
Nissa
  • 4,636
  • 8
  • 29
  • 37
Syed Abdul Khaliq
  • 17
-1
public void MethodName(String strFieldName1, String strFieldName2, String strTableName)
{
//Code to connect with database
String strSQLQuery=String.format("select %s, %s from %s", strFieldName, strFieldName2, strTableName);
st=conn.createStatement();
rs=st.executeQuery(strSQLQuery);
//rest code
}
Grbh Niti
  • 49
  • 1
  • 1
    The question is about leveraging preparedStatement(). Your solution doesn't use it. It is prone to sql injection. – nanosoft May 10 '19 at 16:05
  • 1
    Ouch! SQL code generation by string concatenation without escaping! Please delete this before you boss recognizes, that you wrote this. – ceving Aug 25 '20 at 07:23
-5

Below is the solution in java.

String strSelectString = String.format("select %s, %s from %s", strFieldName, strFieldName2, strTableName);
Rob
  • 26,989
  • 16
  • 82
  • 98
Grbh Niti
  • 49
  • 1