PHP MySQL: issue selecting different columns from the same table

Question

This has surely come up before but I haven't found a solution. I am trying to select username and password from a database to verify users a simple login script. It should simply find a row in the users table with a username and password matching those submitted through the login form.

I can match the username without any problem but not the password and I have no idea why.

The table contains columns called "username" and "password" and there is only 1 row in the table with a username 'admin' and a password 'testpassword'.

Here is the function containing three options - options 1 and 4 work, the other two don't. Option 2 is the same as option 1 except it looks up a different column. I have checked that the column name in the query matches the columns in the table and that the submitted values match. I'm not getting any error messages and can't see what might be wrong (something basic, I'm sure...).

function new_session ($username, $pw, $inactive) {

    // echo statements verify that variable match database values
    echo "<h2>username = " . $username . "</h2>";
    echo "<h2>password = " . $pw . "</h2>";
    echo "<h2>inactive = " . $inactive . "</h2>";

    $db = mydb::getConnection();

    //option 1
    $statement = $db->prepare('SELECT * FROM users WHERE username = :parameter');
    $statement->bindValue(':parameter', $username);

    //option 2
    //$statement = $db->prepare('SELECT * FROM users WHERE password = :parameter');
    //$statement->bindValue(':parameter', $pw);

    //option 3
    //$statement = $db->prepare('SELECT * FROM users WHERE password = :parameter1 AND username = :parameter2');
    //$statement->bindValue(':parameter1', $username);
    //$statement->bindValue(':parameter2', $pw);

    //option 4
    //$statement = $db->prepare('SELECT * FROM users WHERE username = "admin" AND password = "testpassword"');

    $statement->execute();
    $row = $statement->fetchAll();

    if (count($row) == 1) {
        // SESSION data is set here for options 1 and 4
    } 
}

when trying to troubleshoot queries. i try to write the query in plain text form and run it. If i can't get results like that then i need to edit my base query before trying to edit in code form — hamobi
– hamobi, Commented Mar 13, 2014 at 17:40
option 3 has the params in the wrong order. param 1 should be pw and param 2 should be username. That should work fine — Ronnie
– Ronnie, Commented Mar 13, 2014 at 17:42

Damien Black · Accepted Answer · 2014-03-13 17:45:49Z

First thing you need to check is if the passwords in your data base are hashed. They probably should be, and if they are you need to compare using the hashing function PASSWORD

$statement = $db->prepare('SELECT * FROM users WHERE password = PASSWORD(:parameter)');
$statement->bindValue(':parameter', $pw);

Now, if your passwords aren't hashed (shame on you), you might have a different problem. As you can see in the above, password is a function name in mysql. It might be having problems parsing your statement because you are using password as a column name. Put tick-marks around the column name password. Like this:

$statement = $db->prepare('SELECT * FROM users WHERE `password` = :parameter');
$statement->bindValue(':parameter', $pw);

Notice that those are tick marks, not a single quote. They are found on the same key that ~ is on, above the tab key. These tick marks will indicate that password is a column name.

Beardminator · Accepted Answer · 2014-03-14 17:53:21Z

2

The word "PASSWORD" is a mysql command. so escape it first like this:

 //option 3
    //$statement = $db->prepare('SELECT * FROM users WHERE `password` = :parameter1 AND username = :parameter2')

If this query gives error, then I think you have your password encoded. Then use this for md5:

$statement->bindValue(':parameter', md5($pw));

And for sha1:

$statement->bindValue(':parameter', sha1($pw));

I see no other errors which might could result in no rows :o

edited Mar 14, 2014 at 17:53

answered Mar 13, 2014 at 17:47

Beardminator

7748 silver badges20 bronze badges

Comments

Shane · Accepted Answer · 2014-03-13 18:03:54Z

1

Thanks for all the suggestions and taking time to look at this, I have escaped the word password as suggested however I'm ashamed to say the problem was that the maxlength on my password form input was trimming the last character and I didn't spot it.

answered Mar 13, 2014 at 18:03

Shane

3257 silver badges17 bronze badges

1 Comment

Beardminator Over a year ago

Don't worry mate. At least you will know about this now. And you can prevent to happen it next time. This is how we all learn!

Collectives™ on Stack Overflow

PHP MySQL: issue selecting different columns from the same table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related