PDO query() vs prepare()
debt(d5/e3/b3/t7)
Closest to 'specialist tool catches it' (d5). The detection_hints list semgrep and phpstan as tools, and automated detection is marked yes with a specific code pattern ($pdo->query('...' . $var . '...')). These are specialist SAST tools rather than default linters or compiler errors, placing this firmly at d5.
Closest to 'simple parameterised fix' (e3). The quick_fix states: replace query() with prepare()->execute() whenever a query contains any variable. This is a pattern-level swap — each call site needs updating but the change is local and mechanical, not architectural. Multiple call sites may exist but each fix is self-contained, so e3 is appropriate rather than e5.
Closest to 'localised tax' (b3). The issue applies to web and cli contexts broadly, but the burden is per-query: each affected query must be reviewed and potentially rewritten. It doesn't impose a cross-cutting architectural constraint on the codebase — it's a recurring local tax on database interaction code, not a system-shaping decision.
Closest to 'serious trap' (t7). The misconception field states explicitly that developers believe escaping (addslashes, manual quoting) makes query() safe — it does not. This contradicts a widely-held mental model where 'sanitising input' is taught as sufficient. The trap is compounded by second-order SQL injection (noted in common_mistakes), where even 'trusted' data can be malicious. This is worse than a minor gotcha and rises to t7.
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();