How should I sanitize database input in Java?

Question

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.

score 10 · Accepted Answer · edited Aug 09 '16 at 22:44

10

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

edited Aug 09 '16 at 22:44

Tharwen

3,057
2
24
36

answered Mar 26 '09 at 22:30

Josh Stodola

81,538
47
180
227

score 7 · Answer 2 · edited Aug 10 '16 at 00:02

7

Use PreparedStatement instead of Statement

edited Aug 10 '16 at 00:02

Tharwen

3,057
2
24
36

answered Mar 26 '09 at 22:27

Geo

93,257
117
344
520

score 5 · Answer 3 · edited Aug 09 '16 at 23:46

5

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.

edited Aug 09 '16 at 23:46

Tharwen

3,057
2
24
36

answered Mar 26 '09 at 22:29

Seb

24,920
5
67
85

score 3 · Answer 4 · answered Mar 26 '09 at 22:47

3

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);
ps.executeQuery();

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.

answered Mar 26 '09 at 22:47

duffymo

305,152
44
369
561

This is exactly the kind of attack that prepared statements are meant to protect against. It will escape the ;. – danieltalsky Mar 26 '09 at 22:48
Technically the PreparedStatement interface does not guarantee it. Although if your driver does something different, find a new vendor. – Tom Hawtin - tackline Mar 26 '09 at 23:05
@daniel - I'll have to try it and see for myself. Whether I'm correct about that or not, do you not agree that server-side validation is still a good idea? – duffymo Mar 26 '09 at 23:13
@duffymo: Of course data validation is a good idea, both for security and for application logic. But server-side prepared statements should keep parameter values separate from the SQL syntax throughout the query execution. They are never interpolated into the query, so no escaping is necessary. – Bill Karwin Mar 26 '09 at 23:26
1

"should" - there's the key word. – duffymo Mar 27 '09 at 01:43
But prepared statements have inconvenience w.r.t to IN clause/ dynamically appending `AND` conditions based on availability of Input, ORDER BY clause etc, which might lead to performance drawbacks / code readability issues. Is there an alternative for that ? – user9920500 Mar 11 '20 at 06:20
None that I know of. I doubt that your performance concerns are measurable. This answer is 11 years old. Ask a new question if you must. Your comment isn't helpful. – duffymo Mar 11 '20 at 09:44

score 3 · Answer 5 · edited Apr 20 '15 at 00:45

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.

How should I sanitize database input in Java?

5 Answers5

Linked