Escaping user input in SQL parameters

Question

I have the following WHERE condition in a SELECT statement:

WHERE ('.' + column_name LIKE @prefix))

And the @prefix parameter is set as follows:

cmd.Parameters.AddWithValue("@prefix", "%[^a-z]" & prefix & "%")

The prefix variable is taken from form input.

The problem I'm having is when the user enters a '%' or '[' or other special character. How can I make sure these are escaped rather than being treated as special characters?

Related: Generally, how do you stop people entering '%' in their input and having this treated as a wildcard?

possible duplicate of How to cleanse dynamic SQL in SQL Server -- prevent SQL injection — Preet Sangha
– Preet Sangha, Commented Jun 6, 2012 at 1:30
@PreetSangha I think this question is narrower in scope, because it asks for escaping metacharacters in LIKE expressions. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Jun 6, 2012 at 1:34

Sergey Kalinichenko · Accepted Answer · 2012-06-06 01:32:45Z

2

SQL's LIKE operator provides an optional ESCAPE clause:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

You can set the escape character to a single character of your liking, and escape metacharacters with it.

answered Jun 6, 2012 at 1:32

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Flash Over a year ago

Can you give me an example of this for my query?

Sergey Kalinichenko Over a year ago

@Andrew You can use Regex.Replace(prefix, "[~%_[\\]]", "!$0") to escape your prefix with the exclamation point !, and then use exclamation point in your escape clause: column_name LIKE @prefix ESCAPE '!'.

Flash Over a year ago

Thanks. I have changed to prefix = Regex.Replace(prefix, "[!~%_[\]]", "!$0"). I gather the ! needs to be escaped since it is the escape character. Also, why did you include ~?

Sergey Kalinichenko Over a year ago

@Andrew "why did you include ~?" Because it is next to the ! on the keyboard, so I pressed the wrong key :) It was supposed to be [!%_[\\]].

Collectives™ on Stack Overflow

Escaping user input in SQL parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related