PHP and SQlite: Login

Question

I need to do a Login form with PHP an SQlite. Registration works, and I can select all created users and echo them. When I try to check if username and password, which I get by $_POST, are matching: it echoes that I'm logged in now. But when I type in a wrong user/pw, it echoes the "invalid"-string, but there's also the following error:

Notice: Undefined variable: row in D:\xampp\htdocs\phpProjektSnippets\blogLogin.php on line 17

Line 17 is where I've got the if($row['username'] ...

This is my code:

$db = new PDO('sqlite:mysqlitedb.db');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE user_name = :name AND user_password = :pass";
$statement = $db->prepare($sql);
$statement->execute(array('name' => $username, 'pass' => $password));

try {
    $statement = $db->prepare($sql);
    $statement->execute(array('name' => $username, 'pass' => $password));

    foreach ($statement as $row);

    if ($row['user_name'] == $username && $row['user_password'] == $password){
        echo "Welcome " .$row['user_name']. ", You are now logged in.<br/ >";
    }else{
        echo "User Name or Password is invalid";
    }  
}
catch(PDOException $e) {
    echo "Something went wrong: ".$e->getMessage();
}

What's wrong here?

Answer 1

First of all, you should not use an SQL query that looks like yours. It is vulnerable for SQL injections and thereby unsafe!

Please read the answers on How can I prevent SQL-Injection in PHP for some very helpful tips on how to do it right.

The problem is that you append to the SQL string whatever the user submits to the server - that might be malicious code that gives an attacker access to your database or deletes data or other bad things might happen. See the link above for more information.

In your case, the code would look like this:

<?php
    $db = new PDO('sqlite:mysqlitedb.db');
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $username = $_POST['username'];
    $password = md5($_POST['password']);

    $sql = "SELECT * FROM users WHERE user_name = :name ";

    try {
        $statement = $db->prepare($sql);
        $statement->execute(array('name' => $username));

        foreach ($statement as $row){
           echo $row['user_name'] . '<br />';
        }
    }
    catch(PDOException $e) {
        echo "Something went wrong: ".$e->getMessage();
    }
?>

Edit: Since you configured PDO with $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ) to throw exceptions in case of an error, you should also catch them. I added the try/catch to the code.

Answer 2

Adding to Hexaholic answer and further to your question.

This line catch your code error

catch(PDOException $e) {
    echo "Something went wrong: ".$e->getMessage();
}

If you want to write error in case of invalid user credentials, use if true...else...statement.

if(some argument){
    //do something if the argument result is true;
    }else{
    do something if the argument is not true;}

So your code should be something like this.

<?php
$db = new PDO('sqlite:mysqlitedb.db');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * FROM users WHERE user_name = :name AND user_password = :pass";

try {
    $statement = $db->prepare($sql);
    $statement->execute(array('name' => $username, 'pass' => $password));
    $row = $statement->fetch();
        if ($row['user_name'] == $username && $row['user_password'] == $password){
            echo "Welcome " .$row['user_name']. ", You are now logged in.<br/ >";
        }else{
            echo "User Name or Password is invalid";
        }           
}
catch(PDOException $e) {
    echo "Something went wrong: ".$e->getMessage();
}
?>

And if this is working, remember to rectify your hashing issue.

Maybe this picture can better help you understand for the code above

Wrong password output.

Correct password output.

Answer 3

Trouble is with semicolon in this line: foreach ($statement as $row);

Foreach simply does nothing instead of subsequent if.