Your first database wrapper's childhood diseases

  1. Error reporting.
  2. Connection
  3. General purpose query function.
  4. Type-hinted binding.
  5. Select function
  6. SQL injection in Insert and Update methods
  7. Statefulness
  8. Protected PDO instance
  9. Database credentials as class variables.
  10. Inheritance
  11. Comments (3)

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 appears to be highly common, repeated in almost every wrapper. Let's sort them out.

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.

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->setAttributePDO::ATTR_ERRMODEPDO::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.

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_error_handler("myErrorHandler");
function 
myErrorHandler($errno$errstr$errfile$errline)
{
    
error_log("[$errno$errstr in $errfile:$errline");
    
header('HTTP/1.1 500 Internal Server Error'TRUE500);
    
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, connection have to be done only once. Means you should create only single PDO connection that have to be used all the way through script execution.

You have to understand that each PDO instance creates a distinct connection to DB server. Thus, you should never ever opening and closing a new connection in 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.

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. To make only single instance of your wrapper available you have two options:

General purpose query function.

There are many variations, but in the end you need 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 incomparable in power with any of your functions, being more flexible than any number of specialized methods you created in your class:

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,

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 newbie PHP users, who have very 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 never be used at all. And you'll end up with using this function as unreadable gibberish like this

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

which no one will be able to comprehend or use for any query that is more complex than silly lookup, making this function an ugly substitution for a full featured SQL query, with obfuscated syntax and limited functionality.

You have to understand that keywords in SQL serve for the great purpose of readability, makes whole statement readable and unambiguous, comprehensible by the every programmer in the world. And so you can tell that your initial idea to save yourself typing of SELECT or WHERE turs 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 class 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 have 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 PDO instance protected, then you 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 and 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

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 practical issue but rather 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.


Related articles: