Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database.
Learn all about MySQL foreign keys in this white paper.
Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper.
Learn the importance of data validation in web applications when information is accepted from third parties, or even from internal users.
SQL Poisoning/InsertionHave questions that weren't answered here? Feel we've left something out? Have any feedback? Let us know: Contact Us
IntroductionSQL 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 ManipulationThe 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
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
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 LeakageOkay, 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:
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:
That will run the following query:
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 PoisoningSo 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:
ConclusionsPreventing 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
|Copyright © 2010-2015 Eliacom, Inc. All rights reserved.|