Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

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?


Emphasis of the question changed slightly, in the light of the initial responses received. Base question unchanged.


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:

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).

share|improve this question
I don’t buy the arguments against stored procedures. They are simply not true. – Konrad Rudolph Jul 21 '11 at 14:10
What's up with the no-nulls requirement? – Mark Canlas Jul 21 '11 at 15:05
@Konrad Rudolph - If you write your application on MySQL and then decide to migrate to DB2, do you really think the stored procedures are going to be compatible? Likewise if you want to migrate to SQLLite? Also, suppose you upgrade your OS--if your stored procedures are compiled in C (which they are in DB2), they'll probably all need recompiling. These are reasonable arguments--not absolute, but reasonable. – Matthew Flynn Jul 21 '11 at 16:54
@Matthew Duh. I was actually thinking of “parametrised queries” when reading that and commenting about it. Stored procedure = whole ’nother story. – Konrad Rudolph Jul 21 '11 at 20:58

3 Answers 3

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

  @id VARCHAR(5)
  EXEC("SELECT * FROM Client WHERE ClientId = " + @id);

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

share|improve this answer
Thank you for the focus on parameterised queries, rather than procs. However, I am asking whether the database can be protected by methods other than by such queries - in particular methods that are confined to the database layer only. – Chris Walton Jul 21 '11 at 10:35
+1 In addition to that, I'd like to state that stored procs are mostly considered secure because it is the only way to prevent users from directly accessing tables while still maintaining a way to retrieve the data. It's the only way to ensure row-based and column-based privileges when the user needs to have direct database access with his client without anything in between. – Falcon Jul 21 '11 at 10:36
@Chris - I think what Craig is saying here is that you can't assume that procs actually do protect you. It's perhaps not a complete answer, more a correction of the assumption in the title. – Jon Hopkins Jul 21 '11 at 11:48
@Jon - I have altered the title of the question, and made some edits to the question, in the light of Craig's correction. I was not aware of the assumption I was making in the question, until I started receiving replies. – Chris Walton Jul 21 '11 at 11:52
To reinforce what Craig writes above, see, "Lateral SQL Injection: A New Class of Vulnerability in Oracle" – Bruce Ediger Jul 21 '11 at 18:16

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.

share|improve this answer
Domain: – Dan McGrath Jul 21 '11 at 11:11
+1 for roles. They are a major contributor to this - I did not include roles in my question, but they were a part of the setup - in particular the views were assigned a restricted role such as you suggest for clients. Point taken on the performance hit of views. Domains included validation tests - ranges and length mostly. Your comments on the data nullable rule are much more polite than some I heard about this rule. I did not state explicitly that the permissions would be set up appropriately though this was my assumption. – Chris Walton Jul 21 '11 at 11:15

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.

share|improve this answer
+1 for comprehending my actual query in the light of my implicit, unconscious assumptions, and for responding to it appropriately. As for why one might bother - I am working on a project where much of the code will be generated from a relevant description of the architecture - and part of this architecture describes how to generate database access routines. It is still open as to what shape these generated routines will take. – Chris Walton Jul 21 '11 at 12:32

protected by gnat Feb 16 at 20:30

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.