Search Function not working properly

Question

I have a function that gets called with a parameter called "name" now it works fine.. until i do a search with a ' in it.. so I am sending this as the name

Plastic & Foam 3'X5' XLT

When i send that it returns empty in the website (no errors) but if i send jusr Plastic & Foam it works fine, it comes back ok. Now in the sql part I get this when i send the query through management studio like this

exec sp_productSearch 'Plastic & Foam 3''X5'' XLT'

SELECT Name FROM PProducts WHERE NAME LIKE '%Plastic & Foam 3'X5' XLT'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'X5'.

In C# i am sending the text list this

Server.HtmlEncode(txtproductname.Text.Trim().Replace("'", "''"))

Any Ideas what I may be doing wrong?

PS: The stored procedure is simple, is something like this

Select Name from PProducts Where NAME LIKE '%' + @NAME  + '%' ;

Thank you.

allowing `'` in your search queries / database is a very bad practice (sql injection) — Matten, Dec 26 '11 at 16:33
We need to see C# code, not SQL queries, to be able to answer this. Also, please change the title to summarize your problem. Thanks. — Arjan, Dec 26 '11 at 16:33
Please include your C# code that shows where you call the `sp_productSearch` stored procedure. I suspect that you need to use parameters in your SQL query rather than injecting the value of the string to search directly into your stored procedure call. — Tim S. Van Haren, Dec 26 '11 at 16:36
Why do you `HtmlEncode` anything when it goes to a SQL Server? Are you using parameterized queries? — Oded, Dec 26 '11 at 16:39
Please post the whole procedure so we can see if you need to use dynamic SQL at all. — Martin Smith, Dec 26 '11 at 16:44
The issue as mentioned in an answer below was the Server Encode, this is existing code so I am just fixing the bugs but could not see the issue, now i do.. thank you — jedgard, Dec 26 '11 at 19:50

score 3 · Answer 1 · edited May 23 '17 at 12:03

3

You're passing ' characters "raw" to a query in SQL? You're facing a problem called SQL injection.

Please find out a solution for passing data "safely" to your SQL server. For C# you should check eg. this question for closer tips.

edited May 23 '17 at 12:03

Community

1
1

answered Dec 26 '11 at 16:41

mico

12,730
12
59
99

4

I guess he is facing a problem called [Little Bobby Tables](http://imgs.xkcd.com/comics/exploits_of_a_mom.png) ;-) – Uwe Keim Dec 26 '11 at 16:54

Martin Smith · Accepted Answer · 2011-12-26T17:03:11.183

It looks like you already have a parameter called @NAME

So your stored procedure just needs to do

SELECT Name
FROM   PProducts
WHERE  NAME LIKE '%' + @NAME + '%'

Or, better, so it returns correct results in the event that any of your product names contain pattern characters such as % don't use LIKE at all and use

SELECT Name
FROM   PProducts
WHERE  CHARINDEX(@NAME,NAME) > 0

Or, even better, use Full Text Search

I presume that you are building the query string inside the stored procedure and then executing it. This is not necessary based upon what you have posted so far and is a SQL injection risk.

Also don't manually escape the txtproductname.Text value. Pass it in "as is" using parameters.

lalibi · Answer 3 · 2011-12-26T17:36:53.173

1

EDITED

This last part of your questions is a little bit scary

Select Name from PProducts Where NAME LIKE ''%' + @NAME  + '%'''

I was wondering why you use so many quotes and you are possible executing SQL through string concatenation:

EXEC 'Select Name from PProducts Where NAME LIKE ''%' + @NAME  + '%'''

If that is the case then a quick/dirty way to solve it is to escape quotes twice which means something like:

EXEC sp_productSearch 'Plastic & Foam 3''''X5'''' XLT'

This statement should run without problems but it is a really poor way to execute SQL query with user input. Other answers cover that already.

On another note, & becomes & with the call of Html Encode according to this page, so be careful with that too.

edited Dec 26 '11 at 17:36

answered Dec 26 '11 at 16:57

lalibi

3,057
3
33
41

Read the question. They say this query in SSMS `exec sp_productSearch 'Plastic & Foam 3''X5'' XLT'` generates this error `Incorrect syntax near 'X5'.`. No `HTMLEncode` in sight and on it's own searching for `Plastic & Foam ` rather than `Plastic & Foam` would just mean potentially wrong results not a syntax error. Though I agree it seems incorrect to use it. – Martin Smith Dec 26 '11 at 17:05
those exta quotes were a mistake when writing it there thanks – jedgard Dec 26 '11 at 19:21

Search Function not working properly

3 Answers3