Test whether or not log-in system is protected against sql injection

Question

So for a school project I have to make a site with a log-in system. It has a username and password field, and a submit button. It compares the username and password with those in a MySQL database. If the combination is in the database, the user may proceed, else they are redirected to the log-in page. I use prepared PDO statements for my database connection.

Now my teacher wants me to test the safety by performing sql attacks on the log-in system. Unfortunately I have no idea what to put in these boxes, and what would be the outcome. For example, I have tried putting values in both username and password fields that will return true, like this:

1==1,  1===1,  0 is null

But I do not know whether or not I have succeeded and if attackers may access or truncate my database by these sort of statements.

Html code:

<form method="post" action="includes/login.php">
<input type="text" name="gebruikersnaam" >
<input type="password" name="wachtwoord" >
<input type="submit"  value="login">
</form>

Php authentication:

$myusername=$_POST['gebruikersnaam']; 
$mypassword=$_POST['wachtwoord']; 
$sql="SELECT * FROM leerling WHERE leerlingnummer='$myusername' and wachtwoord='$mypassword'";
$sql2="SELECT * FROM lop WHERE gebruikersnaam='$myusername' and wachtwoord='$mypassword'";
$statement2=$conn->prepare($sql2);
$statement2->execute();
$count2=$statement2->rowcount();

if($count2==1){proceed}

$statement = $conn->prepare($sql);
$statement->execute();
$count= $statement->rowcount();

if($count==1){proceed}

else {deny access}

http://en.wikipedia.org/wiki/SQL_injection -- P.S The code could help us. — Marco Acierno, Apr 12 '14 at 13:48
As I said, I have tried these things, however I do not know if any system was jeopardised. — JJJ, Apr 12 '14 at 13:49
You use mysqli but don't use prepared Statements.. your current code is not safe! Please read http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — Marco Acierno, Apr 12 '14 at 14:54
@MarcoAcierno I use PDO, not mysqli. $conn is my PDO connection. — JJJ, Apr 12 '14 at 15:04
Ok, the problem is the same. -- http://it2.php.net/pdo.prepared-statements — Marco Acierno, Apr 12 '14 at 15:05

score 1 · Answer 1 · answered Apr 12 '14 at 13:57

Imagine this query:

SELECT id FROM users WHERE email=? AND password=? LIMIT 1

Now imagine the values would be foo@bar.hello and an empty string for password:

SELECT id FROM users WHERE email='foo@bar.hello' AND password='' LIMIT 1

This would not be harmful if these credentials are not in your database. Now lets give different input:

For email we fill in an empty string, and for password we insert ' OR 1=1 (Note the first apostrophe)

Your teacher wants you to find out whether this means your SQL server will execute the following query:

SELECT id FROM users WHERE email='' AND password='' OR 1=1 LIMIT 1

SQL is a declarative language with which you declare the expectations you have for your result. If your server would interpret our input as stated above, the first users id would be considered correct, simply because one is equal to one.

Well, I have tried this, and it returns 'wrong credentials' so this means it denies me access. Now the funny part is, I do not, at least not exactly, know why. Does the prepare() function protect me here? — JJJ, Apr 12 '14 at 14:00
Because the creators of the `prepare` function made sure that when you place a question mark, nothing but 1 input for the value before the equal sign gets evaluated. `password=?` will make sure only the value for password is considered a correct input. Your teacher wanted to show you that this is a valid defence against SQL injections. She should also tell you not to give variables Dutch names. — Rene, Apr 12 '14 at 14:07

Stephan · Answer 2 · 2014-04-12T14:36:24.757

1

As it is, it is susceptible to SQL injection

The thing to look at when trying to inject is can I close the statement I'm in right now and add more to the end.

so if you enter username = 123456' -- the SQL statement becomes SELECT * FROM leerling WHERE leerlingnummer='123456' --' and wachtwoord='unimortant'

the -- starts a comment so all it does is select whatever student number is entered ignoring the password.

PDO has good alternatives to prevent this from happening called Prepared Statements. You declare your SQL queries and only enter where user infromation is going to be entered by using a ? or :lable and then bind user input to those points. The page does a way better job at explaining it. This way all user data is clearly seperated from the rest of the command and will be treated as a litteral string rather than a command. Stopping SQL injection.

edited Apr 12 '14 at 14:36

answered Apr 12 '14 at 13:57

Stephan

426
5
13

My question is actually, does this script protect me from doing that? Does the prepare() function prevent this from happening? – JJJ Apr 12 '14 at 14:02
Update clearifies. It does not, you have to use prepared statements. (prepared statements don't help if you dont use the parameter binding part of it). – Stephan Apr 12 '14 at 14:03
This won’t work, you can’t prepare multiple statements. – Gumbo Apr 12 '14 at 14:32
You're right, Didn't think of that, What I edited it into should still work though. – Stephan Apr 12 '14 at 14:37

score 1 · Answer 3 · edited Apr 12 '14 at 14:37

$sql="SELECT * FROM users WHERE username = '{$_REQUEST['username']}' AND password = '{$_REQUEST['password']}";

Write query in such format will avoid sql injection.

    $sql = 'SELECT * FROM users WHERE username = ? AND password = ?';
    $query = $db->prepare($sql);
    $query->bindParam(1, $_REQUEST['username']);
    $query->bindParam(2, $_REQUEST['password']);

Or pass the parameter to mysql_real_escape_string function and then pass to queries.

$username=mysql_real_escape_string($_REQUEST['username']);
$password=mysql_real_escape_string($_REQUEST['password']);

Test whether or not log-in system is protected against sql injection

3 Answers3