Other Helpful Stuff

MySQL/SQL Poisoning and SQL Insertion

Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database.

MySQL Foreign Keys

Learn all about MySQL foreign keys in this white paper.

MySQL Foreign Key Errors and Errno: 150

Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper.

MySQL/SQL Data Validation (with PHP)

Learn the importance of data validation in web applications when information is accepted from third parties, or even from internal users.

SQL Poisoning/Insertion

Have questions that weren't answered here? Feel we've left something out? Have any feedback? Let us know: Contact Us

sql insertion and poisening

Introduction

SQL poisoning (aka insertion) is quite possibly the most dangerous issue that faces web sites with database access, particularly access to sensitive information. You risk losing data due to malicious queries dropping tables or entire databases, or possibly leaking data to malicious users, which is even more potentially damaging.

The source of this danger is caused by allowing users to access and modify database information. For any interactivity that persists between computers, this is a necessary evil, and isn't evil at all if dealt with correctly. The risk is greater if you allow for "just anyone" to access the database, but even if you limit usage to users who are logged into your site, even inadvertent mistakes can create errors. Of course, the real risk is bad people. People who want your data, or want to hurt you, and are smart enough and have enough spare time to work on your site to find vulnerabilities. This white paper will focus on a few ways that these bad people do this, and then how it can be stopped.

Unwanted Data Manipulation

The main way that the bad guys try to get at your data is by sending bad data to you, and seeing how your site responds. For instance, if you have a form where the user fills out, which will create a user account for them. One of those things is a name. If they type

"Robert'); drop table users; -- ",

depending on how the database is set up, and depending on if there is a "users" table, they could drop that table. This is because some SQL query processors don't do any pre-validation of the query before trying to run it. So what the SQL parser sees is probably something like

"insert into `some_table` (`name`) VALUES ('Robert'); drop table users; -- ')".

It ignores everything after the -- (the SQL comment flag), and it blithely inserts the name Robert, and then attempts to drop the users table. If the users table doesn't exist, then all that you'll see is an error, but if the bad guy guesses your table names correctly, he now can drop your table information.

Unwanted Data Leakage

Okay, that might not be so bad. You've backed up your data, and you can get it back, patch up the holes in your code, but what if you have some data you don't want people to see. For instance, you store private information for your users, and the bad guys want that information. If you have a way to display that information to users who rightfully should get it, you might do something where you let the web page tell you the username for that user, and then dump something from an information table. So the site posts (either in the site URL or in the HTTP POST variables, some information that will be used as select criteria in a select statement from one of your database tables.

This is a very simple example, which probably isn't very realistic, but it will give you the idea. Say you have a "users" table, and you want to get the information for your particular user. Say the client (the web browser) sends a parameter called "user" to the web server. It then tries to run the query:

"select * from `users` where id='{$_POST['user']}'"

where the $_POST['user'] (using PHP like syntax) is the post variable that was sent to the server. The problem with this, is just the same as the problem with the previous query. The user could post instead of a valid user, something like the following:

"I dont care' or 1"

That will run the following query:

"select * from `users` where id='I dont care' or 1".

This will dump all the rows. That could be bad news if you have information that you've guaranteed to your users will remain private, particularly if it is of a sensitive nature.

How to Prevent SQL Poisoning

So enough about the problem; how do you fix it? Here are some of the best ways that we've found to protect your database from these kinds of attacks:

Prepared Query Statements

Prepared query statements basically means that the query parser attempts to validate the query before attempting to send it to the database. If the query doesn't look valid, then it dies before sending it to the database server. This will catch most attempts, because as attackers play around, they will probably break the query. Prepared statements also typically only allow one query to be passed at a time, which would stop the first attack above from little Bobby Tables. However, just using prepared statements is typically not enough, as you can see that the last attack I discussed wouldn't create an error at all, and was only one query so it would still parse correctly, and dump that table.

Binding Parameters

Something else that you should do when using prepared query statements, is bind parameters. This is particularly important when accepting user input. The SQL parser will appropriately escape whatever value is passed into the prepared statement. This would stop both attacks discussed above. The implementation of prepared statements is different for different interfaces. For PHP programmers, see PHP PDO Prepared Statements or PHP MySQL Prepared Statements to see actually how to implement the prepared statements with bound parameters. Note: bound parameters come in two flavors, named and unnamed: some prepared statement engines don't support names parameters, but they are useful for keeping track of what is what, look it up. Just to whet your appetite, using PHP PDO as an example, you would prepare and execute the query as follows (assuming $pdo is a valid PDO resource) using unnamed parameters:

$statement = $pdo->prepare("select * from `users` where id=?");

$statement->execute(array($_POST['user']));

or using named parameters:

$statement = $pdo->prepare("select * from `users` where id=:id");

$statement->execute(array('id' => $_POST['user']));


Uh oh, You can't bind some user input.

Some things can't be bound. For instance, let's say you have an interface where the client (the web browser) actually tells you which table you're supposed to be looking at. At least in MySQL, you can't bind things like table names, or column names. This poses a problem that can't be fixed just by changing how you execute the queries. Your query might look something like

"select * from `{$_POST['table']}`".

You can't just change this to

"select * from ?",

and bind the table name, and then be sure that there won't be any problems. This is where you have to start thinking.

Data Validation

For the situation where you can't bind the user input, you have to be careful that the user can't use the same tricks discussed above to attack you. The only way to do that is to think about where the data is coming from, think about what the data should look like, and then make sure that the data is appropriately validated before you put it into the query. Data validation takes thought and work. We will briefly overview the major concerns here. Visit the Data Validation White Paper for more information. Data validation breaks down into two main pieces:

Type of Input

Say you want to stop somebody from putting something like "my_table`; drop database my_db; -- ". Hopefully, as long as all your tables are alphanumeric with possibly underscores, you can eliminate any possibility of doing that by checking if the posted table name has a "`" in it. If it does, then it will break your query. When checking the type of input that has been passed, leaner is more secure. Rather than checking from "`", it is safer to check that only the allowed characters are there. If you only allow alphanumeric characters in database names, then only allow alphanumeric characters to get posted. If something else gets posted, you'll have to come up with some error handling to deal with that. Be sparing on telling the user "Hacker, I caught you, and you know where to go!", since even normal users sometimes break things. This will stop the user from performing the more nasty types of queries, but it doesn't stop them from possibly viewing a table that you had no intention of letting them view. That brings us to the other type of data validation.

User Permissions

The best way to stop users from looking at things the shouldn't be looking at is to limit their ability to see it at all. The lower level this is done, the less likelihood an attacker will find a way around it. For instance, using the "select * from `$_POST['table']`" example from above, a user could post something like "other_db`.`another_table". This would allow the user to jump out of the current database altogether, and see another database's content. In order to stop this, you need to check to see if the person trying to access this table actually is supposed to be able to access that table. By using User Permissions, you preset what values of certain things (table names/column names for select/insert/update queries) are allowed. If the user tries to access anything not allowed, the query fails.

Database User Permissions

The previous example of course be stopped by only giving the "database user", the user that the web server uses to connect to the database, access to the one database that it should have access to. In other words, if your web application is only supposed to be looking at one database, then the database user you use for that web application should only have access to that one database. If there are certain tables that you don't want a particular user to be able to see, then you may want to create a specific database user for each end user with specific permissions.

This may be the right solution, but particularly for cases where you allow many users to access you web application, this might become a problem. Let's look at the case where the web application has to create a new database user/update their permissions based on settings in the web application. If it's to be done automatically by the web application, then this gives even more power to the web application, since it has to have some database user information stored that has the power to create users with different levels of permissions. This opens up a new entry point (and a very powerful one) where the web application can be hacked. Additionally, if you have thousands of database users, it may be hard for an administrator to track the permissions of each user, and if one slips by with more permissions than they deserve, it could fall through the cracks. This can be mitigated with monitoring tools and such, but it's still a concern.

Application User Privileges

If giving your web application the ability to create thousands (or more) new database users doesn't sound very appealing, then you can store what tables/databases each end user has access to in your application, and monitor/control access at that point. The implementation of this could look something like this: There is a single database user that is used for the application. It has the ability to do everything that all the users want to do (and nothing more), say viewing information from tables. But some users get to see only certain information from certain tables. You could have a permissions table in the database that contains which tables the user has access to. When the application tries to run our "select * from `$_POST['table']`" query, before running it, it checks "$_POST['table']" against that list of tables the user has access to. If it's there, then great, if not, die (hopefully gracefully).

The upside to this is that you don't need many database users, and you don't need to store database user information for a "super user" that can create new accounts. The down side is that the database user you will be using needs enough permissions that it can do everything it needs to for all the users. This puts all the weight of responsibility on the application. If the application is poorly programmed, then a security breach could occur.

Hybrid Approach: Database and Application Privileges

As you can see this gets complicated if you want to effectively manage users with different levels of permissions in your web application. You can choose a hybrid approach where you have a small number of database users that are used for different functions in the web application. These users don't have permissions to do anything outside of their purview. That way, if an attacker attempts to use that database user to do something out of its purview, it fails. Then you use the web application to assign more refined permissions to each user. This can be effective, since there are a few very well defined database users, which need little tracking. It makes attacks more difficult because you have to use the right entry point, which is already in the area where the database user is allowed to do things. Then the application is responsible for checking whether or not the user is allowed to do exactly the thing being requested.

It is like having two lines of defense, where the application is meant to catch all of the attacks. If it fails, there's a broader line of defense that mitigates how damaging an attack can be.

Conclusions

Preventing SQL poisoning/insertion into web applications is a tricky business. It takes careful tracking of user input, user permissions (both at the application level and database level), utilizing tools for preparing queries in a robust way, and lots of thought. It's probably impossible to make a web application that is completely impervious to attacks, at least not at first. Testing is extremely important, and having a framework which lends itself to refine data validation and application/database user privileges is imperative.

We hope you found this white paper useful. Please let us know if you have any questions you felt were not addressed in the white paper or if you have any feedback: Contact Us