Java PreparedStatement SQL syntax error [duplicate]

Question

This is a really weird error that only started appearing today. When I use a prepared statement with ? for parameters, I get an error, but when I use it without parameters, it works just fine. Here is the error-causing code:

    String table = "files";
    Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
    PreparedStatement prep = conn.prepareStatement("SELECT * FROM ?");
    prep.setString(1, table);
    ResultSet rs = prep.executeQuery();

    while(rs.next()) {
        System.out.println(rs.getString("file_name"));
    }

This produces the following error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''files'' at line 1

Also, changing it to the following works just fine:

    String table = "files";
    Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
    PreparedStatement prep = conn.prepareStatement("SELECT * FROM " + table);
    ResultSet rs = prep.executeQuery();

    while(rs.next()) {
        System.out.println(rs.getString("file_name"));
    }

This doesn't seem to be making a whole lot of sense. Any ideas?

---

Tried it on another table and got more weired results. This works and logs the admin in correctly:

    String sql = "SELECT * FROM " + ADMIN_AUTH_TABLE + " WHERE " + column + " = '" + hashedPassword + "'";
    PreparedStatement prepared = connection.prepareStatement(sql);

The following doesn't cause errors, but returns a message saying that the password entered is incorrect (it's correct - I double triple checked).

    String sql = "SELECT * FROM " + ADMIN_AUTH_TABLE + " WHERE ? = ?";
    PreparedStatement prepared = connection.prepareStatement(sql);
    prepared.setString(1, column);
    prepared.setString(2, hashedPassword);

---

Got it: use ? for values. Also, the answer here helped.

Answer 1

Bind parameters cannot be used for identifiers in the SQL statement. Only values can supplied through bind placeholders.

This will work:

SELECT foo FROM bar WHERE id = ? 

This will not work, because the table name is an identifier

SELECT foo FROM ? WHERE id = 2

You can't supply a column name, because column names are also identifiers. A statement like this will run, but it may not do what you think it does.

SELECT ? AS foo FROM bar WHERE ? = 0

If we supply values of 'foo' for both placeholders, the query will actually be equivalent to a query containing two string literals:

SELECT 'foo' AS foo FROM bar WHERE 'foo' = 0

MySQL will run that statement, because it's a valid statement (if the table bar exists and we have privileges on it.) That query will return every row in bar (because the predicate in the WHERE clause evaluates to TRUE, independent of the contents of the table.. And we get returned the constant string foo.

It doesn't matter one whit that the string foo happens to match the name of column in our table.

---

This restriction has to do with how the SQL optimizer operates. We don't need to delve into all the details of the steps (briefly: parsing tokens, performing syntax check, performing semantics check, determining query plan, and then the actual execution of the query plan.)

So here's the short story: The values for bind parameters are supplied too late in that process. They are not supplied until that final step, the execution of the query plan.

The optimizer needs to know which tables and columns are being referenced at earlier stages... for the semantics check, and for developing a query plan. The tables and columns have to be identified to the optimizer. Bind placeholders are "unknowns" at the time the table names and column names are needed.

(That short story isn't entirely accurate; don't take all of that as gospel. But it does explain the reason that bind parameters can't be used for identifiers, like table names and column names.)

tl;dr

Given the particular statement you're running, the only value that can be passed in as a bind parameter would be the "hashedPassword" value. Everything else in that statement has to be in the SQL string.

For example, something like this would work:

String sqltext = "SELECT * FROM mytable WHERE mycolumn = ?";
PreparedStatement prepared = connection.prepareStatement(sqltext);
prepared.setString(1, hashedPassword);

To make other parts of the SQL statement "dynamic" (like the table name and column name) you'd have to handle that in the Java code (using string concatenation.) The contents of that string would need to end up like the contents of the sqltext string (in my example) when it's passed to the prepareStatement method.

Answer 2

The parameters of PreparedStatement should be applied only in parameters that can be used in conditional clauses. The table name is not the case here.

If you have a select where the table name can be applied in the conditional clause you can do it, otherwise you can not.