如何在PostgreSQL中匹配模式时转义字符串

Question

我想查找文本列以用户给定的字符串开头的行，例如SELECT * FROM users WHERE name LIKE 'rob%'，“ rob”是未经验证的用户输入。如果用户编写的字符串包含特殊模式字符（例如“ rob_”），则它将匹配“ robert42”和“ rob_the_man”。我需要确保字符串在字面上是匹配的，我该怎么做？我是否需要在应用程序级别上处理转义，或者这是一种更漂亮的方法？我正在使用PostgreSQL 9.1和go-pgsql。

Answer 1

_和％字符必须在LIKE语句中用引号引起来，以字面形式进行匹配，因此无法解决。选择是在客户端或服务器端进行（通常通过使用SQL replace（），请参见下文）。为了使它在一般情况下100％正确，还需要考虑一些事项。

默认情况下，在_或％之前使用的引号字符是反斜杠（\），但是可以在LIKE子句之后的ESCAPE子句中对其进行更改。在任何情况下，引号字符都必须在模式中重复两次，才能从字面上匹配为一个字符。

示例：... WHERE field like 'john^%node1^^node2.uucp@%' ESCAPE '^'将匹配john％node1 ^ node2.uccp @，后跟任何内容。

默认的反斜杠存在一个问题：当standard_conforming_strings为OFF时，它已经用于其他目的（PG 9.1默认情况下为ON，但是以前的版本仍在广泛使用，这是需要考虑的一点）。

同样，如果LIKE通配符的引用是在用户输入注入方案中在客户端完成的，则除了在用户输入上已经必需的常规字符串引用之外，它还包括在内。

一看go-pgsql示例，便会发现它使用$ N样式的占位符作为变量...因此，这里尝试以某种通用的方式编写它：它与standard_conforming_strings一起使用，无论是ON还是OFF，都使用服务器端替换[％_]，另一个引号，用引号引起来，并且避免了sql注入：

   db.Query("SELECT * from USERS where name like replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'",
     variable_user_input);

Answer 2

要转义下划线和like表达式中的模式中要使用的百分比，请使用转义字符：

SELECT * FROM users WHERE name LIKE replace(replace(user_input, '_', '\\_'), '%', '\\%');

Answer 3

据我所知，LIKE运算符唯一的特殊字符是百分号和下划线，使用反斜杠可以轻松地手动将其转义。它不是很漂亮，但是可以用。

SELECT * FROM users WHERE name LIKE
regexp_replace('rob', '(%|_)', '\\\1', 'g') || '%';

我发现奇怪的是，PostgreSQL没有附带这样的功能。谁希望他们的用户编写自己的模式？