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:
$y = ‘ ; UNION ALL SELECT password FROM users WHERE “=’
or
$y = ‘ ; DROP TABLES WHERE “=’ You get the idea
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:
PHP Code:
$query = mysql_query(‘SELECT states FROM addresses where rep = ’.$x);
while($result = mysql_fetch_row($query)){
echo $result[0];
}
What happens if, the attack we gave earlier is presented:
$x = ‘ ; UNION ALL SELECT password FROM users WHERE “=’
Now what happens if the same attack is dumped into this?:
PHP Code:
$query = mysql_query(‘SELECT states FROM addresses where rep = ’.$x);
while($result = mysql_fetch_assoc($query)){
echo $result[“states”];
}
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)
cheers,
catch
