Subtle diagonal pinstripe pattern in light grey and cream, giving an impression of printed editorial paper

Building safe SQL queries in WordPress with $wpdb->prepare

WordPress powers a huge slice of the web, and a fair chunk of Australian business relies on it. From a small WooCommerce store selling handmade goods out of a Brisbane studio to a busy media outlet in Sydney running scheduled posts around AEST, the same engine sits underneath. Whenever a developer drops raw user input into a query string, though, that engine can be turned against the site. Attackers scan for unescaped fragments in forms, URL parameters, AJAX endpoints and REST payloads, and a single careless $wpdb->query() call is often all they need.

Australia's regulatory environment makes the stakes clearer. The Notifiable Data Breaches scheme, administered by the Office of the Australian Information Commissioner, obliges organisations to disclose incidents that are likely to result in serious harm. The Australian Cyber Security Centre publishes the Essential Eight maturity guidance, which recommends application-level mitigations, including parameterised database access, as a baseline. Writing secure SQL is not only a craft concern; for many local businesses it is a compliance concern too.

The $wpdb->prepare() method exists precisely to close that gap. It takes a SQL template plus a list of values, then assembles the final query using placeholders that the database engine treats as data rather than executable code. Used correctly, it removes an entire class of bugs that has plagued web applications since the early days of PHP. Used loosely, it gives a false sense of safety and leaves the door ajar.

The following sections walk through how placeholders behave, the slips that catch even experienced developers, and the habits that keep a WordPress codebase resilient during a long life of feature changes, plugin updates and team handovers.

How placeholders work behind the curtain

The first thing to understand is that $wpdb->prepare() is a thin wrapper around the underlying mysqli or mysqlnd driver. It performs two jobs: it substitutes the placeholders you supply with properly quoted values, and it forwards the rendered string to the database. The placeholders are not magic; they are instructions about how to quote and escape the matching value.

The most common form is %s for strings, %d for integers and %f for floating point numbers. There is also a placeholder called %i for identifiers such as column or table names, which is available in newer versions of WordPress. A typical safe call looks like $wpdb->prepare( "SELECT * FROM {$wpdb->posts} WHERE ID = %d", $id ). Because the integer is quoted and escaped by the driver, an attacker passing ?id=1 OR 1=1 cannot break out of the WHERE clause.

A subtle point that trips up developers who learnt the API years ago is that $wpdb->prepare() now uses a different placeholder parser than older WordPress releases. Direct concatenation of variables into the format string is no longer supported in the same way, and passing an array of values requires the second positional argument to be the array itself. The WordPress developer reference documents the current behaviour, and the codebase carries unit tests that lock the contract down.

Identifiers are a special case. Table and column names cannot be bound as values, so they must be whitelisted. A common pattern in Australian agency codebases is to keep a list of allowed meta keys in a constant and check user input against it before composing the query. This keeps the dynamic part of the statement within a controlled vocabulary and prevents attackers from sliding unexpected column names into ORDER BY or GROUP BY clauses.

Common slips when writing queries

The most frequent mistake is the "almost prepared" query. A developer types $wpdb->query( "UPDATE ... WHERE id = $id" ) because the value came from $_POST and felt trusted. Even when the value passes through intval() or sanitize_text_field(), the safer habit is to still feed it through $wpdb->prepare() so the team reviewing the diff can see the boundary clearly. Comments help, but a single chokepoint in the call is the best defence.

Another slip is using %s where %d is appropriate. Suppose a product lookup key in a Melbourne-based e-commerce plugin is stored as a string, but the user passes an integer. Quoting it with %s will work, yet it will also slow the index because the optimiser may treat the column as a string literal. Reaching for the right placeholder is both a security and a performance concern, and the ACSC guidance calls out input handling as one of the highest-return application improvements available.

A third pattern is the mixed concatenation that defeats the purpose of the call. Code such as $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $table WHERE id = %d", $id ) ) still interpolates $table directly, and if $table is taken from a request the query is wide open. The same review pattern is useful for searching across files: tools like Emacs Occur Mode help surface stray interpolation during code review sessions, which is why many Australian engineering teams keep a runbook of regular pattern audits.

Finally, the LIKE clause deserves a mention. Wrapping the user value with %s and then using it inside LIKE without escaping the wildcard characters allows an attacker to slow the table with broad matches. The fix is to escape the literal percent and underscore characters before passing the value to $wpdb->prepare(). The WordPress core itself uses esc_like() for exactly this reason, and plugins that copy its pattern tend to age well.

Handling different data types correctly

WordPress stores a surprising variety of PHP values in its tables. Post meta can hold anything from a serialised array to a JSON string, and a custom plugin may compare against booleans, NULLs or decimals. The prepare() call needs to mirror the schema, not the source language.

For booleans, the cleanest approach is to convert early. A function that expects a flag should map truthy inputs to 1 and falsy inputs to 0, then bind with %d. Passing true or false directly to %s will store an empty string in some MySQL configurations, which is rarely the intent. A Brisbane-based agency I worked with spent an afternoon chasing a "feature flag always off" bug that turned out to be exactly that mismatch.

NULL handling is another quiet source of bugs. $wpdb->prepare() does not bind NULL directly, so the common workaround is to switch the SQL skeleton with a ternary: WHERE meta_value IS NOT NULL AND meta_value = %s versus WHERE meta_value = %s, with the second branch passing an empty string. The pattern is verbose but predictable, and a small helper function makes it readable across the codebase.

Floating point numbers deserve care. %f accepts a number, but MySQL's DECIMAL columns may need explicit casting. A safer pattern is to use round() on the PHP side, then bind with %f. Currency amounts in AUD are particularly sensitive: a few extra decimal places due to a missing round can throw off GST calculations, and Australian WooCommerce sites that store prices in DECIMAL(10,2) columns benefit from a single helper that rounds and binds in one step.

Date and time values also need attention. A cron job scheduled in AEST might be stored as a UTC timestamp, and a query that compares against a human-readable string can drift across daylight saving changes. Binding with %d after a strtotime() conversion keeps the comparison in the same units, and a comment near the query line that names the timezone saves the next developer an hour.

Building complex queries without losing the plot

Real plugins rarely live on a single SELECT. A reporting screen for a Sydney publisher may join three tables, aggregate by post status and filter by date range, all in one statement. The temptation is to mix concatenation with placeholders and hope the result is safe. There is a better way.

The first habit is to keep dynamic table and column names inside an explicit allow-list. A small array of known aliases mapped to user-facing slugs removes the risk of SQL injection through the schema layer. The second habit is to compose the query as an array of fragments and join them with newlines; a single prepare() call at the end of the function then handles the values, while the fragments remain a readable map of the statement.

When subqueries are involved, each one should be prepared independently. A common antipattern is to embed a freshly built subquery as a string argument to the outer prepare() call. Even when the inner subquery is itself safe, mixing string fragments and value placeholders in the same template is hard to read and harder to audit. A local rule of thumb in many teams is: one prepare() per statement, and one statement per function.

ORDER BY deserves special treatment. Binding a column name with %s is wrong because the value will be quoted and the query will fail or behave unexpectedly. The %i placeholder, where available, is the right tool. In older WordPress versions, the only safe path is a whitelist of allowed column names plus the direction, then a direct substitution of the chosen key. The whitelist is the safety net that keeps even a determined attacker from injecting DESC; DROP TABLE wp_users; --.

Auditing existing code and keeping it honest

A secure pattern is only as good as the team's discipline. Australian agencies that have shipped dozens of WordPress sites over the years tend to keep a recurring audit task on the calendar, often tied to a quarterly security review. The audit looks for direct $wpdb->query() calls, unescaped $_REQUEST reads and any string concatenation that touches a SQL keyword.

A useful tool during these reviews is a static analyser. PHP_CodeSniffer with a WordPress ruleset, or PHPStan at a higher level, will flag calls that do not pass through prepare(). For teams that prefer a manual pass, the linked walkthrough on pattern matching shows how a developer can walk through every file and surface the same patterns visually. Pair it with a checklist of the slip categories described above and the review is usually done in an afternoon.

The final piece of the puzzle is the test suite. A test that feeds hostile input into a form and asserts that no row is dropped, no login is bypassed and no error leaks the table prefix is worth more than a thousand comments. PHPUnit, Brain Monkey and the WordPress test framework together cover most of the cases, and a CI pipeline that runs them on every pull request means a regression is caught before it reaches a staging server in Melbourne or a production cluster in Sydney.

Habits that keep a WordPress codebase safe