Is reliance on parametrized queries the only way to protect against SQL injection?
- by Chris Walton
All I have seen on SQL injection attacks seems to suggest that parametrized queries, particularly ones in stored procedures, are the only way to protect against such attacks. While I was working (back in the Dark Ages) stored procedures were viewed as poor practice, mainly because they were seen as less maintainable; less testable; highly coupled; and locked a system into one vendor; (this question covers some other reasons).
Although when I was working, projects were virtually unaware of the possibility of such attacks; various rules were adopted to secure the database against corruption of various sorts. These rules can be summarised as:
No client/application had direct access to the database tables.
All accesses to all tables were through views (and all the updates to the base tables were done through triggers).
All data items had a domain specified.
No data item was permitted to be nullable - this had implications that had the DBAs grinding their teeth on occasion; but was enforced.
Roles and permissions were set up appropriately - for instance, a restricted role to give only views the right to change the data.
So is a set of (enforced) rules such as this (though not necessarily this particular set) an appropriate alternative to parametrized queries in preventing SQL injection attacks? If not, why not? Can a database be secured against such attacks by database (only) specific measures?
EDIT
Emphasis of the question changed slightly, in the light of the initial responses received. Base question unchanged.
EDIT2
The approach of relying on paramaterized queries seems to be only a peripheral step in
defense against attacks on systems. It seems to me that more fundamental defenses are both desirable, and may render reliance on such queries not necessary, or less critical, even to defend specifically against injection attacks.
The approach implicit in my question was based on "armouring" the database and I had no idea whether it was a viable option. Further research has suggested that there are such approaches. I have found the following sources that provide some pointers to this type of approach:
http://database-programmer.blogspot.com
http://thehelsinkideclaration.blogspot.com
The principle features I have taken from these sources is:
An extensive data dictionary, combined with an extensive security data dictionary
Generation of triggers, queries and constraints from the data dictionary
Minimize Code and maximize data
While the answers I have had so far are very useful and point out difficulties arising from disregarding paramaterized queries, ultimately they do not answer my original question(s) (now emphasised in bold).
