How should I sanitize database input in Java?

Could someone please point me to a good beginner guide on safely running SQL queries formed partly from user input? I’m using Java, but a language neutral guide is fine too.

The desired behaviour is that if someone types into the GUI something like

very nice;) DROP TABLE FOO;

The database should treat it as a literal string and store it safely without dropping any tables.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You definitely want to use PreparedStatements. They are convenient. Here is an example.

Method 2

Use PreparedStatement instead of Statement

Method 3

Normally, you shouldn’t create a query concatenating input, but using PreparedStatement instead.

That lets you specify in which places you’ll be setting your parameters inside your query, so Java will take care of sanitizing all inputs for you.

Method 4

PreparedStatement? Yes, absolutely. But I think there’s one more step: validation of input from UI and binding to objects prior to getting close to the database.

I can see where binding a String in PreparedStatement might still leave you vulnerable to a SQL injection attack:

String userInput = "Bob; DELETE FROM FOO";
String query = "SELECT * FROM FOO WHERE NAME = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, userInput);

I’ve gotta admit that I haven’t tried it myself, but if this is remotely possible I’d say PreparedStatement is necessary but not sufficient. Validating and binding on the server side is key.

I’d recommend doing it with Spring’s binding API.

Method 5

Your user input would actually have to be "Bob'; delete from foo; select '" (or something like that) so the implicit quotes added by the prepared statement would be closed:

SELECT * FROM FOO WHERE NAME = 'Bob'; delete from foo; select ''

but if you do that the prepared statement code will quote your quote so you get an actual query of

SELECT * FROM FOO WHERE NAME = 'Bob''; delete from foo; select '''

and your name would be stored as "Bob', delete from foo; select '" instead of running multiple queries.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x