<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[53575] trunk: Database: Add `%i` placeholder support to `$wpdb->prepare` to escape table and column names.</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { white-space: pre-line; overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta" style="font-size: 105%">
<dt style="float: left; width: 6em; font-weight: bold">Revision</dt> <dd><a style="font-weight: bold" href="https://core.trac.wordpress.org/changeset/53575">53575</a><script type="application/ld+json">{"@context":"http://schema.org","@type":"EmailMessage","description":"Review this Commit","action":{"@type":"ViewAction","url":"https://core.trac.wordpress.org/changeset/53575","name":"Review Commit"}}</script></dd>
<dt style="float: left; width: 6em; font-weight: bold">Author</dt> <dd>davidbaumwald</dd>
<dt style="float: left; width: 6em; font-weight: bold">Date</dt> <dd>2022-06-24 20:33:56 +0000 (Fri, 24 Jun 2022)</dd>
</dl>
<pre style='padding-left: 1em; margin: 2em 0; border-left: 2px solid #ccc; line-height: 1.25; font-size: 105%; font-family: sans-serif'>Database: Add `%i` placeholder support to `$wpdb->prepare` to escape table and column names.
WordPress does not currently provide an explicit method for escaping SQL table and column names. This leads to potential security vulnerabilities, and makes reviewing code for security unnecessarily difficult. Also, static analysis tools also flag the queries as having unescaped SQL input.
Tables and column names in queries are usually in-the-raw, since using the existing `%s` will straight quote the value, making the query invalid.
This change introduces a new `%i` placeholder in `$wpdb->prepare` to properly quote table and column names using backticks.
Props tellyworth, iandunn, craigfrancis, peterwilsoncc, johnbillion, apokalyptik.
Fixes <a href="https://core.trac.wordpress.org/ticket/52506">#52506</a>.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunksrcwpincludeswpdbphp">trunk/src/wp-includes/wp-db.php</a></li>
<li><a href="#trunktestsphpunittestsdbphp">trunk/tests/phpunit/tests/db.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunksrcwpincludeswpdbphp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: trunk/src/wp-includes/wp-db.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/src/wp-includes/wp-db.php 2022-06-24 15:07:40 UTC (rev 53574)
+++ trunk/src/wp-includes/wp-db.php 2022-06-24 20:33:56 UTC (rev 53575)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -645,6 +645,34 @@
</span><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Backwards compatibility, where wpdb::prepare() has not quoted formatted/argnum placeholders.
+ *
+ * Historically this could be used for table/field names, or for some string formatting, e.g.
+ * $wpdb->prepare( 'WHERE `%1s` = "%1s something %1s" OR %1$s = "%-10s"', 'field_1', 'a', 'b', 'c' );
+ *
+ * But it's risky, e.g. forgetting to add quotes, resulting in SQL Injection vulnerabilities:
+ * $wpdb->prepare( 'WHERE (id = %1s) OR (id = %2$s)', $_GET['id'], $_GET['id'] ); // ?id=id
+ *
+ * This feature is preserved while plugin authors update their code to use safer approaches:
+ * $wpdb->prepare( 'WHERE %1s = %s', $_GET['key'], $_GET['value'] );
+ * $wpdb->prepare( 'WHERE %i = %s', $_GET['key'], $_GET['value'] );
+ *
+ * While changing to false will be fine for queries not using formatted/argnum placeholders,
+ * any remaining cases are most likely going to result in SQL errors (good, in a way):
+ * $wpdb->prepare( 'WHERE %1s = "%-10s"', 'my_field', 'my_value' );
+ * true = WHERE my_field = "my_value "
+ * false = WHERE 'my_field' = "'my_value '"
+ * But there may be some queries that result in an SQL Injection vulnerability:
+ * $wpdb->prepare( 'WHERE id = %1s', $_GET['id'] ); // ?id=id
+ * So there may need to be a `_doing_it_wrong()` phase, after we know everyone can use Identifier
+ * placeholders (%i), but before this feature is disabled or removed.
+ *
+ * @since 6.1.0
+ * @var bool
+ */
+ private $allow_unsafe_unquoted_parameters = true;
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * Whether to use mysqli over mysql. Default false.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 3.9.0
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1348,6 +1376,37 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Escapes an identifier for a MySQL database (e.g. table/field names).
+ *
+ * @since 6.1.0
+ *
+ * @param string $identifier Identifier to escape.
+ * @return string Escaped Identifier
+ */
+ public function escape_identifier( $identifier ) {
+ return '`' . $this->_escape_identifier_value( $identifier ) . '`';
+ }
+
+ /**
+ * Escapes an identifier value.
+ *
+ * Escapes an identifier value without adding the surrounding quotes.
+ *
+ * - Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000
+ * - To quote the identifier itself, then you need to double the character, e.g. `a``b`
+ *
+ * @link https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
+ * @since 6.1.0
+ * @access private
+ *
+ * @param string $identifier Identifier to escape.
+ * @return string Escaped Identifier
+ */
+ private function _escape_identifier_value( $identifier ) {
+ return str_replace( '`', '``', $identifier );
+ }
+
+ /**
</ins><span class="cx" style="display: block; padding: 0 10px"> * Prepares a SQL query for safe execution.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * Uses sprintf()-like syntax. The following placeholders can be used in the query string:
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1355,6 +1414,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * - %d (integer)
</span><span class="cx" style="display: block; padding: 0 10px"> * - %f (float)
</span><span class="cx" style="display: block; padding: 0 10px"> * - %s (string)
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * - %i (identifier, e.g. table/field names)
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * All placeholders MUST be left unquoted in the query string. A corresponding argument
</span><span class="cx" style="display: block; padding: 0 10px"> * MUST be passed for each placeholder.
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1380,6 +1440,10 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 5.3.0 Formalized the existing and already documented `...$args` parameter
</span><span class="cx" style="display: block; padding: 0 10px"> * by updating the function signature. The second parameter was changed
</span><span class="cx" style="display: block; padding: 0 10px"> * from `$args` to `...$args`.
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @since 6.1.0 Added '%i' for Identifiers, e.g. table or field names.
+ * Check support via `wpdb::has_cap( 'identifier_placeholders' )`
+ * This preserves compatibility with sprinf, as the C version uses %d and $i
+ * as a signed integer, whereas PHP only supports %d.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @link https://www.php.net/sprintf Description of syntax.
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1411,28 +1475,6 @@
</span><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // If args were passed as an array (as in vsprintf), move them up.
- $passed_as_array = false;
- if ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) ) {
- $passed_as_array = true;
- $args = $args[0];
- }
-
- foreach ( $args as $arg ) {
- if ( ! is_scalar( $arg ) && ! is_null( $arg ) ) {
- wp_load_translations_early();
- _doing_it_wrong(
- 'wpdb::prepare',
- sprintf(
- /* translators: %s: Value type. */
- __( 'Unsupported value type (%s).' ),
- gettype( $arg )
- ),
- '4.8.2'
- );
- }
- }
-
</del><span class="cx" style="display: block; padding: 0 10px"> /*
</span><span class="cx" style="display: block; padding: 0 10px"> * Specify the formatting allowed in a placeholder. The following are allowed:
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1453,19 +1495,82 @@
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> $query = str_replace( "'%s'", '%s', $query ); // Strip any existing single quotes.
</span><span class="cx" style="display: block; padding: 0 10px"> $query = str_replace( '"%s"', '%s', $query ); // Strip any existing double quotes.
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.
</del><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $query = preg_replace( "/(?<!%)(%($allowed_format)?f)/", '%\\2F', $query ); // Force floats to be locale-unaware.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdfFi]))/", '%%\\1', $query ); // Escape any unescaped percents (i.e. anything unrecognised).
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdF]))/", '%%\\1', $query ); // Escape any unescaped percents.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // Extract placeholders from the query.
+ $split_query = preg_split( "/(^|[^%]|(?:%%)+)(%(?:$allowed_format)?[sdfFi])/", $query, -1, PREG_SPLIT_DELIM_CAPTURE );
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- // Count the number of valid placeholders in the query.
- $placeholders = preg_match_all( "/(^|[^%]|(%%)+)%($allowed_format)?[sdF]/", $query, $matches );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $split_query_count = count( $split_query );
+ $placeholder_count = ( ( $split_query_count - 1 ) / 3 ); // Split always returns with 1 value before the first placeholder (even with $query = "%s"), then 3 additional values per placeholder.
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ // If args were passed as an array (as in vsprintf), move them up.
+ $passed_as_array = ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) );
+ if ( $passed_as_array ) {
+ $args = $args[0];
+ }
+
+ $new_query = '';
+ $key = 2; // keys 0 and 1 in $split_query contain values before the first placeholder.
+ $arg_id = 0;
+ $arg_identifiers = array();
+ $arg_strings = array();
+ while ( $key < $split_query_count ) {
+ $placeholder = $split_query[ $key ];
+
+ $format = substr( $placeholder, 1, -1 );
+ $type = substr( $placeholder, -1 );
+
+ if ( 'f' === $type ) { // Force floats to be locale-unaware.
+ $type = 'F';
+ $placeholder = '%' . $format . $type;
+ }
+
+ if ( 'i' === $type ) {
+ $placeholder = '`%' . $format . 's`';
+ $argnum_pos = strpos( $format, '$' ); // Using a simple strpos() due to previous checking (e.g. $allowed_format).
+ if ( false !== $argnum_pos ) {
+ $arg_identifiers[] = ( intval( substr( $format, 0, $argnum_pos ) ) - 1 ); // sprintf argnum starts at 1, $arg_id from 0.
+ } else {
+ $arg_identifiers[] = $arg_id;
+ }
+ } elseif ( 'd' !== $type && 'F' !== $type ) { // i.e. ('s' === $type), where 'd' and 'F' keeps $placeholder unchanged, and we ensure string escaping is used as a safe default (e.g. even if 'x').
+ $argnum_pos = strpos( $format, '$' );
+ if ( false !== $argnum_pos ) {
+ $arg_strings[] = ( intval( substr( $format, 0, $argnum_pos ) ) - 1 );
+ }
+ if ( true !== $this->allow_unsafe_unquoted_parameters || '' === $format ) { // Unquoted strings for backwards compatibility (dangerous).
+ $placeholder = "'%" . $format . "s'";
+ }
+ }
+
+ $new_query .= $split_query[ $key - 2 ] . $split_query[ $key - 1 ] . $placeholder; // Glue (-2), any leading characters (-1), then the new $placeholder.
+
+ $key += 3;
+ $arg_id++;
+ }
+ $query = $new_query . $split_query[ $key - 2 ]; // Replace $query; and add remaining $query characters, or index 0 if there were no placeholders.
+
+ $dual_use = array_intersect( $arg_identifiers, $arg_strings );
+ if ( count( $dual_use ) ) {
+ wp_load_translations_early();
+ _doing_it_wrong(
+ 'wpdb::prepare',
+ sprintf(
+ /* translators: %s: A comma-separated list of arguments found to be a problem. */
+ __( 'Arguments (%s) cannot be used for both String and Identifier escaping.' ),
+ implode( ', ', $dual_use )
+ ),
+ '6.1.0'
+ );
+
+ return;
+ }
+
</ins><span class="cx" style="display: block; padding: 0 10px"> $args_count = count( $args );
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( $args_count !== $placeholders ) {
- if ( 1 === $placeholders && $passed_as_array ) {
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( $args_count !== $placeholder_count ) {
+ if ( 1 === $placeholder_count && $passed_as_array ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> // If the passed query only expected one argument, but the wrong number of arguments were sent as an array, bail.
</span><span class="cx" style="display: block; padding: 0 10px"> wp_load_translations_early();
</span><span class="cx" style="display: block; padding: 0 10px"> _doing_it_wrong(
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1486,7 +1591,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> sprintf(
</span><span class="cx" style="display: block; padding: 0 10px"> /* translators: 1: Number of placeholders, 2: Number of arguments passed. */
</span><span class="cx" style="display: block; padding: 0 10px"> __( 'The query does not contain the correct number of placeholders (%1$d) for the number of arguments passed (%2$d).' ),
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $placeholders,
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $placeholder_count,
</ins><span class="cx" style="display: block; padding: 0 10px"> $args_count
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><span class="cx" style="display: block; padding: 0 10px"> '4.8.3'
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1496,9 +1601,14 @@
</span><span class="cx" style="display: block; padding: 0 10px"> * If we don't have enough arguments to match the placeholders,
</span><span class="cx" style="display: block; padding: 0 10px"> * return an empty string to avoid a fatal error on PHP 8.
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- if ( $args_count < $placeholders ) {
- $max_numbered_placeholder = ! empty( $matches[3] ) ? max( array_map( 'intval', $matches[3] ) ) : 0;
-
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( $args_count < $placeholder_count ) {
+ $max_numbered_placeholder = 0;
+ for ( $i = 2, $l = $split_query_count; $i < $l; $i += 3 ) {
+ $argnum = intval( substr( $split_query[ $i ], 1 ) ); // Assume a leading number is for a numbered placeholder, e.g. '%3$s'.
+ if ( $max_numbered_placeholder < $argnum ) {
+ $max_numbered_placeholder = $argnum;
+ }
+ }
</ins><span class="cx" style="display: block; padding: 0 10px"> if ( ! $max_numbered_placeholder || $args_count < $max_numbered_placeholder ) {
</span><span class="cx" style="display: block; padding: 0 10px"> return '';
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1506,9 +1616,33 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- array_walk( $args, array( $this, 'escape_by_ref' ) );
- $query = vsprintf( $query, $args );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $args_escaped = array();
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ foreach ( $args as $i => $value ) {
+ if ( in_array( $i, $arg_identifiers, true ) ) {
+ $args_escaped[] = $this->_escape_identifier_value( $value );
+ } elseif ( is_int( $value ) || is_float( $value ) ) {
+ $args_escaped[] = $value;
+ } else {
+ if ( ! is_scalar( $value ) && ! is_null( $value ) ) {
+ wp_load_translations_early();
+ _doing_it_wrong(
+ 'wpdb::prepare',
+ sprintf(
+ /* translators: %s: Value type. */
+ __( 'Unsupported value type (%s).' ),
+ gettype( $value )
+ ),
+ '4.8.2'
+ );
+ $value = ''; // Preserving old behaviour, where values are escaped as strings.
+ }
+ $args_escaped[] = $this->_real_escape( $value );
+ }
+ }
+
+ $query = vsprintf( $query, $args_escaped );
+
</ins><span class="cx" style="display: block; padding: 0 10px"> return $this->add_placeholder_escape( $query );
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -3738,17 +3872,27 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> /**
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * Determines if a database supports a particular feature.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Determine DB or WPDB support for a particular feature.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * Capability sniffs for the database server and current version of wpdb.
+ *
+ * Database sniffs test based on the version of MySQL the site is using.
+ *
+ * WPDB sniffs are added as new features are introduced to allow theme and plugin
+ * developers to determine feature support. This is to account for drop-ins which may
+ * introduce feature support at a different time to WordPress.
+ *
</ins><span class="cx" style="display: block; padding: 0 10px"> * @since 2.7.0
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 4.1.0 Added support for the 'utf8mb4' feature.
</span><span class="cx" style="display: block; padding: 0 10px"> * @since 4.6.0 Added support for the 'utf8mb4_520' feature.
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * @since 6.1.0 Added support for the 'identifier_placeholders' feature.
</ins><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @see wpdb::db_version()
</span><span class="cx" style="display: block; padding: 0 10px"> *
</span><span class="cx" style="display: block; padding: 0 10px"> * @param string $db_cap The feature to check for. Accepts 'collation', 'group_concat',
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- * 'subqueries', 'set_charset', 'utf8mb4', or 'utf8mb4_520'.
- * @return int|false Whether the database feature is supported, false otherwise.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * 'subqueries', 'set_charset', 'utf8mb4', 'utf8mb4_520',
+ * or 'identifier_placeholders'.
+ * @return bool True when the database feature is supported, false otherwise.
</ins><span class="cx" style="display: block; padding: 0 10px"> */
</span><span class="cx" style="display: block; padding: 0 10px"> public function has_cap( $db_cap ) {
</span><span class="cx" style="display: block; padding: 0 10px"> $version = $this->db_version();
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -3782,6 +3926,8 @@
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px"> case 'utf8mb4_520': // @since 4.6.0
</span><span class="cx" style="display: block; padding: 0 10px"> return version_compare( $version, '5.6', '>=' );
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ case 'identifier_placeholders': // @since 6.1.0, wpdb::prepare() supports identifiers via '%i' - e.g. table/field names.
+ return true;
</ins><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> return false;
</span></span></pre></div>
<a id="trunktestsphpunittestsdbphp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: trunk/tests/phpunit/tests/db.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/tests/phpunit/tests/db.php 2022-06-24 15:07:40 UTC (rev 53574)
+++ trunk/tests/phpunit/tests/db.php 2022-06-24 20:33:56 UTC (rev 53575)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -492,9 +492,11 @@
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'collation' ) );
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'group_concat' ) );
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'subqueries' ) );
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertTrue( $wpdb->has_cap( 'identifier_placeholders' ) );
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'COLLATION' ) );
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'GROUP_CONCAT' ) );
</span><span class="cx" style="display: block; padding: 0 10px"> $this->assertTrue( $wpdb->has_cap( 'SUBQUERIES' ) );
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertTrue( $wpdb->has_cap( 'IDENTIFIER_PLACEHOLDERS' ) );
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->assertSame(
</span><span class="cx" style="display: block; padding: 0 10px"> version_compare( $wpdb->db_version(), '5.0.7', '>=' ),
</span><span class="cx" style="display: block; padding: 0 10px"> $wpdb->has_cap( 'set_charset' )
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1717,9 +1719,129 @@
</span><span class="cx" style="display: block; padding: 0 10px"> false,
</span><span class="cx" style="display: block; padding: 0 10px"> "'hello' 'foo##'",
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ array(
+ 'SELECT * FROM %i WHERE %i = %d;',
+ array( 'my_table', 'my_field', 321 ),
+ false,
+ 'SELECT * FROM `my_table` WHERE `my_field` = 321;',
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'evil_`_field', 321 ),
+ false,
+ 'WHERE `evil_``_field` = 321;', // To quote the identifier itself, then you need to double the character, e.g. `a``b`.
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'evil_````````_field', 321 ),
+ false,
+ 'WHERE `evil_````````````````_field` = 321;',
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( '``evil_field``', 321 ),
+ false,
+ 'WHERE `````evil_field````` = 321;',
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'evil\'field', 321 ),
+ false,
+ 'WHERE `evil\'field` = 321;',
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'evil_\``_field', 321 ),
+ false,
+ 'WHERE `evil_\````_field` = 321;',
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'evil_%s_field', 321 ),
+ false,
+ "WHERE `evil_{$wpdb->placeholder_escape()}s_field` = 321;",
+ ),
+ array(
+ 'WHERE %i = %d;',
+ array( 'value`', 321 ),
+ false,
+ 'WHERE `value``` = 321;',
+ ),
+ array(
+ 'WHERE `%i = %d;',
+ array( ' AND evil_value', 321 ),
+ false,
+ 'WHERE `` AND evil_value` = 321;', // Won't run (SQL parse error: "Unclosed quote").
+ ),
+ array(
+ 'WHERE %i` = %d;',
+ array( 'evil_value -- ', 321 ),
+ false,
+ 'WHERE `evil_value -- `` = 321;', // Won't run (SQL parse error: "Unclosed quote").
+ ),
+ array(
+ 'WHERE `%i`` = %d;',
+ array( ' AND true -- ', 321 ),
+ false,
+ 'WHERE `` AND true -- ``` = 321;', // Won't run (Unknown column '').
+ ),
+ array(
+ 'WHERE ``%i` = %d;',
+ array( ' AND true -- ', 321 ),
+ false,
+ 'WHERE ``` AND true -- `` = 321;', // Won't run (SQL parse error: "Unclosed quote").
+ ),
+ array(
+ 'WHERE %2$i = %1$d;',
+ array( '1', 'two' ),
+ false,
+ 'WHERE `two` = 1;',
+ ),
+ array(
+ 'WHERE \'%i\' = 1 AND "%i" = 2 AND `%i` = 3 AND ``%i`` = 4 AND %15i = 5',
+ array( 'my_field1', 'my_field2', 'my_field3', 'my_field4', 'my_field5' ),
+ false,
+ 'WHERE \'`my_field1`\' = 1 AND "`my_field2`" = 2 AND ``my_field3`` = 3 AND ```my_field4``` = 4 AND ` my_field5` = 5', // Does not remove any existing quotes, always adds it's own (safer).
+ ),
+ array(
+ 'WHERE id = %d AND %i LIKE %2$s LIMIT 1',
+ array( 123, 'field -- ', false ),
+ true, // Incorrect usage.
+ null, // Should be rejected, otherwise the `%1$s` could use Identifier escaping, e.g. 'WHERE `field -- ` LIKE field -- LIMIT 1' (thanks @vortfu).
+ ),
+ array(
+ 'WHERE %i LIKE %s LIMIT 1',
+ array( "field' -- ", "field' -- " ),
+ false,
+ "WHERE `field' -- ` LIKE 'field\' -- ' LIMIT 1", // In contrast to the above, Identifier vs String escaping is used.
+ ),
</ins><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ public function test_allow_unsafe_unquoted_parameters() {
+ global $wpdb;
+
+ $sql = 'WHERE (%i = %s) OR (%10i = %10s) OR (%5$i = %6$s)';
+ $values = array( 'field_a', 'string_a', 'field_b', 'string_b', 'field_c', 'string_c' );
+
+ $default = $wpdb->allow_unsafe_unquoted_parameters;
+
+ $wpdb->allow_unsafe_unquoted_parameters = true;
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $part = $wpdb->prepare( $sql, $values );
+ $this->assertSame( 'WHERE (`field_a` = \'string_a\') OR (` field_b` = string_b) OR (`field_c` = string_c)', $part ); // Unsafe, unquoted parameters.
+
+ $wpdb->allow_unsafe_unquoted_parameters = false;
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $part = $wpdb->prepare( $sql, $values );
+ $this->assertSame( 'WHERE (`field_a` = \'string_a\') OR (` field_b` = \' string_b\') OR (`field_c` = \'string_c\')', $part );
+
+ $wpdb->allow_unsafe_unquoted_parameters = $default;
+
+ }
+
</ins><span class="cx" style="display: block; padding: 0 10px"> /**
</span><span class="cx" style="display: block; padding: 0 10px"> * @dataProvider data_escape_and_prepare
</span><span class="cx" style="display: block; padding: 0 10px"> */
</span></span></pre>
</div>
</div>
</body>
</html>