使用REGEX查询的准备好的SQL语句的安全性 [英] Security for prepared SQL statement with REGEX in query

问题描述

我试图在数据库查询中使用准备好的PDO语句，如下所示，其中占位符变量由引号中的数字表示，即"123"和"456":

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` REGEXP '.*"ABC";.*s:[0-9]+:"123".*') 
AND (`meta_value` REGEXP '.*"DEF";.*s:[0-9]+:"456".*')

我的问题是，最佳实践是绑定到整个REGEX表达式，还是仅绑定"123"和"456"变量(甚至可以在REGEX表达式中绑定)，或者完全不同? >

换句话说，这是首选:

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` :REGEXP1) 
AND (`meta_value` :REGEXP2)

$stmt->bindParam(':REGEXP1', "REGEXP '.*"ABC";.*s:[0-9]+:"123".*'");
$stmt->bindParam(':REGEXP2', "REGEXP '.*"DEF";.*s:[0-9]+:"456".*'");

还是这个? (我知道在占位符周围会出现双引号的问题.)

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` REGEXP '.*"ABC";.*s:[0-9]+:":value1".*') 
AND (`meta_value` REGEXP '.*"DEF";.*s:[0-9]+:":value2".*')

$stmt->bindParam(':value1', '123');
$stmt->bindParam(':value2', '456');

谢谢.

解决方案

不能引用占位符.就这么简单:

SELECT ... WHERE foo = ?
SELECT ... WHERE foo = '?'

第一个是占位符，可以正常工作.另一个正在测试针对问号"字符的相等性.不再是占位符.

然后还有?也是正则表达式元字符的问题.如果可以引用占位符，则给出

SELECT ... WHERE foo REGEXP '^.?' 

?是查询占位符，还是正则表达式零或一"范围运算符?

如果要在正则表达式中使用占位符，则必须构建"正则表达式模式

SELECT ... WHERE foo REGEXP concat('^.', ?)

与构建LIKE模式完全相同的方式:

SELECT ... WHERE foo LIKE '%?%' // wrong
SELECT ... WHERE foo LIKE concat('%', ?, '%') // right

I'm trying to use a prepared PDO statement for use in a database query that looks like the following, where the placeholders variables are represented by the numbers in quotes, i.e., "123" and "456":

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` REGEXP '.*"ABC";.*s:[0-9]+:"123".*') 
AND (`meta_value` REGEXP '.*"DEF";.*s:[0-9]+:"456".*')

My question is, would the best practice be to bind to the whole REGEX expression, or just the "123" and "456" variables (is that even possible in a REGEX expression), or something wholly different?

In other words, which is preferred, this:

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` :REGEXP1) 
AND (`meta_value` :REGEXP2)

$stmt->bindParam(':REGEXP1', "REGEXP '.*"ABC";.*s:[0-9]+:"123".*'");
$stmt->bindParam(':REGEXP2', "REGEXP '.*"DEF";.*s:[0-9]+:"456".*'");

Or this? (I know there would be some issues with double quotes surrounding the placeholder.)

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_fields') 
AND (`meta_value` REGEXP '.*"ABC";.*s:[0-9]+:":value1".*') 
AND (`meta_value` REGEXP '.*"DEF";.*s:[0-9]+:":value2".*')

$stmt->bindParam(':value1', '123');
$stmt->bindParam(':value2', '456');

Thank you.

解决方案

Placeholders can't be quoted. Simple as that:

SELECT ... WHERE foo = ?
SELECT ... WHERE foo = '?'

The first one is a placeholder, and works as expected. The other one is testing for equality against the character "question mark". It's not a placeholder anymore.

And then there's the problem of ? being a regex metacharacter as well. If placeholders COULD be quoted, then given

SELECT ... WHERE foo REGEXP '^.?' 

would that ? be a query placeholder, or is it the regex "zero-or-one" range operator?

If you want to use placeholders in regexes, you have to "build" the regex pattern

SELECT ... WHERE foo REGEXP concat('^.', ?)

Exactly the same way as you would have to build a LIKE pattern:

SELECT ... WHERE foo LIKE '%?%' // wrong
SELECT ... WHERE foo LIKE concat('%', ?, '%') // right

这篇关于使用REGEX查询的准备好的SQL语句的安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！