在 PostgreSQL 中匹配模式时如何转义字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10153440/
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 escape string while matching pattern in PostgreSQL
提问by Betamos
I want to find rows where a text column begins with a user given string, e.g. SELECT * FROM users WHERE name LIKE 'rob%'but "rob" is unvalidated user input. If the user writes a string containing a special pattern character like "rob_", it will match both "robert42" and "rob_the_man". I need to be sure that the string is matched literally, how would I do that? Do I need to handle the escaping on an application level or is it a more beautiful way?
我想找到文本列以用户给定字符串开头的行，例如，SELECT * FROM users WHERE name LIKE 'rob%'但“rob”是未经验证的用户输入。如果用户写入包含特殊模式字符（如“rob_”）的字符串，它将匹配“robert42”和“rob_the_man”。我需要确保字符串字面上匹配，我该怎么做？我需要在应用程序级别处理转义还是更漂亮的方式？
I'm using PostgreSQL 9.1 and go-pgsqlfor Go.
我正在使用 PostgreSQL 9.1 和go-pgsqlfor Go。
回答by Daniel Vérité
The _ and % characters have to be quoted to be matched literally in a LIKE statement, there's no way around it. The choice is about doing it client-side, or server-side (typically by using the SQL replace(), see below). Also to get it 100% right in the general case, there are a few things to consider.
_ 和 % 字符必须被引用才能在 LIKE 语句中逐字匹配，没有办法绕过它。选择是在客户端或服务器端进行（通常使用 SQL replace()，见下文）。此外，为了在一般情况下 100% 正确，还有一些事情需要考虑。
By default, the quote character to use before _ or % is the backslash (\), but it can be changed with an ESCAPE clause immediately following the LIKE clause. In any case, the quote character has to be repeated twice in the pattern to be matched literally as one character.
默认情况下，在 _ 或 % 之前使用的引号字符是反斜杠 (\)，但可以使用紧跟在 LIKE 子句之后的 ESCAPE 子句进行更改。在任何情况下，引号字符都必须在模式中重复两次才能作为一个字符逐字匹配。
Example: ... WHERE field like 'john^%node1^^node2.uucp@%' ESCAPE '^'would match john%node1^node2.uccp@followed by anything.
示例：... WHERE field like 'john^%node1^^node2.uucp@%' ESCAPE '^'将匹配john%node1^node2.uccp@后跟任何内容。
There's a problem with the default choice of backslash: it's already used for other purposes when standard_conforming_stringsis OFF (PG 9.1 has it ON by default, but previous versions being still in wide use, this is a point to consider).
反斜杠的默认选择存在一个问题：当standard_conforming_strings为OFF时，它已用于其他目的（PG 9.1 默认情况下为ON，但以前的版本仍在广泛使用，这是一个需要考虑的问题）。
Also if the quoting for LIKE wildcard is done client-side in a user input injection scenario, it comes in addition toto the normal string-quoting already necessary on user input.
此外，如果 LIKE 通配符的引用是在用户输入注入场景中在客户端完成的，那么它是对用户输入已经必需的正常字符串引用的补充。
A glance at a go-pgsql example tells that it uses $N-style placeholders for variables... So here's an attempt to write it in a somehow generic way: it works with standard_conforming_strings both ON or OFF, uses server-side replacement of [%_], an alternative quote character, quoting of the quote character, and avoids sql injection:
看一眼 go-pgsql 示例就知道它对变量使用 $N 样式的占位符...... [%_]，替代引号字符，对引号字符的引用，避免sql注入：
db.Query("SELECT * from USERS where name like replace(replace(replace(,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'",
variable_user_input);
回答by Clodoaldo Neto
To escape the underscore and the percent to be used in a pattern in likeexpressions use the escape character:
要转义下划线和要在like表达式中的模式中使用的百分比，请使用转义字符：
SELECT * FROM users WHERE name LIKE replace(replace(user_input, '_', '\_'), '%', '\%');
回答by Betamos
As far as I can tell the only special characters with the LIKE operator is percent and underscore, and these can easily be escaped manually using backslash. It's not very beautiful but it works.
据我所知，LIKE 运算符的唯一特殊字符是百分比和下划线，并且可以使用反斜杠轻松手动转义这些字符。它不是很漂亮，但很管用。
SELECT * FROM users WHERE name LIKE
regexp_replace('rob', '(%|_)', '\', 'g') || '%';
I find it strange that there is no such functions shipped with PostgreSQL. Who wants their users to write their own patterns?
我觉得奇怪的是 PostgreSQL 没有附带这样的函数。谁希望他们的用户编写自己的模式？
回答by Jeremy Wall
The best answer is that you shouldn't be interpolating user input into your sql at all. Even escaping the sql is still dangerous.
最好的答案是您根本不应该将用户输入插入到您的 sql 中。即使逃避sql仍然是危险的。
The following which uses go's db/sql library illustrates a much safer way. Substitute the Prepare and Exec calls with whatever your go postgresql library's equivalents are.
以下使用 go 的 db/sql 库说明了一种更安全的方法。用您的 go postgresql 库的等效项替换 Prepare 和 Exec 调用。
// The question mark tells the database server that we will provide
// the LIKE parameter later in the Exec call
sql := "SELECT * FROM users where name LIKE ?"
// no need to escape since this won't be interpolated into the sql string.
value := "%" + user_input
// prepare the completely safe sql string.
stmt, err := db.Prepare(sql)
// Now execute that sql with the values for every occurence of the question mark.
result, err := stmt.Exec(value)
The benefits of this are that user input can safely be used without fear of it injecting sql into the statements you run. You also get the benefit of reusing the prepared sql for multiple queries which can be more efficient in certain cases.
这样做的好处是可以安全地使用用户输入，而不必担心它会将 sql 注入您运行的语句中。您还可以获得将准备好的 sql 重用于多个查询的好处，这在某些情况下会更有效。
