Syntax error while INSERT MySql from Java

Question

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 :)

Please show your query that is send to the db

juergen d
– juergen d

2012-10-05 20:17:31 +00:00
Commented Oct 5, 2012 at 20:17 — juergen d
– juergen d, Commented Oct 5, 2012 at 20:17

Jon Skeet · Accepted Answer · 2012-10-05 20:19:06Z

6

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

answered Oct 5, 2012 at 20:19

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

3 Comments

ZedIsDead Over a year ago

Thank you. I will take a look at PreparedStatemet and see if i get it! Am totaly new to MySql so.. :P

Jon Skeet Over a year ago

@ZedIsDead: This is something you should be looking to do whenever you use any database from any programming language. If some database/language pair doesn't support it, forcing you to build the SQL directly, that should be a warning sign...

paulsm4 Over a year ago

PS: Is "ZedIsDead" a reference to the Quentin Tarantino movie, or the John Boorman movie "Zardoz" that Quentin Tarantino paid homage to? Or something else entirely?

paulsm4 · Accepted Answer · 2012-10-05 20:18:58Z

1

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 ;)

http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm

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...

answered Oct 5, 2012 at 20:18

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Comments

Rob Harrop · Accepted Answer · 2012-10-05 20:22:01Z

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]);
...

Collectives™ on Stack Overflow

Syntax error while INSERT MySql from Java

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related