快速而肮脏的 SQL 字符串转义

发布于 2024-11-28 22:49:07 字数 751 浏览 0 评论 0原文

我正在对带有 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

生活了然无味 2024-12-05 22:49:07

对于“快速而肮脏”的转义,将撇号加倍就足够了。不过,请注意字符串文字中已经存在的问号:

SELECT column FROM table WHERE column = 'A question?' or column = ?

您不想替换第一个问号。另外,应该注意这些极端情况:

SELECT /* Is this a comment?? */ * FROM table
-- -- --  Another comment??
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

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
裂开嘴轻声笑有多痛 2024-12-05 22:49:07

如果您不反对使用第 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.

孤独难免 2024-12-05 22:49:07

也许你可以看看 Apache 的 StringEscapeUtils

public static final String escapeJava(字符串输入)

使用 Java 字符串规则转义字符串中的字符。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文