@AC 8th December 2011 23:01
<quote>It's not exactly tricky is it? Sanitize your inputs, use an ORM to build your queries rather than generating SQL queries by hand, or at the very least use a DB abstraction layer to perform parametrized queries.</quote>
No, don't use an ORM - work out what functionality the app needs from the database and provide that functionality as a set of stored procedures. Using an ORM generates a strong coupling between the database schema and the apps object model, throwing away any chance of true modularity. Using stored procedures for the interface decouples the schema from the application, the application doesn't even have to know what tables and views exist, just what procedures it can call (that's also why you don't use parametrised queries instead of stored procedures). This makes maintenance and future enhancement much less error prone, and is perfectly secure against injection unless you have a lunatic database developer who commits the crime of using a string parameter from outside to construct sql text to be executed. Verify all inputs in the app too of course - and even if you have client side input verification do it again server side, because it's easy for me to write a malicious browser that modifies your client-side Javascript or VBScript or whatever it is you use and sends you whaever inputs I want without their ever having been seen by your client-side validation. And normalise the database schema at least as far as EKNF, preferably to 5NF with possibly some tables left at EKNF to preserve the representation principle, since that too makes maintenance easier (by making the schemas keys and constraints enforce conformance of the data to all business rules that are expressible in the unnormalised schema as domain constraints, functional dependencies, multivalued dependencies, or projection-join dependencies, so that a large class of bugs can never occur. And finally, make sure that the apps connection to the database uses a user (preferably an OS user, but a database user will do at a push) whose only permitted actions in the database are executing the stored procedures provided for the app, has no access to tables or views at all (the stored procedures should have permissions inherited from their authors, instead of from the app, so they can access and update the data; but the app can't do that except by calling the stored procedure, even if some rogue developer tries to stick some ad hoc queries into the app). And encrypt your database backups, using keys that change when they need to rather than stay for ever the same, and encrypt your database too (only change that key if you think there's a risk it is compromised, as it's a pain in the but to do). Of course if your physical security is such that you can guarantee no-one can steal a hard disc, or intercept traffic between you server and your SAN, or get hold of a discarded hard disc with either the database or backups on it, or if the risk is low, the potential damage if the risk is realised is also low and the extra software licensing cost of having database encryption is too high (you won't get it with a MS SQLS Standard Edition license, for example, you need an Enterprise license) you can maybe skip the encryption.
So, maybe not quite a simple as you suggested; but every competent DB developer knows that those are the things that should be done, and none of those things is the least bit complex or difficult, so almost that easy.