Validation, Sanitization, and Safe Queries: The Three Layers That Actually Stop SQL Injection

Most developers can define SQL injection. Far fewer can say, precisely, which line of their code is the one preventing it.

That gap matters. SQL injection has been on the OWASP Top 10 since the list existed, and it survives not because the fix is hard but because the fix is easy to believe you’ve applied. “I sanitized the input” feels like a complete answer. It isn’t.

This post breaks the defense into the three layers it actually consists of – validation, sanitization, and safe querying – with WordPress examples for each, the use cases where each one is the deciding factor, and the hard cases (dynamic ORDER BY, IN () lists, LIKE searches) where the naive approach quietly fails.

First, the root cause

SQL injection has exactly one cause: a query where attacker-controlled text becomes part of the SQL syntax instead of staying a value.

Here’s a plugin doing it wrong:

// DANGEROUS - never ship this
global $wpdb;
$id = $_GET['booking_id'];
$booking = $wpdb->get_row( "SELECT * FROM {$wpdb->prefix}bookings WHERE id = $id" );

A normal visitor sends ?booking_id=42 and the database receives:

SELECT * FROM wp_bookings WHERE id = 42

An attacker sends ?booking_id=42 OR 1=1 and the database receives:

SELECT * FROM wp_bookings WHERE id = 42 OR 1=1

Every booking in the table, returned to someone entitled to see one. Escalate slightly – ?booking_id=0 UNION SELECT user_login, user_pass, 1, 1 FROM wp_users – and the endpoint starts printing password hashes.

Notice what happened. The database did nothing wrong. It received a valid query and executed it faithfully. The vulnerability was created earlier, in PHP, at the moment a string from the user was concatenated into SQL. That’s the only place SQL injection is ever created, and it’s therefore the only place it can be truly prevented.

Keep that in mind as we go, because it explains why the three layers are ordered the way they are.

Layer 1: Validation – decide what “good” looks like and reject everything else

Validation asks a yes/no question: is this input what I expected? If the answer is no, you stop. You don’t clean it up, you don’t try to rescue it – you reject the request.

This is the strongest of the three layers, because it’s an allowlist. You’re defining the small set of values that are acceptable, rather than trying to imagine every dangerous thing an attacker might send. Blocklists lose to creativity; allowlists don’t.

Use case: numeric IDs

Ninety percent of the SQL injection risk in a typical plugin is IDs coming in from $_GET, $_POST, or a REST route. They’re always integers, so say so:

$booking_id = isset( $_GET['booking_id'] ) ? absint( $_GET['booking_id'] ) : 0;

if ( ! $booking_id ) {
	wp_die( esc_html__( 'Invalid booking.', 'my-plugin' ), 400 );
}

absint() casts to an integer and drops the sign. 42 OR 1=1 becomes 42. 0 UNION SELECT... becomes 0. There is no string left to inject with, because the value isn’t a string anymore – it’s an int.

This is worth pausing on: for integer inputs, validation alone genuinely closes the hole. An int cannot carry SQL syntax. That’s why absint() and (int) show up so often in security fixes.

Use case: a value from a fixed set

Statuses, post types, order directions, tab names, filter modes – anything with a known list of legal values:

$status  = isset( $_GET['status'] ) ? sanitize_key( wp_unslash( $_GET['status'] ) ) : 'pending';
$allowed = array( 'pending', 'confirmed', 'cancelled', 'refunded' );

if ( ! in_array( $status, $allowed, true ) ) {
	$status = 'pending'; // fall back, or reject - just never trust the raw value
}

The variable $status is now guaranteed to be one of four strings you wrote. Not “a string that has been cleaned.” One of four literals in your source code. That’s an enormously stronger guarantee, and it’s available any time the input has a finite domain.

Use case: structured formats

$email = isset( $_POST['email'] ) ? wp_unslash( $_POST['email'] ) : '';
if ( ! is_email( $email ) ) {
	wp_send_json_error( array( 'message' => 'Please enter a valid email address.' ), 400 );
}

$date = isset( $_POST['date'] ) ? wp_unslash( $_POST['date'] ) : '';
if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
	wp_send_json_error( array( 'message' => 'Date must be YYYY-MM-DD.' ), 400 );
}

Note that is_email() validates – it returns the email or false – while sanitize_email() transforms, stripping illegal characters and handing you whatever’s left. Those are different operations with different failure modes, and mixing them up is a common source of confusion. More on that in the next section.

Where validation is the only option

There’s one category where validation isn’t merely the strongest layer, it’s the only layer that works: SQL identifiers – table names, column names, and sort direction.

// The user picks a sort column from a dropdown
$orderby  = isset( $_GET['orderby'] ) ? sanitize_key( wp_unslash( $_GET['orderby'] ) ) : 'created_at';
$sortable = array( 'id', 'customer_name', 'created_at', 'total' );
$orderby  = in_array( $orderby, $sortable, true ) ? $orderby : 'created_at';

$order = ( isset( $_GET['order'] ) && 'asc' === strtolower( $_GET['order'] ) ) ? 'ASC' : 'DESC';

Why can’t we just parameterize it? Because placeholders substitute values, and a value in SQL is quoted. ORDER BY 'created_at' DESC sorts every row by the constant string "created_at" – the query runs, returns unsorted rows, and nobody notices for months. Identifiers are structure, not data, and structure must be chosen by your code, from a list your code owns.

Modern WordPress (6.2+) does offer a %i placeholder that escapes an identifier with backticks, which is genuinely useful for dynamic table prefixes in multisite. But %i only guarantees the value is escaped as an identifier – it doesn’t guarantee it’s a column the user is allowed to sort by. Keep the allowlist. %i is a second lock, not a replacement for the first.

WordPress also ships sanitize_sql_orderby(), which checks that a string looks like a legal ORDER BY clause. It’s a reasonable backstop, but it will happily approve a column you never intended to expose. Allowlist first.

Layer 2: Sanitization – clean the data, but know what job you’re doing

Sanitization transforms input into a safe form rather than rejecting it. WordPress ships a well-stocked toolbox:

Use these. Consistently, at every entry point. They protect against a whole family of problems – stored XSS, malformed data, encoding issues – and they cost almost nothing.

But here’s the part that “just sanitize your inputs” skips.

Sanitization is not an SQL defense

sanitize_text_field() was designed to make text safe to store and display. It strips HTML tags. It normalizes whitespace. Nothing in its job description involves SQL syntax, and the name gives no hint of that limitation. Developers reach for it, see the word “sanitize,” and mark the ticket done.

// Still vulnerable, despite the sanitizing
$name = sanitize_text_field( wp_unslash( $_POST['customer_name'] ) );
$rows = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}bookings WHERE customer_name = '$name'" );

sanitize_text_field() does not remove single quotes. It has no reason to – an apostrophe is perfectly valid in a name. So a customer_name of ' OR '1'='1 passes through untouched and breaks straight out of the string literal.

WordPress does have esc_sql(), which is SQL-context escaping, and it works when used correctly. But it’s easy to use incorrectly – people forget the surrounding quotes, or double-escape, or apply it to an identifier where it does nothing useful – and it leaves you responsible for getting the quoting right by hand every single time. Core’s own documentation points you to prepare() instead. Treat esc_sql() as a legacy tool.

The consistency problem

Sanitization is opt-in and manual. You have to remember to call it at every place untrusted data enters your code: form POSTs, URL parameters, cookies, request headers, AJAX handlers, REST endpoints, webhook payloads, imported CSVs, third-party API responses.

Ninety-nine sanitized entry points and one forgotten one leaves you exactly as exposed as zero. And the forgotten one is usually the endpoint someone added in a hurry eleven months later.

Compare that to prepare(), which sits at the single place every query must pass through. Defenses at a chokepoint survive team turnover. Defenses that depend on everyone remembering, every time, do not.

Second-order injection

This one bites teams who think they’ve solved the problem.

// Step 1 - registration. Input is sanitized and stored. So far, fine.
$display_name = sanitize_text_field( wp_unslash( $_POST['display_name'] ) );
$wpdb->insert( $wpdb->prefix . 'profiles', array( 'display_name' => $display_name ), array( '%s' ) );

// Step 2 - a reporting job, three weeks later, written by someone else
$name = $wpdb->get_var( "SELECT display_name FROM {$wpdb->prefix}profiles WHERE user_id = 5" );
$rows = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}orders WHERE buyer_name = '$name'" ); // boom

The second developer reasoned: this value came out of our own database, so it’s trusted. But it was never validated – only sanitized, by a function that has no opinion about quotes. The sanitizer ran and did its job correctly. The value is still attacker-controlled.

The lesson: trust is a property of where data came from originally, not where you read it from most recently. A row in your own database is not automatically safe input for a query.

Layer 3: Safe querying – the structural guarantee

This is where injection is actually prevented, and the mechanism is different in kind from the previous two layers.

Validation and sanitization operate on the data, trying to determine or ensure that the characters in it aren’t dangerous. Prepared statements operate on the query structure. The SQL – with placeholders in it – is defined first. Values are attached afterward, as parameters. There is no point in the process at which a value could be parsed as syntax, no matter what characters it contains.

That’s not filtering. It’s a guarantee.

$wpdb->prepare()

global $wpdb;

$booking = $wpdb->get_row(
	$wpdb->prepare(
		"SELECT * FROM {$wpdb->prefix}bookings WHERE id = %d AND status = %s",
		$booking_id,
		$status
	)
);

The placeholders:

• %d – integer
• %f – float
• %s – string (automatically quoted for you)
• %i – identifier, i.e. table or column name (WordPress 6.2+)

Three rules that trip people up:

1. Never put quotes around a placeholder. Write WHERE name = %s, not WHERE name = '%s'. prepare() adds the quotes itself; adding your own produces broken escaping, and recent WordPress versions flag it as an error.

2. Never interpolate a variable into the query string. This is the mistake that makes a prepare() call decorative:

// Useless - $order_by is already inside the SQL before prepare() ever sees it
$sql = $wpdb->prepare( "SELECT * FROM $table WHERE $column = %s ORDER BY $order_by", $value );

prepare() protects the arguments. Anything you’ve already baked into the format string is beyond its reach. {$wpdb->prefix} is fine – that’s a constant from your own config, not user input – but a variable that traces back to a request is not.

3. Only values are covered. Which brings us back to the allowlist for identifiers.

Better still: don’t write SQL

Most of the time you don’t need a hand-written query at all. $wpdb‘s helper methods build the SQL for you and escape every value according to the format array:

$wpdb->insert(
	$wpdb->prefix . 'bookings',
	array(
		'customer_name' => $name,
		'email'         => $email,
		'guests'        => $guests,
		'created_at'    => current_time( 'mysql' ),
	),
	array( '%s', '%s', '%d', '%s' )
);

$wpdb->update(
	$wpdb->prefix . 'bookings',
	array( 'status' => $status ),     // data
	array( 'id' => $booking_id ),     // where
	array( '%s' ),                    // data format
	array( '%d' )                     // where format
);

$wpdb->delete( $wpdb->prefix . 'bookings', array( 'id' => $booking_id ), array( '%d' ) );

And when you’re working with posts, users, terms, or meta, the highest-level answer is to use the API and not touch $wpdb at all:

$query = new WP_Query(
	array(
		'post_type'      => 'product',
		'post_status'    => 'publish',
		's'              => $search_term,
		'posts_per_page' => 20,
		'paged'          => $page,
	)
);

WP_Query, get_posts(), WP_User_Query, get_term(), update_post_meta() – all of these build parameterized SQL internally. Code you didn’t write is code you can’t get wrong. Reach for raw $wpdb only for custom tables, and even then prefer the helper methods over hand-written SQL.

The hard cases

The basic pattern covers most queries. These four are where people improvise, and improvisation is where bugs live.

An IN () list

You can’t pass an array to a single %d. Generate one placeholder per item:

$ids = array_map( 'absint', (array) ( $_POST['ids'] ?? array() ) );
$ids = array_values( array_filter( $ids ) ); // drop zeros, reindex

if ( empty( $ids ) ) {
	return array();
}

$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT * FROM {$wpdb->prefix}bookings WHERE id IN ( $placeholders )",
		$ids
	)
);

The $placeholders string is built by array_fill(), so it contains only %d and commas – no user data can reach it. prepare() accepts the array of arguments as its second parameter. Note the absint map: belt and braces, and it also guarantees the argument count matches the placeholder count.

A LIKE search

% and _ are wildcards in LIKE. A user searching for 100% shouldn’t accidentally match every row, and a user probing your search box shouldn’t be able to reshape the pattern:

$term = isset( $_GET['s'] ) ? sanitize_text_field( wp_unslash( $_GET['s'] ) ) : '';
$like = '%' . $wpdb->esc_like( $term ) . '%';

$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT * FROM {$wpdb->prefix}bookings WHERE customer_name LIKE %s",
		$like
	)
);

esc_like() escapes the wildcards inside the user’s term; you add the wildcards you intend; prepare() handles the quoting and injection safety. Three functions, three distinct jobs, none of them redundant.

Dynamic ORDER BY and pagination

$sortable = array( 'id', 'customer_name', 'created_at', 'total' );
$requested = isset( $_GET['orderby'] ) ? sanitize_key( wp_unslash( $_GET['orderby'] ) ) : '';
$orderby   = in_array( $requested, $sortable, true ) ? $requested : 'created_at';
$order     = ( isset( $_GET['order'] ) && 'asc' === strtolower( $_GET['order'] ) ) ? 'ASC' : 'DESC';

$per_page = min( 100, max( 1, absint( $_GET['per_page'] ?? 20 ) ) );
$paged    = max( 1, absint( $_GET['paged'] ?? 1 ) );
$offset   = ( $paged - 1 ) * $per_page;

$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT * FROM {$wpdb->prefix}bookings ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
		$per_page,
		$offset
	)
);

Interpolating $orderby and $order directly into the SQL is safe here, and only here, because both are provably one of a handful of literals defined in this file. Delete the allowlist and this line becomes a textbook vulnerability. If you write this pattern, write a comment saying why it’s safe, so the next reader doesn’t “simplify” it.

Note the clamp on per_page too. Not an injection defense, but it stops someone requesting a million rows and taking the site down. Validation earns its keep in more than one way.

Dynamic table names

// WordPress 6.2+
$rows = $wpdb->get_results(
	$wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $wpdb->prefix . 'bookings', $booking_id )
);

Useful for multisite and for shared code that operates across tables. Still pair it with an allowlist if the table name traces back to a request.

Putting it together

Here’s a realistic AJAX handler with all three layers, plus the authorization checks that belong in the same function:

add_action( 'wp_ajax_mp_update_booking', 'mp_update_booking' );

function mp_update_booking() {
	// Authorization - not an injection defense, but the other half of the job
	check_ajax_referer( 'mp_update_booking' );

	if ( ! current_user_can( 'manage_bookings' ) ) {
		wp_send_json_error( array( 'message' => 'Not allowed.' ), 403 );
	}

	global $wpdb;

	// LAYER 1: validate
	$booking_id = absint( $_POST['booking_id'] ?? 0 );
	if ( ! $booking_id ) {
		wp_send_json_error( array( 'message' => 'Invalid booking ID.' ), 400 );
	}

	$status  = sanitize_key( wp_unslash( $_POST['status'] ?? '' ) );
	$allowed = array( 'pending', 'confirmed', 'cancelled', 'refunded' );
	if ( ! in_array( $status, $allowed, true ) ) {
		wp_send_json_error( array( 'message' => 'Unknown status.' ), 400 );
	}

	// LAYER 2: sanitize the free-text field
	$note = sanitize_textarea_field( wp_unslash( $_POST['note'] ?? '' ) );

	// LAYER 3: query safely
	$updated = $wpdb->update(
		$wpdb->prefix . 'bookings',
		array(
			'status'     => $status,
			'note'       => $note,
			'updated_at' => current_time( 'mysql' ),
		),
		array( 'id' => $booking_id ),
		array( '%s', '%s', '%s' ),
		array( '%d' )
	);

	if ( false === $updated ) {
		wp_send_json_error( array( 'message' => 'Update failed.' ), 500 );
	}

	wp_send_json_success( array( 'booking_id' => $booking_id, 'status' => $status ) );
}

Every value that reaches the database has been checked against an expectation, cleaned for storage, and passed as a parameter rather than concatenated. Any one of those three could be removed and the code would still work – which is exactly why all three need to be there deliberately.

One note on wp_unslash(): WordPress adds slashes to the superglobals, so you unslash before sanitizing, not after. It isn’t a security function, but leaving it out corrupts data in ways that later get “fixed” by loosening the sanitization.

A review checklist

Run these greps across your plugin or theme. Each hit is either a bug or a comment explaining why it isn’t:

• $wpdb->query(, ->get_results(, ->get_var(, ->get_row( – is the argument a prepare() call?
• A quoted string containing $ next to SELECT, INSERT, UPDATE, or DELETE – string interpolation into SQL
• prepare( where the first argument contains a $ that isn’t $wpdb->prefix or $wpdb->posts
• '%s' or "%d" inside a prepared query – quoted placeholders
• esc_sql( – legacy; consider migrating to prepare()
• $_GET, $_POST, $_REQUEST, $_COOKIE used without a validation or sanitization call on the same or adjacent line

If you want this automated, install PHPCS with the WordPress Coding Standards and run the WordPress.DB.PreparedSQL and WordPress.Security.ValidatedSanitizedInput sniffs in CI. They catch the majority of these mechanically, on every pull request, without anyone needing to remember.

The summary worth keeping

• Validation decides whether the data is acceptable at all. It’s the strongest layer because it’s an allowlist, and it’s the only defense available for table names, column names, and sort direction.
• Sanitization cleans data for safe storage and display. Necessary, cheap, and worth doing everywhere – but it was never designed to protect SQL syntax, and treating it as an injection defense is how vulnerable code gets shipped with a clear conscience.
• Safe querying is the layer that actually makes injection impossible, because it separates query structure from query values at the protocol level rather than guessing which characters are dangerous.

Untrusted data stays untrusted, no matter how many functions it has passed through. Treat it that way at every step, and let prepare() – or better, the WordPress APIs that call it for you – handle the part that must never be improvised.