Checking if session id ISSET in PDO

Question

I'm switching to PDO prepared statements and I'm having trouble with my code for getting and using session id's.

When a customer registers the session_id is set to equal the user_id (auto-incremented), and session_id is set in the same way when the user returns. This was working properly before switching to PDO, but now session_id aren't being recognized on any subsequent pages (it's viewing 'user_id' as null)

Do I need to switch to using PDO Session Handlers, or is there a way to continue using the method I'm familiar with?

mysql - This is the code I was using to set session id's in (this worked properly):

// Statements defining $user_name, $password and $hashedPassword (as shown below), and mysql_real_escape_string
// Insert statement
if($result) { 
$qry="SELECT * FROM customer_info WHERE user_name='$user_name' AND password='".sha1($salt + $_POST['password'])."'";
$result=mysql_query($qry);
if($result) {
    if(mysql_num_rows($result) == 1) {
    session_regenerate_id();
    $member = mysql_fetch_assoc($result);
    $_SESSION['SESS_USER_ID'] = $member['user_id'];
    session_write_close();
    exit();
    } } }

PDO - This is the code I've tried (session id isn't being set):

$user_name = $_POST['user_name'];
$password = $_POST['password'];  
$hashedPassword = sha1($salt . $password); 
$stmt = $conn->prepare('INSERT INTO customer_info (...) VALUES(...)');
$stmt->bindParam(':user_id', $user_id); 
...  
$insertResult = $stmt->execute();  
if ($insertResult) {
    $qry="SELECT * FROM customer_info WHERE user_name = $user_name AND password=$hashedPassword";
    if($qry) {
        $affected_rows = $stmt->rowCount();
        if ($affected_rows == 1)  {   
            session_regenerate_id();
            $member = $stmt->fetch();
            $_SESSION['SESS_USER_ID'] = $member['user_id'];   
        }  

Additional code that allows me to reference session id on subsequent pages

This code is at the top of every subsequent page:

<?php
  //Starts session and checks if the session variable SESS_USER_ID is present or not
  require_once('auth.php');  
  //Database connection and Open database
  require_once('config.php');  
  $user_id = $_SESSION['SESS_USER_ID']; 
?>

auth.php file

<?php
session_start();
if(!isset($_SESSION['SESS_USER_ID']) || (trim($_SESSION['SESS_USER_ID']) == '')) {
    header("location: login_failed.html");
    exit();
}
?>

Answer 1

To be very specific to the question

PDO - This is the code I've tried (session id isn't being set):

$user_name = $_POST['user_name'];
$password = $_POST['password'];  
$hashedPassword = sha1($salt . $password); 
$stmt = $conn->prepare('INSERT INTO customer_info (...) VALUES(...)');
$stmt->bindParam(':user_id', $user_id); 
...  
$insertResult = $stmt->execute();  
if ($insertResult) {
    $qry="SELECT * FROM customer_info WHERE user_name = $user_name AND password=$hashedPassword";
    if($qry) {
        $affected_rows = $stmt->rowCount();
        if ($affected_rows == 1)  {   
            session_regenerate_id();
            $member = $stmt->fetch();
            $_SESSION['SESS_USER_ID'] = $member['user_id'];   
        } 

Now look at the code carefully and answer yourself when the session id should be set?
This should be done when:

1) the $insertResult isn't FALSE

Oh there's an obvious error in your code, so let me stop here and do explain -

$qry="SELECT * FROM customer_info WHERE user_name = $user_name AND password=$hashedPassword";

This is merely a dumb string in this case. So you instruction itself comes down to:

if ($insertResult) { //Which always will be TRUE regarding result as the string isn't empty!!! 

if ($affected_rows == 1)  {   //if the number of affected rows == 1

then do INSERT the Session ID

What exactly you are doing wrong

1) You do not track errors, so that you can't catch exact nature of the one
2) You do not execute SQL statement and get result from it
3) In case of row count "failure" you do not track this one, but do ignore this instead

What you should do instead

1) Execute the statement : $qry="SELECT * FROM customer_info WHERE user_name = $user_name AND password=$hashedPassword"; (also the SQL statement contains variables, seems like you got this one from old mysql_* functions) like

$stmt->prepare("SELECT * FROM customer_info WHERE user_name = :user_name AND password=:hashedPassword LIMIT 1;"); 
$stmt->execute(array(':user_name' => $username, ':hashedPassword' => $hashedPassword))); 
$result = $stmt->fetchAll(PDO::FETCH_ASSOC)

//now check $result if it found the row, like

if ( $result ){
   // found
} else {
   // not found, do track an error here
}

2) Again - keep error track. I could suggest you to implement a custom error handler, but for an example sake, you can use an array.
It would look like this:

<?php

//at the bottom of the script
$errors = array();

...
...

if ( $result ){
  // do next stuff
  ...
} else {
  //add an error
  array_push('Incorrect data', $errors);
}

then somewhere, you can simply print that errors, like,

//We won't use a global keyword
function print_errors(array $errors){

   if ( !empty($errors) ){
        foreach($errors as $error){
           print '<p><li>' . $error . '</li></p>';
        }

   }
}


//Use like this:
print_errors($errors);

This will give you a confidence where exactly it does stuck

Answer 2

The call to $stmt->fetch() is failing because at this point, $stmt still refers to the SQL INSERT from which you cannot fetch rows.

So what you need to do is execute a SELECT statement to retrieve the newly entered user details and fetch() from that instead. Assuming you have an auto-increment column in the customer_info table, use PDO::lastInsertId() to get the new row's id and use it in your SELECT query.

$user_name = $_POST['user_name'];
$password = $_POST['password'];  
$hashedPassword = sha1($salt . $password); 
$stmt = $conn->prepare('INSERT INTO customer_info (...) VALUES(...)');
$stmt->bindParam(':user_id', $user_id); 
...  
$insertResult = $stmt->execute();  
if ($insertResult && $stmt->rowCount() == 1) {
  // Ok, the INSERT was successful, so now SELECT the row back
  // Use lastInsertId() to get the new row's id
  // Assuming the id column is `user_id`...
  $stmt_user = $conn->prepare("SELECT * FROM customer_info WHERE user_id = :user_id");
  $stmt_user->bindValue(":user_id", $conn->lastInsertId());
  $stmt_user->execute();

  session_regenerate_id();
  // Fetch from the SELECT query
  $member = $stmt_user->fetch();
  $_SESSION['SESS_USER_ID'] = $member['user_id'];   
}

Absent an auto-increment column like I assumed user_id to be, you could do the query with user_name and password by binding the input $_POST['user_name'] and the $hashedpassword just as you had in your original mysql_*() code.