PreparedStatement troubles in Java

Question

This is my method:

 @Override
public void deleteOneRecord(String tableName, String id) throws ClassNotFoundException, SQLException{
   // Validate the parameters here.

   // String sql = "DELETE FROM " + tableName + " WHERE " + column + "=" + value;
    String pKeyColumnName = "";
   // Statement stmt = conn.createStatement();

    DatabaseMetaData dmd = conn.getMetaData();

    ResultSet rs = dmd.getPrimaryKeys(null, null, tableName);
    while(rs.next()){
        pKeyColumnName = rs.getString("COLUMN_NAME");
        System.out.println("PK column name is " + pKeyColumnName);
    }
    //String sql = "delete from " + tableName + " where " + pKeyColumnName + "=" + id;

    String sql2 = "delete from ? where ?=?";

    PreparedStatement pstmt = conn.prepareStatement(sql2);
    pstmt.setString(1, tableName);
    pstmt.setString(2, pKeyColumnName);
    pstmt.setInt(3, Integer.parseInt(id));

    pstmt.executeUpdate(); 
}

This is my test main:

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    DBStrategy db = new MySqlDBStrategy();
    db.openConnection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/book", "root", "admin");
    System.out.println(db.findAllRecords("author", 0).toString());
    db.deleteOneRecord("author", "2");
    System.out.println(db.findAllRecords("author", 0).toString());
    db.closeConnection();

}

The db object works, open connection works, my find all records method works, then my deleteOneRecord blows up. I get this 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 ''author' where 'author_id'=2' at line 1

Now my syntax hasn't changed, I was running this code as just a Statement no problem a few minutes ago, so I must be using PreparedStatement incorrectly somehow.

Any help would be appreciated greatly.

`?` is only used for column values not for column or table names.. — SatyaTNV, Feb 12 '16 at 05:24
You should concat it `"delete from "+tableName+" where "+pKeyColumnName+"=?"` — SatyaTNV, Feb 12 '16 at 05:26
If the caller knows the table name, the caller should also know the PK column name, so make the caller provide that name, instead of wasting time querying the database for it. Performance will suffer badly, the way you do it (`getPrimaryKey()` is not cheap). — Andreas, Feb 12 '16 at 05:27
Andreas, the instructor wanted us to assume the caller did not know the PK. But that's a nice thing for me to bring up in class. Thanks. — Drewtang, Feb 12 '16 at 05:32
This may also produce... interesting results if getPrimaryKeys returns more than one record. — Ken Clubok, Feb 12 '16 at 05:36
@KenClubok I thought Pk's were one and only one in a table, FK's sure tons, but I thought PK's were only one column per table. Lots I have to learn. — Drewtang, Feb 12 '16 at 05:39
Multicolumn PKs are especially common if you have parent-child relationships. The child will typically have the PK column(s) of the parent, plus an additional one. — Ken Clubok, Feb 12 '16 at 05:40

score 3 · Answer 1 · answered Feb 12 '16 at 05:25

3

I don't believe you can use parameters for the table name or the column name. You'll have to concatenate those into the string. Depending on where they come from, be careful about SQL injection vulnerabilities!

answered Feb 12 '16 at 05:25

Ken Clubok

1,238
6
11

that's what I was trying to avoid. I suppose I can run validation on the one incoming table name to make sure its legit. – Drewtang Feb 12 '16 at 05:28
You can use regex to verify that the variables including nothing but legal characters for column and table names. – Ken Clubok Feb 12 '16 at 05:29
1

@Drewtang I guess, the way you're doing it, the `getPrimaryKeys()` call will find nothing if a bad table name is provided, so as long as you check for that (which you don't), you'd be safe. – Andreas Feb 12 '16 at 05:29
Works now, Thank you Mr. Clubok. – Drewtang Feb 12 '16 at 05:33
just starting on database stuff in Java EE 7 this semester. Andreas, if you have the time and could explain how I'd check for that I'd love to hear it. – Drewtang Feb 12 '16 at 05:36
If rs is empty, then the table didn't exist. Or it exists, but has no PK. – Ken Clubok Feb 12 '16 at 05:37

PreparedStatement troubles in Java

1 Answers1