如何在PDO中使用RLIKE来转义星号和问号?

I have a query to search posts by content. I'm still using mysql 5.5 with innoDB so RLIKE seems to be one of the only choices:

我有一个按内容搜索文章的查询。我还在使用mysql 5.5和innoDB，所以RLIKE似乎是唯一的选择之一:

$sql = "SELECT title,content FROM table1 WHERE content RLIKE ?";
$i = 1;
$users = $dbh->prepare($sql);
$users->bindValue($i++, $purifier->purify($_GET['content']), PDO::PARAM_STR);
$users->execute();

But I found that when I enter asterisk or a question mark as a parameter value (e.g www.site.com?content=*), I'm getting

但我发现当我输入星号或问号作为参数值(e)时。g www.site.com ?内容= *),我得到

SQLSTATE[42000]: Syntax error or access violation: 
1139 Got error 'repetition-operator operand invalid' from regexp.

How can I avoid this error? Are * and ? the only two special characters that would cause this error?

我怎样才能避免这个错误呢?*和?只有两个特殊字符会导致这个错误?

1 个解决方案

#1

Rlike requires a valid regular expression as operand. That means that 'Yes, there are other characters than * and ? that will cause an error.'

Rlike需要一个有效的正则表达式作为操作数。这意味着“是的，除了*和?”这会导致错误。

How to avoid? It depends on what you want to achieve exactly. The easiest would be to avoid regular expressions altogether. If you need that the user can enter any regular expression, the best way to validate it would be to use it in a query and catch the error if it is malformed.

如何避免?这完全取决于你想要达到的目标。最简单的方法是完全避免使用正则表达式。如果您需要用户输入任何正则表达式，那么验证它的最佳方法就是在查询中使用它，并在出错时捕获错误。

If you want only to find content that contains the string, using like is much more reliable. And you need to escape only % and _.

如果您只想找到包含字符串的内容，那么使用like要可靠得多。你只需要转义%和_。

$search = '%'.str_replace(array('%',  '_'), array('\%', '\_'), $_GET['content']).'%';
$sql = "SELECT title,content FROM table1 WHERE content LIKE ?";
...

1 个解决方案

#1

更多相关文章

随机推荐