Knowledgebase

Introduction

PHP Data Object (PDO) is a library that allows you to write portable code for accessing different databases. With this data access layer, you can seamlessly switch between databases by tweaking a one-line connection string. You can use the PDO interface with parameterized queries to prevent SQL injection attacks, a web security vulnerability that allows an attacker to interfere with your SQL query string. This approach requires you to define parameters that bind the users' data before sending it to the database server for processing. When working in a data-driven application, using PDO with PHP is inevitable. However, defining parameterized queries requires writing long PHP codes to handle the SQL-bound queries and data values on the technical side.

This forms the basis of this tutorial. In this guide, you'll learn how to save time and write shorter high-quality PDO code for your web applications.

Prerequisites

To follow along with this tutorial, make sure you've the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack. You may use MySQL or the MariaDB server to test this tutorial.

Set Up a Database

SSH to your server and run the command below to connect to your MySQL server.

$ sudo mysql -u root -p

When prompted, enter the root password of your MySQL server and press Enter to proceed. Next, set up a company_db by running the statement below.

mysql> CREATE DATABASE company_db;

Next, create a privileged company_db_user user with full access to the company_db database. Use a strong value for the EXAMPLE_PASSWORD. You'll later use these credentials to connect to your database from PHP.

mysql> CREATE USER 'company_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON company_db.* TO 'company_db_user'@'localhost';
mysql> FLUSH PRIVILEGES;

Use the statement below to create the company_db_user in case you're using the MariaDB server.

MariaDB> GRANT ALL PRIVILEGES on company_db.* TO 'company_db_user'@'localhost' identified by 'EXAMPLE_PASSWORD';

Switch to the company_db database.

mysql> USE company_db;

Create a customers table. This table holds relevant information for your clients, including their primary keys, names, and email addresses.

mysql> CREATE TABLE customers (
       customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       telephone VARCHAR(50),
       email VARCHAR(255)
       ) ENGINE = InnoDB;

Don't enter any data into the customers table for now. You'll later populate the table through a PHP script that implements the PDO library.

Exit from the MySQL command-line interface.

mysql> QUIT;

Create a PHP Resource File

Next, create a /var/www/html/customers.php file in the root directory of your web server. This file will connect to the database you've created and insert data when executed with a JSON payload.

Open the /var/www/html/customers.php using nano.

$ sudo nano /var/www/html/customers.php

Then enter the information below into the file.

<?php

header("Content-Type:application/json");

try {
        $db_name     = 'company_db';
        $db_user     = 'company_db_user';
        $db_password = 'EXAMPLE_PASSWORD';
        $db_host     = 'localhost';

        $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $sql = 'insert into customers
                (
                first_name,
                last_name,
                telephone,
                email
                )
                values
                (
                :first_name,
                :last_name,
                :telephone,
                :email
                )
                ';

        $request = file_get_contents('php://input');
        $params = json_decode($request, true);

        $data = [];
        $data = [
                'first_name' => $params['first_name'],
                'last_name'  => $params['last_name'],
                'telephone'  => $params['telephone'],
                'email'      => $params['email']
                ];

        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);

        $response = [];
        $response['message']    = 'The customer details have been recorded successfully.';
        echo json_encode($response, JSON_PRETTY_PRINT) . "\n";

    } catch (PDOException $e) {
        $error = [];
        $error['message'] = $e->getMessage();
        echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
    }

Save and close the file when done.

The /var/www/html/customers.php explained:

• You're using the code block below to initialize the database variables and the $pdo object.

    $db_name     = 'company_db';
    $db_user     = 'company_db_user';
    $db_password = 'EXAMPLE_PASSWORD';
    $db_host     = 'localhost';
  
    $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

• Next, you're creating an INSERT statement for the customers table with named parameters.

    $sql = 'insert into customers
            (
            first_name,
            last_name,
            telephone,
            email
            )
            values
            (
            :first_name,
            :last_name,
            :telephone,
            :email
            )
            ';

• In the code block below, you're retrieving the user's input data and then assigning it to the $params array. Next, you're defining a $data array to hold the bound data for each column defined in the SQL statement above.

        $request = file_get_contents('php://input');
        $params = json_decode($request, true);
  
        $data = [];
        $data = [
                'first_name' => $params['first_name'],
                'last_name'  => $params['last_name'],
                'telephone'  => $params['telephone'],
                'email'      => $params['email']
                ];

• Finally, you're executing the SQL command($sql) and the bound data($data) and if the execution is successful, you're echoing out a success message. Otherwise, you're throwing an error as you can see below.

        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);
  
        $response = [];
        $response['message']    = 'The customer details have been recorded successfully.';
        echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
  
    } catch (PDOException $e) {
        $error = [];
        $error['message'] = $e->getMessage();
        echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
    }

Test the Application

Execute a JSON payload against the http://localhost/customers.php URL by running the command below.

curl --header "Content-Type: application/json"
--request POST
--data '{"first_name":"JOHN", "last_name":"DOE", "telephone":"111111", "email":"john_doe@example.com" }'
http://localhost/customers.php

You should get a success message shown below, showing that the payload has been inserted successfully into the database.

{
    "message": "The customer details have been recorded successfully."
}

While the above PDO code works pretty well, it has a lot of redundant information, specifically on the parameterized SQL command and user's data retrieval. When working on a project that requires hundreds of such data input files to be created, you might have very long and unmanageable code. In the next step, you'll see how you can shorten the PDO syntax to a cleaner code.

Shorten the PDO Code

Open the /var/www/html/customers.php

$ sudo nano /var/www/html/customers.php

Then, locate the code block below.

        ...
        $sql = 'insert into customers
                (
                first_name,
                last_name,
                telephone,
                email
                )
                values
                (
                :first_name,
                :last_name,
                :telephone,
                :email
                )
                ';

        $request = file_get_contents('php://input');
        $params = json_decode($request, true);

        $data = [];
        $data = [
                'first_name' => $params['first_name'],
                'last_name'  => $params['last_name'],
                'telephone'  => $params['telephone'],
                'email'      => $params['email']
                ];
       ...

Then replace it with.

        ...
        $request = file_get_contents('php://input');
        $params = json_decode($request, true);

        $data = [];
        $data = [
                'first_name' => $params['first_name'],
                'last_name'  => $params['last_name'],
                'telephone'  => $params['telephone'],
                'email'      => $params['email']
                ];

        $columns     = implode(', ', array_keys($data));
        $bind_params = implode(', ', array_map(function($value) { return ':' . $value; }, array_keys($data)));
        $sql         = "insert into customers ($columns) values ($bind_params)";
        ...

As you might have noted above, you've reduced the number of lines needed by PDO to prepare the SQL statement and the bound data by almost a half with the new syntax. Save and close the file. When you're through editing, your new /var/www/html/customers.php file should be similar to the content below.

<?php

header("Content-Type:application/json");

try {
        $db_name     = 'company_db';
        $db_user     = 'company_db_user';
        $db_password = 'EXAMPLE_PASSWORD';
        $db_host     = 'localhost';

        $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $request = file_get_contents('php://input');
        $params = json_decode($request, true);

        $data = [];
        $data = [
                'first_name' => $params['first_name'],
                'last_name'  => $params['last_name'],
                'telephone'  => $params['telephone'],
                'email'      => $params['email']
                ];

        $columns     = implode(', ', array_keys($data));
        $bind_params = implode(', ', array_map(function($value) { return ':' . $value; }, array_keys($data)));
        $sql         = "insert into customers ($columns) values ($bind_params)";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);

        $response = [];
        $response['message']    = 'The customer details have been recorded successfully.';
        echo json_encode($response, JSON_PRETTY_PRINT) . "\n";

    } catch (PDOException $e) {
        $error = [];
        $error['message'] = $e->getMessage();
        echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
    }

The new /var/www/html/customers.php file explained:

• In the new file, you're removing the redundant code by constructing the SQL column names from the $data array, which already has the column names as the array keys.

    $columns     = implode(', ', array_keys($data));

• Next, you're using a PHP custom function to retrieve and add a full colon : to the column names.

    $bind_params = implode(', ', array_map(function($value) { return ':' . $value; }, array_keys($data)));

• Finally, with the column names and the parameterized values, you're constructing the $sql statement below.

    $sql = "insert into customers ($columns) values ($bind_params)";

Test the new /var/www/html/customers.php file by sending a new JSON payload to the http://localhost/customers.php URL.

curl --header "Content-Type: application/json"
--request POST
--data '{"first_name":"MARY", "last_name":"SMITH", "telephone":"222222", "email":"mary_smith@example.com" }'
http://localhost/customers.php

The output below confirms that the new shortened code is working as expected.

{
    "message": "The customer details have been recorded successfully."
}

To confirm that the data is in place, log back to the MySQL server.

$ sudo mysql -u root -p

Key in the root password of your MySQL server and hit Enter to proceed. Next, switch to the company_db.

mysql> USE company_db;

Run a SELECT statement against the customers table.

mysql> SELECT
       customer_id ,
       first_name,
       last_name,
       telephone,
       email
       FROM customers;

As you can see from the output below, you were able to insert data into the customers table using both PDO programming approaches but the last option wins since it has lesser manageable code.

+-------------+------------+-----------+-----------+------------------------+
| customer_id | first_name | last_name | telephone | email                  |
+-------------+------------+-----------+-----------+------------------------+
|           1 | JOHN       | DOE       | 111111    | john_doe@example.com   |
|           2 | MARY       | SMITH     | 222222    | mary_smith@example.com |
+-------------+------------+-----------+-----------+------------------------+
2 rows in set (0.01 sec)

Always use the shortened version to minimize code redundancy when working with PHP Data Objects.

Conclusion

In this guide, you've learned how to save time and write shorter high-quality PDO code for your web application when working with PHP on Ubuntu 20.04.