0

Is there a limit to PreparedStatement variables (?) or requirements for their placement?

I have a method that takes in the parameters to complete a PreparedStatement however, it throws a SQLException.

Here is the PreparedStatement I want to use:

String update = "UPDATE ? SET ? = ? WHERE UserID = ?";

When I add in the first and second variables it runs just fine. Here is the working PreparedStatement:

String update = "UPDATE Student SET First_Name = ? WHERE UserID = ?";

Is there a reason I cannot get the first statement to work?


The entire method:

public static void runUpdate(String givenID, String givenAttribute, String givenUpdate) throws SQLException
{
    // Establish the connection with the database
    Connection conn = SimpleDataSource.getConnection();
    try
    {
        // Create a string with the SQL Update statement                       
        String update = "UPDATE ? SET ? = ? WHERE UserID = ?";

        // Make a Prepared Statement with the connection to the database
        PreparedStatement stat = conn.prepareStatement(update);
        try
        {
            // Set the statement with the given parameters
            stat.setString(1, Utility.getType(givenID));
            stat.setString(2, givenAttribute);
            stat.setString(2, givenUpdate);
            stat.setString(3, givenID);
            // Execute the Update Statement
            stat.executeUpdate();
        }
        finally
        {
            // Close the prepared Statement
            stat.close();
        }
    }
    finally
    {
        // Close the connection to the database
        conn.close();
    }
}
  • 1
    You can't use query parameters for table/column names. Wanting to do so usually indicates that you have a poor (unnormalized) schema (or are writing an ORM). – Colonel Thirty Two Nov 26 '15 at 23:06
  • If you're worried about SQL injection, then that means you're getting table and database names from the user, which is probably a really bad idea no matter what. If you're not getting the table name from the user, then there's no injection risk in string concatenation. If you want to really be sure, create a HashSet with whitelisted table names. – Cruncher Nov 26 '15 at 23:15
  • The table name is found based off of the userId and the column is given as a parameter. I have many "get" methods and am trying to limit the amount of redundant code. –  Nov 26 '15 at 23:20

2 Answers2

0

You can't use the query like this.

String update = "UPDATE ? SET ? = ? WHERE UserID = ?";

You should write the name of table and the name of the column like here.

 String update = "UPDATE table SET column_name = ? WHERE UserID = ?";
Abdelhak
  • 8,299
  • 4
  • 22
  • 36
0

You can use variables in prepared statements only as placeholder of literals in SQL statements. So you cannot use them for column name, or table names. For these you should resort to dynamic SQL statements.

Renzo
  • 26,848
  • 5
  • 49
  • 61