The issue is when trying to update data with a string such as (this is a snippet of the html)

The issue I am receiving is

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 e's bla, blablablabla (one of blablabla' at line 1

You don't put the data into the SQL string. You use ? placeholders and supply the data with methods. This not only eliminates silly syntax problems like you are having, it also helps to protect against SQL injection attacks.

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137

posted Nov 10, 2011 13:08:34

0

Ok thanks, will have a good read up on prepared statements and check back if I have anymore issues.

Thanks
Michael

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137

posted Nov 23, 2011 13:38:50

0

Hi BearBibeault,

Just been reading up about PreparedStatements and they seem straight forward enough, so I will go and implement those.

But I couldn't see any explanations as to 'why' they are more secure and help prevent SQL Injection attacks. Have you come across any info behind how they prevent SQL Injection attacks? The way I am currently looking at is it that even if you replace the "?" with a String, then the String could still contain text such as - ';drop table x;

I am guessing that when data is added to the PreparedStatement object via the .setString method, then something is happening in the background which strips out any dodgy characters?

When looking through the Java books I have on my shelf, I am extremely surprised that only 1 of the 4 I looked through mentions PreparedStatements at all.

The database driver doesn't just do a naive string concatenation. It does the escaping of characters and the formatting of data which is required by the database it is designed to work with. For example in most databases you escape a quote character (like the one which did you in) by replacing it by two quote characters. So you can write a method which does that, and litter your code with calls to that method, or you can let the database driver do it for you.

Your method would have to deal with other things besides quotes, and it would be database-dependent, because different databases do it differently. Especially in the case of dates and timestamps. And so all of that ugly business is encapsulated in the database driver, which applies it to the parameters of PreparedStatement objects. That's a huge bonus to the programmer.

And I agree: I think the tutorials and books should start with PreparedStatement, and relegate Statement to the lunatic fringe, the place where you have to do something which is highly database-specific and which can't be done with a PreparedStatement.

Michael Cropper
Ranch Hand

Joined: Sep 30, 2009
Posts: 137

posted Nov 23, 2011 14:24:01

0

Thanks for the great explanation Paul, it all makes sense now :-)

Now all I need to do is re-code an awful lot of code to use PreparedStatements instead (dam those books / tutorials I read!). Shouldn't take me long :-s