# Using PHP with databases (PDO tutorial)

**URL:** <https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289>\
**Category:** The Occasional Tutorial\
**Created:** [October 12, 2013, 11:10am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289 "2013-10-12T11:10:22Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:10am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/1 "2013-10-12T11:10:22Z")

</div>

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 🙂

Index  
The problem(s) with the mysql\_\* library (this post)  
[Mysqli/PDO](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74968/#msg74968)  
[Using PDO - connecting to your db](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74969/#msg74969)  
[Using PDO - Exceptions](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74970/#msg74970)  
[Using PDO - Your first query](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74971/#msg74971)  
[Using PDO - Parameterized queries](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74972/#msg74972)  
[Using PDO - Fetch methods](http://www.phphelp.com/forum/index.php?topic=21644.msg74975#msg74975)  
[Using PDO - When parameters just don’t do the trick](http://www.phphelp.com/forum/the-occasional-tutorial/using-php-with-databases-(pdo-tutorial)/msg74983/#msg74983)

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]

> **[Basic Tutorial : PHP mysql\_real\_escape\_string() Function - Online Website Design...](http://tutorial.world.edu/web-development/basic-tutorial-php-mysql_real_escape_string-function/)**
>
> Basic Tutorial : PHP mysql\_real\_escape\_string() Function. mysql\_real\_escape\_string () is a PHP function that is used to give the backslash in some php code to be displayed on webpage, but when storing and matching the data in mysql, the code will …...

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]

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:12am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/2 "2013-10-12T11:12:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:12am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/3 "2013-10-12T11:12:40Z")

</div>

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](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, ‘jim.leirvik@gmail.com’, ‘$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2’, 1),  
(2, ‘test1@test.com’, '$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq ', 1),  
(3, ‘test2@test.com’, '$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC ', 0),  
(4, ‘test3@test.com’, '$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa ', 1),  
(5, ‘test4@test.com’, '$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.

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:12am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/4 "2013-10-12T11:12:56Z")

</div>

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:

```auto
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 🙂

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 🙂
```

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:13am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/5 "2013-10-12T11:13:04Z")

</div>

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) "jim.leirvik@gmail.com"
    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) "test1@test.com"
    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) "test2@test.com"
    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) "test3@test.com"
    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) "test4@test.com"
    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: [jim.leirvik@gmail.com](mailto:jim.leirvik@gmail.com) Password: $2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2 Active: 1

## ID: 2 Email: [test1@test.com](mailto:test1@test.com) Password: $2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq Active: 1

## ID: 3 Email: [test2@test.com](mailto:test2@test.com) Password: $2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC Active: 0

## ID: 4 Email: [test3@test.com](mailto:test3@test.com) Password: $2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa Active: 1

ID: 5  
Email: [test4@test.com](mailto:test4@test.com)  
Password: $2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb.  
Active: 0  
--------------------------------------------------------------------------------------------------[/code]

Congratulations, you are using PDO!
```

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:13am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/6 "2013-10-12T11:13:11Z")

</div>

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: [jim.leirvik@gmail.com](mailto:jim.leirvik@gmail.com)  
Password: $2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2  
Active: 1  
----------------------------------------------------------------------------------------------------[/code]

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

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 11:35am UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/7 "2013-10-12T11:35:11Z")

</div>

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) "jim.leirvik@gmail.com"
    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) "test1@test.com"
    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) "test2@test.com"
    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) "test3@test.com"
    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) "test4@test.com"
    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) "jim.leirvik@gmail.com"
    'password' =>
    string(60) "$2y$10$8bd6vaGVAr4qGhydPBKrLOjDCCUFAPPR26CyelFx9yqiJ7VdL8wm2"
    'active' =>
    string(1) "1"
  }
  [1] =>
  array(4) {
    'id' =>
    string(1) "2"
    'email' =>
    string(14) "test1@test.com"
    'password' =>
    string(61) "$2y$10$1CxpwCMjhPOmoQwBe/mNj.hPwbcVtXIyE9vOoRrTe5LAZ.AVHQwBq "
    'active' =>
    string(1) "1"
  }
  [2] =>
  array(4) {
    'id' =>
    string(1) "3"
    'email' =>
    string(14) "test2@test.com"
    'password' =>
    string(61) "$2y$10$IdbT7LRFIohWjLYl7E1/4uTrIpu7M7.GM8BCDCyl2xGNPb0u58.dC "
    'active' =>
    string(1) "0"
  }
  [3] =>
  array(4) {
    'id' =>
    string(1) "4"
    'email' =>
    string(14) "test3@test.com"
    'password' =>
    string(61) "$2y$10$v2VPF9nvi/X04uadLbvq6efnfcpmQzByaC5HkFM.RKwd49JwyOQWa "
    'active' =>
    string(1) "1"
  }
  [4] =>
  array(4) {
    'id' =>
    string(1) "5"
    'email' =>
    string(14) "test4@test.com"
    'password' =>
    string(61) "$2y$10$y6rCIw/L7S6TBTfBWqula.dVv/MlCoasBxSkRqNg87xIBKf3xuNb. "
    'active' =>
    string(1) "0"
  }
}
`
```
```

---

<div class="post-metadata">

**Author:** ![JimL](https://www.phphelp.com/user_avatar/www.phphelp.com/jiml/32/1842_2.png) [@JimL](https://www.phphelp.com/u/JimL)\
**Post date:** [October 12, 2013, 1:18pm UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/8 "2013-10-12T13:18:09Z")

</div>

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.
```

---

<div class="post-metadata">

**Author:** ![phphelp](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/3ec8ea/32.png) [@phphelp](https://www.phphelp.com/u/phphelp)\
**Post date:** [October 12, 2013, 10:18pm UTC](https://www.phphelp.com/t/using-php-with-databases-pdo-tutorial/21289/9 "2013-10-12T22:18:49Z")

</div>

Thanks JimL, This is awesome.
