How To

WordPress SQL Injection Prevention: $wpdb->prepare() Deep Dive

What $wpdb->prepare() does and does not stop, with tested placeholder, LIKE, IN, ORDER BY and %1$s examples from a real WordPress 7.1.4 install.

Varun DubeyVarun Dubey
·11 min read
Two query cards under the title wpdb prepare deep dive: a concatenated query matching 6 rows and a prepared query matching 1 row

Use $wpdb->prepare() for every query that includes a value you did not hard-code, and know what it does not cover. It escapes and quotes values, so a value like x' OR '1'='1 stays a harmless string. It does not check column names, sort directions, table names or how many placeholders you wrote. Most real injection bugs in WordPress code come from those gaps, not from forgetting prepare() entirely.

We rebuilt this guide on 16 November 2026 by running the examples on a throwaway WordPress 7.1.4 install (PHP 8.5.5, WP-CLI 2.12.0) and recording the exact SQL that prepare() produced. Query results came from SQLite through the WordPress performance team’s database plugin, so row counts are from SQLite. The string building is core PHP code and is identical on MySQL and MariaDB. We say where a result depends on the database.

The short version

  • Pass every value as a placeholder argument: %s for strings, %d for integers, %f for floats, and %i for a table or column name (WordPress 6.2 and later).
  • Do not put quotes around %s. prepare() adds them and removes the ones you typed.
  • Always include a placeholder and the right number of arguments. With none, prepare() leaves your query untouched. With the wrong count it returns an empty string.
  • Never use %s for ORDER BY or direction. It becomes a quoted string. Allow-list the column and direction in PHP.
  • Quote positional placeholders yourself. %1$s is not quoted by default, and ID = %1$s with the value 1 OR 1=1 is an injection.
  • Escape LIKE text with $wpdb->esc_like() first, then add your own wildcards.
  • Make the linter your second pair of eyes. WordPress Coding Standards flags interpolated variables in SQL.

What does $wpdb->prepare() actually do?

It turns a template and a list of values into one safe SQL string. It does not run the query. It splits your template at each placeholder, escapes the matching argument, wraps strings in single quotes, casts numbers, and hands the finished string back for you to pass to get_results(), get_var() or query(). We read the implementation in class-wpdb.php on 16 November 2026 to check each behaviour below, and the official reference page describes the same contract.

Here is the difference, tested. The test posts table had six rows. The attack value is the classic 1 OR 1=1 in an integer field and x' OR '1'='1 in a string field.

Query built by

SQL sent to the database

Rows matched

Concatenation, integer field

... WHERE post_type='post' AND ID = 1 OR 1=1

6, every row (the OR bypassed the post type filter too)

Prepared, %d

... WHERE post_type='post' AND ID = 1

1

Concatenation, string field

... AND post_title = 'x' OR '1'='1'

6, every row

Prepared, %s

... AND post_title = 'x\' OR \'1\'=\'1'

0

In the integer case %d cast '1 OR 1=1' to 1. In the string case the quotes inside the value were backslash-escaped, so the whole payload stayed inside one string literal. A normal value with an apostrophe still works: O'Brien report became 'O\'Brien report' and matched its row.

Note what the first row shows. The injected OR did not only widen the match. Because OR binds looser than AND, it also dropped the post_type condition, so a query meant to return public posts would have returned private ones, pages and revisions. That is how a one-field injection becomes a data leak.

Which placeholders does prepare() support?

Placeholder

Use for

What it does

Added

%s

Strings, dates, anything quoted

Escapes the value and wraps it in single quotes

Long-standing

%d

Integers, IDs

Casts to an integer, so '12abc' becomes 12 and 'abc' becomes 0

Long-standing

%f

Floats

Formats a float; core turns it into the locale-independent form internally

Long-standing

%i

Table and column names

Wraps the name in backticks and doubles any backtick inside it

WordPress 6.2

%%

A literal percent sign

Writes one % into the query

Long-standing

We confirmed %d behaviour on the test site: prepare('SELECT %d', 'abc') gave SELECT 0 and '12abc' gave SELECT 12. And %i handled a hostile name by doubling the backtick, so wp_posts`; DROP TABLE wp_users; -- became one quoted identifier and no second statement ran.

// checked on 16 Nov 2026, WordPress 7.1.4
$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE post_type = %s', $wpdb->posts, 'post' );
// SELECT COUNT(*) FROM `wp_posts` WHERE post_type = 'post'

$wpdb->prepare( 'SELECT * FROM %i', 'wp_posts`; DROP TABLE wp_users; --' );
// SELECT * FROM `wp_posts``; DROP TABLE wp_users; --`

Check for %i support before you rely on it in a plugin that must run on older WordPress: $wpdb->has_cap( 'identifier_placeholders' ) returned true on 7.1.4. The core team described the change in its post about moving it to WordPress 6.2. Use the same identifier as both a value and a name in one query and prepare() refuses with a notice, according to the core source.

How do I write each common query safely?

A single value or several values

$row = $wpdb->get_row(
	$wpdb->prepare(
		"SELECT * FROM {$wpdb->posts} WHERE ID = %d AND post_status = %s",
		$post_id,
		$status
	)
);

Interpolating {$wpdb->posts} is fine. It is a table name that WordPress controls, and the WordPress coding standards allow it. A variable that came from a request is not fine.

Insert, update and delete

Use the helper methods and pass a format array. They build prepared SQL for you.

$wpdb->insert( $wpdb->options, array( 'option_name' => $name, 'option_value' => $value ), array( '%s', '%s' ) );
$wpdb->update( $table, array( 'status' => $status ), array( 'id' => $id ), array( '%s' ), array( '%d' ) );
$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );

We ran insert() with the name twp_test'; -- and then delete() on it. The logged queries were INSERT INTO `wp_options` (`option_name`, `option_value`) VALUES ('twp_test\'; --', 'v') and DELETE FROM `wp_options` WHERE `option_name` = 'twp_test\'; --'. The apostrophe was escaped and the comment marker stayed inside the string.

An IN() list

$ids = array_map( 'absint', (array) $ids );
if ( empty( $ids ) ) {
	return array(); // IN () is invalid SQL, so do not run the query
}
$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
$sql = $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE ID IN ($placeholders)", $ids );

On the test site, the list array('3', '5; DROP TABLE x', 7) came out as IN (3, 5, 7). An empty array produced IN (), which is invalid SQL, so keep the guard. Passing an array as the second argument is allowed: core accepts one array of arguments in place of separate ones. The %d placeholders are generated from a count, not from user text, which is why building that string is safe. The WordPress coding standards sniff may still flag it, and a short // phpcs:ignore with a reason is the honest answer. A tutorial on IN clauses and prepare() walks through the same pattern.

A LIKE search

$like = '%' . $wpdb->esc_like( $term ) . '%';
$sql  = $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title LIKE %s", $like );

Without esc_like() the search text keeps its own wildcards. With two test posts titled 100%_off sale and 1005xoff sale, searching for 100%_off matched 2 rows when unescaped (the % and _ acted as wildcards) and 1 row when escaped. That is not an injection, but it is a wrong result and, on a big table, a slow one. The esc_like reference has the details. Escape first, add your wildcards second, and never the other way round.

ORDER BY and LIMIT

$allowed_cols = array( 'post_date', 'post_title', 'ID' );
$orderby = in_array( $requested_col, $allowed_cols, true ) ? $requested_col : 'post_date';
$order   = 'ASC' === strtoupper( $requested_order ) ? 'ASC' : 'DESC';
$sql = $wpdb->prepare(
	"SELECT ID FROM {$wpdb->posts} ORDER BY $orderby $order LIMIT %d",
	$limit
);

A placeholder cannot stand in for a column name or keyword. prepare("ORDER BY %s", 'post_date') produced ORDER BY 'post_date' on the test site, which sorts by a constant string and silently does nothing. The only safe route is an allow-list in PHP, then interpolating the value you chose, never the value you received.

Where do the values that reach a query come from?

More places than the query string. Every one of these is untrusted until you handle it, and each needs the same placeholder treatment:

  • Request parameters from $_GET, $_POST and $_REQUEST, AJAX handlers and REST route arguments.
  • Shortcode attributes and block attributes, which an author or a contributor controls.
  • Headers such as User-Agent and X-Forwarded-For that logging code stores and a later report reads back.
  • Stored data: options, post meta, user meta and rows another plugin wrote. This is the second-order case.
  • Imported files, webhook payloads and API responses from third parties.

For REST routes, WordPress will validate the shape of arguments before your callback runs if you declare them. That is a second layer on top of prepare(), not a replacement. We built a small route with a typed integer, an enum for the status and an enum for the sort order, then sent attack values:

register_rest_route( 'demo/v1', '/posts', array(
	'methods'             => 'GET',
	'permission_callback' => '__return_true',
	'args'                => array(
		'status' => array( 'type' => 'string',  'enum' => array( 'publish', 'draft' ), 'default' => 'publish' ),
		'min_id' => array( 'type' => 'integer', 'minimum' => 0, 'default' => 0 ),
		'order'  => array( 'type' => 'string',  'enum' => array( 'asc', 'desc' ), 'default' => 'desc' ),
	),
	'callback'            => function ( WP_REST_Request $request ) {
		global $wpdb;
		$order = 'asc' === $request['order'] ? 'ASC' : 'DESC';
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT ID, post_title FROM {$wpdb->posts} WHERE post_status = %s AND ID >= %d ORDER BY ID $order LIMIT %d",
			$request['status'], $request['min_id'], 20
		) );
	},
) );

Request

Result

min_id=1

HTTP 200, rows returned

min_id=1 OR 1=1

HTTP 400, rest_invalid_param, the parameter min_id

status=x' OR '1'='1

HTTP 400, rest_invalid_param, the parameter status

order=desc; DROP TABLE x

HTTP 400, rest_invalid_param, the parameter order

status=draft

HTTP 200, valid value accepted

The attack values never reached the query. The callback still uses prepare() and an allow-listed sort order, so the route stays safe if someone later loosens the argument rules. Pick the type that matches the column, enumerate what is allowed, and keep prepare() as the final control. Remember 'permission_callback' => '__return_true' in this demo makes the route public. Real routes need a real permission check.

Why do I see {hash} text instead of a percent sign?

Because prepare() hides literal percent signs behind a random token until the query runs. Since WordPress 4.8.3 the change to esc_sql() and prepare() swaps each % in a prepared string for a placeholder token, which stops a second prepare() call from reading a user’s percent signs as placeholders. If you print a prepared LIKE query, you will see the token. On the test site it looked like LIKE '{ed4673...}100{ed4673...}_off{ed4673...}'.

The token is swapped back when $wpdb->query() and its relatives run. To read the real SQL while debugging, call $wpdb->remove_placeholder_escape( $sql ). To avoid the problem, never concatenate a prepared string into another string and prepare the result again. Build one template and pass every value to a single prepare().

Which mistakes still leave a WordPress query injectable?

These are the ones we tested. Each one produced the output shown.

Mistake

What we saw

Fix

No placeholder: prepare("... = '$title'")

Core logged “The query argument of wpdb::prepare() must have a placeholder” and returned the query untouched. With x' OR '1'='1 it matched every row

Use %s and pass the value as an argument

Wrong argument count

prepare('... %d ... %s', 5) returned an empty string with a notice that the count was wrong

Match the count. Check the return value is not empty

Too many arguments

Returned the query with the extra argument dropped, plus a notice

Remove the extra value or add its placeholder

Positional placeholder with no quotes

ID = %1$s with 1 OR 1=1 produced ID = 1 OR 1=1; %1$d produced ID = 1

Use %d or quote it: '%1$s'

esc_sql() without quotes

With 1 OR 1=1 the output was unchanged, and the query matched every row

Use prepare() with %d

sanitize_text_field() as protection

Returned x' OR '1'='1 unchanged

It cleans text for display, not for SQL. Use placeholders

Second-order: save safely, concatenate later

A value read back from an option and concatenated into SQL matched every row

Prepare at the point of use, whatever the source

ORDER BY with %s

Became a quoted constant

Allow-list the column

Two of these deserve a closer look. The positional placeholder case surprised us. Core only adds quotes to a bare %s and leaves forms like %1$s alone, because wpdb keeps a legacy setting, allow_unsafe_unquoted_parameters, set to true. We confirmed the default in the source and the unquoted output on the test site. The value is still escaped, but an escaped number-looking string in an unquoted numeric slot is still valid SQL. The fix is to quote it yourself or to use %1$d.

The second-order case is worth remembering when you review code. Data you stored safely last week is still user input. A value that came from get_option(), post meta, a form submitted months ago or another plugin’s table goes through prepare() like any other.

Does sanitizing input make prepare() unnecessary?

No. Sanitizing and escaping do different jobs. Sanitize on input so stored data has the shape you expect, validate against what is allowed, and escape or bind for the context where the value lands. SQL needs its own handling, which is what prepare() gives you. The WordPress data validation handbook page and the OWASP SQL injection prevention cheat sheet both put parameterised queries first and treat escaping as a fallback.

If you write code with an AI assistant, this is the area to check first. Our checklist for reviewing AI-written snippets puts prepared SQL among its ten checks.

How do I audit a plugin or theme for unsafe queries?

Run the coding standards, then grep, then try payloads. The linter catches the common mistakes cheaply. The WordPress Coding Standards ship sniffs for exactly this. We ran them on a small file with one unsafe and one safe function:

function twp_find_by_title( $title ) {
	global $wpdb;
	return $wpdb->get_results( "SELECT * FROM {$wpdb->posts} WHERE post_title = '$title'" );
}
# checked on 16 Nov 2026
phpcs --standard=WordPress --sniffs=WordPress.DB.PreparedSQL,WordPress.DB.PreparedSQLPlaceholders sample.php
# sample.php:4:32: error - Use placeholders and $wpdb->prepare(); found interpolated variable $title ...

It flagged the unsafe function and passed the prepared one. The standards are at the WordPress Coding Standards repository. Next, a quick search finds query calls without prepare on the same line:

grep -rnE '\$wpdb->(query|get_(var|row|col|results))\(' wp-content/plugins/my-plugin --include='*.php' | grep -v prepare

This is a triage list, not a verdict. We ran it on the Akismet plugin that ships with the test install and it listed six calls, all constant statements such as OPTIMIZE TABLE {$wpdb->comments} with no outside value. A hit only matters if a request value can reach it. It also misses queries built across several lines or in a variable, so read the call sites too.

Then test with payloads. Find the request parameters a query uses, send 1 OR 1=1, x' OR '1'='1 and a lone apostrophe, and look at the result and the logged query. Query Monitor shows the exact SQL for each request. Our Query Monitor tutorial covers how to read it.

When you find a vulnerable plugin on a live site, update or remove it first. Our write-up of the Ally plugin SQL injection shows what that process looks like for one real case, including how to check which version you run.

Do database errors leak information?

They can. When a query fails and errors are displayed, WordPress prints the failed query and a backtrace. In our test, one bad query with display on produced an HTML block that showed the SQL text and file paths. In production, keep display off and log instead. $wpdb->show_errors was false in our request, and $wpdb->last_error still held the message for logging. Set WP_DEBUG_DISPLAY to false on live sites. Our guide to security mistakes developers make has the rest of the production checklist.

What limits the damage if a query is injectable?

A database user with only the rights WordPress needs. A common setup is data rights (SELECT, INSERT, UPDATE, DELETE) for day-to-day work, with table-change rights (CREATE, ALTER, INDEX, DROP) granted because installs and some plugin updates need them. Separate databases per site mean one injectable plugin cannot read its neighbour. Check what your plugins need before you remove rights, because some create tables on update. None of this replaces prepared queries, it only caps how bad a mistake gets. Our list of eleven database mistakes covers user privileges and more.

Common mistakes people report

These problems come up repeatedly in WordPress core tickets, support threads and plugin forums. Checked on 16 November 2026.

  • Notice: wpdb::prepare was called incorrectly. The most common message. It means the query has no placeholder or the count does not match the arguments, and many threads show it, for example this wordpress.org report of the no-placeholder case. The check came into core with changeset 41662.
  • Placeholder tokens appearing in LIKE queries. Percent signs turn into hash tokens in prepared strings, and plugins that mutate those strings break. The 4.8.3 developer note explains it.
  • Empty IN() lists. Passing an empty array generates invalid SQL. Core Trac discussed this in ticket 56764, which proposed built-in IN support. Until then, guard it yourself.
  • Table and column names in prepare(). People used %s for names and got quoted strings. The 6.1 developer note on identifiers announced the change for 6.1, and it was postponed to 6.2.
  • Reusing one value with positional placeholders. The %1$s form is the way to reuse an argument, and it is the form that is not auto-quoted, which is how the injection above appears.
  • Trusting a linter that was never run. The sniffs only help if CI fails on them. Add them to your pipeline.

Frequently asked questions

Does $wpdb->prepare() prevent all SQL injection?

No. It protects values that you pass as arguments. It does not protect column names, ORDER BY clauses, table names (before %i), operators, or any text you interpolate into the template. Those need allow-lists.

Should I use esc_sql() instead of prepare()?

No. esc_sql() escapes a string but does not quote it, so in a numeric context it changes nothing. We saw 1 OR 1=1 pass through unchanged. Use prepare() with the right placeholder.

What is the difference between %s and %d?

%s escapes and quotes a value as a string. %d casts it to an integer, so a number-shaped attack string becomes a harmless number.

Can I put quotes around %s?

You can, and core strips them, so it is harmless. We checked that ‘%s’ produced a single pair of quotes. The advice is to leave them out so the code reads the same in every query.

How do I escape a LIKE search?

Call $wpdb->esc_like() on the user’s text, add % wildcards yourself, and pass the result as a %s argument.

Does prepare() work with WP_Query and get_posts()?

Those APIs prepare their own SQL, so you do not call prepare() when you use them with standard arguments. A custom posts_where or posts_clauses filter that adds your own SQL is where you must prepare.

Is %i safe for table names from user input?

It makes the name a safe identifier, so it cannot break out of its backticks. It does not decide whether the user is allowed to read that table, so still allow-list the names.

How do I test my code for SQL injection?

Send 1 OR 1=1, x’ OR ‘1’=’1 and a single apostrophe to every parameter that reaches a query, and read the SQL in Query Monitor or the query log. Run the WordPress Coding Standards DB sniffs in CI as well.

How we tested this

All SQL output above came from running the examples with WP-CLI on a throwaway WordPress 7.1.4 install on 16 November 2026 (PHP 8.5.5, WP-CLI 2.12.0, the WordPress SQLite Database Integration plugin 3.1.3). Row counts come from SQLite, and the semantics shown for injection, LIKE wildcards and placeholder handling are the same on MySQL and MariaDB. Code that we did not run, such as the full examples in the query-pattern sections, follows the same calls we did run. The behaviour of prepare(), the placeholder pattern, allow_unsafe_unquoted_parameters and the %i handling was read from the current class-wpdb.php source. The linter output is from PHP_CodeSniffer with the WordPress standard installed on the same day.

Part of the Wbcom Designs family

The all-in-one WordPress community stack

Also ours: wbcomdesigns.comvapvarun.combrndle.com