Edit: Added a more complete guide to proper anti-injection measures, thanks to Tari R. Alfaro's comment.

We often need to make SQL requests to work with dynamically-provided content.

For that, there is the "prepare" mechanism.

From the PHP documentation, it allows one to "prepare" SQL requests.

This is not only provided by PDO, virtually every SQL tool have prepared statements, as "prepare" is a standard RDBMS mechanism.

If you want a more in-depth explanation of "What are prepared statements", make sure to check out this article.

Bad idea: directly insert dynamic data in a SQL request

As seen in the htmlspecialchars example, there's lots of occurences on which we see dynamically-inserted data (like the example below).

<?php
// Example
$user = $_POST['username'];
$db->query("SELECT * FROM users WHERE username = $user");

This creates a few issues.

• RDBMS won't be able to properly optimize the request
• They also won't be able to pre-validate the content type of the field
• This allows for very easy SQL injections.

Good idea: Using the prepare mechanism to securely and efficiently pass dynamic data

I won't go into detail on how preparing statements is a benefit, see the article linked above for that.

Preparing statements is a very easy thing to do.

• Create a request with placeholders instead of your values. Documentation for your SQL library will give you the placeholders to use.
• Execute that request, passing data that should be used instead of those placeholders. Another approach is to manually bind each value before executing.

In PHP, both approaches are very simple.

<?php
// Example with execute-time data passing and unnamed placeholder
$req = $db->prepare("SELECT * FROM users WHERE username = ?");
$req->execute([$username]);
// Example with execute-time data passing and named placeholder
$req = $db->prepare("SELECT * FROM users WHERE username = :username");
$req->execute([
    'username' => $username
]);
// Example with manual binding and named placeholder before execute
$req = $db->prepare("SELECT * FROM users WHERE username = :username");
$req->bindParam('username', $username);
$req->execute();

Additional steps to go through

The part above this one will give you a good base to work on, but to really make things as foolproof as possible, we need to have a few other tweaks and bits.

Preparation emulation

First of all, and because the preparation mechanism is a real database construct, we need to disable what is called "emulation" (which consists in the PDO library simulating the preparation mechanism, for DBMSs that don't have a decent preparation mechanism).

To do that, we need to set a PDO configuration, PDO::EMULATE_PREPARES, to false.

$db->setAttribute(PDO::EMULATE_PREPARES, false);

Data validation

A golden rule of data handling is "never trust the user".

To properly handle form submission, you need another step before trying to even imagine inserting data into your database: validation.

You won't "format" data, you won't change anything, but, for every bit of info that you received, you'll take it, and compare it against a set of rules, to make sure everything is as expected.

Sounds complicated? It isn't.

For a native PHP only solution, you have the filter_var method to work with.

As the documentation shows, you have a lot of different filters and rules you can use to make sure that you are receiving data you expected.

Too bothersome? There are a lot of libraries that can greatly simplify that for you, like this library (siriusphp/validation) .

Final note for MySQL

Remember, folks, that if you want to store UTF-8-encoded data in your MySQL DBMS, you need to use the type utf8mb4, which is the real UTF-8 type, instead of using utf8, which is not the real UTF-8.

The utf8 format is only encoded on 3 bytes, instead of 4, which excludes a lot of characters.