Postponed to WP 6.2: Escaping Table and Field names with wpdb::prepare()

Support for %i to escape Table and Field names was postponed to 6.2, see:
https://make.wordpress.org/core/2022/10/08/escaping-table-and-field-names-with-wpdbprepare-in-wordpress-6-1/

A problem was found during RC5, where some extensions use field LIKE "%%%s%%", and expect the %s to remain unquoted.

This is undocumented behaviour. Officially the only time placeholders should not be quoted (for backwards compatibility reasons) is when using numbered or formatted string placeholders (this is unsafe, and should be avoided).

In this case, the first %% results in a literal "%", but this goes on to affect the %s.

For reference, developers should rely on wpdb::prepare() to quote all variables, so mistakes cannot be made. In this case it’s recommended to use something like the following:

$wpdb->prepare( 
     'field LIKE %s', 
     '%' . $wpdb->esc_like( $var ) . '%' );

Thanks to @AlanP57 for reporting, @hellofromtonya and @sergeybiryukov for reverting the patchpatch A special text file that describes changes to code, by identifying the files and lines which are added, removed, and altered. It may also be referred to as a diff. A patch can be applied to a codebase for testing., @azaozz and @bph for helping document this, and everyone else for generally helping out.

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

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

This has been postponed to WordPress 6.2. See also Postponed to WP 6.2: Escaping Table and Field names with wpdb::prepare()

As part of the WordPress 6.1, wpdb::prepare() has been will be 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

Changed behaviour of esc_sql() in WordPress 4.8.3

As part of the WordPress 4.8.3 release, there is a change in `esc_sql()` behaviour that may affect pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party developers who expect `esc_sql()` to return a string that’s usable outside of the context of building a query to send to WPDB.

While we strongly recommend you do not use `esc_sql()` for other purposes, we understand that it can be tricky to rewrite old code rapidly. To return to the old behaviour, you can use the `$wpdb->remove_placeholder_escape()` method, like so:

echo esc_sql( "100%" );
// "100{9fa52f39262a451892931117b9ab11b5a06d3a15faee833cc75edb18b4411d11}"

echo $wpdb->remove_placeholder_escape( esc_sql( "100%" ) );
// "100%"

#4-8, #4-8-3, #dev-notes, #wpdb

The utf8mb4 Upgrade

In WordPress 4.2, we’re upgrading tables to utf8mb4, when we can. Your site will only upgrade when the following conditions are met:

  • You’re currently using the utf8 character set.
  • Your MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. server is version 5.5.3 or higher (including all 10.x versions of MariaDB).
  • Your MySQL client libraries are version 5.5.3 or higher. If you’re using mysqlnd, 5.0.9 or higher.

The difference between utf8 and utf8mb4 is that the former can only store 3 byte characters, while the latter can store 4 byte characters. In Unicode terms, utf8 can only store characters in the Basic Multilingual Plane, while utf8mb4 can store any Unicode character. This greatly expands the language usability of WordPress, especially in countries that use Han character sets. Unicode isn’t without its problems, but it’s the best option available.

utf8mb4 is 100% backwards compatible with utf8.

Due to index size restrictions in MySQL, this does mean we need to re-create a handful of indexes to fit within MySQL’s rules. Using a standard configuration, MySQL allows 767 bytes per index, which for utf8 means 767 bytes / 3 bytes = 255 characters. For utf8mb4, that means 767 bytes / 4 bytes = 191 characters. The indexes that will be resized are:


wp_usermeta.meta_key
wp_terms.slug
wp_terms.name
wp_commentmeta.meta_key
wp.postmeta.meta_key
wp_posts.post_name

And from Multisitemultisite Used to describe a WordPress installation with a network of multiple blogs, grouped by sites. This installation type has shared users tables, and creates separate database tables for each blog (wp_posts becomes wp_0_posts). See also network, blog, site:


wp_site.domain
wp_sitemeta.meta_key
wp_signups.domain

Of course, the Multisite (and wp_usermeta) keys obey the DO_NOT_UPGRADE_GLOBAL_TABLES setting. The upgrade will only be attempted once, though we’ll probably add a check in a future WordPress version to see if we can upgrade now (say, if you’ve upgraded your MySQL server since upgrading to WordPress 4.2).

If you’re a pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party developer and your plugin includes custom tables, please test that your indexes fit within MySQL’s limits. MySQL won’t always produce an error when the index is too big, so you’ll need to manually check the size of each index, instead of relying on automated testing.

EDIT: One more thing…

If you’d like to upgrade your custom tables to utf8mb4 (and your indexes are all in order), you can do it really easily with the shiny new maybe_convert_table_to_utf8mb4( $tablename ) function. It’s available in `wp-adminadmin (and super admin)/includes/upgrade.php`, and will sanity check that your tables are entirely utf8 before upgrading.

#4-2, #dev-notes, #utf8mb4, #wpdb

MySQL in WordPress 3.9

In WordPress 3.9, we added an extra layer to WPDB, causing it to switch to using the mysqli PHPPHP The web scripting language in which WordPress is primarily architected. WordPress requires PHP 5.6.20 or higher library, when using PHP 5.5 or higher.

For pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.

mysql_query()

There are a few different options for replacing the query functions, depending on what you want to do:

As a drop in replacement to run a query that you don’t expect a return value from (i.e., an INSERT, UPDATE or DELETE query), use $wpdb->query(). This will always return the number of rows effected by the query.

Alternatively, $wpdb->insert(), $wpdb->update(), $wpdb->delete() and $wpdb->replace() are all helper functions that will automatically escape your data, then generate and run the queries for you. Ideally, you should never need to write an SQL statement!

mysql_fetch_*()

If you have a SELECT query, for which you’d normally do a mysql_query() followed by a mysql_fetch_*(), WPDB lets you combine this into one function call.

To get all of the results from a query that returns more than one row, use $wpdb->get_results() to return an array of objects containing your data.

There are also some shortcut functions for common usage:

If you only need a single row from your query, $wpdb->get_row() will return just the data object from that row.

If you only need a single column from a single row, $wpdb->get_var() will return only that field.

And if you need a single column, $wpdb->get_col() will return an array of all the data from that column.

mysql_real_escape_string()

For a drop in replacement, you can use esc_sql(). That said, we strongly recommend switching to $wpdb->prepare(), instead. We have a pretty thorough tutorial available for $wpdb->prepare().

mysql_insert_id()

If you need to get the Insert ID from the last query, $wpdb->insert_id is where you need to look.

Updating your plugin to use WPDB will also future proof it for if we make changes to how WordPress connects to the database – we’ll always maintain backwards compatibility with the current WPDB interface.

For more reading, check the WPDB Codex page, and #21663.

If you’re using MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. in a way that I haven’t covered here, please post it in the comments, we’d be happy to help you out!

#3-9, #database, #dev-notes, #mysql, #wpdb

PHP Warning: Missing argument 2 for wpdb::prepare()

Hello pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party or theme author! You possibly found this post after searching the Internet for the error above: “PHPPHP The web scripting language in which WordPress is primarily architected. WordPress requires PHP 5.6.20 or higher Warning: Missing argument 2 for wpdb::prepare().”

So, this is a new warning in 3.5. No sites are broken, everything is fine as before. But, this is indeed something you need to look at, because you may be exposing your users to a possible SQL injection vulnerability. Now that’s no fun!

First, if you’re a user and you want to get rid of these errors, you should turn off the displaying of errors in PHP. There are many ways to do this, such as in php.ini, .htaccess, etc. For this, you can just put this in wp-config.php. (Note that hiding errors on production sites is good practice anyway.)

@ini_set('display_errors', 0);

If you’re a user, you can stop here. (If you need more help, please don’t comment here, try the helpful Support Forums.) Just be sure to send a link to this post to the developer of the theme or plugin referenced in the error.

Now, developers: Here’s how $wpdb->prepare() is supposed to work:

$wpdb->prepare( "SELECT * FROM table WHERE ID = %d AND name = %s", $id, $name );

See how $id — an integer, presumably — was passed as the second argument? That corresponds to the first placeholder, %d. Then, $name (a string) was passed as the third argument, thus the second placeholder, %s. This makes sure your query is safe, and prevents something like little bobby tables. (Note: the comic is wrong, don’t sanitize — always prepare your queries.)

The problem is, a number of people were calling $wpdb->prepare() with only one argument, like so:

$wpdb->prepare( "SELECT COUNT(*) FROM table" );

See, there’s no parameter (%d, %s, or for floats, %f) in this query. This happens to work fine, but the prepare call isn’t doing anything. You should instead the query directly, as there are no inputs.

But here’s where the problem lies:

$wpdb->prepare( "SELECT * FROM table WHERE id = $id" );

See the problem? That query isn’t secure! You may think you are “preparing” this query, but you’re not — you’re passing $id directly into the query, unprepared. And this, right here, is why $wpdb->prepare() now issues a warning if it isn’t called with more than one argument. Because you can’t prepare a query without more than one argument. Here’s a correct example:

$wpdb->prepare( "SELECT * FROM table WHERE id = %d", $id );

This wasn’t a decision done lightly. We don’t like shoving PHP warnings into the faces of users and developers. But given the potential security risks, we wanted everyone to immediately look at how they are running queries. And, of course, always prepare them properly.

For more: wpdb Codex reference, #22262, and [22429].

#3-5, #dev-notes, #sql, #wpdb