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).

Improve this question
1
  • 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 :-) Commented Jun 30, 2011 at 5:34

3 Answers 3

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 but ... this should really be expanded to at least include a reference/example..
Probably a noob question, but I fail to see an alternative to executeUpdate or executeQuery that takes parameters as input. Where are they?
@narengi: it requires a PreparedStatement as returned by Connection.prepareStatement, you can pass parameters with setInt, setString, etc...
I'd recommend the NamedParamaterJdbcTemplate that comes with spring-jdbc (use of the spring framework is not required).
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

Improve this answer

3 Comments

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.
For the "danger", see: 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.
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.
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.