英文:
Java is throwing a syntax error when I try to execute an SQL query
问题
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;";String updateSecretNumber = "UPDATE secret_number_generator SET secret_number = '" + secretNumber + "' WHERE id = " + id + ";";
英文:
I’m building a Java microservice that uses AWS Aurora MySQL for some operations. The code connects to the database fine and the first three 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 semicolon 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 errorsSystem.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 added a semicolon at the end of them, and now it only shows for the final one.
答案1
得分: 1
由于您已将列secretNumber创建为VARCHAR(20)，在添加它之前，我建议检查secretNumber的长度是否最多为20个字符。
在第30行，您有以下代码：
secretNumber = "R" + env + id + randomDigit;
因此，您可以使用调试器，或在第31行放置以下代码：
if (secretNumber.length() > 20) throw new Exception();
如果这不起作用，您可以尝试在值周围使用单引号，如下所示：
String updateSecretNumber ="UPDATE secret_number_generator " +"SET secret_number = '" + secretNumber + "' " +"WHERE id = '" + id + "'";
此外，单个语句的末尾不需要分号。
英文:
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 semicolon at the end of a single statement is not required.
答案2
得分: 1
以下是翻译好的内容：
前面的示例虽然提供了所需的结果，但在恶意变量值上很容易受损。此版本已经针对SQL注入进行了加固：
使用PreparedStatement，如下所示：
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("更新了 %d 行", count);
英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论