SQL Injection and LIKE statements

Question

What would be the best way to stop SQL injection with a LIKE statement? So here is a example of the code:

string search = Server.HTMLDecode(userEnteredSearchText);
SqlCommand comm = new SqlCommand("SELECT Result WHERE (Keyword LIKE '%" + @search + "%') "
comm.Parameters.Add(new SqlParameter("search", search));

This is what I have been doing other sql statements and it seems like special characters such as ' and % can't break those statements, but I'm guessing with a LIKE statement you need to do a escape key or something?

possible duplicate of [Avoiding SQL Injection in SQL query with Like Operator using parameters?](http://stackoverflow.com/questions/228476/avoiding-sql-injection-in-sql-query-with-like-operator-using-parameters) — oleksii, Jan 05 '13 at 22:28

score 4 · Answer 1 · answered Jan 05 '13 at 22:52

The following looks a little cleaner to me.

string search = Server.HTMLDecode(userEnteredSearchText);
SqlCommand comm = new SqlCommand("SELECT Result WHERE (Keyword LIKE @search) "
comm.Parameters.Add(new SqlParameter("search", String.Format("%{0}%", search)));

score 2 · Accepted Answer · answered Jan 05 '13 at 22:28

2

Sorry the quotes are off. You do it precisely like you do with everything else, except that within the SQL you need to concatenate the %'s. Also, HTMLDecode is probably not doing you any good here at all, right? What if they want to search in the db for things that contain "&"

Search = userEnteredSearchText);

SqlCommand comm = new SqlCommand("SELECT Result WHERE Keyword LIKE '%' + @search + '%'");
comm.Parameters.Add(new SqlParameter("search", search));

So in your example, you were closing the SQL literal to put in @search, which is an invalid keyword or variable - you just need to leave that inside the SQL statement. Other DB's, you need to CONCAT the things together. But the bind variable will properly escape the stuff going to the driver.

answered Jan 05 '13 at 22:28

Colselaw

1,069
9
21

Nice thanks! And what do you mean by your HTMLDecode comment? So I always thought when you take in text from the browser into the server, you should HTMLDecode and when you transfer text from the server to browser, you should HTMLEncode? – Kevin Jan 05 '13 at 22:44
1

When you get data from the request using standard methods, it's already HTTP (not HTML) decoded for you. HTML Decode is for removing HTML encoding - so things like `&` get translated back to `&`. However, under normal circumstances, there's nowhere the user's data will have been HTML encoded. Yes - you should HTML encode when sending dynamic data back, but when you're receiving data, it's not HTML encoded. – Colselaw Jan 05 '13 at 22:47

score -1 · Answer 3 · answered Jan 05 '13 at 22:42

-1

Avoid single quote for sure in the parameters when you are sending a sql query to database. Remove any special character present in the parameter in the c# code itself. I would suggest avoid as many special character as possible.

answered Jan 05 '13 at 22:42

Amit

113
1
9

Downvote is because one purpose of using a parameter is to eliminate the problems with special characters. – Andrew Morton Jan 05 '13 at 22:48

SQL Injection and LIKE statements

3 Answers3