Sqlite + java, weird behavior with prepared statement

Question

I met a problem I can't really explain, using java/sqlite:

    String sql = "Select date, type FROM line ORDER BY ?";
    PreparedStatement st = DB.getConnexion().prepareStatement(sql);
    st.setString(1, sort);
    ResultSet rs = st.executeQuery();

This piece of code gives me the wrong order (default order) [and trust me I checked and rechecked that the parameter was good]

    String sql = "Select date, type FROM line ORDER BY " + sort;
    PreparedStatement st = DB.getConnexion().prepareStatement(sql);
    //st.setString(1, sort);
    ResultSet rs = st.executeQuery();

And this one produces the expected result. I'm a bit at loss here, this makes no sense to me. I tried to restart eeclipse, rebuild project, tested my request directly from sqlite browser, checked the parameter and results at every possible place, but it seems that sqlite's setString() function doesn't properly attributes my parameter, and it doesn't even crash or produce an error.

I'm either missing something really stupid or there's something very wrong going on here.

Possible duplicate of [Using a prepared statement and variable bind Order By in Java with JDBC driver](http://stackoverflow.com/questions/12430208/using-a-prepared-statement-and-variable-bind-order-by-in-java-with-jdbc-driver) — Gurwinder Singh, Jan 01 '17 at 19:53

score 3 · Accepted Answer · answered Jan 01 '17 at 19:57

3

It's actually surprise that this syntax is accepted by the SQL engine. It can probably fail in some others.

Anyway, the sort is interpreted as a string constant, so all the rows will have same value for the sort algorithm. Therefore, as they have the same value, it keeps the original order.

You cannot use such dynamic statement, the only way is the second solution which appends the sort directly to statement string.

answered Jan 01 '17 at 19:57

Zbynek Vyskovsky - kvr000

18,186
3
35
43

I was so obstinate I didn't realise I wasn't dealing with data, a facepalm for me and a thank you for you. I wish it had crashed and called me an idiot. – fen Jan 01 '17 at 22:18

score 1 · Answer 2 · answered Jan 01 '17 at 20:06

That's because you're telling SQL that the parameter is a string. Let's say the parameter is Name. Your statement is going to be something like this:

Select date, type FROM line ORDER BY 'Name'

This might technically be valid SQL, but it's not going to order the columns usefully - it's not ordering by the Name column, it's literally ordering by 'Name', which is always the same constant string.

I don't think you can use parameters for this. You'll have to change the statement itself to have the sort column you want. You want to be sure that you don't allow an injection vulnerability when you do this, of course, so you probably don't want to just stick whatever's in the variable into the statement. You may want to have a whitelist that includes the possible columns in the table, and reject anything that isn't one of those columns.

Sqlite + java, weird behavior with prepared statement

2 Answers2