Securely accessing the database in PHP with PDO & Prepared Statements

catgirl

Administrator
Staff member
Administrator
May 28, 2020
155
28
22
United States
envyforums.net
When working on an application in PHP a common issue you have to deal with is malicious user performing an attack called SQL Injection. When your application reads or writes data from the database you'll often send user input as part of the query.

For example a user might search for articles about pets on a blog which generates the following query:

SQL:
SELECT * FROM Articles WHERE Title LIKE '%Pets%';
The word pets is replaced with your search query. As a side effect without filtering (for example the mysqli_real_escape_string function) a malicious user could add special characters into search field to confuse the PHP script and the database server into running database queries it wouldn't normally run. This can result in unsafe applications where a malicious user can modify data, steal passwords, and other bad things. Thankfully database queries are not all doom and gloom. In this tutorial I explain how to use PHP's PDO Library with Prepared Statements to securely access the database.

Creating a connection
Before you can send queries to and from the database you need to create a connection. You can do this with the following code. It also handles any possible exception.

PHP:
<?php
$servername = 'localhost';
$username = 'root';
$password = 'password';
try {
  $conn = new PDO("mysql:host=$servername;dbname=yourDatabaseName", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>
Selecting a single entry from the database
The most common task in your application will likely be reading items from the database. You can do this with the following code.
PHP:
<?php

// replace this comment with the connection code

$stmt = $conn->prepare("SELECT * FROM Articles WHERE id=:id LIMIT 1");

$stmt->bindParam(':id', $_GET["id"]);

$stmt->execute();

$result = $stmt->fetchAll()[0];

?>
As you can tell from reading the above code you write your database query and then take every piece of user input and bind the parameters into the query rather than interpolating them into the string directly. As a result the PDO library is able to escape the strings for you and ensure no malicious code is injected.

Adding an entry to the database:
Just like reading data from the database adding an entry is just as simple. You prepare your query, bind the parameters, and run it.

PHP:
<?php

// replace this comment with the connection code

$stmt = $conn->prepare("INSERT INTO Articles (title, post) VALUES (:title, :post)");

$stmt->bindParam(':title', $_POST["title"]);

$stmt->bindParam(':post', $_POST["post"]);

?>
Updating an entry in the database:
Just like reading data from the database updating an entry is just as simple. You prepare your query, bind the parameters, and run it.
PHP:
<?php

// replace this comment with the connection code

$stmt = $conn->prepare("UPDATE Articles SET title=:title, post=:post WHERE id=:articleId LIMIT 1");

$stmt->bindParam(':title', $_POST["title"]);

$stmt->bindParam(':post', $_POST["post"]);

$stmt->bindParam(':articleId', $_POST["articleId"]);

?>
Deleting an entry in the database:
Just like reading data from the database deleting an entry is just as simple. You prepare your query, bind the parameters, and run it. As with an update query you should be careful with modifying a database entry and make good use of where and limit clauses. By default MySQL does not keep an object history. Once an entry is modified the old data is discarded and cannot be restored my ordinary means.

PHP:
<?php

// replace this comment with the connection code

$stmt = $conn->prepare("DELETE FROM Articles WHERE id=:articleId LIMIT 1");

$stmt->bindParam(':title', $_POST["title"]);

$stmt->bindParam(':post', $_POST["post"]);

$stmt->bindParam(':articleId', $_POST["articleId"]);

?>
Read More:
You can read more about PHP's PDO Library at https://www.php.net/manual/en/book.pdo.php

Suggestions & Feedback:
What did you think of this tutorial? Do you know more about PHP security and how to write safer code now? How do you keep your PHP applications safe from malicious users?
 
Last edited: