Using PDO in Codeigniter

Like “Great Taste” versus “Less Filling”, a hot debate is the choice between Active Record or PDO in CodeIgniter. Active Record is the clear favorite since it is the out of the box solution. My choice, due mostly to security and comfort, is to use PDO.

How to Use PDO in Codeigniter

Unfortunately, you have to edit a core system file – DB.php. An alternate is proposed here for those unwilling to edit the core. While Codeigniter is highly extensible, the DB.php file is not, according to the user manual.

Edit DB.php for PDO

1) Copy DB.php and paste a copy in the same system/database folder. Rename this file orig-DB.php or something similar. We will only use this file to restore our system, if necessary.

2) Open the DB.php file and comment out from (114 on my install)
// Load the DB classes. Note: Since the active record class is optional
to
return $DB; line (158 on my install).
BE SURE return $DB; IS NOT COMMENTED OUT. You need to return the database connection.
Multi-line comments begin with /* and end with */.

So far we have just commented out about 43 or so lines of code. We are going to add these lines of code immediately before return $DB;
//connect to a database using PDO
try{
//to connect
$DB = new PDO($params['dbdriver'].':host='.$params['hostname'].'; dbname='.$params['database'], $params['username'], $params['password']);
}
catch(PDOException $e) {
echo 'Please contact Admin: '.$e->getMessage();
}


3) Load the database library.
Typically it is best to autoload the library. Go to the app/config/autoload.php in order to enable the database library in all files. Otherwise, to manually load the database library, use
$this->load->database(); where applicable. This should normally be called in a constructor, if not autoloaded.

That is all that is needed to use PDO in Codeigniter. However, perhaps you are truly concerned with website security and want to store your sessions in a database. This is HIGHLY recommended. In order to store sessions in a database when using PDO, you will need to adjust your session library.

Extend Codeigniter Session Library to use PDO

1) Open your application/config/config.php file and, in the session settings, choose to use a database to store your session.
$config['sess_use_database'] = TRUE;

2) Create a sessions table in your database as described in the Codeigniter User Guide under “Saving Session Data to a Database”.

3) Create a new file in your application/libraries folder and name it Session.php.

4) Copy the code below and paste it into your Session.php file.

/**
* CodeIgniter
*
* An open source application development framework for PHP 5.1.6 or newer
*
* EDITED FOR PDO by Chris Ickes
* http://christopherickes.com
* http://twitter.com/#!/Chris_Ickes
*
*/
// ------------------------------------------------------------------------
/**
* Session Class
*
* USE PDO IN SESSIONS STORED IN THE DATABASE
*/
class CI_Session {
var $sess_encrypt_cookie = FALSE;
var $sess_use_database = FALSE;
var $sess_table_name = '';
var $sess_expiration = 7200;
var $sess_expire_on_close = FALSE;
var $sess_match_ip = FALSE;
var $sess_match_useragent = TRUE;
var $sess_cookie_name = 'ci_session';
var $cookie_prefix = '';
var $cookie_path = '';
var $cookie_domain = '';
var $cookie_secure = FALSE;
var $sess_time_to_update = 300;
var $encryption_key = '';
var $flashdata_key = 'flash';
var $time_reference = 'time';
var $gc_probability = 5;
var $userdata = array();
var $CI;
var $now;
/**
* Session Constructor
*
* The constructor runs the session routines automatically
* whenever the class is instantiated.
*/
public function __construct($params = array())
{
log_message('debug', "Session Class Initialized");
// Set the super object to a local variable for use throughout the class
$this->CI =& get_instance();
// Set all the session preferences, which can either be set
// manually via the $params array above or via the config file
foreach (array('sess_encrypt_cookie', 'sess_use_database', 'sess_table_name', 'sess_expiration', 'sess_expire_on_close', 'sess_match_ip', 'sess_match_useragent', 'sess_cookie_name', 'cookie_path', 'cookie_domain', 'cookie_secure', 'sess_time_to_update', 'time_reference', 'cookie_prefix', 'encryption_key') as $key)
{
$this->$key = (isset($params[$key])) ? $params[$key] : $this->CI->config->item($key);
}
if ($this->encryption_key == '')
{
show_error('In order to use the Session class you are required to set an encryption key in your config file.');
}
// Load the string helper so we can use the strip_slashes() function
$this->CI->load->helper('string');
// Do we need encryption? If so, load the encryption class
if ($this->sess_encrypt_cookie == TRUE)
{
$this->CI->load->library('encrypt');
}
// Are we using a database? If so, load it
if ($this->sess_use_database === TRUE AND $this->sess_table_name != '')
{
$this->CI->load->database();
}
// Set the "now" time. Can either be GMT or server time, based on the
// config prefs. We use this to set the "last activity" time
$this->now = $this->_get_time();
// Set the session length. If the session expiration is
// set to zero we'll set the expiration two years from now.
if ($this->sess_expiration == 0)
{
$this->sess_expiration = (60*60*24*365*2);
}
// Set the cookie name
$this->sess_cookie_name = $this->cookie_prefix.$this->sess_cookie_name;
// Run the Session routine. If a session doesn't exist we'll
// create a new one. If it does, we'll update it.
if ( ! $this->sess_read())
{
$this->sess_create();
}
else
{
$this->sess_update();
}
// Delete 'old' flashdata (from last request)
$this->_flashdata_sweep();
// Mark all new flashdata as old (data will be deleted before next request)
$this->_flashdata_mark();
// Delete expired sessions if necessary
$this->_sess_gc();
log_message('debug', "Session routines successfully run");
}
// --------------------------------------------------------------------
/**
* Fetch the current session data if it exists
*
* @access public
* @return bool
*/
function sess_read()
{
// Fetch the cookie
$session = $this->CI->input->cookie($this->sess_cookie_name);
// No cookie? Goodbye cruel world!...
if ($session === FALSE)
{
log_message('debug', 'A session cookie was not found.');
return FALSE;
}
// Decrypt the cookie data
if ($this->sess_encrypt_cookie == TRUE)
{
$session = $this->CI->encrypt->decode($session);
}
else
{
// encryption was not used, so we need to check the md5 hash
$hash = substr($session, strlen($session)-32); // get last 32 chars
$session = substr($session, 0, strlen($session)-32);
// Does the md5 hash match? This is to prevent manipulation of session data in userspace
if ($hash !== md5($session.$this->encryption_key))
{
log_message('error', 'The session cookie data did not match what was expected. This could be a possible hacking attempt.');
$this->sess_destroy();
return FALSE;
}
}
// Unserialize the session array
$session = $this->_unserialize($session);
// Is the session data we unserialized an array with the correct format?
if ( ! is_array($session) OR ! isset($session['session_id']) OR ! isset($session['ip_address']) OR ! isset($session['user_agent']) OR ! isset($session['last_activity']))
{
$this->sess_destroy();
return FALSE;
}
// Is the session current?
if (($session['last_activity'] + $this->sess_expiration) < $this->now)
{
$this->sess_destroy();
return FALSE;
}
// Does the IP Match?
if ($this->sess_match_ip == TRUE AND $session['ip_address'] != $this->CI->input->ip_address())
{
$this->sess_destroy();
return FALSE;
}
// Does the User Agent Match?
if ($this->sess_match_useragent == TRUE AND trim($session['user_agent']) != trim(substr($this->CI->input->user_agent(), 0, 50)))
{
$this->sess_destroy();
return FALSE;
}
// Is there a corresponding session in the DB?
if ($this->sess_use_database === TRUE)
{
//PDO DB adjust $this->CI->db->where('session_id', $session['session_id']);
$stmt = $this->CI->db->prepare("SELECT * FROM $this->sess_table_name WHERE session_id = :session_id");
if ($this->sess_match_ip == TRUE)
{
//PDO DB adjust $this->CI->db->where('ip_address', $session['ip_address']);
$stmt = $this->CI->db->prepare("SELECT * FROM $this->sess_table_name WHERE session_id = :session_id AND ip_address = :session_ip");
$stmt->bindParam(':session_ip', $session['ip_address'], PDO::PARAM_STR, 16);
}
if ($this->sess_match_useragent == TRUE)
{
//PDO DB adjust $this->CI->db->where('user_agent', $session['user_agent']);
$stmt = $this->CI->db->prepare("SELECT * FROM $this->sess_table_name WHERE session_id = :session_id AND user_agent = :user_agent");
$stmt->bindParam(':user_agent', $session['user_agent'], PDO::PARAM_STR, 50);
}
//PDO DB adjust $query = $this->CI->db->get($this->sess_table_name);
$stmt->bindParam(':session_id', $session['session_id'], PDO::PARAM_STR, 40);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// No result? Kill it!
//PDO DB adjust if ($query->num_rows() == 0)
if(empty($row['session_id']))
{
$this->sess_destroy();
return FALSE;
}
// Is there custom data? If so, add it to the main session array
//PDO DB adjust $row = $query->row();
// if (isset($row->user_data) AND $row->user_data != '')
if (isset($row['user_data']) AND $row['user_data'] != '')
{
$custom_data = $this->_unserialize($row['user_data']);
if (is_array($custom_data))
{
foreach ($custom_data as $key => $val)
{
$session[$key] = $val;
}
}
}
}
// Session is valid!
$this->userdata = $session;
unset($session);
return TRUE;
}
// --------------------------------------------------------------------
/**
* Write the session data
*
* @access public
* @return void
*/
function sess_write()
{
// Are we saving custom data to the DB? If not, all we do is update the cookie
if ($this->sess_use_database === FALSE)
{
$this->_set_cookie();
return;
}
// set the custom userdata, the session data we will set in a second
$custom_userdata = $this->userdata;
$cookie_userdata = array();
// Before continuing, we need to determine if there is any custom data to deal with.
// Let's determine this by removing the default indexes to see if there's anything left in the array
// and set the session data while we're at it
foreach (array('session_id','ip_address','user_agent','last_activity') as $val)
{
unset($custom_userdata[$val]);
$cookie_userdata[$val] = $this->userdata[$val];
}
// Did we find any custom data? If not, we turn the empty array into a string
// since there's no reason to serialize and store an empty array in the DB
if (count($custom_userdata) === 0)
{
$custom_userdata = '';
}
else
{
// Serialize the custom data array so we can store it
$custom_userdata = $this->_serialize($custom_userdata);
}
// Run the update query
//PDO DB adjust $this->CI->db->where('session_id', $this->userdata['session_id']);
// $this->CI->db->update($this->sess_table_name, array('last_activity' => $this->userdata['last_activity'], 'user_data' => $custom_userdata));
$stmt = $this->CI->db->prepare("UPDATE $this->sess_table_name SET last_activity = :last_activity, user_data = :user_data WHERE session_id = :session_id");
$stmt->bindParam(':last_activity', $this->userdata['last_activity'], PDO::PARAM_INT);
$stmt->bindParam(':user_data', $custom_userdata, PDO::PARAM_STR);
$stmt->bindParam(':session_id', $this->userdata['session_id'], PDO::PARAM_STR, 40);
$count = $stmt->execute();
// Write the cookie. Notice that we manually pass the cookie data array to the
// _set_cookie() function. Normally that function will store $this->userdata, but
// in this case that array contains custom data, which we do not want in the cookie.
$this->_set_cookie($cookie_userdata);
}
// --------------------------------------------------------------------
/**
* Create a new session
*
* @access public
* @return void
*/
function sess_create()
{
$sessid = '';
while (strlen($sessid) < 32)
{
$sessid .= mt_rand(0, mt_getrandmax());
}
// To make the session ID even more secure we'll combine it with the user's IP
$sessid .= $this->CI->input->ip_address();
$this->userdata = array(
'session_id' => md5(uniqid($sessid, TRUE)),
'ip_address' => $this->CI->input->ip_address(),
'user_agent' => substr($this->CI->input->user_agent(), 0, 50),
'last_activity' => $this->now
);
// Save the data to the DB if needed
if ($this->sess_use_database === TRUE)
{
//PDO DB adjust $this->CI->db->query($this->CI->db->insert_string($this->sess_table_name, $this->userdata));
$stmt = $this->CI->db->prepare("INSERT INTO $this->sess_table_name (session_id, ip_address, user_agent, last_activity) VALUES (:session_id, :ip_address, :user_agent, :last_activity)");
$stmt->bindParam(':session_id', $this->userdata['session_id'], PDO::PARAM_STR, 40);
$stmt->bindParam(':ip_address', $this->userdata['ip_address'], PDO::PARAM_STR, 16);
$stmt->bindParam(':user_agent', $this->userdata['user_agent'], PDO::PARAM_STR, 50);
$stmt->bindParam(':last_activity', $this->userdata['last_activity'], PDO::PARAM_INT);
$stmt->execute();
}
// Write the cookie
$this->_set_cookie();
}
// --------------------------------------------------------------------
/**
* Update an existing session
*
* @access public
* @return void
*/
function sess_update()
{
// We only update the session every five minutes by default
if (($this->userdata['last_activity'] + $this->sess_time_to_update) >= $this->now)
{
return;
}
// Save the old session id so we know which record to
// update in the database if we need it
$old_sessid = $this->userdata['session_id'];
$new_sessid = '';
while (strlen($new_sessid) < 32)
{
$new_sessid .= mt_rand(0, mt_getrandmax());
}
// To make the session ID even more secure we'll combine it with the user's IP
$new_sessid .= $this->CI->input->ip_address();
// Turn it into a hash
$new_sessid = md5(uniqid($new_sessid, TRUE));
// Update the session data in the session data array
$this->userdata['session_id'] = $new_sessid;
$this->userdata['last_activity'] = $this->now;
// _set_cookie() will handle this for us if we aren't using database sessions
// by pushing all userdata to the cookie.
$cookie_data = NULL;
// Update the session ID and last_activity field in the DB if needed
if ($this->sess_use_database === TRUE)
{
// set cookie explicitly to only have our session data
$cookie_data = array();
foreach (array('session_id','ip_address','user_agent','last_activity') as $val)
{
$cookie_data[$val] = $this->userdata[$val];
}
//PDO DB adjust $this->CI->db->query($this->CI->db->update_string($this->sess_table_name, array('last_activity' => $this->now, 'session_id' => $new_sessid), array('session_id' => $old_sessid)));
$stmt = $this->CI->db->prepare("UPDATE $this->sess_table_name SET last_activity = $this->now, session_id = :new_session WHERE session_id = :old_session");
$stmt->bindParam(':new_session', $new_sessid, PDO::PARAM_STR, 40);
$stmt->bindParam(':old_session', $old_sessid, PDO::PARAM_STR, 40);
$stmt->execute();
}
// Write the cookie
$this->_set_cookie($cookie_data);
}
// --------------------------------------------------------------------
/**
* Destroy the current session
*
* @access public
* @return void
*/
function sess_destroy()
{
// Kill the session DB row
if ($this->sess_use_database === TRUE AND isset($this->userdata['session_id']))
{
//PDO DB adjust $this->CI->db->where('session_id', $this->userdata['session_id']);
// $this->CI->db->delete($this->sess_table_name);
$stmt = $this->CI->db->prepare("DELETE FROM $this->sess_table_name WHERE session_id = :session_id");
$stmt->bindParam(':session_id', $this->userdata['session_id'], PDO::PARAM_STR, 40);
$stmt->execute();
}
// Kill the cookie
setcookie(
$this->sess_cookie_name,
addslashes(serialize(array())),
($this->now - 31500000),
$this->cookie_path,
$this->cookie_domain,
0
);
}
// --------------------------------------------------------------------
/**
* Fetch a specific item from the session array
*
* @access public
* @param string
* @return string
*/
function userdata($item)
{
return ( ! isset($this->userdata[$item])) ? FALSE : $this->userdata[$item];
}
// --------------------------------------------------------------------
/**
* Fetch all session data
*
* @access public
* @return mixed
*/
function all_userdata()
{
return ( ! isset($this->userdata)) ? FALSE : $this->userdata;
}
// --------------------------------------------------------------------
/**
* Add or change data in the "userdata" array
*
* @access public
* @param mixed
* @param string
* @return void
*/
function set_userdata($newdata = array(), $newval = '')
{
if (is_string($newdata))
{
$newdata = array($newdata => $newval);
}
if (count($newdata) > 0)
{
foreach ($newdata as $key => $val)
{
$this->userdata[$key] = $val;
}
}
$this->sess_write();
}
// --------------------------------------------------------------------
/**
* Delete a session variable from the "userdata" array
*
* @access array
* @return void
*/
function unset_userdata($newdata = array())
{
if (is_string($newdata))
{
$newdata = array($newdata => '');
}
if (count($newdata) > 0)
{
foreach ($newdata as $key => $val)
{
unset($this->userdata[$key]);
}
}
$this->sess_write();
}
// ------------------------------------------------------------------------
/**
* Add or change flashdata, only available
* until the next request
*
* @access public
* @param mixed
* @param string
* @return void
*/
function set_flashdata($newdata = array(), $newval = '')
{
if (is_string($newdata))
{
$newdata = array($newdata => $newval);
}
if (count($newdata) > 0)
{
foreach ($newdata as $key => $val)
{
$flashdata_key = $this->flashdata_key.':new:'.$key;
$this->set_userdata($flashdata_key, $val);
}
}
}
// ------------------------------------------------------------------------
/**
* Keeps existing flashdata available to next request.
*
* @access public
* @param string
* @return void
*/
function keep_flashdata($key)
{
// 'old' flashdata gets removed. Here we mark all
// flashdata as 'new' to preserve it from _flashdata_sweep()
// Note the function will return FALSE if the $key
// provided cannot be found
$old_flashdata_key = $this->flashdata_key.':old:'.$key;
$value = $this->userdata($old_flashdata_key);
$new_flashdata_key = $this->flashdata_key.':new:'.$key;
$this->set_userdata($new_flashdata_key, $value);
}
// ------------------------------------------------------------------------
/**
* Fetch a specific flashdata item from the session array
*
* @access public
* @param string
* @return string
*/
function flashdata($key)
{
$flashdata_key = $this->flashdata_key.':old:'.$key;
return $this->userdata($flashdata_key);
}
// ------------------------------------------------------------------------
/**
* Identifies flashdata as 'old' for removal
* when _flashdata_sweep() runs.
*
* @access private
* @return void
*/
function _flashdata_mark()
{
$userdata = $this->all_userdata();
foreach ($userdata as $name => $value)
{
$parts = explode(':new:', $name);
if (is_array($parts) && count($parts) === 2)
{
$new_name = $this->flashdata_key.':old:'.$parts[1];
$this->set_userdata($new_name, $value);
$this->unset_userdata($name);
}
}
}
// ------------------------------------------------------------------------
/**
* Removes all flashdata marked as 'old'
*
* @access private
* @return void
*/
function _flashdata_sweep()
{
$userdata = $this->all_userdata();
foreach ($userdata as $key => $value)
{
if (strpos($key, ':old:'))
{
$this->unset_userdata($key);
}
}
}
// --------------------------------------------------------------------
/**
* Get the "now" time
*
* @access private
* @return string
*/
function _get_time()
{
if (strtolower($this->time_reference) == 'gmt')
{
$now = time();
$time = mktime(gmdate("H", $now), gmdate("i", $now), gmdate("s", $now), gmdate("m", $now), gmdate("d", $now), gmdate("Y", $now));
}
else
{
$time = time();
}
return $time;
}
// --------------------------------------------------------------------
/**
* Write the session cookie
*
* @access public
* @return void
*/
function _set_cookie($cookie_data = NULL)
{
if (is_null($cookie_data))
{
$cookie_data = $this->userdata;
}
// Serialize the userdata for the cookie
$cookie_data = $this->_serialize($cookie_data);
if ($this->sess_encrypt_cookie == TRUE)
{
$cookie_data = $this->CI->encrypt->encode($cookie_data);
}
else
{
// if encryption is not used, we provide an md5 hash to prevent userside tampering
$cookie_data = $cookie_data.md5($cookie_data.$this->encryption_key);
}
$expire = ($this->sess_expire_on_close === TRUE) ? 0 : $this->sess_expiration + time();
// Set the cookie
setcookie(
$this->sess_cookie_name,
$cookie_data,
$expire,
$this->cookie_path,
$this->cookie_domain,
$this->cookie_secure
);
}
// --------------------------------------------------------------------
/**
* Serialize an array
*
* This function first converts any slashes found in the array to a temporary
* marker, so when it gets unserialized the slashes will be preserved
*
* @access private
* @param array
* @return string
*/
function _serialize($data)
{
if (is_array($data))
{
foreach ($data as $key => $val)
{
if (is_string($val))
{
$data[$key] = str_replace('\\', '{{slash}}', $val);
}
}
}
else
{
if (is_string($data))
{
$data = str_replace('\\', '{{slash}}', $data);
}
}
return serialize($data);
}
// --------------------------------------------------------------------
/**
* Unserialize
*
* This function unserializes a data string, then converts any
* temporary slash markers back to actual slashes
*
* @access private
* @param array
* @return string
*/
function _unserialize($data)
{
$data = @unserialize(strip_slashes($data));
if (is_array($data))
{
foreach ($data as $key => $val)
{
if (is_string($val))
{
$data[$key] = str_replace('{{slash}}', '\\', $val);
}
}
return $data;
}
return (is_string($data)) ? str_replace('{{slash}}', '\\', $data) : $data;
}
// --------------------------------------------------------------------
/**
* Garbage collection
*
* This deletes expired session rows from database
* if the probability percentage is met
*
* @access public
* @return void
*/
function _sess_gc()
{
if ($this->sess_use_database != TRUE)
{
return;
}
srand(time());
if ((rand() % 100) < $this->gc_probability)
{
$expire = $this->now - $this->sess_expiration;
//PDO db adjust $this->CI->db->where("last_activity < {$expire}");
// $this->CI->db->delete($this->sess_table_name);
$this->CI->db->exec("DELETE FROM $this->sess_table_name WHERE last_activity < $expire");
log_message('debug', 'Session garbage collection performed.');
}
}
}
// END Session Class
/* End of file Session.php */