<!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>[55151] trunk: Database: Add `%i` placeholder support to `$wpdb->prepare` to escape table and column names, take 2.</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/55151">55151</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/55151","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>2023-01-27 18:47:53 +0000 (Fri, 27 Jan 2023)</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, take 2.
<a href="https://core.trac.wordpress.org/changeset/53575">[53575]</a> during the 6.1 cycle was reverted in <a href="https://core.trac.wordpress.org/changeset/54734">[54734]</a> to address issues around multiple `%` placeholders not being properly quoted as reported in <a href="https://core.trac.wordpress.org/ticket/56933">#56933</a>. Since then, this issue has been resolved and the underlying code improved significantly. Additionally, the unit tests have been expanded and the inline docs have been improved as well.
This change reintroduces `%i` placeholder support in `$wpdb->prepare()` to give extenders the ability to safely escape table and column names in database queries.
Follow-up to <a href="https://core.trac.wordpress.org/changeset/53575">[53575]</a> and <a href="https://core.trac.wordpress.org/changeset/54734">[54734]</a>.
Props craigfrancis, jrf, xknown, costdev, ironprogrammer, SergeyBiryukov.
Fixes <a href="https://core.trac.wordpress.org/ticket/52506">#52506</a>.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunksrcwpincludesclasswpdbphp">trunk/src/wp-includes/class-wpdb.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="trunksrcwpincludesclasswpdbphp"></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/class-wpdb.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- trunk/src/wp-includes/class-wpdb.php 2023-01-27 15:53:15 UTC (rev 55150)
+++ trunk/src/wp-includes/class-wpdb.php 2023-01-27 18:47:53 UTC (rev 55151)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -655,6 +655,45 @@
</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">+ * Backward compatibility, where wpdb::prepare() has not quoted formatted/argnum placeholders.
+ *
+ * This is often used for table/field names (before %i was supported), and sometimes string formatting, e.g.
+ *
+ * $wpdb->prepare( 'WHERE `%1$s` = "%2$s something %3$s" OR %1$s = "%4$-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:
+ *
+ * $_GET['key'] = 'a`b';
+ *
+ * $wpdb->prepare( 'WHERE %1s = %s', $_GET['key'], $_GET['value'] ); // WHERE a`b = 'value'
+ * $wpdb->prepare( 'WHERE `%1$s` = "%2$s"', $_GET['key'], $_GET['value'] ); // WHERE `a`b` = "value"
+ *
+ * $wpdb->prepare( 'WHERE %i = %s', $_GET['key'], $_GET['value'] ); // WHERE `a``b` = '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 %1$s = "%2$-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 = %1$s', $_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.2.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">@@ -763,6 +802,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> 'col_meta',
</span><span class="cx" style="display: block; padding: 0 10px"> 'table_charset',
</span><span class="cx" style="display: block; padding: 0 10px"> 'check_current_query',
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ 'allow_unsafe_unquoted_parameters',
</ins><span class="cx" style="display: block; padding: 0 10px"> );
</span><span class="cx" style="display: block; padding: 0 10px"> if ( in_array( $name, $protected_members, true ) ) {
</span><span class="cx" style="display: block; padding: 0 10px"> return;
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1363,6 +1403,36 @@
</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">+ * Quotes an identifier for a MySQL database, e.g. table/field names.
+ *
+ * @since 6.2.0
+ *
+ * @param string $identifier Identifier to escape.
+ * @return string Escaped identifier.
+ */
+ public function quote_identifier( $identifier ) {
+ return '`' . $this->_escape_identifier_value( $identifier ) . '`';
+ }
+
+ /**
+ * 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, you need to double the character, e.g. `a``b`.
+ *
+ * @since 6.2.0
+ *
+ * @link https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
+ *
+ * @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">@@ -1370,6 +1440,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">@@ -1402,6 +1473,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.2.0 Added `%i` for identifiers, e.g. table or field names.
+ * Check support via `wpdb::has_cap( 'identifier_placeholders' )`.
+ * This preserves compatibility with sprintf(), 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">@@ -1433,28 +1508,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">@@ -1475,20 +1528,169 @@
</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">+ // Escape any unescaped percents (i.e. anything unrecognised).
+ $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdfFi]))/", '%%\\1', $query );
</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 );
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ /*
+ * Split always returns with 1 value before the first placeholder (even with $query = "%s"),
+ * then 3 additional values per placeholder.
+ */
+ $placeholder_count = ( ( $split_query_count - 1 ) / 3 );
+
+ // 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 && true === $this->allow_unsafe_unquoted_parameters && str_ends_with( $split_query[ $key - 1 ], '%' ) ) {
+
+ /*
+ * Before WP 6.2 the "force floats to be locale-unaware" RegEx didn't
+ * convert "%%%f" to "%%%F" (note the uppercase F).
+ * This was because it didn't check to see if the leading "%" was escaped.
+ * And because the "Escape any unescaped percents" RegEx used "[sdF]" in its
+ * negative lookahead assertion, when there was an odd number of "%", it added
+ * an extra "%", to give the fully escaped "%%%%f" (not a placeholder).
+ */
+
+ $s = $split_query[ $key - 2 ] . $split_query[ $key - 1 ];
+ $k = 1;
+ $l = strlen( $s );
+ while ( $k <= $l && '%' === $s[ $l - $k ] ) {
+ $k++;
+ }
+
+ $placeholder = '%' . ( $k % 2 ? '%' : '' ) . $format . $type;
+
+ --$placeholder_count;
+
+ } else {
+
+ // Force floats to be locale-unaware.
+ if ( 'f' === $type ) {
+ $type = 'F';
+ $placeholder = '%' . $format . $type;
+ }
+
+ if ( 'i' === $type ) {
+ $placeholder = '`%' . $format . 's`';
+ // Using a simple strpos() due to previous checking (e.g. $allowed_format).
+ $argnum_pos = strpos( $format, '$' );
+
+ if ( false !== $argnum_pos ) {
+ // sprintf() argnum starts at 1, $arg_id from 0.
+ $arg_identifiers[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
+ } 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[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
+ } else {
+ $arg_strings[] = $arg_id;
+ }
+
+ /*
+ * Unquoted strings for backward compatibility (dangerous).
+ * First, "numbered or formatted string placeholders (eg, %1$s, %5s)".
+ * Second, if "%s" has a "%" before it, even if it's unrelated (e.g. "LIKE '%%%s%%'").
+ */
+ if ( true !== $this->allow_unsafe_unquoted_parameters || ( '' === $format && ! str_ends_with( $split_query[ $key - 1 ], '%' ) ) ) {
+ $placeholder = "'%" . $format . "s'";
+ }
+ }
+ }
+
+ // Glue (-2), any leading characters (-1), then the new $placeholder.
+ $new_query .= $split_query[ $key - 2 ] . $split_query[ $key - 1 ] . $placeholder;
+
+ $key += 3;
+ $arg_id++;
+ }
+
+ // Replace $query; and add remaining $query characters, or index 0 if there were no placeholders.
+ $query = $new_query . $split_query[ $key - 2 ];
+
+ $dual_use = array_intersect( $arg_identifiers, $arg_strings );
+
+ if ( count( $dual_use ) > 0 ) {
+ wp_load_translations_early();
+
+ $used_placeholders = array();
+
+ $key = 2;
+ $arg_id = 0;
+ // Parse again (only used when there is an error).
+ while ( $key < $split_query_count ) {
+ $placeholder = $split_query[ $key ];
+
+ $format = substr( $placeholder, 1, -1 );
+
+ $argnum_pos = strpos( $format, '$' );
+
+ if ( false !== $argnum_pos ) {
+ $arg_pos = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
+ } else {
+ $arg_pos = $arg_id;
+ }
+
+ $used_placeholders[ $arg_pos ][] = $placeholder;
+
+ $key += 3;
+ $arg_id++;
+ }
+
+ $conflicts = array();
+ foreach ( $dual_use as $arg_pos ) {
+ $conflicts[] = implode( ' and ', $used_placeholders[ $arg_pos ] );
+ }
+
+ _doing_it_wrong(
+ 'wpdb::prepare',
+ sprintf(
+ /* translators: %s: A list of placeholders found to be a problem. */
+ __( 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %s' ),
+ implode( ', ', $conflicts )
+ ),
+ '6.2.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 ) {
- // If the passed query only expected one argument, but the wrong number of arguments were sent as an array, bail.
</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 ) {
+ /*
+ * If the passed query only expected one argument,
+ * but the wrong number of arguments was sent as an array, bail.
+ */
</ins><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="cx" style="display: block; padding: 0 10px"> 'wpdb::prepare',
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1509,7 +1711,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">@@ -1519,9 +1721,18 @@
</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;
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ for ( $i = 2, $l = $split_query_count; $i < $l; $i += 3 ) {
+ // Assume a leading number is for a numbered placeholder, e.g. '%3$s'.
+ $argnum = (int) substr( $split_query[ $i ], 1 );
+
+ 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">@@ -1529,9 +1740,36 @@
</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'
+ );
+
+ // Preserving old behavior, where values are escaped as strings.
+ $value = '';
+ }
+
+ $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">@@ -3779,11 +4017,13 @@
</span><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.2.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'.
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * 'subqueries', 'set_charset', 'utf8mb4', 'utf8mb4_520',
+ * or 'identifier_placeholders'.
</ins><span class="cx" style="display: block; padding: 0 10px"> * @return bool True when the database feature is supported, false otherwise.
</span><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="lines" style="display: block; padding: 0 10px; color: #888">@@ -3828,6 +4068,12 @@
</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( $db_version, '5.6', '>=' );
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ case 'identifier_placeholders': // @since 6.2.0
+ /*
+ * As of WordPress 6.2, 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 2023-01-27 15:53:15 UTC (rev 55150)
+++ trunk/tests/phpunit/tests/db.php 2023-01-27 18:47:53 UTC (rev 55151)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -494,9 +494,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">@@ -1515,7 +1517,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> public function test_prepare_with_placeholders_and_individual_args( $sql, $values, $incorrect_usage, $expected ) {
</span><span class="cx" style="display: block; padding: 0 10px"> global $wpdb;
</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 ( $incorrect_usage ) {
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( is_string( $incorrect_usage ) || true === $incorrect_usage ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->setExpectedIncorrectUsage( 'wpdb::prepare' );
</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">@@ -1525,7 +1527,11 @@
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> // phpcs:ignore WordPress.DB.PreparedSQL
</span><span class="cx" style="display: block; padding: 0 10px"> $sql = $wpdb->prepare( $sql, ...$values );
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertSame( $expected, $sql );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertSame( $expected, $sql, 'The expected SQL does not match' );
+
+ if ( is_string( $incorrect_usage ) && array_key_exists( 'wpdb::prepare', $this->caught_doing_it_wrong ) ) {
+ $this->assertStringContainsString( $incorrect_usage, $this->caught_doing_it_wrong['wpdb::prepare'], 'The "_doing_it_wrong" message does not match' );
+ }
</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><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1534,7 +1540,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> public function test_prepare_with_placeholders_and_array_args( $sql, $values, $incorrect_usage, $expected ) {
</span><span class="cx" style="display: block; padding: 0 10px"> global $wpdb;
</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 ( $incorrect_usage ) {
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ if ( is_string( $incorrect_usage ) || true === $incorrect_usage ) {
</ins><span class="cx" style="display: block; padding: 0 10px"> $this->setExpectedIncorrectUsage( 'wpdb::prepare' );
</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">@@ -1544,7 +1550,11 @@
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> // phpcs:ignore WordPress.DB.PreparedSQL
</span><span class="cx" style="display: block; padding: 0 10px"> $sql = $wpdb->prepare( $sql, $values );
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- $this->assertSame( $expected, $sql );
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ $this->assertSame( $expected, $sql, 'The expected SQL does not match' );
+
+ if ( is_string( $incorrect_usage ) && array_key_exists( 'wpdb::prepare', $this->caught_doing_it_wrong ) ) {
+ $this->assertStringContainsString( $incorrect_usage, $this->caught_doing_it_wrong['wpdb::prepare'], 'The "_doing_it_wrong" message does not match' );
+ }
</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"> public function data_prepare_with_placeholders() {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1703,24 +1713,37 @@
</span><span class="cx" style="display: block; padding: 0 10px"> true,
</span><span class="cx" style="display: block; padding: 0 10px"> "'{$placeholder_escape}'{$placeholder_escape}s",
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+
+ /*
+ * @ticket 56933.
+ * When preparing a '%%%s%%', test that the inserted value
+ * is not wrapped in single quotes between the 2 "%".
+ */
</ins><span class="cx" style="display: block; padding: 0 10px"> array(
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- "'%'%%s%s",
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ '%%s %d',
+ 1,
+ false,
+ "{$placeholder_escape}s 1",
+ ),
+ array(
+ '%%%s',
</ins><span class="cx" style="display: block; padding: 0 10px"> 'hello',
</span><span class="cx" style="display: block; padding: 0 10px"> false,
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- "'{$placeholder_escape}'{$placeholder_escape}s'hello'",
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ "{$placeholder_escape}hello",
</ins><span class="cx" style="display: block; padding: 0 10px"> ),
</span><span class="cx" style="display: block; padding: 0 10px"> array(
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- "'%'%%s %s",
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ '%%%%s',
</ins><span class="cx" style="display: block; padding: 0 10px"> 'hello',
</span><span class="cx" style="display: block; padding: 0 10px"> false,
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- "'{$placeholder_escape}'{$placeholder_escape}s 'hello'",
</del><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ "{$placeholder_escape}{$placeholder_escape}s",
</ins><span class="cx" style="display: block; padding: 0 10px"> ),
</span><del style="background-color: #fdd; text-decoration:none; display:block; padding: 0 10px">- /*
- * @ticket 56933.
- * When preparing a '%%%s%%', test that the inserted value
- * is not wrapped in single quotes between the 2 hex values.
- */
</del><span class="cx" style="display: block; padding: 0 10px"> array(
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ '%%%%%s',
+ 'hello',
+ false,
+ "{$placeholder_escape}{$placeholder_escape}hello",
+ ),
+ array(
</ins><span class="cx" style="display: block; padding: 0 10px"> '%%%s%%',
</span><span class="cx" style="display: block; padding: 0 10px"> 'hello',
</span><span class="cx" style="display: block; padding: 0 10px"> false,
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -1727,15 +1750,374 @@
</span><span class="cx" style="display: block; padding: 0 10px"> "{$placeholder_escape}hello{$placeholder_escape}",
</span><span class="cx" style="display: block; padding: 0 10px"> ),
</span><span class="cx" style="display: block; padding: 0 10px"> array(
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ "'%'%%s%s",
+ 'hello',
+ false,
+ "'{$placeholder_escape}'{$placeholder_escape}s'hello'",
+ ),
+ array(
+ "'%'%%s %s",
+ 'hello',
+ false,
+ "'{$placeholder_escape}'{$placeholder_escape}s 'hello'",
+ ),
+ array(
</ins><span class="cx" style="display: block; padding: 0 10px"> "'%-'#5s' '%'#-+-5s'",
</span><span class="cx" style="display: block; padding: 0 10px"> array( 'hello', 'foo' ),
</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">+
+ /*
+ * Before WP 6.2 the "force floats to be locale-unaware" RegEx didn't
+ * convert "%%%f" to "%%%F" (note the uppercase F).
+ * This was because it didn't check to see if the leading "%" was escaped.
+ * And because the "Escape any unescaped percents" RegEx used "[sdF]" in its
+ * negative lookahead assertion, when there was an odd number of "%", it added
+ * an extra "%", to give the fully escaped "%%%%f" (not a placeholder).
+ */
+ array(
+ '%f OR id = %d',
+ array( 3, 5 ),
+ false,
+ '3.000000 OR id = 5',
+ ),
+ array(
+ '%%f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}f OR id = 5",
+ ),
+ array(
+ '%%%f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}{$placeholder_escape}f OR id = 5",
+ ),
+ array(
+ '%%%%f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}{$placeholder_escape}f OR id = 5",
+ ),
+ array(
+ "WHERE id = %d AND content LIKE '%.4f'",
+ array( 1, 2 ),
+ false,
+ "WHERE id = 1 AND content LIKE '2.0000'",
+ ),
+ array(
+ "WHERE id = %d AND content LIKE '%%.4f'",
+ array( 1 ),
+ false,
+ "WHERE id = 1 AND content LIKE '{$placeholder_escape}.4f'",
+ ),
+ array(
+ "WHERE id = %d AND content LIKE '%%%.4f'",
+ array( 1 ),
+ false,
+ "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}.4f'",
+ ),
+ array(
+ "WHERE id = %d AND content LIKE '%%%%.4f'",
+ array( 1 ),
+ false,
+ "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}.4f'",
+ ),
+ array(
+ "WHERE id = %d AND content LIKE '%%%%%.4f'",
+ array( 1 ),
+ false,
+ "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}{$placeholder_escape}.4f'",
+ ),
+ array(
+ '%.4f',
+ array( 1 ),
+ false,
+ '1.0000',
+ ),
+ array(
+ '%.4f OR id = %d',
+ array( 1, 5 ),
+ false,
+ '1.0000 OR id = 5',
+ ),
+ array(
+ '%%.4f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}.4f OR id = 5",
+ ),
+ array(
+ '%%%.4f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}{$placeholder_escape}.4f OR id = 5",
+ ),
+ array(
+ '%%%%.4f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}{$placeholder_escape}.4f OR id = 5",
+ ),
+ array(
+ '%%%%%.4f OR id = %d',
+ array( 5 ),
+ false,
+ "{$placeholder_escape}{$placeholder_escape}{$placeholder_escape}.4f OR id = 5",
+ ),
+
+ /*
+ * @ticket 52506.
+ * Adding an escape method for Identifiers (e.g. table/field names).
+ */
+ 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_{$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 ),
+ 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %i and %2$s',
+ 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.
+ ),
+ array(
+ 'WHERE %2$i IN ( %s , %s ) LIMIT 1',
+ array( 'a', 'b' ),
+ 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %2$i and %s',
+ null,
+ ),
+ array(
+ 'WHERE %1$i = %1$s',
+ array( 'a', 'b' ),
+ 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s',
+ null,
+ ),
+ array(
+ 'WHERE %1$i = %1$s OR %2$i = %2$s',
+ array( 'a', 'b' ),
+ 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s, %2$i and %2$s',
+ null,
+ ),
+ array(
+ 'WHERE %1$i = %1$s OR %2$i = %1$s',
+ array( 'a', 'b' ),
+ 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s and %1$s',
+ null,
+ ),
</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><span class="cx" style="display: block; padding: 0 10px"> /**
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ * The wpdb->allow_unsafe_unquoted_parameters is true (for now), purely for backwards compatibility reasons.
+ *
+ * @ticket 52506
+ *
+ * @dataProvider data_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property
+ *
+ * @covers wpdb::prepare
+ *
+ * @param bool $allow Whether to allow unsafe unquoted parameters.
+ * @param string $sql The SQL to prepare.
+ * @param array $values The values for prepare.
+ * @param string $expected The expected prepared parameters.
+ */
+ public function test_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property( $allow, $sql, $values, $expected ) {
+ global $wpdb;
+
+ $default = $wpdb->allow_unsafe_unquoted_parameters;
+
+ $property = new ReflectionProperty( $wpdb, 'allow_unsafe_unquoted_parameters' );
+ $property->setAccessible( true );
+ $property->setValue( $wpdb, $allow );
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $actual = $wpdb->prepare( $sql, $values );
+
+ // Reset.
+ $property->setValue( $wpdb, $default );
+ $property->setAccessible( false );
+
+ $this->assertSame( $expected, $actual );
+ }
+
+ /**
+ * Data provider for test_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property().
+ *
+ * @return array[]
+ */
+ public function data_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property() {
+ global $wpdb;
+
+ $placeholder_escape = $wpdb->placeholder_escape();
+
+ return array(
+
+ 'numbered-true-1' => array(
+ 'allow' => true,
+ 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)',
+ 'values' => array( 'field_a', 'string_a', 'field_b', 'string_b' ),
+ 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = string_b)',
+ ),
+ 'numbered-false-1' => array(
+ 'allow' => false,
+ 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)',
+ 'values' => array( 'field_a', 'string_a', 'field_b', 'string_b' ),
+ 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = \'string_b\')',
+ ),
+ 'numbered-true-2' => array(
+ 'allow' => true,
+ 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)',
+ 'values' => array( 'field_a', 'string_a', 'field_b', '0 OR EvilSQL' ),
+ 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = 0 OR EvilSQL)',
+ ),
+ 'numbered-false-2' => array(
+ 'allow' => false,
+ 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)',
+ 'values' => array( 'field_a', 'string_a', 'field_b', '0 OR EvilSQL' ),
+ 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = \'0 OR EvilSQL\')',
+ ),
+
+ 'format-true-1' => array(
+ 'allow' => true,
+ 'sql' => 'WHERE (%10i = %10s)',
+ 'values' => array( 'field_a', 'string_a' ),
+ 'expected' => 'WHERE (` field_a` = string_a)',
+ ),
+ 'format-false-1' => array(
+ 'allow' => false,
+ 'sql' => 'WHERE (%10i = %10s)',
+ 'values' => array( 'field_a', 'string_a' ),
+ 'expected' => 'WHERE (` field_a` = \' string_a\')',
+ ),
+ 'format-true-2' => array(
+ 'allow' => true,
+ 'sql' => 'WHERE (%10i = %10s)',
+ 'values' => array( 'field_a', '0 OR EvilSQL' ),
+ 'expected' => 'WHERE (` field_a` = 0 OR EvilSQL)',
+ ),
+ 'format-false-2' => array(
+ 'allow' => false,
+ 'sql' => 'WHERE (%10i = %10s)',
+ 'values' => array( 'field_a', '0 OR EvilSQL' ),
+ 'expected' => 'WHERE (` field_a` = \'0 OR EvilSQL\')',
+ ),
+
+ 'escaped-true-1' => array(
+ 'allow' => true,
+ 'sql' => 'SELECT 9%%%s',
+ 'values' => array( '7' ),
+ 'expected' => "SELECT 9{$placeholder_escape}7", // SELECT 9%7.
+ ),
+ 'escaped-false-1' => array(
+ 'allow' => false,
+ 'sql' => 'SELECT 9%%%s',
+ 'values' => array( '7' ),
+ 'expected' => "SELECT 9{$placeholder_escape}'7'", // SELECT 9%'7'.
+ ),
+ 'escaped-true-2' => array(
+ 'allow' => true,
+ 'sql' => 'SELECT 9%%%s',
+ 'values' => array( '7 OR EvilSQL' ),
+ 'expected' => "SELECT 9{$placeholder_escape}7 OR EvilSQL", // SELECT 9%7 OR EvilSQL.
+ ),
+ 'escaped-false-2' => array(
+ 'allow' => false,
+ 'sql' => 'SELECT 9%%%s',
+ 'values' => array( '7 OR EvilSQL' ),
+ 'expected' => "SELECT 9{$placeholder_escape}'7 OR EvilSQL'", // SELECT 9%'7 OR EvilSQL'.
+ ),
+
+ );
+ }
+
+ /**
</ins><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 class="cx" style="display: block; padding: 0 10px"> public function test_escape_and_prepare( $escape, $sql, $values, $incorrect_usage, $expected ) {
</span></span></pre>
</div>
</div>
</body>
</html>