Your first database wrapper's childhood diseases

1. Prepared statements support
2. Error reporting
• A special note to JSON API writers
3. Connection
4. General purpose query function.
5. Type-hinted binding.
6. Select function
7. SQL injection in Insert and Update methods
8. Statefulness
9. Protected PDO instance
10. Code repetition
11. Database credentials as class variables.
12. Inheritance
13. A minimalistic PDO wrapper example
14. Comments (7)

Almost every PHP user, while learning OOP, is trying to create a DB wrapper of their own. Although a good idea by itself, it leads to numerous mistakes, caused by the lack of practical experience.

Interestingly, that these mistakes appear to be surprisingly common, repeated in almost every wrapper. Let's sort them out.

Prepared statements support

This one is not is not very common but the most fatal if present. In case your class does not offer a usable support for prepared statements it must be thrown away immediately, or at least heavily rewritten. Both mysqli and PDO offer functions to run a query through a prepared statement - so there is no excuse to not have one in your wrapper.

Error reporting

To be frank, if it's your first wrapper, then just leave the error reporting alone. PDO and PHP will do it much better, really. Everything you can think of is either superfluous or makes your experience worse than with raw PHP handling.

• No need to echo the error message right away
• No need to silence the error message with empty try catch
• No need for the dedicated silent production mode.
• No need for the dedicated $error variable (what you gonna do with it anyway?).
• No need for the manual error logging code.
• No need for the try..catch at all.

Just let PDO to throw an exception, but do not catch it - and it will suit both production and development modes all right.

All you need is three simple things, one dedicated to PDO and two PHP settings that have to be common for the whole application:

1. Set PDO in exception mode.

   $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

2. Tell PHP to report ALL errors:

   error_reporting(E_ALL);

3. On a development server just turn displaying errors on:

   ini_set('display_errors', 1);

   On a production server turn displaying errors off while logging errors on:

   ini_set('display_errors', 0);
   ini_set('log_errors', 1);

This. Is. All.

Of course, some improvements to the error handling could be done, but for the basic wrapper that's more than enough: after configuring PHP and PDO this way you will be always notified of the every error occurred, in the most suitable way - on a live server errors will be logged for your future inspection, while on a development server it's good to see them right on screen.

If you still want to handle errors some specific way, then you have to understand that it have to be done NOT on the database wrapper level but in a site-wide error handler. You are welcome to write one, but that would be absolutely irrelevant to any database stuff.

Please read more about most fundamental principles of error reporting - it should answer all your further questions. If not, you're welcome to ask in the comments, it will help me to improve the article.

A special note to JSON API writers

Your database error like "Driver not found" or "Syntax error in the SQL query" should be never ever revealed to the client. Only a generalized error message like "Server error", along with proper HTTP response code have to be sent to the client. Your ["error" =>$e->getMessage()] will tell nothing useful to a honest client, will reveal some potentially sensitive information to a malicious user, and will leave yourself without error message logged. Thus if you want to return an error in JSON format (which for a properly written client is not necessary as it should be satisfied with HTTP error code alone), then write an error handler that will log the error message and send JSON response to the client:

set_exception_handler(function ($e)
{
    error_log("[$errno] $errstr in $errfile:$errline");
    header('HTTP/1.1 500 Internal Server Error', TRUE, 500);
    header('Content-Type: application/json');
    echo json_encode(["error" => 'Server error']);
    exit;
});

And it will properly handle every error that may occur in your application, not only database errors.

Connection

Like it is said in the main article, a connection has to be made only once. Means you should create only a single PDO instance that has to be used all the way through the script execution.

You have to understand that each PDO instance creates a distinct connection to DB server. Thus, you should never ever open and close a new connection in the each function. Because it will considerably slow down your PHP and won't let you utilize some DB features that can be used only within the same connection - i.e. transactions or getting the insert id.

Besides, if you don't close connections (by creating a separate connection from each class constructor for example), things become much worse, as it will result in opening hundreds simultaneous connections from the same PHP script instance, killing your database server in an instant with TOO MANY CONNECTIONS error.

Therefore, most direct and straightforward way would be to create a single instance of your wrapper that connects to database in its constructor, and then use this single object wherever you need. If you need the PDO instance in the function - pass it as a function parameter. If you need a PDO instance in the class - pass it as a constructor parameter and assign to a class variable.

General purpose query function.

There are many variations, but to be honest, you need a very simple method like this:

function run($sql, $params = NULL)
{
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt; 
}

You won't believe, but this function is just incomparably more powerful than any function you made, being more flexible than any number of specialized methods you created in your class:

• First, it can run any kind of query supported by PDO, including ones you never had an idea to create a method for - a SHOW TABLES or INSERT IGNORE for example.
• Second, with method chaining you can get the resulting data in dozens of different formats, instead of just blunt fetchAll() implemented in your select() method

Some examples:

// Getting single column from the single row
$id = $db->run("SELECT id FROM users WHERE email=?", [$email])->fetchColumn();

// Getting single row
$user = $db->run("SELECT * FROM users WHERE email=?", [$email])->fetch();

// Getting array of rows
$users = $db->run("SELECT * FROM users LIMIT ?,?", [$offset, $limit])->fetchAll();

You can get the number of affected rows as well:

$sql = "UPDATE users SET balance = ? WHERE id =? "
$updated = $db->run($sql, [$balance, $id])->rowCount();

As you can see,

• there is no need for the dedicated bind() function - PDO can bind all your variables already, simply by sending them into execute().
• no need to distinguish SELECT queries with ugly string parsing.
• no need for separate functions to get different query results

All you need is just a simple method that accepts a query and array with variables to be bound as parameters, and which returns a PDO statement, which makes this function extremely flexible and convenient.

Type-hinted binding.

You don't need it. Just send all your parameters directly to execute().

Select function

Many beginner PHP users who have little experience with SQL, tend to create a function like this

public function select($table, $fields = "*", $where = "")...

thinking it would be extremely handy to run queries like SELECT * FROM table using a "neat" call:

$data = $db->select("table");  

In reality, a query without WHERE or ORDER BY or at least LIMIT clause will be almost never used. And you'll end up with using this function as an unreadable gibberish like this

$data = $db->select("foo", "bar", ["baz" => 1]);

which no one will be able to comprehend or use for any query more complex than a silly lookup. In the end such a function turns up to be an ugly substitution for a fully featured SQL query, with obfuscated syntax and limited functionality.

You have to understand that keywords in SQL serve for the great purpose of readability, making whole statement readable and unambiguous, comprehensible by the every programmer in the world. And having that in mind now you can tell that your initial idea to save yourself typing of SELECT or WHERE clauses turns to be not that brilliant.

Therefore, either look for a mature query builder (that keeps all the keywords in place, but in the form of builder's methods names), or just keep with raw SQL.

SQL injection in Insert and Update methods

Almost everyone makes their Insert() and Update() helper methods essentially vulnerable to SQL injection, as it's explained in the "An SQL injection against which prepared statements won't help" article here.

Statefulness

That's a problem which is hard to spot at first but that will make you a lot of headaches in the future.

Your wrapper has to be stateless. Means no runtime class variables have to be ever used, like $this->query, $this->result, $this->statement and such. Mind your class can be used with different queries at the same time. Imagine there is a $result variable, and you are going to run a nested query. A nested query call will override all your stateful variables, wrecking havoc.

Let's take, for example, such a primitive class, just to demonstrate the problem.

class StatefulDB {
    protected $conn;
    protected $stmt;
    public function __construct($pdo) {
        return $this->pdo = $pdo;
    }
    public function query($sql) {
        $this->stmt = $this->pdo->query($sql);
    }
    public function fetch() {
        return $this->stmt->fetch();
    }
}

Now, if we try to run a nested query like this

$db = new StatefulDB($pdo);
$db->query("SELECT * FROM users");
while ($row = $db->fetch()) {
    $db->query("UPDATE users SET foo='bar'");
}

We will get an error, because during the first iteration $this->stmt would be overwritten by UPDATE query. The same goes for all other stateful variables - $sql, $error, whatever. So you can see that having such variables related to a particular query is no the way to go.

If you want to introduce a state, to keep some stuff linked to a particular query, you have to use another class for this. Just look at PDO and mysqli: both use a main object to hold a connection, while particular query is linked to a distinct object of a statement or a query result class.

Protected PDO instance

You have to realize that if you decided to make a PDO instance protected, then you'll have to duplicate more than a dozen PDO methods in your class, or you'll just be unable to access them. While for a simple wrapper it's better just to make PDO instance public, so you'll be able to call a rarely used method through it:

 $yourDb->pdo->beginTransaction();

another two possibilities wold be:

1. Just extend PDO, adding methods you like, so all PDO methods will be kept in your wrapper.
2. Create a proxy for the PDO's methods using __call or __callStatic magic function.

Both examples can be found in the adjacent article, Simple yet efficient PDO wrapper

Code repetition

The code in your wrapper mot likely looks WET.

Just look at all of your methods. They share like 90% of code. Every single one runs the query and gets the result with an addition of some checks and verifications. Why not to have just a single method that does execute your query, and then use this routine in all other methods? This would make your code DRY and meaningful.

Database credentials as class variables.

This is not a critical fault but rather a matter of sanity.

There is absolutely no point in making database credentials (like user, host, password etc.) as class variables. You aren't going to use them in the object, you need them only in constructor. So, make them constructor parameters instead.

Inheritance

Again, not a practical issue but rather a matter of sanity. Do not extend your application classes from DB class.

First of all, it's a matter of sanity. You should never extend an applications class (i.e. User class) from a database class. User is not a database. It's a user. They have nothing in common. Database have to be used as a service by a user, not being a parent.

If you have a CRUD class, you may extend a User from it. But note that CRUD is essentially different from DB wrapper class. Either way, remember that connection in any case have to be only one.

A minimalistic PDO wrapper example

Given PDO is already a very powerful database wrapper, all we really need is a simple class that has only one method to execute prepared queries in one go, let's call it run() as not to be confused with vanilla PDO's query(). Note that for sake of usability this class can be used with mysql database only.

class DB
{
    public $pdo;

    public function __construct($db, $username = NULL, $password = NULL, $host = '127.0.0.1', $port = 3306, $options = [])
    {
        $default_options = [
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ];
        $options = array_replace($default_options, $options);
        $dsn = "mysql:host=$host;dbname=$db;port=$port;charset=utf8mb4";

        try {
            $this->pdo = new \PDO($dsn, $username, $password, $options);
        } catch (\PDOException $e) {
            throw new \PDOException($e->getMessage(), (int)$e->getCode());
        }
    }
    public function run($sql, $args = NULL)
    {
        if (!$args)
        {
            return $this->pdo->query($sql);
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

Notice that PDO variable is made public. That's because we will need to access the full PDO functionality through this variable.

To use this class first create an instance, like this

$db = new DB($dbname, $user, $password);

and then use either the run() function or any PDO's internal methods through corresponding property:

$db->run("INSERT INTO users VALUES (null, ?,?,?)", [$name, $email, $password]);
$id = $db->pdo->lastInsertId();

In case your code is OOP, then include this class' instance a constructor parameter in all other classes that require a database interaction:

class User
{
    /* @var MyPDO */
    protected $db;

    protected $data;

    public function __construct(MyPDO $db)
    {
        $this->db = $db;
    }

    public function find($id)
    {        
        $this->data = $this->db->run("SELECT * FROM users WHERE id = ?", [$id])->fetch();

    }
}

And then use it this way:

$mypdo = new MyPDO('mysql:host=localhost;dbname=test;charset=utf8');
$user = new User($mypdo);
$user->find($id);