Need help understanding MySQL injection

Question

From http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php I got:

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

I read the whole article but I still have some major issues understand what it is and how can it be done.

In the first example, what will they actually see?

As far as i understood, if I actually echo $name, the will see all the names because it will always "be true" am I correct?

The other thing I dont understand is whether THE MySQL injection problem is solved with mysql_real_escape_string(), there has to be more to it.

What I really dont get is that mysql_real_escape_string() is made to solve that issue, why isn´t this done automatically, I mean is there a reason you have to add every time mysql_real_escape_string(), is there cases when you should use it and thats why they dont make this automatic?

I hope the question is clear enough, maybe my luck of understanding of the topic makes the question confusing so please ask for any clarification if necessary!

Thanks in advance!!

Answer 1

Bobby Tables has a great summary of how SQL injection works. Of much benefit is the examples it gives in several languages (C#, Java, Perl, PHP, etc.)

In the case of PHP, it depends a lot how you're accessing the database. You could benefit from using a database extraction layer such as ADODB which parameterizes queries.

Answer 2

as far as i know, when making websites you must always assume that the end user is a dirty stinking menace that wants to break your stuff. so you should always clean your strings with mysql_real_escape_string(); , htmlentities(); and others. code can be injected into your form data that can exit what it was doing, insert new code and then have complete control of your database and possibly your file structures depending on what it has access to. this means that tables , values, passwords and your entire database can be destroyed or modified.

There are instances where you may want to inject code yourself , for instance what if you wanted to make a user interface that could input code to your database. (ala phpMyAdmin) . maybe it would be ncier to have it automatically escape code somehow, and then un-escape it if you wanted it.. maybe something that should be discussed with the creators of PHP/mySQL?

This is as much as i know. I hope someone else can give you more insight than this. just remember to always clean your returning values from forms and user input. =) ~PtK

Answer 3

In the first example at the Tizag link, the query looks like it's expected by the script author to fetch at most one row. So given that every row will be fetched, the most likely outcome is probably that the information for the first row returned will be acted upon; since there's no ORDER BY clause in the tampered-with query, this may be the user stored first in the table, but of course order isn't defined in SQL when the ORDER BY clause is missing, so who can say. What you can say is that as long as the table isn't empty, it will fetch the details of a valid user.

I'm not sure what you mean by "if I echo $name"; the $name variable is assigned the value "timmy" in the code. So they would see timmy, I guess. If you mean, if you tried to echo to the user information obtained by the query, what would they see - well, it depends on the code you are using. If you are looping through a resultset and they used SQL injection to fetch rows you didn't expect them to fetch, then they will likely see all the rows, including rows you didn't intend them to see. If your code just fetches and acts on the information from one row, then they'll still see one row, though again it may be a row you didn't mean them to be able to reach.

As for why the functionality offered by mysql_real_escape_string() isn't automatic, that's because for it to be automatic would rely on a computer being able to work out from your SQL code what you intended to do, rather than just do what you said to do. That is both difficult and frankly undesirable, because no-one wants a computer second-guessing what they want to do (particularly programmers).

If you want to be able to get away from using mysql_real_escape_string() and the like, you might want to look at using parameterised queries, which allow you to take a slightly more hands-off approach. You still have to make it clear to the computer what parts of your query are variables you want escaped, though, because that's just part and parcel of communicating to the computer what you want to happen.

Answer 4

You are correct, in the first example, the person who entered the "bad" name, has been allowed to alter the database query you are performing and in this case, they've altered it to show all rows in the table.

As far as how it can be as easy to prevent as using a subroutine to escape special characters, you need to understand that a string (or any data) can be understood on different levels. When you are accepting user input and then using it to build a database query, you want the database server to interpret the string as data. However the only reason the database server does this is because you use special characters like a single quote so it knows it knows where a string begins and ends. Escape Characters work by telling the database server (or any other system that interprets them) not to interpret special characters as special characters, but to interpret them as data, just like the rest of the string. That way if an one of those special characters is in your string, it's special function will just be ignored.

As for why this isn't done automatically? There is no way for the database server to know what data can be trusted and what data cannot. Only the programmer knows that, if they're lucky! And you can't just do it on all data because those special characters, (such as a single quote) are there for a reason - they convey meaning to the database server - if you escape all of them then there is no way to convey their meaning. This a really fundamental concept in computer science - that the same information can be interpreted on different levels in a system and a system might use special patterns of data within that information to denote when data needs to be interpreted at a different level.

You might find it useful to read up on the concept of abstraction layers too for a more fundamental understanding.

Good luck!

Answer 5

MySQL won't escape automatically, because you build the query string yourself. For example:

$query = 'SELECT * FROM users WHERE name="' . $name . '"';

You just pass the raw string stored in $query, which is open to SQL injection. For example, if $name is [something" OR "1=1] your query string ends up being:

$query = 'SELECT * FROM users WHERE name="something" OR "1=1"

That would return every user from the user table. Which is why you need to escape values. However, if you use PDO, it is done for you if you use the binding functionality. It's a 2 step process, preparing the querying, then "binding" the data/variables to the placeholders. In PDO, your query string would look something like this:

$query = 'SELECT * FROM users WHERE name=":name"';
$bindings = array('name'=>'something');
prepare($query);
execute($bindings);

Then, things are automatically escaped for you.

Answer 6

When discussing SQL injection, the most common example is "foo' OR 1 = 1" deleting an entire table or revealing passwords. These injections can be foiled by escaping the strings.

However, there are much simpler injections where mysql_real_escape_string() is ineffective. For example, say you have page where the user can delete selected entries from your database. A common implementation is to build up query to delete the entries based on GET or POST variables, e.g.:

$row_to_delete = $_POST['id'];
$query = "DELETE FROM table WHERE id=$row_to_delete";

As you can see, the user can easily post whatever "id" they want to this script, potentially deleting the entire table, even if mysql_real_escape_string() is performed on the string. This same vulnerability can be exploited to guess which "id" belongs to the administrator and tamper with values all over the place. As far as I know, the only protection is to validate ALL get and post parameters from every possible angle you can think of. Basically, don't just do form validation - do PARAMETER validation.

You'd be surprised how easy it is to let such a simple vulnerability slip into your code.