Php PDO issues translating from mysql

Question

Okay, I'm really struggling to get to grasps with PDO, even after 2 days of trying to convert everything.

I'm now at the stage of creating an array for the $user_data['???'] And here's what I've got.

if (logged_in() === true) {
    $session_user_id = $_SESSION['user_id'];
    $user_data = user_data($session_user_id, 'id', 'username', 'password', 'email', 'active', 'coins');
    $user_id = $user_data['id'];
    if (user_active($user_data['username'] === false) {
        session_destroy();
        header('Location: index.php');
        exit();
    }
}

So that's my way of getting the data for $user_data['???'] The functions to go with it are..

function user_data($user_id){
    $data = array(); 
    $user_id = (int)$user_id;

    $func_num_args = func_num_args();
    $func_get_args = func_get_args();

    if ($func_num_args > 1) {
        unset($func_get_args[0]);

        $fields = '`' . implode('`, `', $func_get_args) . '`';
        $data = //mysql_fetch_assoc(//mysql_query("SELECT $fields FROM `users` WHERE `user_id` = $user_id"));

        return $data;
    }
}

function user_active($username) { 
    $username = sanitize($username);
    $query = //mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `active` = 1");
    return (//mysql_result($query, 0) == 1) ? true : false;
}

I'm pulling my hair out trying to figure out how to convert this into PDO, can anyone give me any help?

Also, after I do convert it to PDO. Would it be as simple for to say welcome the user with a simple message of Welcome <?php $user_data['username'] ?>, Hope you enjoy your stay! or would I need to use a completely different method now?

Thanks in advance !

Answer 1

So by the looks of the example code I get the impression that you're migrating from the deprecated mysql extension over to PDO.

Something the mysql extension does that may be tripping you up is that if you don't specify the mysql link resource to mysql_query it uses the lastly created link resource that was created by mysql_connect. Once you move to using PDO you're going to have to have the PDO connection available in the user_data and user_active functions. The simplest approach would be to create a PDO connection in each function, it's terribly repetitive and not a good solution at all, but it works.

There are also some issues with sql injection vulnerabilities in the code. Aside from that you could write the functions like this:

<?php

function user_data($user_id){
  $data = array(); 
  $user_id = (int)$user_id;

  $func_num_args = func_num_args();
  $func_get_args = func_get_args();

  if ($func_num_args > 1) {
    unset($func_get_args[0]);

    // connect to the DB
    $dsn = 'mysql:dbname=<your_db_name>;host=127.0.0.1';
    $user = '<your_db_user>';
    $password = '<your_db_user_password>';
    $dbh = new PDO($dsn, $user, $password);

    // request the data
    $fields = '`' . implode('`, `', $func_get_args) . '`';
    $sql = sprintf('select %s from users where user_id = ? limit 1', $fields);
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($userid));
    $data = $stmt->fetch(PDO::FETCH_ASSOC);

    return $data;
  }
}

function user_active($username) { 
  $username = sanitize($username);

  // connect to the DB
  $dsn = 'mysql:dbname=<your_db_name>;host=127.0.0.1';
  $user = '<your_db_user>';
  $password = '<your_db_user_password>';
  $dbh = new PDO($dsn, $user, $password);

  $sql = 'select count(user_id) from users where username = ? and active';
  $stmt = $dbh->prepare($sql);
  $stmt->execute(array($username));

  return $stmt->fetchColumn() == 1;      

}  

Hope it helps.