CODEWITHSUNDEEP
javamysqlsyntaxinserttimestamp
ZedIsDead
ZedIsDead

Reputation: 105

Syntax error while INSERT MySql from Java

I get an syntax error while trying to INSERT some values to Mysql from Java.

My code looks like this:

date = new SimpleDateFormat("yyyy-MM-dd").parse(nextLine[0]);
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(date.getTime());
        st.executeUpdate("INSERT INTO " + tick + "(day, open, high, low, close, volume) VALUES (" + sqlDate + ", " + nextLine[1] + ", " + nextLine[2] + ", " + nextLine[3] + ", " + nextLine[4] + ", " + nextLine[5] + ")");

My exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '00:00:00.0, 128.40, 128.50, 127.30, 128.20, 1415200)' at line 1

Would be glad for any help :)

Upvotes: 1

Views: 1871

Answers (3)

Rob Harrop
Rob Harrop

Reputation: 3473

Dates need to be quoted in MySQL so you'll ned to wrap sqlDate with single quotes:

st.executeUpdate("INSERT INTO " + tick + "(day, open, high, low, close, volume) VALUES ('" + sqlDate + "', " + nextLine[1] + ", " + nextLine[2] + ", " + nextLine[3] + ", " + nextLine[4] + ", " + nextLine[5] + ")");

I'm assuming the other values are numeric and therefore are safe without quotes.

The biggest issue with this approach is that you are susceptible to SQL injection attacks. You should switch to using prepared statements instead:

PreparedStatement ps = conn.prepareStatement("INSERT INTO my_table(day, open, high, low, close, volume) VALUES (?,?,?,?,?,?)");
ps.setTimestamp(1, sqlDate);
ps.setBigDecimal(2, nextLine[1]);
...

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1503290

While it would be possible to just fix the immediate syntax error, I would strongly recommend against it. You shouldn't be including the values directly in your SQL at all.

Instead, use a parameterized query via PreparedStatement, and set your values into the parameters instead. In this case, you'd use PreparedStatement.setTimestamp to set the value - after changing the query to be parameterized in the first place, of course.

Benefits of parameterized SQL:

  • Prevention of SQL injection attacks
  • Avoidance of error-prone string conversions (as witnessed in this case)
  • Separation of code and data, leading to SQL which is simpler to read

Upvotes: 6

paulsm4
paulsm4

Reputation: 121849

STRONG SUGGESTION:

1) Create a Java string variable before you call executeUpdate()

2) Make sure your string is syntactically correct (dates quoted, commas between values, etc etc)

ALSO:

"prepare" is your friend ;)

I think you might already be using them ... but you didn't show that part of the code ... and I certainly didn't see any placemarkers ("?")

But I think 1) making the string first, and 2) inspecting the string (e.g. in your debugger) before your JDBC call will be a huge help... IMHO...

Upvotes: 1

Related Questions