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 don't understand is whether THE MySQL injection problem is solved with mysql_real_escape_string(), there has to be more to it.

What I really don't 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 that's why they don't make this automatic?

Answer 1

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 2

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.