How to work with Prepared Statements in Java?

Question

I'm trying to update an Entry in my mySQL Database. It works fine with here for example:

        String sql = "SELECT * FROM entries WHERE user_id = ?";

        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setInt(1, currUserId);
        System.out.println("Curr User Id: " + currUserId);
        //Execute Query
        ResultSet rs = stmt.executeQuery();

But here I just always get an Error and dont know why.

        String sql = "UPDATE entries SET ? = ? WHERE user_id = ? AND id = ?";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setString(1, type);
        stmt.setString(2, value);
        stmt.setInt(3, App.connection.currUserId);
        stmt.setInt(4, id);

        int returnValue = stmt.executeUpdate();

This is my Error:

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 ''username' = 'benNeu' WHERE user_id = 13 AND id = 22' at line 1```

you can't set column names in prepared statements only data – nbk Mar 10 '21 at 13:33 — nbk, Mar 10 '21 at 13:33

Mark Rotteveel · Answer 1 · 2021-03-10T13:36:17.783

You cannot parameterize object names, so it is not possible to set a column name through a prepared statement. So instead of SET ? = ?, you will have to use SET yourcolumn = ?, where yourcolumn is the right column name.

If that column is dynamic, you will need to concatenate it into your query string (it is recommended you check the column name against a list of allowed column names to prevent SQL injection issues).

score 1 · Answer 2 · answered Mar 10 '21 at 13:36

The parameter in SET ? = is invalid.

Why?

The JDBC driver and the engine can apply parameters at a limited number of places in the SQL statement. These correspond to where usually you could see literal values.

You cannot use parameters in the place of a table name, a column name (as you are trying to do in your question), and other SQL clauses in general. Remember parameters are applied, they are not concatenated into the SQL statement.

Applying parameters into a SQL statement has two main benefits:

Safety: The query is safe from SQL Injection. If you type a dangerous code segment as a parameter it won't harm the execution; it may fail, but won't delete the whole database because of it.
Performance: The SQL statement will be identical when you call it multiple times with different parameter values. This allows much efficient caching in the engine, and much faster execution.

How to work with Prepared Statements in Java?

2 Answers2