Can a Regular Expression Be Used to Prevent SQL Injection

Question

Before I ask - I do know about paranterised queries but this question is specifically baout regular expressions to prevent SQL injection

Let's say I have a querystring with a paramater of q and I have a regular expression of ^201[0-9]Q[0-9]$ that I run against the parameter that's passed into SQL.

Would this suffice as protection or can the regex be exploited anyway? Some pseudo code:

 if NOT validateToken(quarter) then
     sql = "EXEC dbo.spTest '"&quarter&"'"
 end if

Thanks

Answer 1

In your case, yes, the check is enough to prevent SQL Injection. The kind of check you are putting up is the kind of check needed when dealing with user input.

So, as a rule of thumb you should never concatenate user-provided data and SQL code, unless, that is, you provide a comprehensive check about the content of the parameters.

WAIT! Are you checking the parameter in .Net or in SQL? If in .Net, you're safe. If unsafe code gets to SQL, you're royally screwed.