Binding parameters in PHP

Introduction

Binding parameters helps safely add user data into database queries. It stops bad data from causing errors or security problems.

When inserting user input into a database query.

When updating database records with values from a form.

When selecting data based on user choices.

When you want to avoid SQL injection attacks.

When you want your database code to be cleaner and easier to read.

Syntax

PHP

$stmt = $pdo->prepare('SQL query with placeholders');
$stmt->bindParam(':name', $variable, PDO::PARAM_TYPE);
$stmt->execute();

Placeholders in the SQL query start with a colon (:), like :name.

bindParam links a variable to a placeholder, so the variable's value is used when executing.

Examples

This example binds an integer user ID to the :id placeholder.

PHP

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
$stmt->execute();

Here, a string name is bound to the :name placeholder for inserting a new user.

PHP

$name = 'Alice';
$stmt = $pdo->prepare('INSERT INTO users (name) VALUES (:name)');
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();

Sample Program

This program creates a table, inserts two users using bound parameters, then prints all users.

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Create a simple table
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');

// Prepare insert statement with placeholder
$stmt = $pdo->prepare('INSERT INTO users (name) VALUES (:name)');

// Bind parameter and execute
$name = 'Bob';
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();

// Change name and insert again
$name = 'Carol';
$stmt->execute();

// Select and show all users
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo "User {$row['id']}: {$row['name']}\n";
}
?>

Important Notes

bindParam binds the variable by reference, so if the variable changes before execute(), the new value is used.

Use PDO::PARAM_INT for integers and PDO::PARAM_STR for strings to help the database understand the data type.

Always use binding to keep your database safe from injection attacks.

Summary

Binding parameters safely adds user data to database queries.

Use placeholders in SQL and bindParam to link variables.

This prevents security problems and makes code cleaner.