Does ADO.NET sanitize wildcards out of string SqlParameters?

Question

Does ADO.NET sanitize wildcards (i.e. "%" or "_") in string-type SqlParameters? Furthermore, does it need to? I've tried Googling for this, but it's hard to find exactly what I want to know.

For example, suppose I have a parameterized query:

string sql = "SELECT * FROM Users WHERE UserName LIKE @prefix";

SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("prefix", User.UserName);
//etc...

But the user somehow manages to get himself a username like %. His idea is that he'll see all users, not just the ones he was supposed to see. Will this work, or would the % get a) escaped when the value is parameterized, or b) evaluated as a literal % by the SQL server?

Note that I realize the above would be a really lousy design. It's just a made-up example.

Edit: I've confirmed by testing that option b) doesn't happen.

declare @prefix as varchar(10);
set @prefix = '%';
select * from MyTable where MyField like @prefix;
--returns everything

Similar question: http://stackoverflow.com/questions/258757/escape-a-string-in-sql-server-so-that-it-is-safe-to-use-in-like-expression — Forgotten Semicolon, Mar 02 '11 at 16:53

score 2 · Answer 1 · answered Mar 02 '11 at 16:24

2

No! There is nothing to sanitize, it is valid value. It is your problem that you put it into LIKE statement. ADO.net prevents ' and other chars that can cause injection.

answered Mar 02 '11 at 16:24

Andrey

59,039
12
119
163

Well, it doesn't PREVENT a single quote, or any other character, but it makes sure all the characters will be interpreted as part of the parameter value, not as a metacharacter used to delimit the value. If you want to, you can think of it as replacing single quotes with two occurances, but actually if you're using the SqlProviders it will use a native protocol to talk to MSSQL and won't just send strings to parse them again at the server! – The Dag Feb 19 '16 at 13:03
@TheDag first time I hear that it doesn't send the strings. Any details on that one? – Andrey Feb 19 '16 at 15:08

Magnus · Accepted Answer · 2011-03-02T16:49:53.370

1

You need to escape it yourself, this would do it:

string sql = "SELECT * FROM Users WHERE UserName LIKE (@prefix + '%') ESCAPE '~'";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("prefix", User.UserName.Replace("~", "~~").Replace("%", "~%"));

edited Mar 02 '11 at 16:49

answered Mar 02 '11 at 16:37

Magnus

45,362
8
80
118

I like the `escape` clause. Didn't know you could do that, so I've learned something new. Wouldn't that replacement string have to be `"~%"`, though? And I think you'd need `Replace("~", "~~")` before that, right? – Justin Morgan - On strike Mar 02 '11 at 16:44
Consider data with single quotes and search also having `single quote`. Does this solution work? Then what about data with two consecutive single quotes? – LCJ Dec 13 '12 at 15:38
@Lijo You escape single quote with another single quote – Magnus Dec 13 '12 at 20:00

Does ADO.NET sanitize wildcards out of string SqlParameters?

2 Answers2