How do i write a sql query with user input and wildcards

Question

Usually i write my where statements as WHERE key=@0 then add a param. Now i would like the user to specific a few letters such as 'oat' and would like to stick wildcards around it %oat% which matches 'coating'. So how do i write the query so wildcards are around the user input (the 'seachword').

Now lets take it a step further. I would not like the user to write % so he cannot do blah%ing. Perhaps % is part of the sentence (or it could be flat out illegal but i prefer it be part of the sentence). How do i put my wildcards around a word or sentence and disallow the user from putting the wildcard between his words? (and preferably make % part of the sentence)

C# ado.net sql/sqlite

Answer 1

Continue using a param, but add the wildcard in the param prior to binding.

C#:

  param = '%' + Regex.Replace(param, @"[%?]", String.Empty) + '%'

SQL:

select * from ... where key like :param

Answer 2

RE: How do i put my wildcards around a word or sentence and disallow the user from putting the wildcard between his words? (and preferably make % part of the sentence)

I think you would need to Replace any % the user supplies with \% and use

LIKE @Expression ESCAPE '\'

Answer 3

If you use prepared statements (i.e. SQLiteCommand, a subclass of DbCommand), this will be taken care of for you. E.g.:

using (SqlCommand myCommand = new SQLiteCommand("SELECT * FROM TABLE WHERE (COLUMN LIKE = '%' + @input + '%')"))
{
        myCommand.Parameters.AddWithValue("@input", input);
        // ...
}

See also this similar previous question.