SQL error or missing database (near "?": syntax error)

Question

Would you be that kind and tell me whats wrong in here? conn is DriverManager.getConnection(DB_URL)

        try {
            PreparedStatement prepStmt = conn.prepareStatement(
                    "UPDATE week SET ?=? WHERE id>=? AND id<=?");
            prepStmt.setString(1, s);
            prepStmt.setFloat(2, x);
            prepStmt.setInt(3, c);
            prepStmt.setInt(4, d);
            prepStmt.executeUpdate();
        } catch(SQLException e) {
            System.err.println("Error during data update");
            e.printStackTrace();
        }

Error is in first line of "try" and it goes like "SQL error or missing database (near "?": syntax error)". I have to add that when I put this statement in cmd with "?" substituted with values it works as charm.

use PreparedStatement prepStmt = conn.prepareStatement( "UPDATE week SET "+s+"=? WHERE id>=? AND id<=?"); — Braj
– Braj, Commented Nov 20, 2014 at 13:52

Eran · Accepted Answer · 2014-11-20 13:49:10Z

2

You can't pass column names as parameters to the prepared statement. You can only pass values as parameters :

    try {
        PreparedStatement prepStmt = conn.prepareStatement(
                "UPDATE week SET some_column_name=? WHERE id>=? AND id<=?");
        prepStmt.setFloat(1, x);
        prepStmt.setInt(2, c);
        prepStmt.setInt(3, d);
        prepStmt.executeUpdate();
    } catch(SQLException e) {
        System.err.println("Error during data update");
        e.printStackTrace();
    }

answered Nov 20, 2014 at 13:49

Eran

395k57 gold badges726 silver badges793 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brlaranjeira · Accepted Answer · 2014-11-20 13:54:14Z

1

You cannot pass a column name to the PreparedStatement. What you could do to overcome this is change it in the string.

try {
    PreparedStatement prepStmt = conn.prepareStatement(
    "UPDATE week SET " + s + " =? WHERE id>=? AND id<=?");
    prepStmt.setFloat(1, x);
    prepStmt.setInt(2, c);
    prepStmt.setInt(3, d);
    prepStmt.executeUpdate();
} catch(SQLException e) {
    System.err.println("Error during data update");
    e.printStackTrace();
}

answered Nov 20, 2014 at 13:54

brlaranjeira

3672 silver badges11 bronze badges

3 Comments

Tomasz Mlynarczyk Over a year ago

Ok but can't i just do it like this: 'String stmt="UPDATE week SET "+s+" = "+String.valueOf(x)+" WHERE id>= "+String.valueOf(c)+" AND id<="+String.valueOf(d);'

Tomasz Mlynarczyk Over a year ago

or even better: 'String stmt="UPDATE week SET "+s+" = "+x+" WHERE id>= "+c+" AND id<= "+d;'?

brlaranjeira Over a year ago

You can do it, but it is a good practice to pass parameters, it helps preventing SQL Injection (not quite sure... someone correct me if I'm wrong, please). Also, looking at the code I've posted, building the string this way may be a potential security flaw. Try doing it statically, using a flag to define the column name. E.g if (foo_flag) s= "column_1" (...)

brso05 · Accepted Answer · 2014-11-20 13:49:04Z

0

You should not do ? = ? you should specify the field id = ?...or whatever field you want there.

PreparedStatement prepStmt = conn.prepareStatement(
                    "UPDATE week SET columnName=? WHERE id>=? AND id<=?");

answered Nov 20, 2014 at 13:49

brso05

13.3k2 gold badges25 silver badges41 bronze badges

Comments

MiladiuM · Accepted Answer · 2018-07-18 12:48:44Z

As it was mentioned you can not pass column names or sql syntax to the PreparedStatement. however you can use String.format() to pass anything to your sql:

try {
                String sql = "UPDATE week SET %s=? WHERE id>=? AND id<=?";
                sql = String.format(sql , "s")
                PreparedStatement prepStmt = conn.prepareStatement(sql);
                prepStmt.setFloat(1, x);
                prepStmt.setInt(2, c);
                prepStmt.setInt(3, d);
                prepStmt.executeUpdate();
            } catch(SQLException e) {
                System.err.println("Error during data update");
                e.printStackTrace();
            }

sa you can see ? = ? is replaced with %s = ? and later the column name was replaced with String.format and later the String was passed to the PreparedStatement. however this should not be used pass data because it defies the purpose of PreparedStatements.

Collectives™ on Stack Overflow

SQL error or missing database (near "?": syntax error)

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related