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='[email protected]' 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?

Answer 1

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.