PDO validating login data

Question

Okay.. I am completely new to this PDO stuff.. I have tried to recreate my mysql script (working) to a PDO script (not working).. I have tested that my DB login informations is correctly programmed for PDO..

This is my PDO script...

<?

session_start();
//connect to DB
require_once("connect.php");

//get the posted values
$email=htmlspecialchars($_POST['email'],ENT_QUOTES);
$pass=md5($_POST['psw']); 

//now validating the email and password
$sql - $conn_business->prepare( "SELECT email, password FROM members WHERE email='".$email."'");
$sql -> execute();
$count = $sql->rowCount();
$result = $sql -> fetch();
// Now use $result['rowname'];

$stmt = $conn_business->prepare("SELECT * FROM members WHERE email='".$email."'");
$stmt ->execute();
$act = $stmt -> fetch();

//if email exists
if($count > 0)
{
//compare the password
if(strcmp($result["password"],$pass)==0)
{
    // check if activated
    if($act["activated"] == "0")
    {
        echo "act"; //account is not activated yet
    }
    else
    {
        echo "yes"; //Logging in
        //now set the session from here if needed 
        $_SESSION['email'] = $email;
    }
}
else
        echo "no"; //Passwords don't match
}
else
    echo "no"; //Invalid Login

?>

And this is my old mysql script...

session_start();
require_once("connect.php");
//get the posted values
$email=htmlspecialchars($_POST['email'],ENT_QUOTES);
$pass=md5($_POST['psw']); 

//now validating the username and password
$sql="SELECT email, password members WHERE email='".$email."'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result); 

$sql2="SELECT * FROM members WHERE email='".$email."'";
$result2=mysql_query($sql2);
$row2=mysql_fetch_array($result2);
$act = $row2['activated'];

//if username exists
if(mysql_num_rows($result)>0)
{
    //compare the password
    if(strcmp($row['password'],$pass)==0)
    {
        // check if activated
        if($act == "0")
        {
            echo "act";
        }
        else
        {
            echo "yes";
            //now set the session from here if needed 
            $_SESSION['email'] = $email;
        }
    }
    else
            echo "no";
}
else
        echo "no"; //Invalid Login

Does anybody know, what I have done wrong? It is an automatically script.. It is called through AJAX and return data based on 'no', 'yes' and 'act' that tells the AJAX/jQuery script what to do.. As I said - the mysql script is working, so please if anyone could tell me what I have done wrong with the PDO script..

EDIT:

when it returns the data to the jQuery script, this should happen: if yes: start session, redirect to page2.php with session started. else if act: write in a field that the account is not activated. else: write that email and password didn't match.

The thing is, that when I try to write the correct e-mail and password - it continues to write : "email and password didn't match" instead of redirecting.. When I say that it is not working it is because the mysql script does as described but the PDO script doesn't..

And I have tried to change the 'echo "no";' to 'echo "yes";' to see if the login would start anyway, but somehow it continues to write that the email and password didn't match..

SOLUTION:

I ahven't told this because I thought it was unnecessary, but the reason for it not to work was because of that i have had my old mysql code in comment marks on top of the page, so that the session_start command didn't work.. After deleting the old code it worked, but then I found something else to change, and that is in the PDO script when it is validating it says: $sql - $conn_business->prepare( "SELECT email, password FROM members WHERE email='".$email."'"); and then I just changed the '-' after $sql to '=' and now, everything works perfectly... Anyhow thank you everybody.. hope this code can help others..

"not working" is pretty broad...are seeing any error messages or other debugging indicators? — GDP, May 28 '12 at 14:57
One thing that catches the eye is that you're not doing any error chekcing after your queries. See http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo on how to do that. — Pekka, May 28 '12 at 15:07
@Pekka I've read it now, but I still don't know exactly how, where and why to do it.. :/ It is so easy with normal mysql, I just thought it wouldn't be that more advanced for dealing with PDO... — Corfitz., May 28 '12 at 15:15
this line looks wrong: $sql - $conn_business->prepare( "SELECT email, password FROM members WHERE email='".$email."'"); what's that minus sign there, surely a typo? — sdjuan, May 28 '12 at 16:00

score 0 · Answer 1 · answered May 28 '12 at 16:14

i believe the second query in your scripts is not necessary you could simple do

   SELECT * FROM members WHERE email=:EMAIL AND password=:PWS;

use bindParam method

   $qCredentials->bindParam(":EMAIL",$EMAIL);
   $qCredentials->bindParam(":PWS",$PWS);

then do more understable outputs rather than yes or no..
try "Unable to login: Invalid credentials supplied" for invalid types of values or
"Unable to login: Invalid credentials, couldn't find user" for invalid user credentials.
You could try to start the session after the user has been successfully logged in your IF condition returning yes, and the methods

$PDOstatement->debugDumpParams()
$PDOstatement->errorInfo()
$PDOstatement->errorCode()

will help you understand what went wrong with a query!

score 0 · Accepted Answer · answered May 28 '12 at 16:20

Did you even read the manual before you "started using" PDO?

That is not how prepared statements are supposed to be used! Your code is filled with SQL injections.
Why are you selecting same row twice ?
The strcmp() is not for checing if one string is identical to another.

And hashing passwords as simple MD5 is just a sick joke.

session_start();

//very stupid way to acquire connection
require_once("connect.php");

//get the posted values
$email = htmlspecialchars($_POST['email'],ENT_QUOTES);
if (filter_var( $email, FILTER_VALIDATE_EMAIL))
{
    // posted value is not an email
}

// MD5 is not even remotely secure
$pass = md5($_POST['psw']); 

$sql = 'SELECT email, password, activated FROM members WHERE email = :email';

$statement = $conn_business->prepare($sql);
$statement->bindParam(':email', $email, PDO::PARAM_STR);

$output = 'login error';

if ($statement->execute() && $row = $statement->fetch())
{
    if ( $row['password'] === $pass )
    {
        // use account confirmed
        if ( $row['activated'] !== 0 ) {
            $output = 'not activated';
            $_SESSION['email'] = $email;
        }

        $output = 'logged in';
    }
}

echo $output;

I understand the code you've wrote, and thank you very much for showing a perfect way of doing this.. But how would be the best way of connecting to a database? and if I have to match the posted password with the one in the Database, I have to md5 encode it to compare. — Corfitz., May 28 '12 at 17:14
instead of "naked" MD5, you should be using [crypy()](http://php.net/manual/en/function.crypt.php), with either Sha512 or Blowfish. As for connecting to the database, it encompasses much larger issues: the user authentication should be done by service (an object), which has the connection object provided to it in the constructor. I would consider application, in which modularity is achieved by including a lot of files with variables in global scope, a very flawed one. — tereško, May 28 '12 at 19:23

PDO validating login data

2 Answers2