Giving SQL Injection the Respect it Deserves

Giving SQL Injection the Respect it Deserves

Rate This
  • Comments 22
 

Hello, Michael here...

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

-and-

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

Comments
  • I just posted an article on the SDL blog about the recent news of SQL injection vulnerabilities...

  • 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 (www.microsoft.com/hellosecureworld7) 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.

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

  • Hi everyone, Bryan here. Michael wrote a great post here on SDL-required SQL injection defense techniques

  • In light of the recent wake of SQL injection attacks on ASP sites, I'd like to highlight some relevant

  • Microsoft has recently published a series of best practices to help developers build SQL code that is

  • While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm

  • While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm

  • Microsoft has recently published a series of best practices to help developers build SQL code that is

  • ...i z tego powodu należy poświęcać temu tematowi należną uwagę: Giving SQL Injection the Respect it Deserves. (...) The SDL is very specific about what do here, there are three requirements - they are requirements not recommendations, which me

  • 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

  • 本文翻译自微软博客上刊载的相关文章,英文原文版权归原作者所有,特此声明。(特别感谢NeilCarpenter对本文写作提供的帮助)

    近期趋势

    从去年下半年开始,很多网站被损害,他们在用于生成动...

  • <p>This year SQL injection attacks are being stepped up and even automated against SQL Server. While SQL injection attacks can occur against any DBMS, my blog will only address SQL Server.</p ...

  • If you're doing ASP.NET development then you need to check these tools out. As per this security bulletin

Page 1 of 2 (22 items) 12
Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post