PHP PDO: Connect to MySQL and Use Prepared Statements

PDO (PHP Data Objects) is the built-in way to talk to a database. Connect with new PDO($dsn, $user, $password), then run any query that contains a value with prepare() and execute(), so the value is sent separately from the SQL:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();      // an array, or false if no row matched

The examples on this page need a database server, so they are shown as plain code. The SQL injection example further down runs.

Connect to MySQL with PDO

The first argument is a DSN: the driver name, then the connection details. Put the connection in one place and include it where you need it.

<?php
// db.php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows as ['id' => 1, ...]
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements, real int types
];

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database unavailable.');
}

return $pdo;

• charset=utf8mb4 belongs in the DSN. Without it, accents and emoji can arrive as ? or garbled bytes.
• Never print $e->getMessage() to visitors: it can contain the host and user name. Log it.
• Keep the password out of the code, in an environment variable or a config file outside the web root.

Other databases only change the DSN: pgsql:host=localhost;dbname=shop for PostgreSQL, sqlite:/var/data/app.db for an SQLite file. Each needs its PDO driver installed (pdo_mysql, pdo_pgsql, pdo_sqlite); php -m lists the ones you have.

Prepared statements with ? and :name

A placeholder marks where a value goes. Use positional ? placeholders with a list, or named ones with an associative array:

<?php
// Positional
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute(['books', 20]);

// Named: easier to read when there are many values
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price < :max');
$stmt->execute(['cat' => 'books', 'max' => 20]);

// bindValue when you want to set the type explicitly
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY id LIMIT :limit');
$stmt->bindValue('limit', 10, PDO::PARAM_INT);
$stmt->execute();

Do not put quotes around a placeholder: WHERE name = '?' compares with the literal text ?. A prepared statement can also be executed many times with different values, so prepare it once before a loop, not inside it.

Why string concatenation in SQL is dangerous

This is the one block on the page that runs: it builds the query the wrong way and the right way for the same input. Change $email and run it again.

<?php
$email = "nobody@example.com' OR '1'='1";

// Wrong: the input becomes part of the SQL
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
echo "Concatenated:\n$sql\n\n";

// Right: the SQL stays fixed, the value travels separately
$sql = 'SELECT * FROM users WHERE email = ?';
$params = [$email];
echo "Prepared:\n$sql\n";
echo 'Values: ', json_encode($params), "\n";

In the concatenated query, the quote in the input closes the string, and OR '1'='1' makes the condition true for every row: the query returns all users. On a login form that means logging in without knowing the password, and if the driver accepts several statements in one call, input like '; DROP TABLE users; -- runs a second statement of the attacker's choosing. In the prepared version the database receives the SQL and the value separately, so the quote is just a character inside an email that matches nobody.

Escaping functions like addslashes() are not a fix: they miss cases that depend on the connection's character set. Use placeholders for every value, including numbers and values that "come from your own code".

Fetch rows: fetch, fetchAll and fetchColumn

<?php
// One row
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();                 // ['id' => 42, 'name' => 'Ada', ...] or false
if ($user === false) {
    http_response_code(404);
}

// All rows
$rows = $pdo->query('SELECT id, name FROM users ORDER BY name')->fetchAll();

// Loop row by row without building an array of all rows
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo $row['name'], "\n";
}

// One value
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// Two columns as key => value
$names = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);  // [42 => 'Ada', ...]

query() is fine when the SQL contains no values from outside. As soon as a variable is involved, switch to prepare().

Insert, update and delete

<?php
$stmt = $pdo->prepare('INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)');
$stmt->execute([$email, $name, password_hash($password, PASSWORD_DEFAULT)]);
$id = (int) $pdo->lastInsertId();

$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$newName, $id]);
echo $stmt->rowCount();                 // rows changed

$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();

lastInsertId() returns a string, so cast it. With MySQL, rowCount() after an UPDATE counts rows that actually changed, so setting a name to the value it already has gives 0. Store passwords with password_hash, never as plain text.

Transactions

A transaction makes several statements succeed or fail together. If anything throws, roll back and nothing is saved:

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, $to]);
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')->execute([$from, $to, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

This relies on errors throwing exceptions (the default since PHP 8.0), so a failed statement jumps to the catch. With MySQL, the tables must use InnoDB; MyISAM ignores transactions. The try/catch page covers Throwable and rethrowing.

WHERE IN with an array

A single ? holds a single value, so IN (?) with an array does not work. Build one placeholder per value:

<?php
$ids = [3, 8, 15];

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));   // "?,?,?"
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($placeholders)");
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
} else {
    $products = [];                     // IN () would be a syntax error
}

The string interpolated into the SQL is only question marks and commas, so the query is still safe.

Common mistakes with placeholders

• Table and column names. ORDER BY ? sorts by a constant string, not by the column. Pick identifiers from an allowlist: $sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name';, then put $sort in the SQL.
• LIMIT ? with emulated prepares. With the default ATTR_EMULATE_PREPARES on MySQL, execute(['10']) sends LIMIT '10', a syntax error. Bind it with PDO::PARAM_INT or turn emulation off.
• The same named placeholder twice. WHERE first = :q OR last = :q works only with emulated prepares. With real prepares you get SQLSTATE[HY093]: Invalid parameter number; use :q1 and :q2.
• Forgetting that fetch() returns false. $user['name'] on false is a warning and null; check the result first.