Giving SQL Injection the Respect it Deserves

You may have read recently about a large number of Web servers that were compromised through a SQL injection attack. The malicious SQL payload is very well designed, somewhat database schema agnostic and generic  so it could compromise as many database servers as possible. While the attack was a SQL injection attack that attacked and compromised back-end databases courtesy of vulnerable Web pages, from a user’s perspective the real attack was compromised Web pages that serve up malware to attack user’s through their browsers. In essence, there were two sets of victims: the Web site operators and the users who visited the affected Web sites. In this post, I want to focus on what the first set of users, the Web site operators, can do to protect themselves.

The fact that the malicious payload was so generic shows that the science of SQL injection has not taken a back seat to research in other vulnerability types, such as buffer overflows or cross-site scripting issues.

I think the first lesson from this attack is this:

If you have a Web server (doesn’t matter what type), and it’s hooked up to a database (doesn’t matter what type) you need to go in and review your code that performs the database work.

So now that you’ve determined the database access code, now what? The SDL is very specific about what do here, there are three requirements – they are requirements not recommendations, which means you must do the following coding requirements and defenses

  • Use SQL Parameterized Queries
  • Use Stored Procedures
  • Use SQL Execute-only Permission

Use SQL Parameterized Queries

From the SDL documentation:

“Applications accessing a database must do so only using parameterized queries.

Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application. This vulnerability allows for unauthorized, interactive, logon to a SQL server which may result in the execution of malicious commands leading to the possible modification (or deletion) of Operating System or user data.

Combining the use of parameterized queries and stored procedures helps to mitigate the risk of successful exploitation of user input which is not correctly verified.”

This defense has been known about forever; heck, David and I discussed this in detail in the first edition of Writing Secure Code in 2002:

From page 320, “Another way to perform this kind of processing is to use placeholders which are often referred to as parameterized commands.”

Just about every database access technology supports parameterized queries; work out what they are for your DB technology and use them: the defense for a PHP/MySQL combo will not be the same as a C#/SQL Server combo.

The most likely cause of these recent compromises is using string concatenation to build SQL statements. Just don’t do it, even if you think you’re safe, just don’t use string concatenation to build SQL statements! There are some very specialized cases where string concatenation is valid, but they are rare, especially for Web apps. In my opinion, any use of string concatenation in a Web application is a high-priority bug.

Use Stored Procedures

From the SDL documentation:

“Applications accessing databases should do so only using stored procedures. ”


“Do not use “exec @sql” construct in your stored procedures.

Using stored procedures helps to mitigate the SQL injection threat to a great extent since type checking is available for parameters. If the attacker supplies input that does not match the type constraints the stored procedures will throw an exception. In the vast majority of the cases, this should be properly handled within the application.

However, if the stored procedures perform string manipulation in their code and then execute that query using the “exec @sql” construct incorrect handling of user input can produce the same SQL injection vulnerability as would be seen at the application layer.”

Note the words “help mitigate,” by themselves stored procedures do not remove SQL injection vulnerabilities; they just raise the bar on the attacker by hiding much of the underlying database schema from the attacker.

Use SQL Execute-only Permission

This next defense is interesting in that it is a defense in depth method; in this case it assumes the attacker has successfully found a SQL injection bug in your code. Now what? Thankfully, this defense will stop most every attack dead in its tracks.

From the SDL documentation:

“Only grant ‘execute’ permission on all stored procedures, and grant that permission only for the application domain group.

Ensure that this group is granted execute permissions only on your stored procedures. Do not grant any other permission on your database to any other user or group.”

This is a great defense, because if the attacker attempts to access any other database object other than through a stored procedure (you can use views also), the underlying database permissions model prevents the attack by denying access to the attacker.

It’s interesting that the SDL offers three SQL injection requirements; only one actually remedies the problem (secure by design) and the other two offer mores defenses assuming failure (secure by default.)

Of course, a simple set of rules is not a substitute for careful design, implementation, and test. The SDL is a holistic process that covers the software lifecycle end-to-end, so don’t mistake these simple rules as a guarantee that you will avoid SQL injection problems. You need to understand the situations in which the rules apply. You may find, for example, that string concatenation is the best – or perhaps only – solution to a particular problem and these rules may not guard against SQL injection in those situations. Follow secure development practice throughout the lifecycle of your project – including things we left out of this blog, like testing and security response, for best results.

About the Author
Michael Howard

Principal Security Program Manager

Michael Howard is a principal security program manager on the Trustworthy Computing (TwC) Security team at Microsoft, where he is responsible for managing secure design, programming, and testing techniques across the company. Michael is an architect of the Security Development Read more »

Join the conversation

  1. NicoAtMicrosoft

    This is a great article on SQL database security.  I know that Microsoft provides a good tutorial on how to secure against SQL Injection attacks ( but I’ve seen a lot of best practices methods and they seem to all emphasize not using concatenation for building SQL statements.  But this is the first time I’ve seen emphasis on using stored procedures, so I’ll definitely be sure to take note!


    I’m pleased to see you got the right (and only needed) defense first on the list. I’m disappointed that you again bring the bogus idea that Stored Procedures add anything to the mix WHEN parameterized queries are used.  Do it right, the _one and only_ right way, and the rest of the stuff is just unneeded fluffery.

  3. Sunday Ironfoot

    I think the real issue here is amateur software engineers/web developers. SQL Injection is one of the oldest and most obvious tricks in the book and is relatively easy to guard against, if you’re developing websites and you don’t know about SQL Injection then you shouldn’t be developing websites. Also a developer who doesn’t know about SQL Injection probably know’s nothing about XSS, Session hijacking or Networking sniffing either. Let someone like that loose on your application’s code base and you’re asking for trouble.

    Another thing, ‘Use SQL Parameterized Queries’, ‘Use Stored Procedures’ and ‘Use SQL Execute-only Permission’ are all good advice. Another one you could add is to ‘Use an Object Relational Mapper (ORM)’ like Hibernate/NHibernate, Ling 2 SQL etc. as these generally parameterise all input data for you automatically.

  4. kcbtke252

    As a programmer/web developer, I wanted to add that anyone who is developing websites and isn’t using Stored Procedures has no business developing websites.  Parameterized Queries are fine but you have to exeute them in your ASP .Net code and that means they’re being run on the Web Server (IIS) and that’s wasted resources.  

    As we know Stored Procedures are run on the SQL Server which is why we use them, they obviously reduce SQL injection and takes the load off of IIS.  

    Kills 3 birds with one stone, any questions?

    Thanks for listening.

    Kevin C. Brown

  5. contrari4n

    I never cease to be amazed at the number of web developers (like IDisposable above) who avoid stored procedures like the plague.

    I fully understand, and have kept up to date with, the heated debate on the subject. I also accept that proponents of parameterized queries have argued their case well.

    However, there are couple of arguments for stored procedures that I haven’t seen rebuffed anywhere.

    o You are sending more over the network than necessary, reducing scalability.

    o Not all referential integrity can be maintained by declarative means, and other options such as triggers often do not perform well. It is not uncommon for enforcement rules to be added to stored procedures.

    o It is essential that the DBA is aware of all data access during the development phase of a project, and able to vet all queries and updates for performance, scalability, integrity, security requirements, etc. How can this be managed if developers are writing ad hoc SQL in their code?

Comments are closed.