read

PHP Data Objects (PDO) is a database access layer providing a more secure and uniform interface for accessing and altering data within databases. Where most programmers learn to connect to MySQL databases with the MySQL or MySQLi extensions that can be prone to several security pitfalls, such as SQL injection, PDO provides methods to create prepared statements, which clean dynamic user input thus preventing the same security risks that the older extensions suffer from.

PDO offers an abstraction to many database engines, but requires a driver in order to connect to each of them. As such PDO cannot perform any database interactions by itself, but there are quite a few drivers already available to interface with popular database engines. Regardless of which database engine is being used, PDO abstracts the programmer from the underlying logic and uses the same functions to issue queries and fetch data; it’s just simply a case of switching the driver being used.

Connecting to a Database

I use a try/catch block approach when connecting to a database. This is because PDO throws exceptions when errors occur. I believe this is a better approach then using many if statements to determine the cause of the problem. To connect to a MySQL database a new PDO object needs to be created, passing to it the driver to be used, in this case mysql, followed by the hostname of the machine hosting the database and the credentials used to access it.

Here I have also included an array of settings which includes only the argument to make connections to the MySQL database persistent. This ensures a constant connection to the database that can improve performance when there is a lot of traffic creating frequent connection requests. However this does not come without downfalls (depending upon the situation of course). Two main concerns that can occur whilst using persistent connections are:

• Transactions: Having a persistent connection means that an Apache process considers all MySQL queries as part of that same transaction. This is bad if MySQL never issues a commit or a rollback because if something were to happen during a transaction some data may be lost. This can be solved by creating a register_shutdown_function() that does a database rollback.

• Threads: Also since there will only be a limited number of MySQL connections open at a time, there has to be enough database threads available to serve all these request. The best way around this is to ensure that there are always slightly more database threads than Apache threads to prevent Apache from blocking when trying to connect to MySQL more times then there are threads.

Lastly I have set attributes on the connection to always fire exceptions when an error occurs. This is so error can be handled more gracefully.

  try {
    $dbconn = new PDO("mysql:host=localhost;dbname=dbname", username, password, array(PDO::ATTR_PERSISTENT => true));
    $dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch(PDOException $e) {

    // If the connection was unsuccessful then print
    // out to the user this fact and kill the script.
    file_put_contents("PDOErrors.log", $e->getMessage(), FILE_APPEND);

    // header ( 'HTTP/1.1 500 Internal Server Booboo' );
    header("Content-Type: application/json");
    die(json_encode(array("message" => "Failed to connect to the database. Please try again.", code => 500)));
  }

In the catch block I have printed out the error to a file on the server in which this code is run, so that I can see what has gone wrong, whilst a more user friendly message is sent back to the client in a JSON formatted object so that it can be dealt with appropriately.

Querying the Database

When querying the database, I have used prepared statements to avoid such technical problems as SQL injection. Prepared statements escape the values that have been passed to it before issuing the query. Note in the code below that the MySQL statement has a placeholder within it that starts with a colon and will be replaced with an escaped value before the query is executed.

If placeholders have been used, it is then necessary to replace them. The bindParam method is responsible for this. This method can also be used to dictate what datatype should be expected and throw an error if this condition is not met.

  try {
    $dbstat = $dbconn->prepare("INSERT INTO Table(Value) VALUES(:value)");
    $dbstat->bindParam(":value", $_POST['value'], PDO::PARAM_STR);
    $dbstat->execute();
  } catch(PDOException $e) {

    // If the query was unsuccessful, probably due to email
    // address duplication, then print out to the user this
    // fact and kill the script.
    file_put_contents("PDOErrors.log", $e->getMessage(), FILE_APPEND);

    // header ( 'HTTP/1.1 400 Naughty Request' );
    header("Content-Type: application/json");
    die(json_encode(array("message" => "Email already registered.", code => 400)));
  }

As before, in the catch block I have printed out the error to a file on the server in which this code is run, so that I can see what has gone wrong. A JSON encoded object is also sent back to the client to be dealt with appropriately.