The Drupal disaster-in-the-making reminds me to actually present SQL Injection on this blog the way I teach my B.U. Students. Drupal’s problem is a SQL injection vulnerability in its content management system.  By way of reminder, SQL injection lets an attacker run arbitrary SQL on a database through an application that is not supposed to be used that way and is usually presented as “secure” because it maintains its own users and permissions. SQL Injection breaches are arise from application coding errors, almost exclusively.  I can’t actually think of a SQL injection vulnerability that I’ve ever heard of that arose from anything other than application code.  These are not database vulnerabilities; they arise from ignorant or lazy coding of database access code.  Yes, Drupal, I’m looking at you.  But don’t worry, you’ve got legions of fellow travelers. There are exactly three things needed for an application to have a SQL injection vulnerability.  The first part everyone knows.  The other two are rarely-questioned application programming patterns which compose, in essence, the ignorant/lazy part.  These conditions are:

Running Dynamic SQL Generated with Unsanitized Texts Application Connects as Admin-Level Database User Application Runs Dynamic SQL for Unauthenticated or Poorly-Authenticated End Users

If you block any of these conditions, you have blocked an injection attack.

Condition 1: Running Dynamic SQL Generated with Unsanitized Texts

OK, everyone knows this.  The programmer takes an argument from a web page submission and plugs it into SQL as part of preparing a database interaction. Here’s a sample in Java:

String loginSQL = "SELECT USER_NAME, AVATAR_IMAGE, LAST_POST_TITLE, LAST_POST_TIME 
FROM USER_INFO WHERE USER_ID IN (SELECT USER_ID FROM USER_PASSWORD WHERE
USER_EMAIL = '" + parm (USER_EMAIL_PARM) + "' AND HASH_PASSWORD = 
PASS_HASH_FUNCTION (1, '3315', 'passHash1', '" + parm [USER_PASSWORD_PARM] + 
"')";

This example is simplified to some degree, but the effect is the same.  If an attacker enters a password like this Oracle example:

LOL' 
UNION ALL SELECT CURRENT_USER, NULL, PRIVILEGE, SYSDATE
FROM SESSION_PRIVILEGES
-- ' ;

…then the attacker may well get the identity of the current database user and a list of all privileges active for that user.

The discussion is not over.

I saw a StackOverflow discussion on dynamic SQL devolve into a nasty, frothy argument against it because some thought any dynamic SQL, anywhere, always constitutes a SQL Injection vector.  Others said it’s only a vector when poorly coded.  Neither is really correct, because this is not the only condition needed for an injection vulnerability.

Condition 2: Application Connects as Admin-Level Database User

Most web applications and web frameworks follow what Oracle calls the “One Big Database User” model.  Such an application connects to the database as the owner of the tables it uses.  I’m a database guy.  I recognize this as both risky and unnecessary. However, simply identifying this as an intentional developer choice makes me recall a lot of unpleasant conversations I’ve had with developers.  'What, hey, ho?  Creating and selecting a specific user account for an application, other than the table owner?'  This is effectively inconceivable for many application developers.  'What else are we supposed to do?'  The social dysfunction and gappy reasoning around this assumption could fill volumes. So the application connects as the owner of the application tables — what’s wrong with that?  What’s wrong is that the application owner has the privilege of dropping any object it owns.  It can do a lot of destruction not intended by the application as end-user functionality.  The real question is this: Why do we configure an application with database privileges we know we never want its users to have?  The application programmer knows that his/her intended ordinary end user should not be dropping tables or creating triggers or experimenting with constraints.  (As an aside, SQLFiddle’s intended end user is expected to do these things.) The database owner may also be granted some extremely powerful privileges - adding or dropping disk storage, creating end users, querying system information.  Usually these needs go away after installation.  The end user should not have them.  For anything not intended as a programming platform, the application does not ever have to connect as the table owner.  Period.  The application needs to connect as a separate end user with only the privileges needed to work those specific operations needed for the application. When the application connects with escalated privileges, the ability to run SQL distorted by an injection attack makes that distorted SQL all the more dangerous.  If you limit the attacker by restricting the application’s database account, you’ve already mitigated the damage (and possibly eliminated it.)

Alternative approach, or, why doesn’t SQL Fiddle fall apart?

SQLFiddle is a utility site that allows you to run SQL on a mystery cloud database about which all you know is that it acts like (and presumably is) whichever database type you chose: Oracle 11gR2, MySQL 5.6.6, PostgreSQL 9.3.1 or others.  Here is an example for Oracle: So to run arbitrary SQL on SQL Fiddle, you basically… enter arbitrary SQL and click.  See the “arbitrary SQL” part?  So how can it keep working? Isn’t SQLFiddle basically a SQL injection engine?  Don’t attackers tear this to shreds?  Well, no.  So what’s the difference between this site and all the “injectible” sites?  The difference is not that this is some kind of wacko nonconformist web site.  It’s just that SQL injection is arises from more than just the dynamic SQL aspect on which people focus exclusively. If you ‘SELECT USER FROM DUAL’ on SQLFiddle to find your connection account, you get a random-looking user ID. It has relatively few system privileges and little access outside its account.  It’s a web application that connects with exactly the database privileges it intends its users to have.

Condition 3: Application Runs Dynamic SQL for Unauthenticated or Poorly-Authenticated End Users

Most security breaches are long-term affairs.  Triggering a failure in a target system (like the Stuxnet worm) is certainly a noticeable proportion of professional attacks, but they are definitely in the minority.  The professional hackers afflicting businesses now are stealth agents, preparing huge payoffs with long preparation and long deferring theft or damage until the target is well understood and the maximum effect is possible.  And rather than a single devastating attack, professional hackers typically keep their attack going on for long periods - like a parasite unnoticeably consuming the resources of its victim.   Consequently, the hacker must have a entry vector that is untraceable as far as possible. For SQL Injection attacks on unknown systems, a further hurdle of effort is needed.  Even on systems with an injection vulnerabilty like my trivial example above, it can take a lot of experimenting and exploration to map out a database to attack.  An attacker may be able to execute arbitrary SQL, but he is still sending it in chunks that fit into a web form with a scaffolding of the attack code needed to inject that arbitrary SQL.  Even if it’s automated, the attacker is still issuing thousands of SQL commands before gaining enough information to effect the actual attack - such as querying the data dictionary, discovering tables, finding privileged users, looking for network connectivity to exploit, checking what data is in what table. With SQL Injection, an attacker keeps stealth by switching identities and changing the (apparent) source IP address used when exploiting the target website.  Here a poorly implemented web authentication system greatly facilitates the attack.  The actual injection may hardly be visible through the web logs and if those logs make it difficult for an administrator to figure out who’s doing what, they may actually protect the attacker.  Even a very strong logminer can miss the signs of an injection attack. When the attack gets to the database, the database usually has little information to determine who is actually submitting the code.  With common web coding patterns, it often has none.  Is this SQL being done on behalf of an unregistered window shopper, a registered user, a group admin, or web-accessible database administration code?  The database has its parsers, its history, its knowledge of internal database structures, access to user listings, and it can hardly use them at all when the web application gives it little or nothing for identifying the end user.  It can’t detect an attack, and its audit logs are probably as inscrutibly huge as the web logs.

Wrap-Up

The implementation choices made by developers of web applications and web frameworks are simply that: choices.  There are more options and better options for using a database application than what is used conventionally. Developers, please think hard about these choices.  Do not assume you can cover all the injection vectors, or at least please apply some security in depth.  You have a DBMS, you need to use its capabilities.  Remember, even if your application starts out with a simple schema and no sensitive data, think about what happens if it succeeds.  You do want this to succeed, right?  That means tens of thousands of end users.  What happens to your database application then?  First of all, you’ll attract the attention of possible attackers,  Second, your users will ask for more features.  These will involve more database access and new kinds of data.  They will drive your application from the simple “CRUD” database use it starts with to something providing more complex interrogation of more sensitive data.  And what will you That is a downstream expense, an exorbitant technical debt that you don’t want to pay.