Is reliance on parametrized queries the only way to protect against SQL injection?

Question

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:

1. No client/application had direct access to the database tables.
2. All accesses to all tables were through views (and all the updates to the base tables were done through triggers).
3. All data items had a domain specified.
4. No data item was permitted to be nullable - this had implications that had the DBAs grinding their teeth on occasion; but was enforced.
5. 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:

1. An extensive data dictionary, combined with an extensive security data dictionary
2. Generation of triggers, queries and constraints from the data dictionary
3. 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).

Answer 1

Stored procs don't automatically protect against injection. What about this

CREATE PROC proc
  @id VARCHAR(5)
AS
BEGIN
  EXEC("SELECT * FROM Client WHERE ClientId = " + @id);
END

Using parameterised queries will protect you against injection, whether they are in procs or not.

Answer 2

So is a set of (enforced) rules such as this an appropriate alternative to stored procedures in preventing SQL injection attacks? If not, why not?

No, because they inflict rather a heavy penalty on the developers. A per-item breakdown:

1. No client/application had direct access to the database tables.

Use roles. Clients should only be able to access the DB through a restricted role which only has SELECT, INSERT, UPDATE and DELETE access to those tables (and rows, where possible) which it needs access to. If you want to make sure that no client can spam or delete all entries, use an API for data modification.

2. All accesses to all tables were through views.

That could be anything from negligible to a huge performance cost, depending on the efficiency of the views. It's unnecessary complexity which slows down development. Use roles.

3. All data items had a domain specified.

Could be a lot of work to maintain, and should probably be normalized into a separate table.

4. No data item was permitted to be nullable - this had implications that had the DBAs grinding their teeth on occasion; but was enforced.

That's just plain wrong. If the developers are unable to handle NULLs you have big problems.

Can a database be secured against such attacks by database (only) specific measures?

You do not need stored procedures, just use parametrized queries with a function that escapes the arguments, such as pg_query_params. Of course, if your database is world writeable or the client role has full access to everything, you're screwed anyway. Somebody just has to come along and realize what the client is doing, and then cook up a client in five minutes that destroys (or worse, poisons) your DB.

Answer 3

I'm not sure your rules do protect you completely.

The first problem is that you state they're enforced but, as well as coming with a significant overhead, I've never seen perfect enforcement.

Secondly my reading of them is that rules like these might make things harder to exploit but they don't prevent it. For instance, not having direct access to the tables doesn't actually change much if the views allow you to access the same data. If the client needs to do something a view needs to facilitate that and if a view facilitates it the same functionality / data can be utilised by an attacker.

Remember too that it's not just about updating or deleting data. Part of the vulnerability with SQL injection is information gathering and for that you don't care whether the data has been passed back through the view vCustomers or underlying Customers table. You may have protected yourself from some weaknesses but not all. Similarly if the updates can be made by the client, even if through triggers, then SQL can be written to fire the triggers and make updates.

(In terms of all updates being done via triggers I'm going to say two things: (1) when I read this I did a little bit of sick in my mouth and (b) you don't like Stored Procedures because they're "less maintainable; less testable; highly coupled; and locked a system into one vendor" but you do use triggers about which the same things can basically be said.)

All you need is one hole which allows execution of SQL statements (and I don't see any of these rules preventing that) and the attacker is in. They may be finding a very unintuitive database behind them but if they're determined that will only slow them down rather than stop them).

The other thing here is that you're also adding complexity and (as well as the overhead that creates), complexity tends to lead to holes that can be exploited.

I'm not saying that such a set of rules couldn't be created - more why would you bother? They seem more cumbersome and less reliable than just going with the widely accepted methods of preventing this sort of attack.