如何使用 Java 在 PostgreSQL 中安全地转义 SQL 的任意字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9741206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to safely escape arbitrary strings for SQL in PostgreSQL using Java
提问by kaliatech
I have a special caserequiring that I generate part of a SQL WHERE clause from user supplied input values. I want to prevent any sort of SQL Injection vulnerability. I've come up with the following code:
我有一个特殊情况，要求我从用户提供的输入值生成 SQL WHERE 子句的一部分。我想防止任何类型的 SQL 注入漏洞。我想出了以下代码：
private String encodeSafeSqlStrForPostgresSQL(String str) {
//Replace all apostrophes with double apostrophes
String safeStr = str.replace("'", "''");
//Replace all backslashes with double backslashes
safeStr = safeStr.replace("\", "\\");
//Replace all non-alphanumeric and punctuation characters (per ASCII only)
safeStr = safeStr.replaceAll("[^\p{Alnum}\p{Punct}]", "");
//Use PostgreSQL's special escape string modifier
safeStr = "E'" + safeStr + "'";
return safeStr;
}
Questions:
问题：
- Do you see any issues?
- Can you provide a better solution?
- Are there any existing libraries to help with this?
- 你看到任何问题吗？
- 你能提供更好的解决方案吗？
- 是否有任何现有的图书馆可以帮助解决这个问题？
Notes:
笔记：
This is a common question on SO and elsewhere, but the only answer I've seen is to always use PreparedStatements. Fwiw, I'm using JasperReports. I want to keep the query inside of JasperReports. The built-in Jasper parameter functions for query parameter handling (including the X{} functions) are not sufficient for what I need to parametrize. I could try creating a custom Jasper QueryExecutor that would allow me to inject my own X{} functions, but that's more complicated than just generating a dynamic SQL where clause with Jasper's $P!{} syntax.
I looked at the OWASP libraries. They do not have a PostgresSQL codec yet. I looked at the OracleCodecthough and its escaping seemed simplistic. I'm not sure it would be of much helping preventing SQL injection attacks.
In my code I'm adding the E so as to not be dependent on PostgreSQL's standard_conforming_strings setting. Ideally I wouldn't have to add that and then the function wouldn't have to be PostgreSQL specific. More info: http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE.
这是 SO 和其他地方的常见问题，但我看到的唯一答案是始终使用 PreparedStatements。Fwiw，我正在使用 JasperReports。我想将查询保留在 JasperReports 中。用于查询参数处理的内置 Jasper 参数函数（包括 X{} 函数）不足以满足我需要参数化的需求。我可以尝试创建一个自定义 Jasper QueryExecutor，它允许我注入我自己的 X{} 函数，但这比仅使用 Jasper 的 $P!{} 语法生成动态 SQL where 子句要复杂。
我查看了OWASP 库。他们还没有 PostgresSQL 编解码器。不过，我查看了OracleCodec，它的转义似乎很简单。我不确定这对防止 SQL 注入攻击有多大帮助。
在我的代码中，我添加了 E 以便不依赖于 PostgreSQL 的 standard_conforming_strings 设置。理想情况下，我不必添加它，然后该函数就不必特定于 PostgreSQL。更多信息：http: //www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE。
Ideally I would've liked a more generic and robust solution that I knew would be safe and support all possible UTF-8 strings.
理想情况下，我会喜欢一个更通用、更强大的解决方案，我知道它是安全的并且支持所有可能的 UTF-8 字符串。
采纳答案by A.H.
The most easiest way would be to use PostgreSQL's Dollar Quotingin the combination with a small randomtag:
最简单的方法是将 PostgreSQL 的Dollar Quoting与一个小的随机标签结合使用：
For each invocation calculate a small, random tag (e.g 4 characters)(redundant)- Look whether or not the quote tag is part of the input string.
- If it is, recalculate a new random tag.
Otherwise build your query like this:
$tag$inputString$tag$
对于每次调用计算一个小的随机标签（例如 4 个字符）（冗余）- 查看引号标记是否是输入字符串的一部分。
- 如果是，重新计算一个新的随机标签。
否则像这样构建您的查询：
$tag$inputString$tag$
This way you escape the whole hassle of different nested quoting techniques andyou also set up a moving target by using a random tag.
通过这种方式，您可以避开不同嵌套引用技术的全部麻烦，并且您还可以使用随机标签设置移动目标。
Depending on your security requirements this might do the job or not. :-)
根据您的安全要求，这可能会起作用，也可能不起作用。:-)
回答by sixtyfootersdude
I asked a similar questionhere, but I think that the best thing to do is to use org.postgresql.core.Utils.escapeLiteral. This is a Postgres library so using it should be safe. If/when Postgres adds new string delimiters this method should be updated.
我在这里问了一个类似的问题，但我认为最好的办法是使用org.postgresql.core.Utils.escapeLiteral. 这是一个 Postgres 库，因此使用它应该是安全的。如果/当 Postgres 添加新的字符串分隔符时，应该更新此方法。
