SQL Injection: Not an Issue

Denis Gobo has a post about Microsoft addressing SQL injection. Microsoft is being more proactive in preventing SQL injection by providing tools to scan sites and code. Here is a thought; stop publishing example code with inline SQL. Best practices are 'best' for a reason. Denis closes by stating that the 'problem is of course that you should never ever run as dbo or even worse sa.' And it really is almost that simple. An application account should have the minimum permissions needed for execution. If you use stored procedures, you do not even need to grant select permissions on tables (you aren’t using dynamic SQL, are you?). What is better than that?

Best practices tell you to check your inputs but really you can be rather lazy about this if you are parameterizing (it’s a word now) your stored procedure calls and are not using dynamic SQL; pretty hard to inject a string into a number field. That means that all you need to do is validate strings. Even if you do not do that, the account can only connect and run procedures. Failing this (because you really absolutely cannot see how you cannot use dynamic SQL (think harder)), use parameterized queries. Make certain that the application limits inputs—why is a name field large enough to contain an entire SQL block?

So what have we learned?

• Minimum permissions
• Use stored procedures
• Check your inputs

I do not mean to oversimplify, but I cannot make it sound complicated. If you code right, your work is done. SQL injection is an issue for half-assed developers. The end.

Tags: sql injection, security