1

I would like to know if you can make Sql Server look at incoming query strings and reject ones that match a certain pattern. In other words, for a certain class of queries (perhaps identified by a regular expression), I want to know if Sql Server could be made to return an error message rather than processing them as it normally would.

To be more concrete, and to give a potential use case, consider this description of a Sql Injection attack. If my website were under sustained attack from techniques along those lines, and if getting the website patched looked like a lengthy process, it would be tempting (if Sql allowed it) to try to get some provisional relief by putting something in Sql that basically said

If a query matches regex CAST(0x[0-9A-Za-z]{20,} then don't execute it!

My guess is that, if this sort of filtering is possible, then doing it would require writing some kind of custom Sql Server Add-In DLL. But maybe you can do something like this with Sql Server 2008's built-in auditing features? I really have no idea, and I'm not sure where in the documentation would be the best place to start looking.

If you want to point out why the database is a stupid place to try to block sql injection attacks, that's fine. But my primary question is not whether it's a good idea in this particular use case but whether this sort of query filtering/rejection is even possible.

Improve this question

3 Answers 3

Reset to default
2

Your hunch was correct: the database is the wrong place to tackle this problem. The whole reason SQL injections work is because the commands are valid SQL. Generally by the time your application passes the query to the database server the special characters have been turned back into normal text.

If you don't feel like mitigating this at the application level then you should look into software firewall solutions such as dotDefender. In my opinion this is also attacking the problem in the wrong place though and something will eventually get through the firewall's logic.

Improve this answer
3

No, you can't have SQL server reject statements based on some pattern match. How about denying SELECT privileges to all users and force them to use stored procedures you create to access data in your underlying tables? As long as you do a good job validating the inputs to the sprocs you should be fairly safe.

Improve this answer
0
1

No there's no way to have the SQL Server reject commands based on a pattern match. To do something like this you'd have to write a listener to accept the SQL Commands, check them, then pass them off to the database engine for execution.

You'd be much better off handling this at the application layer.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .