Additional Defenses as a Mitigation Against Future Mistakes

Prepared statements do not cover it all

Proper use of prepared statements is our primary defense against SQL injection. Prepared statements are great, but we have to remember to use them every time we write code that touches SQL; we’re never “done” with applying this defense. And if we’re building complex, dynamic SQL statements with user input in parts of the SQL that aren’t parameterizable, we need to exercise a great deal of caution in many places in the codebase. If we’re sloppy in just one of those places, we can wind up leaving the door open to future SQL injection.

Access control

It would be great if we could complete a one-time task that would protect us throughout future development. Unfortunately, we don’t have anything quite that powerful, but proper use of database permissions can get us part of the way there. In theory, we could have a single database user for each table that we want to work with. In practice, this is unlikely to be effective except in very small applications. There are likely to be a large number of tables in an application. And some interactions involve using multiple tables in a single statement. If the number of tables doesn’t get you, the number of combinations of tables will.

Dedicated databases

While it isn’t worthwhile to introduce a dedicated database account for every table, it can be worthwhile to introduce them for particularly sensitive tables, such as audit tables or tables that contain passwords. It would be unfortunate if SQL injection in some random part of your application allowed an attacker to change admin passwords or cover their tracks by deleting audit records.

                                                 Q U I Z