这是允许用户进行通配符搜索的正确方法吗?

Given a textbox name for example, the user requirement wants to be able to do a wildcard search (such as contains, starts with, ends with).

例如，给定一个文本框名称，用户需求希望能够进行通配符搜索(例如包含、以开头、以结尾)。

Is it ok to accept the sql wildcard characters ('%' and '_') as input as long as I am still using parameterized query in the backend (Java)? Effectively, allowing the user to build his own regular expression which is what the user's requirement is all about.

是否可以接受sql通配符('%'和'_')作为输入，只要我仍然在后台使用参数化查询(Java)?有效地，允许用户构建自己的正则表达式，这正是用户的需求。

Example:

例子:

User types in the

用户类型
```
textbox = '%are%'
```

This parameter is feed to the backend as such:

此参数将反馈给后端:

public class PersonDaoImpl {

        public List<Person> search(String name){//name gets the value from textbox w/ sql wildcards
        Query q = mgr.createNativeQuery('select * from Person where name like :name'); //default to always use like since expecting searchkey with sql wildcards    
        q.setParameter('name', name);//gives the input from the screen
        return q.getResultList();
        } 
}

The result set would include people with names 'Waren', 'Jared', 'Clare', 'Blare' as expected since user provided a regular expression.
结果集将包括名字为“Waren”、“Jared”、“Clare”、“Blare”的人，因为user提供了一个正则表达式。

With the SQL Parameterize Query, I can ensure that I won't be allowing SQL Injection. This implements the user requirement for wildcard search, but perhaps does it violate anything that I may have missed?

通过SQL参数化查询，我可以确保不允许SQL注入。这实现了通配符搜索的用户需求，但它是否违反了我可能错过的任何东西?

UPDATES: Just found out that Google allows wildcard too, from their help page.

更新:刚刚发现谷歌也允许通配符，从他们的帮助页面。

3 个解决方案

#1

Well, it violates the fact that the user needs to know (or be told) how to construct SQL "LIKE" syntax, but that's all. You could end up with a slow query this way, in that it won't usually be able to use an index, but I wouldn't be concerned in terms of security or correctness.

它违背了用户需要知道(或被告知)如何构造SQL“LIKE”语法的事实，但仅此而已。您可能会以这种方式得到一个缓慢的查询，因为它通常不能使用索引，但是我并不关心安全性或正确性。

#2

It's "safe", but probably not a good idea, for two reasons:

它是“安全的”，但可能不是一个好主意，原因有两个:

It's probably not the best ui design to require your users to know sql syntax for this.
要求用户了解sql语法可能不是最好的ui设计。
It's horrible for performance: these queries often can't use your indexes, so they are slow to execute. And they require a lot of cpu time to compare all that text, so they add a lot of load (disproportionate to the already high execution time) to your server. You want a solution that relies on a full-text index instead.
这对性能来说很糟糕:这些查询通常不能使用索引，所以执行起来很慢。它们需要大量的cpu时间来比较所有的文本，因此它们增加了大量的负载(与已经很高的执行时间不成比例)。您需要一个依赖全文索引的解决方案。

#3

~~I am curious, how does the name parameter end up getting set in the request? What platform is this?~~ (OP missed setParameter earlier)

我很好奇，name参数是如何在请求中设置的?这是什么平台?(OP)早些时候错过的setParameter

As you noted the user need to know the wild-card syntax i.e. the use of %, _, etc. A more popular approach is to just get the string from the username, along with an option for 'exact match'/'starts-with'/'anywhere-in-name'. If you go that route you will also be able to execute a more efficient query in the first two cases.

正如您所指出的，用户需要知道wild-card语法，即%、_等的使用。更流行的方法是从用户名中获取字符串，以及“精确匹配”/“starts-with”/“anywherestname”选项。如果您走那条路线，您还可以在前两种情况下执行更有效的查询。

EDIT:

编辑:

If the customer insists on contains query then I think your current approach of requiring the end-user to input a pattern better then converting the input string to pattern by putting % around it.

如果客户坚持包含查询，那么我认为您当前的方法是要求最终用户更好地输入模式，然后将输入字符串转换为模式，并在其周围加上%。

This is because the users will still have the option of not adding (or selectively adding) the % to the search string, resulting in faster query execution. For example:

这是因为用户仍然可以选择不向搜索字符串添加(或有选择地添加)%，从而导致更快的查询执行。例如:

If the user enter search string Don the query is select ... from ... where name like 'Don'. The RDBMS will most likely use the index on name.

如果用户输入搜索字符串Don，查询是select…从…名字像‘不’。RDBMS很可能会在名称上使用索引。
If the user enter search string Don% the query is select ... from ... where name like 'Don%'. The RDBMS will still quite likely use the index on name.

如果用户输入搜索字符串Don%，查询将被选择…从…名字就像‘%’的地方。RDBMS仍然很可能在名称上使用索引。
If the user enter search string %Don or %Don% then the index cannot be used.

如果用户输入搜索字符串%Don或%Don%，则不能使用索引。

#1

#2