Using PHP with databases (PDO tutorial)

Welcome to this tutorial on using databases in PHP. I will be adding more sections in near future. If you have any questions/comments please contact me :slight_smile:

Index
The problem(s) with the mysql_* library (this post)
Mysqli/PDO
Using PDO - connecting to your db
Using PDO - Exceptions
Using PDO - Your first query
Using PDO - Parameterized queries
Using PDO - Fetch methods
Using PDO - When parameters just don’t do the trick

The problem(s) with the mysql_* library

If you are learning PHP, you have probably come across tutorials telling you to use mysql_* functions (mysql_connect, mysql_query, mysql_real_escape_string, etc), you are not part of the problem. The problem is the people writing these tutorials, spreading deprecated information teaching newcomers bad habits.

Let’s look at one of these tutorials, as an example
[php]<?php
$con = mysql_connect(“localhost”, “username”, “password”);
if (!$con)
{
die('Could not connect: ’ . mysql_error());
}

// some code to get username and password

// escape username and password for use in SQL
$username = mysql_real_escape_string($_POST[‘username’]);
$password = mysql_real_escape_string($_POST[‘password’]);

$sql = “SELECT * FROM users WHERE
user=’” . $username . “’ AND password=’” . $password . “’”

// more code

mysql_close($con);
?>[/php]

At least we are escaping the variables before inserting them into the query. One might argue that setting the variables username and password is wasting memory, but real world implications is probably close to zero.

So what is wrong with this code? It works!

Not safe
Well, first of all mysql_real_escape_string does not protect you from all SQL injection attacks. I have had companies literally in shock over me handing them their databases from SQL injections because “it is not possible, we are escaping everything”. In order to properly avoid SQL injections when using the mysql_* library you have to properly escape everything, which you eventually will fail at.

Messy
In addition it results in messy code, as you will have escape functions everywhere.

ps: please do not end files with ?>, you should not stop PHP if you aren’t following it with HTML/output!

Deprecated
From PHP 5.5 the mysql_* functions will throw warnings, and they will later be removed from PHP alltogether.

Note: this tutorial requires PHP >= 5.5
Password hash / verify requires PHP >= 5.5
Fix with using another method of using passwords.

Short array referencing requires PHP >= 5.4
Fix with changing short arrays to old style arrays.
[php]$result = [‘test’];
someFunction ([‘data’]);[/php]to:
[php]$result = array(‘test’);
someFunction (array(‘data’));[/php]
Namespaces requires PHP >= 5.2
Fix with changing class names to not use namespaces
[php]$pdoConn = new \PDO…[/php]to:
[php]$pdoConn = new PDO…[/php]

Mysqli/PDO
13 July 2004, PHP 5.0.0 is released. With it are the new database extensions Mysqli and PDO (as an PECL extension). These offer simple protection against one of the most used exploits against online applications, SQL injection. Yet we still to this day see large products and corporations beeing torn to shreds by hackers using simple and well known exploits. How this can happen 10 years after the solution was released is quite the mystery, and the reasons are probably up for debate. Wether it’s ignorance or lack of knowledge is probably case-spesific though.

So which one is better?
They will both get the job done, and you will probably find lots of info on which one is better online.

I will recommend using PDO because:
[ul][li]it allows you to switch database by just changing the connection string (MSSQL, Sybase, MySQL/MariaDB, Oracle, PostgreSQL, SQLite)[/li]
[li]it makes binding parameters easier (I think so anyway)[/li][/ul]

From the PHP manual:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data.

Using PDO - connecting to your db
In order to make my life simpler (and now also yours), I have created a PDO wrapper class that will take care of my PDO connection and DB functions.

Save this as DB.php
[php]<?php

class DB {

/**
*
* PDO connection
* @var PDO
*/
private $pdoConn = null;

/**
* Class constructor
*/
public function __construct() {
$this->_initDb();
}

/**
* Get PDO database connection
*
* @return
*/
public function getPDOConn() {
return $this->pdoConn;
}

/**
* Init db connection
*/
private function _initDb() {
$this->pdoConn = new \PDO(‘mysql:dbname=database_name;host=localhost;charset=utf8’, ‘database_username’, ‘database_password’);
$this->pdoConn->exec(“set names utf8”);
$this->pdoConn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$this->pdoConn->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
}

/**
* Executes parametarized query
* @param string $query
* @param array $params
* @param string $fetch_method
*/
public function query($query, $params = [], $fetch_method = ‘OBJ’, $class = ‘’) {
$stmt = $this->pdoConn->prepare($query);

  $result = $stmt->execute($params);
  
  if ($result) {
     $querybit = explode(" ", trim($query));
     if ($querybit[0] == 'SELECT') {
        if (strtoupper($fetch_method) === 'CLASS') {
           $ret = $stmt->fetchAll(constant('PDO::FETCH_CLASS'), $class);
        } else {
           $ret = $stmt->fetchAll(constant('PDO::FETCH_' . strtoupper($fetch_method)));
        }
     } else {
        $ret = [TRUE];
     }
  }      
  
  return !empty($ret) ? $ret : null;

}

/**
* Get last inserted id
*
* @return integer
*/
public function getLastInsertedId() {
return $this->pdoConn->lastInsertId();
}

/**
* Generate unnamed placeholders.
* Accepts an array of values that are to be inserted into the database.
*
* @param array $array
* @return string
*/
public function generatePlaceholders ($array) {
return rtrim(str_repeat(’?,’, count($array)), ‘,’);
}

}[/php]

Just edit line 32 to match your database details
[php]$this->pdoConn = new \PDO(‘mysql:dbname=database_name;host=localhost;charset=utf8’, ‘database_username’, ‘database_password’);[/php]

In this tutorial I will be using this database schema:

[code]-- phpMyAdmin SQL Dump
– version 3.4.10.1deb1
http://www.phpmyadmin.net

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/
!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/
!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/
!40101 SET NAMES utf8 */;


– Database: test



– Tabellstruktur for tabell user

CREATE TABLE IF NOT EXISTS user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
email varchar(255) NOT NULL,
password varchar(72) NOT NULL,
active tinyint(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email),
KEY password (password),
KEY active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;


– Dataark for tabell user

INSERT INTO user (id, email, password, active) VALUES
(1, ‘[email protected]’, ‘$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2’, 1),
(2, ‘[email protected]’, '$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq ', 1),
(3, ‘[email protected]’, '$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC ', 0),
(4, ‘[email protected]’, '$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa ', 1),
(5, ‘[email protected]’, '$2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb. ', 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/
!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/
!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;[/code]

Connecting to the database
[php]<?php
require_once (‘DB.php’);
$db = new DB();[/php]

That’s it, you should now have a database connection in the $db object.

Using PDO - Exceptions
The DB class will throw exceptions on errors. If you get your database credentials wrong, you will be presented with this:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'tests'@'localhost' (using password: YES)' in /srv/www/test/public/db.php on line 32 PDOException: SQLSTATE[28000] [1045] Access denied for user 'tests'@'localhost' (using password: YES) in /srv/www/test/public/db.php on line 32 Call Stack: 0.0000 124600 1. {main}() /srv/www/test/public/index.php:0 0.0000 127712 2. db->__construct() /srv/www/test/public/index.php:6 0.0000 127804 3. db->_initDb() /srv/www/test/public/db.php:16 0.0000 128124 4. PDO->__construct() /srv/www/test/public/db.php:32

So this clearly states there are some “uncaught exception” and then lots of stuff. So how do we use this?

Catching errors
As we are now using exceptions in PHP we should wrap our code in a try/catch. While we’re at it we add some error reporting to the script.
[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

} catch (Exception $e) {
var_dump($e);
}[/php]

Now whenever an exception is thrown we will catch it with our catch block! Here we would normally do some sort of error handling, like presenting the user with a pretty error message while we log the error to a log file/table. For now we are just var_dumping it to see the content of the exception-object. Note the echo of the

 tag before var_dumping, using the preformatted text tag before print_r/var_dump gives you a much easier to read output.

Without pre:

class PDOException#3 (9) { protected $message => string(87) "SQLSTATE[28000] [1045] Access denied for user 'tests'@'localhost' (using password: YES)" private $string => string(0) "" protected $code => int(1045) protected $file => string(27) "/srv/www/test/public/DB.php" protected $line => int(32) private $trace => array(3) { [0] => array(6) { 'file' => string(27) "/srv/www/test/public/DB.php" 'line' => int(32) 'function' => string(11) "__construct" 'class' => string(3) "PDO" 'type' => string(2) "->" 'args' => array(3) { ... } } [1] => array(6) { 'file' => string(27) "/srv/www/test/public/DB.php" 'line' => int(16) 'function' => string(7) "_initDb" 'class' => string(2) "DB" 'type' => string(2) "->" 'args' => array(0) { ... } } [2] => array(6) { 'file' => string(30) "/srv/www/test/public/index.php" 'line' => int(7) 'function' => string(11) "__construct" 'class' => string(2) "DB" 'type' => string(2) "->" 'args' => array(0) { ... } } } private $previous => NULL public $errorInfo => NULL public $xdebug_message => string(454) " PDOException: SQLSTATE[28000] [1045] Access denied for user 'tests'@'localhost' (using password: YES) in /srv/www/test/public/DB.php on line 32 Call Stack: 0.0000 124604 1. {main}() /srv/www/test/public/index.php:0 0.0000 127684 2. DB->__construct() /srv/www/test/public/index.php:7 0.0000 127776 3. DB->_initDb() /srv/www/test/public/DB.php:16 0.0000 128092 4. PDO->__construct() /srv/www/test/public/DB.php:32 " } 

With pre:

[code]class PDOException#3 (9) {
protected $message =>
string(87) “SQLSTATE[28000] [1045] Access denied for user ‘tests’@‘localhost’ (using password: YES)”
private $string =>
string(0) “”
protected $code =>
int(1045)
protected $file =>
string(27) “/srv/www/test/public/DB.php”
protected $line =>
int(32)
private $trace =>
array(3) {
[0] =>
array(6) {
‘file’ =>
string(27) “/srv/www/test/public/DB.php”
‘line’ =>
int(32)
‘function’ =>
string(11) “__construct”
‘class’ =>
string(3) “PDO”
‘type’ =>
string(2) “->”
‘args’ =>
array(3) {

}
}
[1] =>
array(6) {
‘file’ =>
string(27) “/srv/www/test/public/DB.php”
‘line’ =>
int(16)
‘function’ =>
string(7) “_initDb”
‘class’ =>
string(2) “DB”
‘type’ =>
string(2) “->”
‘args’ =>
array(0) {

}
}
[2] =>
array(6) {
‘file’ =>
string(30) “/srv/www/test/public/index.php”
‘line’ =>
int(7)
‘function’ =>
string(11) “__construct”
‘class’ =>
string(2) “DB”
‘type’ =>
string(2) “->”
‘args’ =>
array(0) {

}
}
}
private $previous =>
NULL
public $errorInfo =>
NULL
public $xdebug_message =>
string(454) "
PDOException: SQLSTATE[28000] [1045] Access denied for user ‘tests’@‘localhost’ (using password: YES) in /srv/www/test/public/DB.php on line 32

Call Stack:
0.0000 124628 1. {main}() /srv/www/test/public/index.php:0
0.0000 127012 2. DB->__construct() /srv/www/test/public/index.php:7
0.0000 127104 3. DB->_initDb() /srv/www/test/public/DB.php:16
0.0000 127420 4. PDO->__construct() /srv/www/test/public/DB.php:32
"
}
[/code]

For now we’ll just pretty print an error message:
[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]

Now we have this output:

[code]Error:
1045: SQLSTATE[28000] [1045] Access denied for user ‘tests’@‘localhost’ (using password: YES)

Stack trace:
/srv/www/test/public/DB.php Line #32
/srv/www/test/public/DB.php Line #16
/srv/www/test/public/index.php Line #7[/code]

Much better :slight_smile:

Just following the stack trace we can see how this error occurs.

Line 7 in index.php
[php]$db = new DB();[/php]
calls

Line 16 in DB.php
[php]$this->_initDb();[/php]
calls

Line 32 in DB.php
[php]$this->pdoConn = new \PDO(‘mysql:dbname=test;host=localhost;charset=utf8’, ‘tests’, ‘ghiShppmuHTulkXPq9CVBv9tkJj8ytyz’);[/php]

And there is my error, changing to the correct credentials (database name tests -> test, in my case)
[php]$this->pdoConn = new \PDO(‘mysql:dbname=test;host=localhost;charset=utf8’, ‘test’, ‘ghiShppmuHTulkXPq9CVBv9tkJj8ytyz’);[/php]

And the error disappears :slight_smile:

Using PDO - Your first query

[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$users = $db->query(‘SELECT * FROM user’);

echo ‘

’;
var_dump($users);

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]

Output

array(5) { [0] => class stdClass#4 (4) { public $id => string(1) "1" public $email => string(21) "[email protected]" public $password => string(60) "$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2" public $active => string(1) "1" } [1] => class stdClass#5 (4) { public $id => string(1) "2" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq " public $active => string(1) "1" } [2] => class stdClass#6 (4) { public $id => string(1) "3" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC " public $active => string(1) "0" } [3] => class stdClass#7 (4) { public $id => string(1) "4" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa " public $active => string(1) "1" } [4] => class stdClass#8 (4) { public $id => string(1) "5" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb. " public $active => string(1) "0" } }

Let’s try to pretty print some data
[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$users = $db->query(‘SELECT * FROM user’);

echo ‘

Users

’;
foreach ($users as $user) {
echo 'ID: ’ . $user->id . ‘
’;
echo 'Email: ’ . $user->email . ‘
’;
echo 'Password: ’ . $user->password . ‘
’;
echo 'Active: ’ . $user->active . ‘
’;
echo ‘
’;
}

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]
Output:

[code]Users

ID: 1
Email: [email protected]
Password: $2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2
Active: 1

ID: 2
Email: [email protected]
Password: $2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq
Active: 1

ID: 3
Email: [email protected]
Password: $2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC
Active: 0

ID: 4
Email: [email protected]
Password: $2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa
Active: 1

ID: 5
Email: [email protected]
Password: $2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb.
Active: 0
--------------------------------------------------------------------------------------------------[/code]

Congratulations, you are using PDO!

So by using Mysqli or PDO I am automagically safe against SQL injections? Well, no. While PDO gives you a way to not lock your application to use one spesific type of database, it does not automatically solve the problem of injections.

Using PDO - Parameterized queries
That’s why we should always use parameterized queries. These work as follows:
[ul][li]First we prepare a SQL statement, using placeholders where we would normally insert our variables.[/li]
[li]Then we bind our variables to the statement.[/li]
[li]Lastly we execute the statement.[/li][/ul]

This way the user submitted / untrusted data is never actually part of the query string, and can not (however malicious as it may be) interfere with the query process.

Our first parameterized query. As mentioned earlier the query function accepts multiple parameters. The first one is the query string itself, then an array of variables that will be inserted into the query. The rest of the parameters are not of interest, yet.
[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$users = $db->query(‘SELECT * FROM user WHERE id = ?’, [1]);

echo ‘

Users

’;
foreach ($users as $user) {
echo 'ID: ’ . $user->id . ‘
’;
echo 'Email: ’ . $user->email . ‘
’;
echo 'Password: ’ . $user->password . ‘
’;
echo 'Active: ’ . $user->active . ‘
’;
echo ‘
’;
}

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]

Output:

[code]Users

ID: 1
Email: [email protected]
Password: $2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2
Active: 1
----------------------------------------------------------------------------------------------------[/code]

Congratulations, you have just executed a parameterized and safe query using PDO without having to escape anything :slight_smile:

Using PDO - Fetch methods

Our query function accepts multiple parameters:
[php]query($queryString, $paramArray, $fetchMethod, $class)[/php]

The fetch methods will define how your returned data is presented.

The DB class is pre set to use PDO::FETCH_OBJ, you can change this in the DB.php file if you want.

PDO::FETCH_OBJ:
returns an anonymous object with property names that correspond to the column names

PDO::FETCH_ASSOC:
returns an array indexed by column name

PDO::FETCH_BOTH:
returns an array indexed by both column name and number

PDO::FETCH_BOUND:
Assigns the values of your columns to the variables set with the ->bindColumn() method

PDO::FETCH_CLASS:
Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist

PDO::FETCH_INTO:
Updates an existing instance of the named class

PDO::FETCH_LAZY:
Combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating the object variable names as they are used

PDO::FETCH_NUM:
returns an array indexed by column number

Default fetch method
To get data in the default fetch method you don’t have to submit a fetch method at all. This will output an array containing object representations of the result.

[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$users = $db->query(‘SELECT * FROM user’);

echo ‘

’;
var_dump($users);

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]
Output:

array(5) { [0] => class stdClass#4 (4) { public $id => string(1) "1" public $email => string(21) "[email protected]" public $password => string(60) "$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2" public $active => string(1) "1" } [1] => class stdClass#5 (4) { public $id => string(1) "2" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq " public $active => string(1) "1" } [2] => class stdClass#6 (4) { public $id => string(1) "3" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC " public $active => string(1) "0" } [3] => class stdClass#7 (4) { public $id => string(1) "4" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa " public $active => string(1) "1" } [4] => class stdClass#8 (4) { public $id => string(1) "5" public $email => string(14) "[email protected]" public $password => string(61) "$2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb. " public $active => string(1) "0" } }

How to use different fetch methods
In our code all you have to do is send in the method name. Here we send in an empty parameter array and change from OBJ to ASSOC, to retrieve an assosiative array.
[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$users = $db->query(‘SELECT * FROM user’, [], ‘ASSOC’);

echo ‘

’;
var_dump($users);

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]
Output:

array(5) { [0] => array(4) { 'id' => string(1) "1" 'email' => string(21) "[email protected]" 'password' => string(60) "$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2" 'active' => string(1) "1" } [1] => array(4) { 'id' => string(1) "2" 'email' => string(14) "[email protected]" 'password' => string(61) "$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq " 'active' => string(1) "1" } [2] => array(4) { 'id' => string(1) "3" 'email' => string(14) "[email protected]" 'password' => string(61) "$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC " 'active' => string(1) "0" } [3] => array(4) { 'id' => string(1) "4" 'email' => string(14) "[email protected]" 'password' => string(61) "$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa " 'active' => string(1) "1" } [4] => array(4) { 'id' => string(1) "5" 'email' => string(14) "[email protected]" 'password' => string(61) "$2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb. " 'active' => string(1) "0" } }

Using PDO - When parameters just don’t do the trick
Some times you just have to add variables directly into the query. This will happen when you need to add an ORDER BY, or a LIMIT clause to your queries. Since parameters in a parameterized query aren’t executed as part of a query “ORDER BY ?” and/or “LIMIT ?,?” will not work. Some also use variable table or column names, which won’t work either with placeholders.

So how do you do it (securely)?

You should make 100% sure the data inserted is what you think it is. I do it like this:

[php]<?php
ini_set(‘error_reporting’, E_ALL);
ini_set(‘display_errors’, ‘1’);

try {
require_once (‘DB.php’);
$db = new DB();

$order = !empty($_GET[‘order’]) && $_GET[‘order’] === ‘desc’ ? ‘desc’ : ‘asc’;
$start = !empty($_GET[‘start’]) && $_GET[‘start’] > 0 && $_GET[‘start’] < 1000 ? (int) $_GET[‘start’] : 0;
$limit = !empty($_GET[‘limit’]) && $_GET[‘limit’] > 0 && $_GET[‘limit’] < 1000 ? (int) $_GET[‘limit’] : 10;

$users = $db->query(’
SELECT *
FROM user
ORDER BY id ’ . $order . ’
LIMIT ’ . $start . ‘,’ . $limit);

echo ‘

’;
var_dump($users);

} catch (Exception $e) {
echo ‘

Error:

’;
echo $e->getCode() . ‘: ’ . $e->getMessage();
echo ‘

Stack trace:

’;
foreach ($e->getTrace() as $trace) {
echo $trace[‘file’] . ’ Line #’ . $trace[‘line’] . ‘
’;
}
}[/php]

This ensures that the data inserted is safe, and that we have a default fallback value if no value (or an invalid value) is submitted from the user.

Thanks JimL, This is awesome.

Sponsor our Newsletter | Privacy Policy | Terms of Service