1.8.8 Error in SQL syntax using PreparedStatement

Discussion in 'Spigot Plugin Development' started by PvPNiK, Feb 4, 2020.

  1. Hello, I used PreparedStatement a year ago on an old project.
    now I am using the same code.
    Code (Java):
        public boolean update(Database database, String table, String key, Object value, String whereKey, Object whereValue) {
            Connection con = SyrexCore.communicationManager.get(database).getCon();

            try {
                PreparedStatement ps = con.prepareStatement("UPDATE ? SET ? = ? WHERE ? = ?");
                ps.setString(1, table.toLowerCase());
                ps.setString(2, key.toLowerCase());
                ps.setObject(3, value);
                ps.setString(4, whereKey.toLowerCase());
                ps.setObject(5, whereValue);
                ps.executeUpdate();
                return true;
            } catch (SQLException var9) {
                var9.printStackTrace();
                return false;
            }
        }
    But I get this error:
    https://pastebin.com/zNdsatby

    Where is my mistake?
     
  2. I don't think wins should be in quotes
     
  3. Neither should soup and id, IIRC, only the values can be ? and completed afterwards, the table name and column names must be specified.
    You're also missing a semi-column at the end (not sure if it matters)
     
    • Agree Agree x 1
  4. thanks!
    so in my code, only "value" and "whereValue" will be "?" because they object (meaning values),
    and all the other arguments will not be "?". because they are strings (table name, column name and the key)
     
  5. The syntax is incorrect because you're using parameters for the columns (as mentioned in earlier comment) - Why this happens is because PreparedStatement escapes all values set with it (adds quotes to strings etc) to avoid an SQL Injection.

    Unfortunately only way to dynamically change the column name that I know of (like attempted here) is by making it part of the sql string.
    Great care needs to be taken to ensure that the string inserted into the sql doesnt come from any user (to avoid sql injection)
     
    #5 AuroraLS3, Feb 5, 2020
    Last edited: Feb 5, 2020
  6. You cannot use '?' to replace table names and keys
    because they should be like this `table` but not 'table'