9

Trying to get started with JDBC (using Jetty + MySQL). I'm not sure how to escape user-supplied parameters in a SQL statement. Example:

String username = getDangerousValueFromUser();
Statement stmt = conn.createStatement();
stmt.execute("some statement where username = '" + username + "'"));

How do we escape "username" before use with a statement?

Joachim Sauer
  • 302,674
  • 57
  • 556
  • 614
user291701
  • 38,411
  • 72
  • 187
  • 285

1 Answers1

16

Use Prepared statement.

for example : 

con.prepareStatement("update Orders set pname = ? where Prod_Id = ?");
pstmt.setInt(2, 100);
pstmt.setString(1, "Bob");
pstmt.executeUpdate();

It will prevent raw SQL injection

If you want to escape sql string then check for StringEscapeUtils.escapeSql(). Note that that this method was deprecated in Commons Lang 3.

Also See

Community
  • 1
  • 1
jmj
  • 237,923
  • 42
  • 401
  • 438
  • @lvella to escape sql you can use [`StringEscapeUtils.escapeSql()`](http://commons.apache.org/lang/api-2.4/org/apache/commons/lang/StringEscapeUtils.html#escapeSql%28java.lang.String%29), read its doc before you use – jmj Jan 02 '13 at 22:16
  • 2
    @JoachimSauer Well, at that time I indeed needed to know how to escape parameters for an SQL query, and felt frustrated by this answer that it had nothing to do with what I needed (although it was the same thing OP asked). So don't go assuming what does or does not services someone, because it is easy to forget that not everybody does the same kind of programming tasks you are used to. – lvella Jan 02 '13 at 23:50
  • @Ivella: first off: I *honestly* can't imagine a good reason to manually escape data for an SQL statement, could you tell me what it was for you? Second: those cases are rare. Really, really rare. 90% of the time when someone asks for "escaping SQL data in Java" they simply don't know about prepared statement and assume the build-the-sql-string approach is the only one that works. – Joachim Sauer Jan 03 '13 at 08:24
  • @JoachimSauer A use case: you need to generate values that go in a `IN ('a', 'b', 'c')`, where you have say in the order of 5 to 10 values. I agree about your 90% figure (it might even be higher than this). But that leaves 10% of the cases where a `PreparedStatement` doesn't cut it. – avernet May 24 '13 at 23:37
  • 1
    @avernet: you can do that (and I've done it): generate `IN (?, ?, ?)` and call the appropriate `set*()` methods. – Joachim Sauer May 25 '13 at 07:49
  • @JoachimSauer That is a very good point! And now I got some code to change. So I think that 90% figure needs to be revised upwards ;). – avernet May 25 '13 at 19:33
  • 5
    Looking for the same answer - my usecase is calling `BULK INSERT` in SQL Server. This statement **does not accept parameter** for the bulk file name. So that is perfectly valid use case for SQL parameter escaping. But I agree that 99.99% people don't know what they are asking :). http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure – Pavel Horal Oct 15 '13 at 07:29
  • Another use case: constructing long WHERE clasues from SQL fragments. – David Given Mar 03 '17 at 19:33