Prepare(), Execute() working but FETCH_ASSOC not working

Question

I am trying to create a simple login API which allows login to a database on entering matching email and password and throws the id of that row in result.

Note: 1) I am using var_dump at places only for test purpose.
2) discalimer: I am not using password encryption just for simplicity of understanding the flow.
3) I am using PDO but no framework. Here is a part of the login.php code. 'user' is a class declared in user.php and table name is 'users' containing columns as id, email, password and username.

//prepare user object
$user= new user($db);

$user->email=isset($_GET['email'])?$_GET['email']:die();
$user->password=isset($_GET['password']) ? $_GET['password'] : die();


$stmt=$user->login();
var_dump($stmt);
if ($stmt->rowCount()>0) {
  $row=$stmt->fetch(PDO::FETCH_ASSOC);
  var_dump($row);
  $user_arr=array(
    "status" => true,
    "message" => "succesfully logged in",
    "id" => $row['id']
  );
} else {
  $user_arr=array(
    "status" => false,
    "message" => "login attempt failed due to invalid email or password"
  );
}
print_r(json_encode($user_arr));

Below is the snippet of the login function from user.php

 function login(){
 //select all query


 $query="SELECT
              'id','email','password','username'
          FROM ".$this->table_name."
           WHERE
              email='".$this->email."'
                AND password='".$this->password."'";

//prepare query statement
$stmt=$this->conn->prepare($query);
if ($stmt->execute()) {
  return $stmt;
} else {
  return null;
}

The output is

object(PDOStatement)#4 (1) { ["queryString"]=> string(194) "SELECT 'id','email','password','username' FROM users WHERE email='upasana@api.com' AND password='def456'" } array(4) { ["id"]=> string(2) "id" ["email"]=> string(5) "email" ["password"]=> string(8) "password" ["username"]=> string(8) "username" } {"status":true,"message":"succesfully logged in","id":"id"}

So, basically, FETCH_ASSOC is not working? Why is it treating the id as id and throwing as a string rather than finding the associated value?

**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, May 29 '20 at 23:04
I am well aware of the warnings and given disclaimer however this is not a code for production so how does your answer stands relevant? — code-mon, May 30 '20 at 06:18

jeprubio · Accepted Answer · 2020-05-29T22:17:39.160

2

FETCH_ASSOC is working fine, the problem is in your sql, if you write the fields between single quotes you are getting strings and not the fields content.

Just write the fields without quotes:

$query = "SELECT id, email, password, username ...";

And you should use prepared statements to prevent from SQL injection attacks.

edited May 29 '20 at 22:17

answered May 29 '20 at 22:00

jeprubio

17,312
5
45
56

Prepare(), Execute() working but FETCH_ASSOC not working

1 Answers1