1

Is there a built-in method to escape a string for SQL? I would use setString, but it happens I am using setString multiple times in the same combined SQL statement and it would be better performance (I think) if the escape happened only once instead of each time I say setString. If I had the escaped string in a variable, I could re-use it.

Is there no way to do this in Java?

Current method, multi-source search. In reality they are three entirely different where statements including joins, but for this example I will just show the same where for each table.

String q = '%' + request.getParameter("search") + '%';
PreparedStatement s = s("SELECT a,b,c FROM table1 where a = ? UNION select a,b,c from table2 where a = ? UNION select a,b,c FROM table3 where a = ?");
s.setString(1, q);
s.setString(2, q);
s.setString(3, q);
ResultSet r = s.executeQuery();

I know this is not a big deal, but I like to make things efficient and also there are situations where it is more readable to use " + quote(s) + " instead of ? and then somewhere down the line you find setString.

Improve this question
3
  • What characters are you trying to escape? Commented Jul 13, 2011 at 14:35
  • It would be helpful for you to show us some of your code. Commented Jul 13, 2011 at 14:43
  • @RMT: All characters escaped by setString I do not want the possibility of an injection attack or syntax error due to the value of search. Commented Jul 13, 2011 at 14:58

4 Answers 4

Reset to default
5

If you use setString for a parameter (e.g. PreparedStatement.setString), there may well be no actual escaping required - it's likely that the data will be passed separately from the SQL itself, in a way that doesn't require escaping.

Do you have any concrete indication that this really is a performance bottleneck? It seems very unlikely that within a database query, the expensive part is setting the parameters locally...

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

2 Comments

It is unlikely. There is no bottleneck here. I just like to a) efficient code and b) sometimes it is more readable. See last part of my edit.
@George: I disagree about the readability, actually - because using a prepared statement you're keeping the code separate from the data. But I agree that named parameters are more readable than positional ones :)
4

Short answer: I wouldn't bother. It's best to do escaping at the last popssible moment. When you try to escape a string early and keep it around, it becomes much more difficult to verify that all strings have been escaped exactly once. (Escaping a string twice is almost as bad as not escaping it at all!) I've seen plenty of programs that try to escape strings early and then run into trouble because they need to update the string and then the programmer forgets to re-do the escape, or they update the escaped version of the string, or they have four strings and they escape three of them, etc. (I was just working on a bug where a programmer did HTML escapes on a string early, then decided he had to truncate the string to fit on a form, and ended up trying to output a string that ended with "&am". That is, he truncated his escape sequence so it was no longer valid.)

The CPU time to escape a string should be trivial. Unless you have a very large number of records or very big strings that are re-used, I doubt the savings would be worth worrying about. You'd probably be better off spending your time optimizing queries: saving a read of one record would probably be worth far more than eliminating 1000 trips through the string escape logic.

Longer answer: There's no built-in function. You could write one easily enough: Most flavors of SQL just need you to double any single quotes. You may need to also double backslashes or one or two other special characters. The fact that this can be different between SQL engines is one of the big arguments for using PreparedStatements and letting JDBC worry about it. (Personally I think there should be a JDbC function to do escaping that could then know any requirements specific to the DB engine. But there isn't so that's how it is.)

In any case, it's not clear how it would work with a PreparedStatement. There'd have to be some way to tell the PreparedStatement not to escape this string because it's already been escaped. And who really knows what's happening under the table in the conversation between JDBC and the DB engine: Maybe it never really escapes it at all, but passes it separately from the query. I suppose there could be an extra parameter on the setString that says "this string was pre-escaped", but that would add complexity and potential errors for very little gain.

Improve this answer

2 Comments

Why is the longer answer seem shorter then the short answer :S +1 for well descriptive answer
@RMT: Because I just can't leave things alone, and I kept going back and adding to my short answer!
3

Do not use org.apache.commons.lang.StringEscapeUtils.escapeSql(yourUnscapedSQL);

It does not escape characters like \

Improve this answer

2 Comments

this would be better as a comment on the original question. it doesn't really answer the question
Should be modded up to get above all suggestions to use StringEscapeUtils.escapeSql (which imo is incredibly dangerously named - as Kim pointed out it does NOT correctly escape sql). Anyone who attempts to rely on StringEscapeUtils in production is going to FAIL.
1

You can use StringEscapeUtils from Apache commons:

org.apache.commons.lang.StringEscapeUtils.escapeSql(yourUnscapedSQL);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.