Using SQLParameter for SQL: "LIKE '%@parameter%'"

I'm going crazy over this, and would appreciate your help.

I've got 2 queries in my web app:



The first one works and the second one doesn't -- because the second one uses the parameter @SearchTerm. If I were to replace the parameter name with an actual value then the second query would work.

I'm plugging the SQL into a data access class that I wrote, where I am trying to handle the parameter value...The relevant code:



By the way, the Dictionary<string, string> parameters variable contains one key-value pair, set in the web app itself (before being sent to the data access class -- which is included as a reference in the web app):



tbSearchTerm.Text is the value of a text box on a web page.

Anyway, the SQL connection is fine, and everything works for the first query (without the parameter). It's the second query that just won't work no matter what I do. In debug mode, I can see that the Dictionary<string, string> parameters variable does contain the parameter name and value (in method MakeDbCommand). After the parameter is added to the SqlCommand and returned to method GetTableInfo, it also appears that the parameter is stored correctly in _dataAdapter.SelectCommand. But the GetTableInfo method is returning a null DataTable, ONLY for that query.

Can you help?

Thanks! That worked...

Now the big question is, why does it work?

Is that escaping/concatenation of the parameter name only something you have to do inside LIKE operators?

Thanks again.

SQL sees '@SearchTerm' as a literal string.

so in



SQL is actually looking for the literal string "@SearchTerm" in the column. when you use the concatenation(sp?), SQL uses the variable value of the parameter.

It will make more sense when I explain why your original code didn't work. You had this:

The problem there is that your query parameter is inside quotes. Because it's in quotes, SQL Server will interpret it as a string literal and never sees it as a parameter at all. To make SQL Server both see the parameter and add the wild cards, you have to concatenate the wildcards to the parameter. Think about what would happen if your wrote a query like this:vs if you write the query like this:


You could have also fixed your bug like this:

and

Hi I have a code that pass parameters, but if the parameters are empty it return all rows, I uses the LIKE operator. How can i validate the parameters so that it will return no "results found"

here is my code:

select isnull(a.pdlttr,'-') pdlttr, a.pddcto, isnull(a.pddsc1,'-')pddsc1, replace(a.pddsc2,' ','-')pddsc2, a.pddoco, convert(char(10),DATEADD(day,CONVERT(INT,convert(int,a.PDTRDJ,100)) - ((1000*(CONVERT(int,convert(int,a.PDTRDJ,100))/1000))) - 1,
DATEADD(year,CONVERT(int,convert(int,a.PDTRDJ,100)/1000), '1 Jan 1900')) ,101)PDTRDJ,
(a.pdlnid/1000)pdlnid, isnull(a.pduom,'-') pduom, isnull(a.pdlitm,'-') pdlitm, a.pdanby,
isnull(a.pdcrcd,'-') pdcrcd, (a.pdfrrc/100)pdfrrc, (a.pdprrc/100)pdprrc, a.pdan8
from PHI_SCM_TMP a WHERE
(((a.pddsc1 + a.pddsc2 like '%%' ) and
(a.pddsc1 + a.pddsc2 like '%%' ) and
(a.pddsc1 + a.pddsc2 like '%%'))
or a.pdlitm = '3033' )
and a.pdlttr not in (120, 980, 999)
and a.pdan8 <> 99999999
and a.pddcto = 'OP'

Dear f'lar, your post helped me solve the same issue randall was facing. Thanks a lot !

I also wanted to share some thoughts.
Parameters help out protecting against injection attacks.

But after seeing your example, I felt a little uncomfortable:
param.Value = "%" + parameter.Value + "%";

This sample demonstrates the % wildcard can be a part of the parameter value.

So, I could also have something like:
param.Value = parameter.Value;
Where parameter.Value is the user entered string "%My Search%"

I don't like the idea of users injecting special SQL symbols, and I thought SQL Parameters would prevent this by, for instance, encoding the % wildcard.

Do you know what I mean?

Now I wonder: How "permeable" are the sql parameters in regards to injection?


Cordially,
Agustin Garzon.

There are special SQL Symbols, and then there are special SQL Symbols. The parameter will guarantee only that the text is a valid string literal. If the user wants to use wild cards, they're welcome to it. It won't allow injection, because you can't take an existing query and turn it in to something new.

I get the picture you're worried about someone trying to use a wildcard in a username or password field, for example, to force your code to perhaps log them in even though they don't have an account. In that case the answer is simple: don't compare with LIKE for your authentication. Use plain old = instead. That doesn't use wildcards

Yeah, for authentication we always use the unequivocal equality symbol

I'll research more about SQL Parameters anyway.

My concern was a user being able to do something he's not supposed to do, like broadening a search with a wildcard. I would think it's for the developer to decide. Anyway, it's a harmful symbol. I'll run a research to see what else to expect

Thanks for your comments f'lar
Later


Agustin.

Think about it this way: what if you wanted to allow the user to include wild cards for a search? You would need a reliable secure way to do it. More to the point, you would need to be able to include the wild cards as part of the rest of the search text when creating your query parameters. It just has to work.

This is actually a much more common case than specifically needing to disallow user wildcards. Also, comparing the need to sanitize user wild cards from a query string when this works versus the need to shoehorn them in to the query somehow when it doesn't, I'd be much more worried about the results in the latter case. Typically, if you're using a LIKE query and specifically must restrict user wild cards, you'll know it.