Why his PDO query isn't work

Hi guys,

I've been moving to PDO from mysqli. This is my first try and it doesn't work.
What wrong with the code?

$query = $conn->prepare("SELECT id, email, name, password, level FROM user WHERE email = :email and password = :password LIMIT 1");
$query->execute(array(':email' => $email, ':password' => $password));
$num_rows = $query->fetchColumn();
if($num_rows == 1) {
    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
        echo $row['name'];
    }
} else {
    header("location: ../index.php");
}

Code (markup):

Thank you,

 

What is the error?

 

it redirects to index.php?

 

change fetchColumn() to fetchAll()

 

looks like there is nothing wrong with your code..
is $password stored in a plain text? how about md5( $password )?

also:
http://php.net/manual/en/pdostatement.fetchcolumn.php
PDOStatement::fetchColumn() returns a single column in the next row of a result set.
otherwise it will return false..

if object was compared to 1 it will return false right?

i suggest you should debug what is $num_rows value after fetchColumn

 

might be you have multiple records, try

if($num_rows > 0) {
​

and see if that does the trick. Or try changing fetchmode first like this

$query->setFetchMode(PDO::FETCH_ASSOC);

while($row = $query->fetch()) {​

 

Most likely got it in one. The Query is trying to pull multiple items, why only grab "ID" and throw away the rest of the row? Much less the second ->fetch would NEVER run because of the fetchColumn.

Since this looks like a user login, so there should ONLY EVER be one row returned...

$statement = $conn->prepare('
	SELECT id, email, name, level
	FROM user
	WHERE email = :email
		AND password = :password
	LIMIT 1
');

$query->execute([
	':email' => $email,
	':password' => $password
]);

if ($user = $query->fetch(PDO::FETCH_ASSOC)) {
	echo $user['name'];
} else {
	header('location: ../index.php');
}

Code (markup):

Of course I wouldn't be wasting time with that header redirect bullshit -- I don't know who's been advocating doing that for damned near everything, but they need a good swift kick in the crotch.

P.S. The ENTER and TAB keys are your friend -- USE THEM! This illegible "dump everything onto one line" crap is just another way to be sure you make mistakes. Oh, I also used PHP 5.4 array there -- if it throws an error, update to a MODERN version of PHP!

-- edit -- Oh, I would also NEVER return password from the database, access to anything security related (and if done properly hashed with something like SHA256) should be mono-directional.

 

Thanks guys for your help.
I solve it like this:

try {
   $sql="
     SELECT
       id
       , email
       , name
       , password
       , level
     FROM
       user
     WHERE
       email = :email
     AND
       password = :password
   ";
   $stmt = $conn->prepare($sql);

   $stmt->execute(array(':email' => $email, ':password' => $password));
   $users = array();
   $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
   if (count($users <> 0 )) {
     foreach ( $users AS $user ) {
       echo $user['name'];
     }
   } else {
     header("location: ../index.php");
   }
} catch (PDOException $e) {
   echo $e->getMessage();
}

Code (markup):

The previous code doesn't show any error. It's just not working.

deathshadow is right. It's a login script. So I has to redirect to the user profile page.

 

How about using the PHP crypt() function? it uses the stored hash as a part of the check (ie, you have to pull it out to do a crypt($inputpassword,$dbpassword) == $dbpassword - is there a way around that? Or is there a better way than using PHPs built-in crypt()-function for the passwords? I thought crypt() was supposed to be the "new brand spanking thing"?

 

Crypt is good too, though I prefer SHA256 / SHA512 using hash(). Really it makes no difference either way if all you're doing is dumping it into a query.

Really I don't grasp why they thought we needed CRYPT in the first place, other than people doing stuff client side that has no malfing business client-side. It's redundant to the very good hash() function, AND takes control of what hashing scheme is actually used away from the developer.

Though I know WHY they are taking control away and encouraging it, too many dipshits still using md5()

 

Thanks PoPSiCLe, for introduce PHP crypt() function. I'd never known it exists.

When you store user password in the database you store real password or encrypted data. I don't want anyone even myself know the user's data so I store it as md5().

According to definition of PHP crypt() from W3Schools:

This function behaves different on different operating systems, some operating systems supports more than one type of encryption. PHP checks what algorithms are available and what algorithms to use when it is installed.

Code (markup):

@deathshadow, why md5() is bad. Some times ago I've debated with a colleague that md5() can be decoded and he pointed out that I am stupid. He says md5() is uncrackable. I lost the battle because I has very weak evidence to support my theory.

I do really want to do some research on this.
Thank you,

 

md5() is not reversible, however it's very, very weak. It has a limited uniqueness (hence different strings can return the same md5()-hash), and it already exists millions of ready-made md5() hashtables making it a chinq to extract passwords from leaked databases. Have a look at the links here: https://www.google.com/search?q=the+problem+with+md5
crypt() stores the password, hashed, in the database - when you want to compare the user's password with the encrypted one, you create a combined value crypt($inputvalue, $storedpassword == $storedpassword) - if that matches, you're good to go. Hence, even if the database leaks, the hashed values from crypt are mostly useless, since it's next to impossible to get a perfect match, and it also takes a lot more time to check than just running a md5-hash-check. (you can't just compare the tables, you actually have to test every single crypt()-password against every single word in the dictionary and process it - not a huge timewaste, but more complicated than checking hash-tables). If you're also enforcing some form of password security on the site (mix of characters and digits, has to have one special character and so on), you're pretty safe.

 

First off, anyone who says any algorythm is uncrackable is full of shit. PERIOD.

The problem with MD5 is simple, it's been rainbow tabled to death to the point you can typically type an MD5 into google and it will come up with the result.
http://en.wikipedia.org/wiki/Rainbow_table

... and it can be brute forced due to it's having no collision detection rather easily.
http://www.dailytech.com/MD5+Is+Off...+Certificates+Impersonate+CA/article13842.htm

There are methods for reducing the risk like going rat**** bat**** insane with really big salts, but at that point is it REALLY so hard to simply:

hash('sha256', $value)

???

 

Oh, and one little side-note. Since CRYPT chooses the algorithm for you based on the PHP implementation and what's available on the host OS, it's not reliably portable across platforms if you're using it to store values in a DB. Just something to keep in mind. With HASH you can at least SAY what it is and know what it is in the codebase.

With Crypt, they want you to put what encoding is used in the bloody salt, which isn't even used as a salt! See example 3:
http://php.net/manual/en/function.crypt.php#example-4943

Which IMHO shows just how herpaderp and unnecessary CRYPT is since again, we have the perfectly good HASH function:
http://us1.php.net/manual/en/function.hash.php

I have similar views about password_hash -- which serves little to no legitimate purpose, particularly since it's bcrypt ONLY.

http://php.net/manual/en/function.password-hash.php

PHP already has MORE than enough functions without adding ones completely redundant to what already exists with ZERO improvement in functionality! "Oh but password_hash uses blowfish!" -- right, as if that's magically better than SHA512 or Whirlpool? As if they couldn't have simply, oh I don't know... added that to the existing function?

Admittedly I say the same thing about the herpafreakingderp AUDIO and VIDEO tags in HTML 5, which are redundant to OBJECT.

 

Thanks, I'll explore about this. Maybe I could apply and update my database.