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
Since you have created the column
secretNumber
as aVARCHAR(20)
I would check to make suresecretNumber
is at most 20 characters long before adding it.On line 30 you have the following.
So, you can either use the debugger, or on line 31 you can put the following.
If that doesn’t work, you can try single-quotes around the values, I guess.
Additionally, a semi-colon at the end of a single statement is not required.
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: