This editorial was originally published on April 12, 2011. It is being re-run as Steve is away on vacation.
SQL Injection is not the fault of the SQL Server. Brian Kelley pointed that out, and reminded me that SQL Injection isn’t an case of malformed SQL. It’s legitimate code, including SQL commands that we might use from any query connection, especially an administrative one. We regularly issue update and delete commands from our applications, and SQL Injection takes advantage of this to issue an update the we might not be expecting.
Would you expect this input handwritten injection from an application? Or this table guessing attempt? You wouldn’t, but they can come through data entry in your application if the input isn’t well sanitized. Someone setting all your prices to $0.01 or all of your customers to “W3 0wnz U!” isn’t what you want to happen. You can’t necessarily prevent all of these patterns or check for every permutation, but you can prevent things like ‘shutdown’ or ‘drop table’ from being run by your application. Even adding a new user to the database system isn’t something I would want to allow.
Education is the key here. As Andy Leonard (blog | @AndyLeonard) would say, design patterns are important. When developers have an understanding of the issue, many of these things will be avoided. Having standard ways to begin building an application, checking for bad input, and setting up database users and permissions easily, should make this easy for anyone that wants to code against a database. We still have work to do here to build better frameworks, and ORM tools that require elevated permissions to the database are not the answer. They might become the answer, but they aren’t a better solution right now.
Grant Fritchey wrote a nice piece about developers and DBAS, noting the need that we both have the same goals, but need to learn to communicate better. This is one area where we ought to make an effort to communicate better, pass along education about security issues, and work to make life easier for developers to work with a database.
That also means teaching them to work with the minimum privileges needed in order to make an application work, just in case someone plans on submitting some input you didn’t expect.