Using parameterized SQL LIKE Statements

Question

I'm trying to parameterize my SQL Statements but I'm having some trouble with the LIKE statement. I tried different solutions but nothing works. My code:

sqlCmd = new SqlCommand("SELECT " + form1.cusId.Text + "," + form1.cusName.Text" FROM " + form1.getTable() + " WHERE " + form1.getCusId() + " LIKE @filterCustomers", connection);
sqlCmd.Parameters.AddWithValue("@filterCustomers", form1.filterCus().Trim() + "%");

I'm getting

"Must declare the scalar variable @filterCustomers".

Why ist that happening? Since this is th common solution...

You seem to be injecting stuff into your SQL string all over the place, so I'm not sure you're reaping any of the primary benefits of parametrized queries. — Asad Saeeduddin, Mar 30 '15 at 06:36
What is the value of `form1.filterCus().Trim()` exactly? Debug your code and tell us. And since you want to get your table and column names dynamically, I hope you create a strong validation or black list for them. — Soner Gönül, Mar 30 '15 at 06:37
possible duplicate of [Howto? Parameters and LIKE statement SQL](http://stackoverflow.com/questions/251276/howto-parameters-and-like-statement-sql) — Philip Stuyck, Mar 30 '15 at 06:38
I've seen that in a couple of examples. It's just all about the apostophes isn't it? Nevermind... That example above doesn't work — , Mar 30 '15 at 06:39
btw ... this smells like [SQLInjection](http://stackoverflow.com/questions/601300/what-is-sql-injection)! And, ultimately, it looks like you are doing SQL in your web-layer - please look into n-tier-architecture (and ORM btw) — , Mar 30 '15 at 07:02
How to avoid injection? Should I replace every single parameter? So every columnname and the tablename? — , Mar 30 '15 at 07:11

score 0 · Answer 1 · answered Mar 30 '15 at 06:39

0

What if you try

sqlCmd.Parameters.Add("@filterCustomers",SqlDbType.VarChar,8).Value = form1.filterCus().Trim() + "%";

Instead of:

sqlCmd.Parameters.AddWithValue("@filterCustomers", form1.filterCus().Trim() + "%");

You can change the.VarChar for what you data type is and the 8 for the required max field lenght, or remove the ,8 completely for no limit in the parameter lenght

answered Mar 30 '15 at 06:39

maam27

434
2
19

What difference that makes? If OP's code won't work with his variables, your code don't work either in my opinion.. – Soner Gönül Mar 30 '15 at 06:42
I get the same error message :( When I do this with not parameterized values then it's working fine – Mar 30 '15 at 06:45
@ScorpX What `form1.filterCus().Trim()` returns exactly? And what are the names of your table and columns? And your column types? – Soner Gönül Mar 30 '15 at 06:47
Just thought since it said to declare the variable maybe the issue of that would be the way of declaration, I just posted this as a thing to try. nothing more. But @ScorpX perhaps its due to `form1.filterCus()`, have you tried replacing the value of the variable to something pre set? like set it instead of the `form1.filterCus()` to a string there. – maam27 Mar 30 '15 at 06:48
form1.filterCus() is just a method that grabs the String from a textbox. The column type is an Integer but it worked that way everywhere. After the statement I use "sqlDatAdapter = new SqlDataAdapter(sqlCmd.CommandText, connection); sqlDatAdapter.Fill(datTable); form1.setDataGrid = datTable;" – Mar 30 '15 at 06:54
Perhaps the parameter doesn't work with the method, have you tried setting the output of the method to a variable before the query and using the varable to set the parameter value? – maam27 Mar 30 '15 at 06:56
does the string get the value like it should? where does it break if you try it in those steps? – maam27 Mar 30 '15 at 07:03
Yes the value is correct. It breaks when I fill the DataTable from the DataAdapter. – Mar 30 '15 at 07:06

null · Answer 2 · 2015-03-30T07:14:46.303

0

you need to put your string inside single quotes ':

...+" LIKE '"+ form1.filterCus().Trim()+"%'", connection);

if you don't worry about SQL INJECTION

EDIT: you can also use dynamic sql:

sqlCmd = new SqlCommand("EXEC ('SELECT..... WHERE FIELD LIKE ''' + @filterCustomers + '%''')", connection);
sqlCmd.Parameters.AddWithValue("@filterCustomers", form1.filterCus().Trim() + "%");

edited Mar 30 '15 at 07:14

answered Mar 30 '15 at 06:52

null

7,377
3
20
41

Think you made a small mistake with your awnser since `LIKE @filterCustomers", connection);` suddenly got something more added behind the value of the `LIKE` statment – maam27 Mar 30 '15 at 06:57
When I try this he returns nothing when I'm searching an element. – Mar 30 '15 at 06:57
@Scorp, because you are passing the parameter wrong – null Mar 30 '15 at 06:58
@Farhęg nope, that will render to `LIKE '@filterCustomers'` which will search for `@filterCustomers` and not its content. Please see this answer for [a correct solution](http://stackoverflow.com/a/251380/57508). – Mar 30 '15 at 07:00
@AndreasNiedermair, of course it will, I just wanted to tell about right use of like statement – null Mar 30 '15 at 07:02
@Farhęg yep, absolutely. But, this still smells like [SQL Injection](http://stackoverflow.com/questions/601300/what-is-sql-injection) – Mar 30 '15 at 07:05
Your edit doesn't make much sense: `@filterCustomers + '%'` and `form1.filterCus().Trim() + "%"` which will result in `foo%%`... – Mar 30 '15 at 17:34

Using parameterized SQL LIKE Statements

2 Answers2