使用MySQL搜索:如何逃避通配符 [英] Searching using MySQL: How to escape wildcards
问题描述
我正在使用如下查询(使用JDBC)来搜索我的数据库:
I am currently searching my database with a query (using JDBC) like this:
"... AND LCASE(Items.Name) LIKE '%" + searchString.toLowerCase() + "%';"
现在,这显然是非常糟糕的,因为它允许SQL注入以及插入通配符例如%和_。
Now, this is obviously very bad, because it allows for SQL injection as well as insertion of wildcard symbols such as % and _.
我的问题是,我如何做一个查询,以便即使searchString包含任何这些字符,它们将被字面处理? / p>
My question is, how can I do a query such that even if the searchString contains any of these characters, they will be treated literally?
推荐答案
首先,不要使用 LCASE
与 LIKE
,除非您使用区分大小写的区域设置(这不是MySQL的默认值)。
First, don't use LCASE
with LIKE
unless you're using a case-sensitive locale (which is not the default with MySQL).
为了转义这些字符,只需在 \
字符前缀,因此 foo%bar
成为 foo\%bar
。
As far as escaping those characters, just prefix them with a \
character, so foo%bar
becomes foo\%bar
.
(自从我使用Java以来已经有一段时间了,但这可能是这样的:)
(It's been a while since I've used Java, but might this work:)
searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_')
(或使用正则表达式):
(or using a regex):
Regex r = new Regex('(?:%|_)', '\\\\$&');
r.replaceAll(searchString)
只要阻止SQL注入,只需绑定变量正常:
As far as preventing SQL injection, just bind the variable as normal:
WHERE LCASE(Items.Name) LIKE ?
创建绑定的字符串,如:
And create the bound string like:
'%' + searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_') + '%'
这篇关于使用MySQL搜索:如何逃避通配符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!