在MySQL中为正则表达式转义用户输入的最佳方法是什么? [英] What's the best way to escape user input for Regular Expressions in MySQL?

问题描述

我想将表示为$ dangerous_string的用户输入用作MySQL查询中RegEx的一部分.

执行此操作的最佳方法是什么?我想将用户的字符串用作文字-如果它包含在MySQL RegEx中表示某些含义的任何字符，则这些字符实际上不应影响我的正则表达式.

$dangerous_string = $_GET["string"];
//do something here
$dangerous_string = what_goes_here($dangerous_string);
$sql = "SELECT * FROM table WHERE search_column REGEX '" . $mysqli->real_escape_string("[[:<:]]$dangerous_string") . "'";

//etc....

解决方案

AFAIK，MySQL的正则表达式没有本地化的方法.您可以使用preg_quote(http://www.php.net/manual/zh/function.preg-quote.php)在PHP中完成此操作，这可能会为您完成工作，但显然并非出于此目的而设计的. /p>

如果遇到这种情况，我的首选方法是在PHP中构建一个正则表达式白名单，然后将其应用于危险的字符串:

$safeString = preg_replace('/[^\w]/','',$dangerousString);

这将从您的字符串中删除所有非单词字符(即A-Za-z0-9_以外的任何其他字符).

注意，我相信给出的其他答案不会删除/转义正则表达式特殊字符，这是您的要求.

I'd like to take user input, denoted as $dangerous_string, and use it as part of a RegEx in a MySQL query.

What's the best way to go about doing this? I want to use the user's string as a literal -- if it contains any characters that mean something in MySQL RegEx, those characters should not actually affect my Regular Expression.

$dangerous_string = $_GET["string"];
//do something here
$dangerous_string = what_goes_here($dangerous_string);
$sql = "SELECT * FROM table WHERE search_column REGEX '" . $mysqli->real_escape_string("[[:<:]]$dangerous_string") . "'";

//etc....

解决方案

AFAIK, there is no native way of escaping for MySQL regex. You can do it in PHP with preg_quote (http://www.php.net/manual/en/function.preg-quote.php) which would probably do the job for you, but is obviously not designed for the purpose.

My preferred way if I were in your situation would be to construct a regex whitelist in PHP that you can then apply to your dangerous string:

$safeString = preg_replace('/[^\w]/','',$dangerousString);

This removes any non-word characters (i.e. anything except A-Za-z0-9_) from your string.

NB I believe the other answers given will not remove/escape regex special characters, which I believe is your requirement.

这篇关于在MySQL中为正则表达式转义用户输入的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！