6

Is there a Java library for escaping special characters from a string that is going to be inserted into an SQL query.

I keep writing code to escape various things, but I keep finding some new issue trips me up. So a library that takes care of all or most of the possibilities would be very handy.

EDIT: I am using MySQL (if that makes any difference).

Ankur
  • 50,282
  • 110
  • 242
  • 312
  • 2
    NO. STOP. USE PARAMETERS. Really. The only case where escaping needs to be done is in *very* limited cases of dynamic SQL buidling -- and even then it is generally just the *structure* (e.g. columns/tables) elements and not the *data* itself. Sorry for shouting. Trying to eradicate the mindset of using string building (and thus escaping) for SQL :-) –  Jun 30 '11 at 05:34

3 Answers3

7

Well... jdbc. Pass the strings as parameters, and don't append them to the query string

Maurice Perry
  • 32,610
  • 9
  • 70
  • 97
  • 3
    +1 but ... this should really be expanded to at least include a reference/example.. –  Jun 30 '11 at 05:48
  • Probably a noob question, but I fail to see an alternative to executeUpdate or executeQuery that takes parameters as input. Where are they? – narengi Jan 04 '13 at 17:42
  • @narengi: it requires a PreparedStatement as returned by Connection.prepareStatement, you can pass parameters with setInt, setString, etc... – Maurice Perry Jan 05 '13 at 19:05
  • 1
    I'd recommend the NamedParamaterJdbcTemplate that comes with spring-jdbc (use of the spring framework is not required). – Kimball Robinson Sep 26 '13 at 19:17
1

A little bit more research points me to this:

http://devwar.blogspot.com/2010/06/how-to-escape-special-characters-in.html

Which suggests to use apache.commons.lang.StringEscapeUtils, I will try this out

Ankur
  • 50,282
  • 110
  • 242
  • 312
  • 2
    **DANGER. THAT IS NOT INJECTION SAFE.** That won't always work. The problem is the *locale* is important to "safely" escape the string in MySQL. This is why PHP introduces `mysql_real_escape_string` when there was already a `mysql_escape_string`. The former tries to "fix" the approach by taking the locale into account (and it *still doesn't always work*.) I won't down-vote because this is your own answer -- although it should be a supplement to the original post -- but it's *not a good approach*. –  Jun 30 '11 at 05:41
  • For the "danger", see: [Preventing SQL Injection Attacks with MySQL and PHP](http://www.phpgenious.com/2009/01/preventing-sql-injection-attacks-with-mysql-and-php/) and note: "*The character set is ignored by mysql_escape_string, which can leave some vulnerabilities ope[n] for sophisticated hackers*." If a given life-vest is going to *fail in certain situations*, consider choosing a different life-vest at the store. It might just be important. –  Jun 30 '11 at 06:07
  • Actually, the method in `StringEscapeUtils` won't even work as much as `mysql_escape_string`. It will promptly fail if the data contains a newline, for instance. There are also other "basic" characters that need to be escaped that are missed. –  Jun 30 '11 at 06:15
0

I know this is a long time thread, but using the commonslang library there is a method called escapeSql(String). Also using prepared statement automatically escape the offending SQL character.

Ryan Webb
  • 334
  • 1
  • 2
  • 7