If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Shoestring SQL Injection Prevention

People have criticized me for not making real world arguments... they say every system I discuss is for high security, high assurance, high budget, with intelligent users. I must be honest, that is the majority of my experience and this has always been a stumbling block when posting here or searching for work. Security is a spectrum, high assurance.high security doesn't mean "different" it merely means "more" (or as is sometimes the case, less). the following tutorial is an example of translating higher security techniques to your average low brow php+mysql web application.

Attackers frequently use SQL injection attacks as a means of compromising the confidentiality, integrity, and sometimes even the availability of RDBMS powered web applications. While SQL injection attacks are a trivial matter to prevent (though this by no means is to indicate that people with means do in fact prevent such attacks) with more advanced RDBMS and commercial security software, they tend to present a problem with popular free products. Traditionally the solutions revolve around verifying every single variable that might end up in a query to ensure that potentially dangerous characters are properly escaped. Although this is a good policy, it can frequently be impractical, especially in a retrofit situation or with complicated inputs and code developed by an external agency.

I purpose a simple solution that will work with all major scripting and programming languages, all major RDBMS (including traditionally minimalistic ones like mySQL), all major platforms. Additionally this solution will work with the majority of managed hosting accounts and is simple to retrofit even complex existing web applications.

This solution does not aim to remove the potential for SQL injection attacks, merely to prevent plaintext disclosures and provide containment. I feel this is sufficient to decrease the exposure factor and the annualized rate of occurrence dramatically enough when compared with the cost of implementation to be a viable option for nearly any organization utilizing RDBMS powered web applications.

This paper assumes the reader has a basic knowledge of SQL, a web scripting language (I selected PHP because it is sufficiently chatty and widespread), and injection attacks.

Part 1: The power of least privilege

Least privilege is the concept of giving a subject (user or process) only the minimum rights required to complete a given task. Consider the following query:

UPDATE table SET x=$x WHERE x=$y

If this is made from a typical mySQL account, the mySQL user most likely has the following rights:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP

Which means, not only can that process insert data, it can also select data, remove data and create entirely new schemas (and potentially files). I am sure it doesn’t take a rocket scientist to see the dangers in:

We want to calculate all of the required user rights and then break them down into the smallest sets possible. Ideally you’ll end up with one user that can SELECT and nothing else another user that can INSERT and nothing else, still another user that can UPDATE and nothing else, etc… (Odds are that sometimes you may need an account that does multiple things for compound queries, but these accounts should be used only when required.)

All scripting languages that I know of offer a way to create multiple RDBMS connections, each under a separate user and then tie each query to the applicable connection and effective rights.

If the mySQL account used in the aforementioned query were limited to UPDATE these types of attacks would fail. No matter how insecure the queried variables are.

Least privilege is a very simple in concept, but sometimes calculating and then formally defining the required rights can be an arduous task. Also, this least privilege implementation fails to deal with attacks using the same right (more on this later), e.g. using a SELECT query to pull from the accounts table as well as the originally intended news table. Least privilege is also ineffective if an entire script is compromised, because the attacker can then call all of the available accounts (which may or may not be all RDBMS rights). Lastly on this line, many hosted accounts only allow one database account, which of course removes the ability to use least privilege altogether.

Another method of least privilege can be achieved using the scripting/programming language to only assign pre-approved values to variables (here you are least privileging the variables themselves). This is done by calling the returned query data by its actual column name rather than by column number, or worse still queried column order. Observer the following php examples:

In this case the second SELECT is allowed because the query must have been granted SELECT permissions to run in the first place. In the first example, loop will output all the passwords, in the second example the script will only spit out the results which come from the “states” column, consequently all of those password values never make their way to the script’s output and even though the attacker successfully injected SQL data, they were not able to leverage anything from this.

This variable least privilege is difficult to implement into existing web applications, however it is a nice an simple way to deal with SQL Injection without having to mess around with what may be complicated data sanitation. This is especially useful when sourcing out code as it allows the simplest of requirements in this regard.

Part 2: Pass-through encryption

Pass-through encryption is another simple concept that can solve many problems revolving around unauthorized disclosure. The idea here is to encrypt all private user information, using their web application authentication data as a passphrase.

The advantages are clear, by using an SQL injection or any other method of database compromise, the attacker can only view the encrypted information. Additionally this technique can be implemented at a single point offering a high level of assurance while easing the retrofitting of an otherwise complicated system.

Using this system it is important to store account information (which in this case doubles as key management) in an encrypted format, most simply an md5 hash, otherwise an attacker could gain access to the entire system by using plaintext account information (keys) to decrypt the important tuples. I believe all modern web applications store account information in this way already.

Perhaps most importantly, since this scheme uses the subject’s existing account information, the encryption will be completely seamless.

While this protects confidentiality of unique tuples, it does not stop attacks against confidentiality of shared objects (member only resources on a PHPNUKE site for example), though as a rule these are rarely important enough to spend much effort protecting. However, if you need to protect them, enjoy section 3.

Part 3: Faking multi-level security

Multi-level security is most common in high assurance operating systems, network controllers, and databases… but that doesn’t mean we can’t kinda-sorta fake multi-level security. The idea here is to create several (in this case) non-hierarchical encrypted pseudo-compartments.

Here we run into the problem that the majority of free, readily available (ideally universal) hosted web accounts do not feature any viable means of many to one encryption. So we have to fake this as well, but can it be done without allowing potential attackers to gain information about same level accounts? Granted this will be different information than your average web application attacker comes across, that doesn’t mean it won’t still be useful.
This many to one relationship can either be accomplished via a trusted agent, at is a small application that accepts a private key and distributes a common key or of course with any number of one to many encryption schemes on the market today. My money is on the former for no out of pocket solutions. Data for any given compartment will be encrypted using the same key, this ensures that even if the standard access controls are compromised, the protected data remains protected.

A nice beauty of encryption is that it can fill in for bad access controls.

Part 4: Summary

Adding multiple RDBMS accounts is a very simple way to dramatically reduce exposure, however it does not address same type injection attacks and in the case of hosted accounts may not be possible at all. Advantages include: easy to implement, plus the user has a lot of gray area for implementation. The can use two accounts or 10, makes no difference other than security through granularity vs. usability.

Requiring variables be called by their column name is difficult to retrofit; however it is likely to be easier than adding different and potentially unique data cleansing techniques. This technique will not stop all confidentiality attacks and it will not stop any integrity or availability attacks. This is an excellent approach for using third parties to develop new code.

Using encryption to create a sort of cross between mandatory access controls and least privilege are also a simple way to offer strong confidentiality controls, but again these do nothing for integrity or availibility. (however in the case of web applications these frequently have lower costs of compromise associated with them)

\"If computers are to become smart enough to design their own successors, initiating a process that will lead to God-like omniscience after a number of ever swifter passages from one generation of computers to the next, someone is going to have to write the software that gets the process going, and humans have given absolutely no evidence of being able to write such software.\" -Jaron Lanier

In this case the second SELECT is allowed because the query must have been granted SELECT permissions to run in the first place. In the first example, loop will output all the passwords, in the second example the script will only spit out the results which come from the “states” column, consequently all of those password values never make their way to the script’s output and even though the attacker successfully injected SQL data, they were not able to leverage anything from this.

Cool. I never even thought of mysql_fetch_assoc it that way. I just used it on my selects completely unaware of that aspect. Sometimes you just get lucky I guess . Nice tut.

All scripting languages that I know of offer a way to create multiple RDBMS connections, each under a separate user and then tie each query to the applicable connection and effective rights.

Is that your security recommendation? Create a large number of users for different parts of the same application?

Sounds like a management nightmare. Firstly, you could never make it fine-grained enough. On some databases, you can create views with different permissions than their tables, but you can't restrict access to rows within those views.

Also, bear in mind the management effort required to make this even remotely effective. It's rediculous.

I agree that you should run it with the least priviliege, but the least privilege required to run the WHOLE application. More database connections = more difficult code to write, harder app deployment and pain in the arse management.

Encryption -
The idea here is to encrypt all private user information, using their web application authentication data as a passphrase.

I've never heard anything so rediculous in my life. Issues:

1. What happens if the user forgets their password?! You lose data
2. Indexing. A database cannot index encrypted data.
3. Obfuscation - You're using encryption to protect one part of code from another. It's just a form of obfuscation which provides you with extra code to write, but doesn't actually add any security.

If an attacker gains your user's password, they can get the data anyway.

Moreover, if an attacker gains part of the user's data encrypted (but not the password itself), they then might be able to do dictionary attacks against the user's password! This is because of course, they are gaining something which is hashed against it.

---

I don't agree at all with your methods to protect your application.

You're working from the assumption that it contains SQL injection vulnerabilities, and you're looking for ways to mitigate their seriousness. This is like closing the stable door after the horse has bolted.

What you should do, is use a proper database access layer which handles automatic escaping of data going into the database, thus preventing SQL injections from being possible in the first place.

If you use a proper data access layer throughout, you will have no problems with injections in most queries, as they will be automatically built. Then you can concentrate on ensuring that the tiny number of queries that need to be built manually (typically just 2-3 in some of my applications) do not contain injection vulnerabilities. These are easy to grep for anyway.

Is that your security recommendation? Create a large number of users for different parts of the same application?

A large number of users? Typically three users, and FYI the most secure systems break the root user (more or less the equiv to a DB account that has all the rights) into three parts: System Administrator, System Operator, and Information Systems Security Officer... in fact this is required for higher levels of assurance by DOD-5200.28-STD.

So yes, that is a suggestion.

Sounds like a management nightmare. Firstly, you could never make it fine-grained enough. On some databases, you can create views with different permissions than their tables, but you can't restrict access to rows within those views.

A few extra users is a nightmare? I said later on in the article: "The can use two accounts or 10, makes no difference other than security through granularity vs. usability." even 2 accounts will reduce injection exposure and in no way creates a "management nightmare." The granularity required is based on this balance, so clearly it can be finely grained enough.
You will also not that I suggested to divide users by rights type, not by tables as this would typically be less useful. Restricting access to rows isn't relevant to anything I've stated here.

Also, bear in mind the management effort required to make this even remotely effective. It's rediculous.

Oh yeah, one hard coded account into three that can be changed in a single DB connection module of an existing web application... damn that is hard to implement.

I agree that you should run it with the least priviliege, but the least privilege required to run the WHOLE application. More database connections = more difficult code to write, harder app deployment and pain in the arse management.

Yes this as well, but this has absolutely NOTHING to do with SQL injection. More database connections are minimallisitcally more effort in an object oriented programming language.
This also allows simpler development as the specification development is far simpler.

I've never heard anything so rediculous in my life. Issues:

I guess you should look into EFS sometime.

1. What happens if the user forgets their password?! You lose data

There are many, many ways to prevent this that are beyond the scope of this document. Clearly following the pros and cons of every single step all the way out and how to resolve any secondary issues and beyond is not realistic.

2. Indexing. A database cannot index encrypted data.

There is no situation that would require indexing of private user data since each user will likely never access anything more than a single row on a given occasion.

3. Obfuscation - You're using encryption to protect one part of code from another. It's just a form of obfuscation which provides you with extra code to write, but doesn't actually add any security.

No, data is being encrypted not code. This ensure confidentiality in the event the code suffers a failure resulting in inadvertent disclosure.

If an attacker gains your user's password, they can get the data anyway.

Is the title of this tutorial "How to protect database information from every risk" or is it "Shoestring SQL Injection Protection." Gaining a user's password has nothing to do with SQL injection protection.

Moreover, if an attacker gains part of the user's data encrypted (but not the password itself), they then might be able to do dictionary attacks against the user's password! This is because of course, they are gaining something which is hashed against it.

No, there are many ways to prevent this. Depending on the type of encryption available to the user.

I don't agree at all with your methods to protect your application.

Clearly.

You're working from the assumption that it contains SQL injection vulnerabilities, and you're looking for ways to mitigate their seriousness. This is like closing the stable door after the horse has bolted.

Not at all, SQL injection successful or not can still be mitigated. This approach is far superior as a method of quickly and cheaply resolve these issues than the traditional data sanitation techniques. (which also fall under distributed security rather than centralized which is always inferior)

What you should do, is use a proper database access layer which handles automatic escaping of data going into the database, thus preventing SQL injections from being possible in the first place.

If you are developing an application from scratch, sure. Using an existing unassured application this is unpredictable.

If you use a proper data access layer throughout, you will have no problems with injections in most queries, as they will be automatically built. Then you can concentrate on ensuring that the tiny number of queries that need to be built manually (typically just 2-3 in some of my applications) do not contain injection vulnerabilities. These are easy to grep for anyway.

Same as previous.

Another important way these approaches differ is that yours addressing existing problems only and may need to be reworked with upgrades and as new vulnerability types are discovered (a few years ago no one knew what SQL injection attacks were.)

Personally my preferred solution is not to do so. However there are some ways of mitigating it:

URL filtering type solutions - like mod_security (and that one for IIS, url scanner or something)

These are NOT magic bullets, and have significant problems with false positives (blocking legitimate requests). If they're tuned right however, most SQL injection exploit attempts will be blocked, and most legitimate requests will be let through.

They work by having various rules as to what is / isn't allowed in. For example, query strings and post payloads can be searched for sql injection strings. "union select" for example.

The problem is, these strings occasionally appear legitimately in post payload or query strings (for example, the one in Antionline where I'm posting this).

Some of the example rules in mod_security are absolutely rediculous and will significantly annoy typical forum users.

However, these can benefit from logs, so you can analyse your logs, and if you see a rule which is kicking off false positives, either make an exception rule for it, or remove it entirely (only if you consider whatever it's blocking out sufficiently benign)

For example, there is usually no point in having the IIS codered rules enabled, because you won't be running an IIS server vulnerable to that ancient exploit anyway.

Oh I completely agree that filters are also an excellent tool, and I would recommend them for pretty much any web server. I did not include them, or several other methods (other server configurations or tools) because I felt that would run the risk of being uselessly vague or too specific to be useful. Many of those techniques would be focused on overall webserver security and less on SQL injections specifically.

Adding multiple RDBMS accounts is a very simple way to dramatically reduce exposure, however it does not address same type injection attacks and in the case of hosted accounts may not be possible at all. Advantages include: easy to implement, plus the user has a lot of gray area for implementation. The can use two accounts or 10, makes no difference other than security through granularity vs. usability.

Sounds like a management nightmare.

Also, bear in mind the management effort required to make this even remotely effective. It's rediculous.

This is what I did for with small CMS I worked on over last summer, and was easy to do. I had one user for the administrative section, and a limited user for everything else. Simple stuff. As far as giant applications go, it certainly wouldn't be fun to manage but I don't think it's a nightmare. Too much work would be giving db accounts for every user of the application... instead only for certain functions of the application is reasonable IMO.

Nearly everyone with a webhost would be cut off from that application though. I had to accomodate them and allow them to disable it in the configs of my CMS.

1. What happens if the user forgets their password?! You lose data

Reseting the password and mailing it out somehow seems like a fair solution to me. But that's offtopic. Nice tut!

Too much work would be giving db accounts for every user of the application... instead only for certain functions of the application is reasonable IMO.

I agree, as a rule (high assurance and very low assurance applications aside) this method of role based security is the most beneficial with the lowest cost of upkeep.

Reseting the password and mailing it out somehow seems like a fair solution to me. But that's offtopic. Nice tut!

The issue here is if user authentication information is used to encrypt their provate data, these keys will be lost if the authentication information is reset. (Just like with EFS) resolutions for this are numerous (including my favorite, a completely seperate authenication recovery system that stores user passwords in a recoverable way and uses them to generate new passwords).