quick and dirty SQL string escaping
I'm putting the finishing touches on a home rolled QueryBuilder
class for a web application with a postgresql DB. It uses PreparedStatement
for all queries and is prote开发者_如何学Pythoncted against SQL injection.
However I wanted a "quick and dirty" way of representing the QueryBuilder
with its toString()
method, only for debugging purposes. The method will assemble the query string as is normally passed into the PreparedStatement
, then simply replace each ?
in the string with its corresponding single-quoted value. The toString()
javadoc will warn other devs that it's an unsafe approximation only to be used for debugging, etc etc.
I know the values should have their single quotes doubled up (ie O'Connell
escapes to O''Connell
). Are there any other special characters that should be dealt with that I'm forgetting? I looked for similar questions but only found people getting scolded to use PreparedStatement
(which they should, let the record show).
EDIT: not looking to use a third party tool for this particular task, I really just want the quick and dirty here. I do appreciate the links all the same though - I may consider them for other uses.
LAST EDIT: thanks to all for the helpful pointers. I just want to add that for anyone who stumbles in here from google, do not use these tricks for anything hitting the database, use PreparedStatement
.
For "quick and dirty" escaping, doubling the apostrophes is good enough. Be aware of question marks already inside string literals, though:
SELECT column FROM table WHERE column = 'A question?' or column = ?
You don't want to replace the first question mark. Also, these corner-cases should be taken care of:
SELECT /* Is this a comment?? */ * FROM table
-- -- -- Another comment??
WHERE column = ?
There's only one bind value in that statement. For a less quick-and-dirty solution, you could use a library like jOOQ for this problem, though (disclaimer: I work for the company behind jOOQ). It'll do the inlining for you, also for the more nasty data types:
DSLContext ctx = DSL.using(SQLDialect.POSTGRES);
Object[] bindValues = { 1, "a'bc", Date.valueOf("2012-09-24"), "xy".getBytes() };
String string = ctx.query(
"SELECT 1 WHERE A = ? AND B = ? AND C = ? AND D = ?",
bindValues).toString();
The above will render
SELECT 1
WHERE A = 1
AND B = 'a''bc'
AND C = date '2012-09-24'
AND D = E'\\170\\171::bytea
If you're not averse to using a 3rd-party open source library, then I'd say take a look at Apache Commons Lang's StringEscapeUtils.escapeSql(String str).
EDIT: I just checked the source. It does nothing more than replace a single quote ('
) with two single quotes (''
) as you do.
Maybe you can give a look at the escapeJava(String input)
from Apache's StringEscapeUtils.
public static final String escapeJava(String input)
Escapes the characters in a String using Java String rules.
精彩评论