PDO query() vs prepare()

Also Known As

TL;DR

Explanation

PDO::query($sql) sends a full SQL string to the database and executes it immediately. Any variables interpolated into that string become part of the SQL syntax itself — the database cannot distinguish data from code, which enables SQL injection. PDO::prepare() separates SQL structure from data at the protocol level: the query is compiled first, then values are bound and transmitted separately. This guarantees that input is always treated as data, never executable SQL. query() is only safe for strictly static queries with zero dynamic input.

How It's Exploited

Common Misconception

Why It Matters

Common Mistakes

• Using query() with string concatenation for 'simple' lookups — still exploitable.
• Assuming internal or trusted input cannot be malicious — second-order SQL injection is common.
• Using query() for convenience instead of switching to prepared statements.

Avoid When

• Never use query() with user input, even if validated or escaped.
• Avoid query() in any context where data may originate from a database, API, or user (second-order injection risk).

When To Use

• Use query() only for completely static SQL with no variables (e.g. SELECT VERSION(), SHOW TABLES).
• Use prepare() for every query that includes external input, variables, or dynamic conditions.

Code Examples

// SQL injection — direct interpolation
$email = $_GET['email'];
$user  = $pdo->query("SELECT * FROM users WHERE email = '$email'")->fetch();

// also unsafe — concatenation
$id = $_GET['id'];
$user = $pdo->query('SELECT * FROM users WHERE id = ' . $id)->fetch();

// query() — safe only for static SQL
$version = $pdo->query('SELECT VERSION()')->fetchColumn();

// prepare() — always for dynamic input
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute([':email' => $email]);
$user = $stmt->fetch();

References

• ↗ https://www.php.net/manual/en/pdo.query.php

Tags

Related Terms