How to use * as a wild card in SQL query safely

Question

I need to implement a search where user can input * as a wild card. The database they are searching is a SQL server. I was thinking of just replacing the * with a %:

userInput = userInput.replace('*', '%');

I'm worried that since I'm doing this "by hand" I might introduce some bugs or security flaws. Do you see any problems doing it like this? Is there any library to do this for me?

I use Hibernate as an ORM mapper and Criteria API to create the query if it helps with answers.

Answer 1

That is exactly what we do in our company. We have two products, one that uses a simple replace in the code like your example. The other one which is very stored procedure heavy does it within the 'search' stored proc itself.

Either way, we haven't had any instances of security issues or user complaints about the system.

Just to show the stored procedure layout, but probably redundant as you said your using ORM:

CREATE PROC [dbo].[p_aaa]
    @username nvarchar(100) = 'dbo',
    @rows int = 0,
    @name nvarchar(100) = '%'       
AS

SET @name = REPLACE(@name, '*', '%')

Answer 2

I believe changing * to % will not cause any problems, if all the data in that table is public based on this search value. There is also similar topic which has more regexp to sql examples.

This will make your application more independent than using database vendror-specific regexp matching mechanism.