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

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.