Is it possible to select the exact password to a username in a database?

Question

I have created a database with a table (UserPass) which essentially stores Usernames and Passwords.

Now in my form I want to ask a user to input his username and password and while testing this, I realized that I can input any username from the database and any password to login.

Is it possible to select in the SQL query the password that is in the same line as the username?

I tried something like:

$username = $_POST['username'];
$sql = "SELECT Password FROM UserPass WHERE Username = $username";

But the following mysqli_query failed:

$query = mysqli_query($cxn, $sql);

So here is the entire action.php script:

<?php
include "info.php";
include "god.php";
session_start();
if($_POST['god'] == $god)
{   

    header( "refresh:0;url=../web.html" );
}

else if(empty($_POST['god']))
{

}

else 
{
    echo "Can't you read: DON'T TRY!!!!!!!";
    exit();
}

$cxn = mysqli_connect($host, $user, $password, $dbname) or die("Go");
//check username
$userI = $_POST["username"];
$userSql = "SELECT Username FROM UserPass ";
$result = mysqli_query($cxn, $userSql) or die("Query failed!");
while($line = mysqli_fetch_assoc($result))
{
    extract($line);

    foreach ($line as $key => $val)
    {
        if($_POST['username'] == $val)
        {
            //check for password 
            $username = $_POST['username'];
            $pass = $_POST['password'];
            $sql = "SELECT Password FROM UserPass";
            $passres = mysqli_query($cxn, $sql) or die("Request cannot be handled now.");
            while ($passline = mysqli_fetch_assoc($passres))
            {
                extract($passline);
                foreach ($passline as $k => $v) 
                {
                    if($_POST['password'] == $v)
                    {
                        header( "refresh:0;url=../web.html");


                    }

                    else 
                    {
                        session_destroy();
                    }

                }
            }

        }
    }
}
/*
if($userI == $line['Username'])
{
    //check for password
    $pass = $_POST['password'];
    $sql = "SELECT * FROM UserPass";
    $res = mysqli_query($cxn, $sql) or die("Pass query failed");
    $passline = mysqli_fetch_assoc($res);
    if($pass == $passline['Password'])
    {
        header( "refresh:4;url=../web.html");
        session_start();
        echo "Login succesful, session started, session id: ";
    }
}
*/


    /*
    if($_POST['username'] == $val)
    {
        //check for password
        $b = $_POST['username'];
        $pass = $_POST['password'];
        $sql = "SELECT * FROM UserPass";
        $passres = mysqli_query($cxn, $sql);
        $passline = mysqli_fetch_row($passres);
        foreach ($passline as $k => $v ) 
        {
            if($_POST['password'] == $v)
            {
                header( "refresh:0;url=../web.html");
                session_start();
            }
        }


    }
    */
    /*
    else 
    {
        print("Destroying Laptop...US Government...Destroying Laptop...\n");
        exit();
    }
    */              
?>

Why would anybody bother logging in? I would just go to `web.html` directly... — jeroen, May 25 '15 at 08:26
your logic is wrong. When you have checked that the User exists in the database then your query should be like `select username from UserPass where username=$username and password=$password` . Using this you will not face the issue that you have — Amar Agrawal, May 25 '15 at 08:28
Storing the password in plain is insecure but might be okay in some situations. But please emphasize while people register that you don't store their passwords safely and that they should not reuse an important one. — Marcel Pfeiffer, May 25 '15 at 08:33

score 0 · Answer 1 · answered May 25 '15 at 08:26

You just need to check if there is a record that contains both username and password of the same user:

$password = mysqli_real_escape_string($password);
$username = mysqli_real_escape_string($username);
$sql = "SELECT Password FROM UserPass WHERE Username = '$username' AND Password = '$password'";

if there is 1 such result, it is OK.

BTW, you should not store passwords in plain text - instead use one-way hashing function and compare only the hashes of the passwords.

score 0 · Answer 2 · answered May 25 '15 at 08:29

Your SQL query should contain an 'AND' like this:

$link = mysqli_connect("myhost","myuser","mypassw","mybd") or die("Error " . mysqli_error($link));

$username = mysqli_real_escape_string($link, $_POST['username']);
$password = mysqli_real_escape_string($link, $_POST['password']);

$sql = "SELECT * FROM UserPass WHERE username = '{username }' AND password = '{$password}' LIMIT 1";

$query = mysqli_query($link, $sql);

if ($query && mysqli_num_rows($query)>0) {
     //user is authenticated
}
?>

By using the logical operator AND your query must match two conditions to give you an answer. That conditions should be known only by the users.

Also please do not store the password field as clear text in database. It's not safe. You should use sha1 hash. For more information about this please take a look here http://en.wikipedia.org/wiki/SHA-1

Additionally, I would *strongly* recommend to use prepared statements to avoid SQL injections. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php for more information — The Mighty Rubber Duck, May 26 '15 at 00:31

Is it possible to select the exact password to a username in a database?

2 Answers2