Of course, there’s a lot that can be said when it comes to auditing the security of SQL based applications. Even so, I’ve been looking at a lot of application code lately and keep coming across the same errors again and again.
In short, here’s something that you must absolutely find in your application code if you want any kind of assurance that the database is secure: Bound Queries (aka parameterized queries).
There seems to be a fundamental misunderstanding that implementing queries through the use of Stored Procedures solves all of your problems instantly. This just isn’t true. In fact, what I keep finding is that programmers who got caught using dynamically built queries in their code (one of the worst things to do when you’re using user input) satisfied the auditor by switching to Stored Procedures. Some of this confusion may come from blog postings such as this which are confusing, misleading and in some places just plain wrong!
The problem is that what I find them doing is taking the SQL query out of their code and putting it directly into the Stored Procedure with no additional validation, testing, etc. occurring! When they do this all that they’ve really done is relocate the SQL injection potential into the Stored Procedure.
While we’re not trying to replace data input validation, using Parameterized or Bound Queries instead actually renders the data safe, preventing SQL injection.
So there it is. In a nutshell, if your applications are taking user data and performing SQL queries using that data, you should require that Parameterized or Bound Queries are in use!