How to include a PHP variable inside a MySQL statement

sql

sql-injection

prepared-statements

pdo

byAnton Shumikhin·Jan 21, 2025

Employ prepared statements with placeholders to safely insert a PHP variable in a MySQL statement. Here's how it's done with PDO:

$pdo = new PDO($dsn, $user, $password);
$stmt = $pdo->prepare("SELECT * FROM table WHERE column = :value");
$stmt->execute([':value' => $phpVariable]);

And for mysqli:

$mysqli = new mysqli($host, $user, $password, $database);
$stmt = $mysqli->prepare("SELECT * FROM table WHERE column = ?");
$stmt->bind_param('s', $phpVariable);
$stmt->execute();

Cautiously sidestep direct concatenation to block out SQL injection and always channel parameter binding.

Architecting safe PHP and SQL fusion

A PHP variable embedded in a SQL statement invites SQL injection risks. Prepared statements curtail this risk by distinguishing SQL logic and input data.

Handling MySQLi's prepared statements

MySQLi provides an orderly framework to prepared statements. It allows a single call execution using bind_param. Executing an INSERT statement can be handled like this:

$mysqli = new mysqli($host, $user, $password, $dbname);
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$stmt->execute(); // Let's get this party started!

Harnassing PDO's named placeholders

PDO takes pride in its support of named placeholders, promoting legibility of your codebase. While at it, remember to:

Sanitize and validate user inputs prior to their journey into your SQL statements.
Arrange formatting for table or column names outside the realm of prepared statements.

For instance, with dynamically defined column names, let's engage a disciplined whitelist to validate and confirm their safety for interpolation:

$columns = ['name', 'email', 'nickname']; // whitelist on the duty
$columnToSelect = $_POST['column'];
if (!in_array($columnToSelect, $columns)) {
    die('This column is not on the guest list.'); // Sorry, not sorry.
}

$statement = $pdo->prepare("SELECT :column FROM users");
$statement->bindParam(':column', $columnToSelect);

Considering data types in binding

While binding parameters, be meticulous to specify the fitting data type to the bind_param call (i denotes integers, d indicates doubles, s signifies strings, and b implies blobs):

$age = 30;
$ageParamType = 'i'; // 'i' as in integer, not eye
$stmt->bind_param($ageParamType, $age);

Dodge these common missteps

Practical coding is a minefield of potential missteps. Let's walk you through some:

Inserting directly can invite chaos

Placing a variable in your SQL statement like:

$query = "SELECT * FROM users WHERE id = $userID"; // Are you sure about that?

Is an open invite to security risks. If $userID can be influenced by the user, it's an open door to SQL injection.

Cozying up `$GLOBALS` with SQL statements is a bad idea

Utilizing $GLOBALS or similar superglobals directly in your SQL statements is a strict no-no. It exposes your code to potential security threats.

Data types deserve attention

Ensure the data type in your bind_param aligns with the SQL data type anticipated. An oversight here can trigger weird and buggy behavior in your data handling.

Gratuitous use of quotes

Understand where quotes are needed. While using prepared statements, the extension does it for you. Manually quoting variables, particularly within prepared statements, can lead to confusion and bugs.

From theory to practice

Using PHP variables in `WHERE` clauses

The magic of prepared statements really shines when you need to use PHP variables to filter results. Abracadabra:

$stmt = $pdo->prepare("SELECT * FROM products WHERE category = :category");
$stmt->execute([':category' => $category]); // Filter magic in action

Dynamic sorting with a whitelist

For on-the-go ORDER BY clauses, once again, resort to the tried-and-runner, whitelist practice. Resist the temptation to inject the sort column and direction directly into the statement:

$sortWhitelist = [
  'name_asc' => 'name ASC',
  'name_desc' => 'name DESC', // The VIP sorting options
  // pump in more allowed options
];

$sortOption = $sortWhitelist[$_GET['sort']] ?? 'name ASC'; // Sorting made easy!

$stmt = $pdo->prepare("SELECT * FROM users ORDER BY $sortOption");
$stmt->execute();