6

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.

palto
  • 3,523
  • 5
  • 32
  • 38
  • 1
    If you are using `like`, you also want to escape existing `%`, `_`, and `[...]`. This *might* be a duplicate of: [Escape a string in SQL Server so that it is safe to use in LIKE expression](http://stackoverflow.com/questions/258757/escape-a-string-in-sql-server-so-that-it-is-safe-to-use-in-like-expression) – Kobi Aug 14 '12 at 07:03
  • but as long as you are using a PreparedStatement, you will not induce any security flaws. – John Smith Aug 14 '12 at 07:09
  • @Kobi Thanks, I did not know about _ and []. Have to try how those affect the query. Hibernate allows me to set a Escape character so I might update this question with me trying to escape things I don't want users to use. – palto Aug 14 '12 at 07:29

2 Answers2

2

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, '*', '%')
XN16
  • 5,679
  • 15
  • 48
  • 72
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.

Community
  • 1
  • 1
d1e
  • 6,372
  • 2
  • 28
  • 41
  • Isn't it the other way around? If I transform it in the Service-layer, I'm hard coding what escapes I have to do in this specific database. If I do it in the DAO layer, I can have different implementations for different storages. I might even have a NOSQL datastore. – palto Aug 14 '12 at 08:57