skip to Main Content

Im building a java microservice that uses AWS Aurora Mysql for some operations. the code connects to the database fine and the first 3 queries I had were throwing a similar error till I realized I was missing semicolons. The final query (the update statement) however is still giving me the same syntax error even with the semi colon and I can’t seem to figure out why.

here is the code below

try {
            conn = DriverManager.getConnection(jdbcUrl);

            setupStatement = conn.createStatement();
            idInsertStatement = conn.createStatement();
            secretNumberUpdateStatement = conn.createStatement();

            String createTableIfNotExists = "CREATE TABLE IF NOT EXISTS secret_number_generator(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, secret_number VARCHAR(20);";

            String generateSecretId = "INSERT INTO secret_number_generator VALUES(null,null);";
            
            String getLastRecordId = "SELECT id FROM secret_number_generator ORDER BY id DESC LIMIT 1;";
            
            setupStatement.addBatch(createTableIfNotExists);
            idInsertStatement.addBatch(generateSecretId);
            setupStatement.executeBatch();
            idInsertStatement.executeBatch();
            readStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = readStatement.executeQuery(getLastRecordId);
         
            while(resultSet.next()){
                id = String.valueOf(resultSet.getLong("id"));
            }

            /*
              Logic for setting up secretNumber
            */

            }
            secretNumber = "R" + env + id + randomDigit;
            
            String updateSecretNumber = "UPDATE secret_number_generator SET secret_number = " + secretNumber + " WHERE id = " + id + ";";
            
            secretNumberUpdateStatement.addBatch(updateSecretNumber);
            secretNumberUpdateStatement.executeBatch();

            resultSet.close();
            setupStatement.close();
            idInsertStatement.close();
            secretNumberUpdateStatement.close();
            readStatement.close();
            conn.close();

        } catch (SQLException ex) {
            // Handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } finally {
            System.out.println("Closing the connection.");
            if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
        }

The error I am getting for that update query is

SQLException: 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 '' at line 1

as mentioned the same error was showing for the previous queries before I add a semicolon at the end of them, now only shows for the final one.

2

Answers


  1. Since you have created the column secretNumber as a VARCHAR(20) I would check to make sure secretNumber is at most 20 characters long before adding it.

    On line 30 you have the following.

    secretNumber = "R" + env + id + randomDigit;
    

    So, you can either use the debugger, or on line 31 you can put the following.

    if (secretNumber.length() > 20) throw new Exception();
    

    If that doesn’t work, you can try single-quotes around the values, I guess.

    String updateSecretNumber = 
       "UPDATE secret_number_generator " +
       "SET secret_number = '" + secretNumber + "' " +
       "WHERE id = '" + id + "'";
    

    Additionally, a semi-colon at the end of a single statement is not required.

    Login or Signup to reply.
  2. While the previous example gives you the desired result it can easily break on malicious variable values. This version is hardened against SQL injection:

    Use a PreparedStatement like so:

    PreparedStatement ps = connection.prepareStatement(
        "UPDATE secret_number_generator " +
        "SET secret_number = ? " +
        "WHERE id = ?";
    ps.setString(1, secretNumber);
    ps.setString(2, id);
    int count = ps.executeUpdate();
    System.out.println("updated %d rows", count);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search