Escaping Table and Field names with wpdb::prepare() in WordPress

As part of WordPress 6.2, wpdb::prepare() has been updated to escape Identifiers (such as Table and Field names) with the %i placeholder (#52506).

This ensures these values are escaped correctly and don’t lead to SQL Injection Vulnerabilities.

Example

$table = 'my_table';
$field = 'my_field';
$value = 'my_value';

$wpdb->prepare('SELECT * FROM %i WHERE %i = %s', $table, $field, $value);

// Output:
//   SELECT * FROM `my_table` WHERE `my_field` = 'my_value'

While this protects you against SQL Injection, where possible you should limit the values the user (attacker) can choose via an allow-list of trusted values; e.g.

$fields = array(
    'name'    => 'user_nicename',
    'url'     => 'user_url',
    'created' => 'DATE(created)',
  );

$sql .= ' ORDER BY ' . ($fields[$order_field] ?? 'user_login');

Performance Improvement

The change to add support for %i has a small performance improvement, as there is a little bit less Regular Expression work involved (generally the more parameters, the better the improvement).

In the Future

This was going to be released in WordPress 6.1, but a problem was identified in RC5 where the use of '%%%s%%' (which can often be seen in LIKE queries) stopped working. For reference, the documentation says “numbered or formatted string placeholders” will not have quotes added by this function (an old/unsafe feature), but this also happens when a placeholder immediately follows a “%”.

WordPress is looking to use %i in coreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. (#56091).

This change will help developers use the literal-string type for the $query parameter (this is where the $query is written as a developer-defined string, and all user values are provided separately).

Props to @davidbaumwald for reviewing this dev notedev note Each important change in WordPress Core is documented in a developers note, (usually called dev note). Good dev notes generally include a description of the change, the decision that led to this change, and a description of how developers are supposed to work with that change. Dev notes are published on Make/Core blog during the beta phase of WordPress release cycle. Publishing dev notes is particularly important when plugin/theme authors and WordPress developers need to be aware of those changes.In general, all dev notes are compiled into a Field Guide at the beginning of the release candidate phase..

#6-1, #dev-notes, #dev-notes-6-1, #performance, #wpdb