Error in the syntax of the SQL insert query using variables

Question

While trying to insert a record in the sql table I am using 10 variables for 10 columns in the table, but as when I run the query it throws an error. I have tried looking if there is any typo in my code but can't find any:

Exception in thread "main" java.sql.SQLSyntaxErrorException: 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 '','2019','12','3','10660','2018-12-11','UTC',''{"sleepIQScore": {"min": 0, "max"' at line 1

My code is:

public void insertDataTable1() throws SQLException {
    connection = new MyConnection().getConnection();
    Random random = new Random();
    String timeZone = "UTC";
    String dummyJson = "'{\"sleepIQScore\": {\"min\": 0, \"max\": 0, \"sum\": 0, \"count\": 0}}')";
    int longestSessionDuration = 1000 + random.nextInt(9999), bamUserID = 1000000 + random.nextInt(9999999);
    int year = 2019, month = 12, sleepSessionCount =3;
    java.sql.Date longestSessionStart = new java.sql.Date(Calendar.getInstance().getTime().getTime());

    String sql = "INSERT INTO aggregates_all_time(bam_user,year,month,sleep_session_count," +
            "longest_session_total_duration,longest_session_start,timezone, current_stats, second_longest_session_stats, prior_stats)"
            + "VALUES ("+bamUserID+"','"+year+"','"+month+"','"+sleepSessionCount+"','"+longestSessionDuration+"','" +
            ""+longestSessionStart+"','"+timeZone+"','"+dummyJson+" ','"+dummyJson+"','"+dummyJson+")";

    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    int i = preparedStatement.executeUpdate();
    System.out.println(i + " Row/s inserted");

}

In "VALUES ("+bamUserID+"','" you can probably remove the first single quote — Steve T
– Steve T, Commented Dec 11, 2018 at 19:48
This is not how you are supposed to use prepared statements. Do not concatenate values into a query string. It is unsafe and/or leads to bugs like your question. Use parameters instead. — Mark Rotteveel
– Mark Rotteveel, Commented Dec 11, 2018 at 19:49
Avoid concatenating strings. Use a PreparedStatement instead. Your code is not safe agains SQL injection. — The Impaler
– The Impaler, Commented Dec 11, 2018 at 19:49

Mark Rotteveel · Accepted Answer · 2018-12-11 19:55:53Z

2

You should never concatenate values into a query string like this. It is unsafe because it opens your application to SQL injection which is one of the major causes of security issues. In addition, it leads to bugs like your question because of missing quotes, etc.

However, the solution is not to add those missing quotes, because that still leaves you open to SQL injection risks. Instead, you need to use parameters and set the values for those parameters before execution.

A reduced example based on the query in your question:

try (PreparedStatement preparedStatement = connection.prepareStatement(
        "INSERT INTO aggregates_all_time(bam_user, year, month, ..) values (?, ?, ?, ..)")) {
    preparedStatement.setInt(bamUserID);
    preparedStatement.setInt(year);
    preparedStatement.setInt(month);
    // other values

    preparedStatement.executeUpdate();
}

See also Using Prepared Statements in the JDBC tutorial.

answered Dec 11, 2018 at 19:55

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

db07 Over a year ago

Thank you so much Mark.

Collectives™ on Stack Overflow

Error in the syntax of the SQL insert query using variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related