使用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屋!