I think this also illustrates why DBA's need to included in development and why code must be vetted by someone competent to do so.
If you are going to use the command object then you can go one better and wrap it all up in a DLL.
In addition to the SQL Injection attack protection you are running compiled code.
I have seen cases where tweaking IIS caused ASP to start spewing out either hacker friendly error messages or in one case ASP source code complete with connection strings.
Don't forget about the Open Web Application Security Project has another great source:
http://www.owasp.org/
Good information, thank you Chris.
As a DBA SQL Injection is one of my big concerns. My biggest problem usually lies in getting our developers to appreciate that it's a real problem that they need to be constantly aware of.
By limiting (eliminating) all direct grants at the table level and forcing all access through stored procedures, we can go quite a long way to safeguard our data. As Chris points out, stored procs, by themselves, don't eliminate the threat, but if all access is controlled through them, an attacker will not be able to do, or see, anything in the database that isn't specifically allowed anyway.
Well... with cross-database ownership chaining, there is still quite a bit of recon an intruder can do. Hence the reason to use Command objects or the equivalent thereof as well as stringent input validation.
True enough, but as a rule we don't allow cross-database ownership chaining. That, combined with locking down master as much as possible helps mitigate that threat.
I was not implying that forcing the use of sp's eliminates the risk, but as a DBA I'm not willing to just leave it all in the developer's hands either... The problem should be attacked at both ends.
Database Administrators should also be invloved in the development of organisational standards for software development. In my organisation we have a document that details the programming standards, and this includes standards for accessing databases. Code reviews help ensure that developers adhere to these standards.
We also have a library which all user-input is passed through and casted to the correct data type. All developers are obliged to use this library.
We also have a library which all end-user input is passed through and casted to the correct data type. All developers are obliged to use this library.
Sorry about the multiple posts - I kept on getting an error message each time I posted saying there was a problem casting from DBNull to a string - didn't realise that the post was succeeding each time.
If you really lock down master, then true, you've mitigated most of the rest. Most folks don't think to do this, unfortunately.
