快速和脏的SQL字符串转义 [英] quick and dirty SQL string escaping

问题描述

对于具有postgresql数据库的Web应用程序，我正在对一个家庭滚动的 QueryBuilder 类进行整理。它为所有查询使用 PreparedStatement ，并受到SQL注入的保护。

然而，我想要一个快速而肮脏的方式来表示 QueryBuilder 与其 toString（）方法，仅用于调试目的。该方法将按照通常传递给 PreparedStatement 的查询字符串进行汇编，然后将字符串中的每个？替换为其相应的单引号。 toString（） javadoc将警告其他开发人员仅仅用于调试等不安全的近似。

我知道这些值应该是单引号加倍（即 O'Connell 转义为 O''Connell ）。有什么其他的特殊字符应该被处理，我忘记了吗？我寻找类似的问题，但只发现人们被谴责使用 PreparedStatement （他们应该让记录显示）。

编辑：不希望使用第三方工具进行此特定任务，我只是想在这里快速而肮脏。我感谢链接都一样，但我可能会考虑用于其他用途。

最后编辑：感谢所有有用的指针。我只想补充一下，对于那些从google绊脚石的人来说，不要使用这些技巧来触发数据库，​​请使用 PreparedStatement 。 / p>

解决方案

对于快速而肮脏的转义，将撇号加倍是足够好的。请注意已经在字符串文字中的问号：

  SELECT column FROM table WHERE column = '一个问题？'或者列=？ 
  

您不想替换第一个问号。此外，这些角落应该被照顾：

  SELECT / *这是一个评论?? * / * FROM table 
  -   -   - 另一个评论？ 
 WHERE column =？ 
  

该语句中只有一个绑定值。对于一个不那么快速和肮脏的解决方案，您可以使用像 jOOQ 这样的库来解决这个问题，但是（免责声明：我在jOOQ后面为公司工作）。它会为你做内联，也是更讨厌的数据类型：

  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（）; 
  

以上将呈现

  SELECT 1 
 WHERE A = 1 
 AND B ='a''bc'
 AND C = date'2012-09- 24'
 AND D = E'\\\170\\171 :: bytea 
  

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 protected 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

这篇关于快速和脏的SQL字符串转义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！