17

I am trying to build SQL for a parameter query in C# for a query which will contain the LIKE %% command.

Here is what I am trying to acheive (please note that the database is Firebird)

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%?%'", TABLE, NAME);
 cmd.Parameters.AddWithValue(NAME, "JOHN");

Now I have tried every single permutation to get the parameter to work, I have tried;

  • Adding the % character to the parameter,

    cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");

  • or

    cmd.Parameters.AddWithValue(NAME, "'%" + "JOHN" + "%'");

I cannot seem to get this to work, how can I use a parameter for the LIKE query to work.

Suggestions are welcome!

Aurelio De Rosa
  • 20,508
  • 8
  • 45
  • 69
  • it seems like there is some missing code, where are you assigning the SQL to the command? What error are you getting or is the query result just not what you expect? – Jeff Martin Mar 19 '09 at 22:37
  • yes, SQL is being assigned to the Cmd.CommandText parameter. –  Mar 19 '09 at 22:50

3 Answers3

27

You can't have parameters inside of a string literal in the query. Make the entire value the parameter, and add the wildcards to the string:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE ?", TABLE, NAME);
Cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");
Guffa
  • 640,220
  • 96
  • 678
  • 956
  • Pay closer attention: he moved the wild cards to the parameter. This will work, but imo it's the wrong way to do it. – Joel Coehoorn Mar 19 '09 at 23:01
  • @Myhiad: Notice that there is only ? after the like operator, no wildcard characters and no apostrophes. – Guffa Mar 19 '09 at 23:06
  • @Joel Coehoorn: There is no reason to be rude. The question doesn't contain any information about how the query looks after the parameter has been changed. – Guffa Mar 19 '09 at 23:12
4
var SQL = string.Format("SELECT * FROM {0} WHERE {1} LIKE '%' + ? + '%'", TABLE, NAME);
Cmd.CommandText = SQL;
Cmd.Parameters.Add("?", SqlDbType.VarChar, 50).Value = "JOHN";
Joel Coehoorn
  • 362,140
  • 107
  • 528
  • 764
  • is this SQL valid for Firebird? –  Mar 19 '09 at 23:02
  • I didn't see the firebird requirement. I updated the sql to make sure it's okay, but the parameter code is wrong because the SqlDbType enum was intended for Sql Server. I still like to avoid AddWithValue, though – Joel Coehoorn Mar 19 '09 at 23:10
  • this is just not working for me. I am getting the following error; FirebirdSql.Data.FirebirdClient.FbException : Dynamic SQL Error expression evaluation not supported ----> FirebirdSql.Data.Common.IscException : Exception of type 'FirebirdSql.Data.Common.IscException' was thrown. –  Mar 19 '09 at 23:14
  • For Firebird (and standard SQL), you need to use the `||` concatenation operator instead of `+`, that is `LIKE '%' || ? || '%'` – Mark Rotteveel Mar 09 '19 at 17:10
-5

In the past when doing this, i've simply integrated it into the sql, making sure that i replace single quotes with question marks to deal with sql injection. Eg:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE '%{2}%'",
  TABLE,
  NAME,
  JOHN.Replace("'","?"));
Chris
  • 37,118
  • 43
  • 178
  • 226