Security letting users use wildcards

Question

I need to let my users use asterisks (*) as wildcards in search.

Is it secure to convert the asterisks to % and use LIKE in the sql query.

I know that user-regexp can result in regular epressions that take forever to calculate. I don't think that i possible in this case but is it any other security issues with doing this?

Can you show us an example query of what you propose? I would set a time limit for user searches, to keep them from pounding your servers too hard. — Brad, Jun 12 '12 at 22:30
Perhaps your application would be better served by integrating a search engine such as Lucene. — , Jun 12 '12 at 22:37

score 1 · Accepted Answer · answered Jun 12 '12 at 22:34

Wildcards in like expressions can cause changes in query execution that make the RDBMS use full-table scans instead of using indexes. This may slow down the query when there is a lot of data. I would recommend checking user's input for presence of at least a few non-wildcard characters in front of the first asterisk.

Also note that if you convert * to %, and use LIKE, you'd need to take care of _ as well, otherwise it would match any single character, not just the underscore.

score 0 · Answer 2 · answered Jun 13 '12 at 01:21

If all you are doing is a simple replace like so

str_replace('*','%',$query)

then I don't foresee any security concerns, which I believe is what you are concerned about. You're not going to open up any SQL Injection possibilities or anything (at least not by doing this replacement, you may still have security concerns if you aren't escaping the input).

However, as some other users have pointed out you will open up some performance issues. What will happen if I search for just *, am I going to get your entire table back? The best way (if you don't want to use a database search engine) is going to do some user validation. Most likely if you want to prevent a full table scan on your query, you will want to restrict the user to doing only leading or trailing wildcards.

Good:

*foo
bar*

Not as good:

*foo*
ba*r

It will really depend on how many rows you have and how much you trust your users to provide actual input though.

score 0 · Answer 3 · edited May 23 '17 at 12:03

The real question is, is it safe to let the user decides of part of the query.

Even in a simple case, providing user-data to the database is unsafe.

Allowing users input to be directly provided to the database may be dangerous, but as far as your filter and use your database escaping strategy (ie: mysql_real_escape_string() (well prepared statements or any ORM will do that for you) should be safe enough.

However, it may lead to performance problems, a simple EXPLAIN on your query should warn you on how much rows are scanned by your RDBMS engine.

The best way to implement such feature is to use Search Engine. (Isn't it what you're trying to do?)

There are many choices outta there to help you implementing this.

You may take a look to Sphinx, Solr, Xapian or even Lucene. There are all excellent choice.

They basically allow you to "index" your content to make some fulltext search while increasing performance.

They can also give some incredible functionalities like OR, AND, LIKE, MINUS, etc. comparators/operators.

You may then be interested in this question: Choosing a stand-alone full-text search server: Sphinx or SOLR?

Security letting users use wildcards

3 Answers3