MySQL PDO Statements

Hello Matie ...

In the software development world, safety is very important. Especially when dealing with sensitive user information.

As such, it's good practice to cement your security implementation from the get go.

In this tutorial we'll be looking at protecting the database from malicious user attacks by using the PDO_MYSQL Driver. PDO is an acronym for PHP Data Objects, and can be conceptualised as a layer for your mysql queries to protect your data. PDO Statements ensure that any incoming data is seen, but is not executed as an SQL statement. This helps to protect against malicious SQL attacks such as injections etc.

PDO Statements are termed prepared statements in that they only take parameters you give. So you write the PDO Statement before, and then as it executes it doesn't treat anything else as an executable. Thus protecting the integrity of your data.

Okay we'll start by initiating a DB connection with your MySQL database as illustrated below:

<?php
    // change the `some_database` to whatever you'ved named yours
    $connectionData = "mysql:host=localhost;dbname=some_database;charset=utf8mb4";

    // We set these to show errors and ensure to get the data as an array
    $pdoOptions = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // associative array
    ];

    try {
        $conn = new PDO($connectionData, "database_username", "database_password", $pdoOptions);
        echo "Connection Succesful!";
    } catch (PDOException $e) {
        echo "This connection did not do the things : " . $e->getMessage();
    }
?>

Please note that the connection details are just the auth details to your MySQL databse. Please ensure that you get the connected message. If you can't get the Connection Succesful message, please feel free to scream at me in the comments below. This query will have the trademarked PDO empty parameters as placeholders. These will be either a question mark or a random variable name with a colon before them.

We'll start with with a simple select on a random table on your local database oke (South African slang for dude).

This is how we'll do it:

<?php
    try {
        $conn = new PDO($connectionData, "database_username", "database_password", $pdoOptions);

        // the simple select query
        $statement = $conn->prepare("SELECT * FROM users WHERE id=?");
        $statement->execute([$id]); 
        $theUserRecord = $statement->fetch();
    } catch (PDOException $e) {
        echo "This connection did not do the things : " . $e->getMessage();
    }
?>

We decided to go with unnamed parameters in this example. Here's the same query with named parameters:

<?php
    try {
        $conn = new PDO($connectionData, "database_username", "database_password", $pdoOptions);

        // the simple select query
        $statement = $conn->prepare("SELECT * FROM users WHERE id=:id");
        $statement->execute(['id' => $id]); 
        $theUserRecord = $statement->fetch();
    } catch (PDOException $e) {
        echo "This connection did not do the things : " . $e->getMessage();
    }
?>

There are advantages of using named over unnamed parameters, and vice versa, depending on your use case. Please take some time to research these as homework :)

In the rest of this tutorial, we'll be complementing our work with an INSERT, an UPDATE, and a DELETE statement.

Here comes the insert :

<?php
    try {
        $conn = new PDO($connectionData, "database_username", "database_password", $pdoOptions);

        $statement = $conn->prepare("INSERT INTO users (name, age) VALUES (?, ?)");
        $statement->execute(['thomas', 29]);
        $statement = null;
    } catch (PDOException $e) {
        echo "This connection did not do the things : " . $e->getMessage();
    }
?>

Pretty cool isn't it? We've just inserted a dude named Thomas, aged 29 into the database.

And now let's update an existing record:

<?php
    $statement = $conn->prepare("UPDATE users SET name = ? WHERE id = ?");
    $statement->execute(['Samurai', 1]);
?>

This will update a record with the user id 1, and change its name to Samurai. I assume this was because he realised his last name was Jack.

Hopefully you're getting used to how we parse the parameters there pretty brain. A question mark and a value in the execute function, does the trick.

Now for the grand finale. Ladies and gentle men, primates of old ages. I give you; the DELETE query :

<?php
    $statement = $conn->prepare("DELETE FROM users WHERE id = ?");
    $statement->execute([2]);
?>

"PDO Statements are termed prepared statements in that they only take parameters you give. Prepared parameters, prepared statements."

The Village Geek

"Alright, alright, alright ..." - Matthew McConaughey

Well there you have it. DELETE, UPDATE, SELECT, and INSERT using prepared statements. PDO is actually easy to use, so please play around with the code, and try it on your own projects Matie. The effort is nothing compared to the safety gains.

Go Crazy!!

Talk to you next time ...


Cape Town, South Africa