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?

Answer 1

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.