快速而肮脏的 SQL 字符串转义
我正在对带有 postgresql 数据库的 Web 应用程序的家庭滚动 QueryBuilder
类进行最后的修饰。它使用 PreparedStatement
进行所有查询,并防止 SQL 注入。
然而,我想要一种“快速而肮脏”的方式来表示 QueryBuilder
及其 toString()
方法,仅用于调试目的。该方法将按照通常传递到 PreparedStatement
中的方式组装查询字符串,然后只需将字符串中的每个 ?
替换为其相应的单引号值。 toString() javadoc 将警告其他开发人员,这是一个不安全的近似值,只能用于调试等。
我知道这些值应该将单引号加倍(即 O'Connell
转义到 O''Connell
)。还有其他我忘记的特殊字符需要处理吗?我寻找了类似的问题,但只发现人们被责骂使用PreparedStatement(他们应该这样做,让记录显示)。
编辑:不想使用第三方工具来完成这个特定的任务,我真的只是想要快速而肮脏的东西。尽管如此,我还是很欣赏这些链接——我可能会考虑将它们用于其他用途。
最后编辑:感谢所有人的有用指示。我只是想补充一点,对于从 google 偶然发现这里的任何人,不要将这些技巧用于任何访问数据库的操作,请使用 PreparedStatement
。
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
.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
对于“快速而肮脏”的转义,将撇号加倍就足够了。不过,请注意字符串文字中已经存在的问号:
您不想替换第一个问号。另外,应该注意这些极端情况:
该语句中只有一个绑定值。对于一个不那么快速和肮脏的解决方案,您可以使用像 jOOQ 这样的库来解决这个问题(免责声明:我在 jOOQ 背后的公司工作)。它会为你做内联,也为更讨厌的数据类型:
上面将呈现
For "quick and dirty" escaping, doubling the apostrophes is good enough. Be aware of question marks already inside string literals, though:
You don't want to replace the first question mark. Also, these corner-cases should be taken care of:
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:
The above will render
如果您不反对使用第 3 方开源库,那么我建议您看看 Apache Commons Lang 的 StringEscapeUtils.escapeSql(String str)。
编辑:我刚刚检查了 来源。它只不过像您一样用两个单引号 (
''
) 替换一个单引号 ('
)。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.也许你可以看看 Apache 的 StringEscapeUtils。
Maybe you can give a look at the
escapeJava(String input)
from Apache's StringEscapeUtils.