Make WordPress Core


Ignore:
Timestamp:
06/24/2022 08:33:56 PM (20 months ago)
Author:
davidbaumwald
Message:

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 #52506.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/wp-includes/wp-db.php

    r53505 r53575  
    646646
    647647    /**
     648     * Backwards compatibility, where wpdb::prepare() has not quoted formatted/argnum placeholders.
     649     *
     650     * Historically this could be used for table/field names, or for some string formatting, e.g.
     651     *   $wpdb->prepare( 'WHERE `%1s` = "%1s something %1s" OR %1$s = "%-10s"', 'field_1', 'a', 'b', 'c' );
     652     *
     653     * But it's risky, e.g. forgetting to add quotes, resulting in SQL Injection vulnerabilities:
     654     *   $wpdb->prepare( 'WHERE (id = %1s) OR (id = %2$s)', $_GET['id'], $_GET['id'] ); // ?id=id
     655     *
     656     * This feature is preserved while plugin authors update their code to use safer approaches:
     657     *   $wpdb->prepare( 'WHERE %1s = %s', $_GET['key'], $_GET['value'] );
     658     *   $wpdb->prepare( 'WHERE %i  = %s', $_GET['key'], $_GET['value'] );
     659     *
     660     * While changing to false will be fine for queries not using formatted/argnum placeholders,
     661     * any remaining cases are most likely going to result in SQL errors (good, in a way):
     662     *   $wpdb->prepare( 'WHERE %1s = "%-10s"', 'my_field', 'my_value' );
     663     *     true  = WHERE my_field = "my_value  "
     664     *     false = WHERE 'my_field' = "'my_value  '"
     665     * But there may be some queries that result in an SQL Injection vulnerability:
     666     *   $wpdb->prepare( 'WHERE id = %1s', $_GET['id'] ); // ?id=id
     667     * So there may need to be a `_doing_it_wrong()` phase, after we know everyone can use Identifier
     668     * placeholders (%i), but before this feature is disabled or removed.
     669     *
     670     * @since 6.1.0
     671     * @var bool
     672     */
     673    private $allow_unsafe_unquoted_parameters = true;
     674
     675    /**
    648676     * Whether to use mysqli over mysql. Default false.
    649677     *
     
    13491377
    13501378    /**
     1379     * Escapes an identifier for a MySQL database (e.g. table/field names).
     1380     *
     1381     * @since 6.1.0
     1382     *
     1383     * @param string $identifier Identifier to escape.
     1384     * @return string Escaped Identifier
     1385     */
     1386    public function escape_identifier( $identifier ) {
     1387        return '`' . $this->_escape_identifier_value( $identifier ) . '`';
     1388    }
     1389
     1390    /**
     1391     * Escapes an identifier value.
     1392     *
     1393     * Escapes an identifier value without adding the surrounding quotes.
     1394     *
     1395     * - Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000
     1396     * - To quote the identifier itself, then you need to double the character, e.g. `a``b`
     1397     *
     1398     * @link https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
     1399     * @since 6.1.0
     1400     * @access private
     1401     *
     1402     * @param string $identifier Identifier to escape.
     1403     * @return string Escaped Identifier
     1404     */
     1405    private function _escape_identifier_value( $identifier ) {
     1406        return str_replace( '`', '``', $identifier );
     1407    }
     1408
     1409    /**
    13511410     * Prepares a SQL query for safe execution.
    13521411     *
     
    13561415     * - %f (float)
    13571416     * - %s (string)
     1417     * - %i (identifier, e.g. table/field names)
    13581418     *
    13591419     * All placeholders MUST be left unquoted in the query string. A corresponding argument
     
    13811441     *              by updating the function signature. The second parameter was changed
    13821442     *              from `$args` to `...$args`.
     1443     * @since 6.1.0 Added '%i' for Identifiers, e.g. table or field names.
     1444     *              Check support via `wpdb::has_cap( 'identifier_placeholders' )`
     1445     *              This preserves compatibility with sprinf, as the C version uses %d and $i
     1446     *              as a signed integer, whereas PHP only supports %d.
    13831447     *
    13841448     * @link https://www.php.net/sprintf Description of syntax.
     
    14121476        }
    14131477
    1414         // If args were passed as an array (as in vsprintf), move them up.
    1415         $passed_as_array = false;
    1416         if ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) ) {
    1417             $passed_as_array = true;
    1418             $args            = $args[0];
    1419         }
    1420 
    1421         foreach ( $args as $arg ) {
    1422             if ( ! is_scalar( $arg ) && ! is_null( $arg ) ) {
    1423                 wp_load_translations_early();
    1424                 _doing_it_wrong(
    1425                     'wpdb::prepare',
    1426                     sprintf(
    1427                         /* translators: %s: Value type. */
    1428                         __( 'Unsupported value type (%s).' ),
    1429                         gettype( $arg )
    1430                     ),
    1431                     '4.8.2'
    1432                 );
    1433             }
    1434         }
    1435 
    14361478        /*
    14371479         * Specify the formatting allowed in a placeholder. The following are allowed:
     
    14541496        $query = str_replace( "'%s'", '%s', $query ); // Strip any existing single quotes.
    14551497        $query = str_replace( '"%s"', '%s', $query ); // Strip any existing double quotes.
    1456         $query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.
    1457 
    1458         $query = preg_replace( "/(?<!%)(%($allowed_format)?f)/", '%\\2F', $query ); // Force floats to be locale-unaware.
    1459 
    1460         $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdF]))/", '%%\\1', $query ); // Escape any unescaped percents.
    1461 
    1462         // Count the number of valid placeholders in the query.
    1463         $placeholders = preg_match_all( "/(^|[^%]|(%%)+)%($allowed_format)?[sdF]/", $query, $matches );
     1498
     1499        $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdfFi]))/", '%%\\1', $query ); // Escape any unescaped percents (i.e. anything unrecognised).
     1500
     1501        // Extract placeholders from the query.
     1502        $split_query = preg_split( "/(^|[^%]|(?:%%)+)(%(?:$allowed_format)?[sdfFi])/", $query, -1, PREG_SPLIT_DELIM_CAPTURE );
     1503
     1504        $split_query_count = count( $split_query );
     1505        $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.
     1506
     1507        // If args were passed as an array (as in vsprintf), move them up.
     1508        $passed_as_array = ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) );
     1509        if ( $passed_as_array ) {
     1510            $args = $args[0];
     1511        }
     1512
     1513        $new_query       = '';
     1514        $key             = 2; // keys 0 and 1 in $split_query contain values before the first placeholder.
     1515        $arg_id          = 0;
     1516        $arg_identifiers = array();
     1517        $arg_strings     = array();
     1518        while ( $key < $split_query_count ) {
     1519            $placeholder = $split_query[ $key ];
     1520
     1521            $format = substr( $placeholder, 1, -1 );
     1522            $type   = substr( $placeholder, -1 );
     1523
     1524            if ( 'f' === $type ) { // Force floats to be locale-unaware.
     1525                $type        = 'F';
     1526                $placeholder = '%' . $format . $type;
     1527            }
     1528
     1529            if ( 'i' === $type ) {
     1530                $placeholder = '`%' . $format . 's`';
     1531                $argnum_pos  = strpos( $format, '$' ); // Using a simple strpos() due to previous checking (e.g. $allowed_format).
     1532                if ( false !== $argnum_pos ) {
     1533                    $arg_identifiers[] = ( intval( substr( $format, 0, $argnum_pos ) ) - 1 ); // sprintf argnum starts at 1, $arg_id from 0.
     1534                } else {
     1535                    $arg_identifiers[] = $arg_id;
     1536                }
     1537            } 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').
     1538                $argnum_pos = strpos( $format, '$' );
     1539                if ( false !== $argnum_pos ) {
     1540                    $arg_strings[] = ( intval( substr( $format, 0, $argnum_pos ) ) - 1 );
     1541                }
     1542                if ( true !== $this->allow_unsafe_unquoted_parameters || '' === $format ) { // Unquoted strings for backwards compatibility (dangerous).
     1543                    $placeholder = "'%" . $format . "s'";
     1544                }
     1545            }
     1546
     1547            $new_query .= $split_query[ $key - 2 ] . $split_query[ $key - 1 ] . $placeholder; // Glue (-2), any leading characters (-1), then the new $placeholder.
     1548
     1549            $key += 3;
     1550            $arg_id++;
     1551        }
     1552        $query = $new_query . $split_query[ $key - 2 ]; // Replace $query; and add remaining $query characters, or index 0 if there were no placeholders.
     1553
     1554        $dual_use = array_intersect( $arg_identifiers, $arg_strings );
     1555        if ( count( $dual_use ) ) {
     1556            wp_load_translations_early();
     1557            _doing_it_wrong(
     1558                'wpdb::prepare',
     1559                sprintf(
     1560                    /* translators: %s: A comma-separated list of arguments found to be a problem. */
     1561                    __( 'Arguments (%s) cannot be used for both String and Identifier escaping.' ),
     1562                    implode( ', ', $dual_use )
     1563                ),
     1564                '6.1.0'
     1565            );
     1566
     1567            return;
     1568        }
    14641569
    14651570        $args_count = count( $args );
    14661571
    1467         if ( $args_count !== $placeholders ) {
    1468             if ( 1 === $placeholders && $passed_as_array ) {
     1572        if ( $args_count !== $placeholder_count ) {
     1573            if ( 1 === $placeholder_count && $passed_as_array ) {
    14691574                // If the passed query only expected one argument, but the wrong number of arguments were sent as an array, bail.
    14701575                wp_load_translations_early();
     
    14871592                        /* translators: 1: Number of placeholders, 2: Number of arguments passed. */
    14881593                        __( 'The query does not contain the correct number of placeholders (%1$d) for the number of arguments passed (%2$d).' ),
    1489                         $placeholders,
     1594                        $placeholder_count,
    14901595                        $args_count
    14911596                    ),
     
    14971602                 * return an empty string to avoid a fatal error on PHP 8.
    14981603                 */
    1499                 if ( $args_count < $placeholders ) {
    1500                     $max_numbered_placeholder = ! empty( $matches[3] ) ? max( array_map( 'intval', $matches[3] ) ) : 0;
    1501 
     1604                if ( $args_count < $placeholder_count ) {
     1605                    $max_numbered_placeholder = 0;
     1606                    for ( $i = 2, $l = $split_query_count; $i < $l; $i += 3 ) {
     1607                        $argnum = intval( substr( $split_query[ $i ], 1 ) ); // Assume a leading number is for a numbered placeholder, e.g. '%3$s'.
     1608                        if ( $max_numbered_placeholder < $argnum ) {
     1609                            $max_numbered_placeholder = $argnum;
     1610                        }
     1611                    }
    15021612                    if ( ! $max_numbered_placeholder || $args_count < $max_numbered_placeholder ) {
    15031613                        return '';
     
    15071617        }
    15081618
    1509         array_walk( $args, array( $this, 'escape_by_ref' ) );
    1510         $query = vsprintf( $query, $args );
     1619        $args_escaped = array();
     1620
     1621        foreach ( $args as $i => $value ) {
     1622            if ( in_array( $i, $arg_identifiers, true ) ) {
     1623                $args_escaped[] = $this->_escape_identifier_value( $value );
     1624            } elseif ( is_int( $value ) || is_float( $value ) ) {
     1625                $args_escaped[] = $value;
     1626            } else {
     1627                if ( ! is_scalar( $value ) && ! is_null( $value ) ) {
     1628                    wp_load_translations_early();
     1629                    _doing_it_wrong(
     1630                        'wpdb::prepare',
     1631                        sprintf(
     1632                            /* translators: %s: Value type. */
     1633                            __( 'Unsupported value type (%s).' ),
     1634                            gettype( $value )
     1635                        ),
     1636                        '4.8.2'
     1637                    );
     1638                    $value = ''; // Preserving old behaviour, where values are escaped as strings.
     1639                }
     1640                $args_escaped[] = $this->_real_escape( $value );
     1641            }
     1642        }
     1643
     1644        $query = vsprintf( $query, $args_escaped );
    15111645
    15121646        return $this->add_placeholder_escape( $query );
     
    37393873
    37403874    /**
    3741      * Determines if a database supports a particular feature.
     3875     * Determine DB or WPDB support for a particular feature.
     3876     *
     3877     * Capability sniffs for the database server and current version of wpdb.
     3878     *
     3879     * Database sniffs test based on the version of MySQL the site is using.
     3880     *
     3881     * WPDB sniffs are added as new features are introduced to allow theme and plugin
     3882     * developers to determine feature support. This is to account for drop-ins which may
     3883     * introduce feature support at a different time to WordPress.
    37423884     *
    37433885     * @since 2.7.0
    37443886     * @since 4.1.0 Added support for the 'utf8mb4' feature.
    37453887     * @since 4.6.0 Added support for the 'utf8mb4_520' feature.
     3888     * @since 6.1.0 Added support for the 'identifier_placeholders' feature.
    37463889     *
    37473890     * @see wpdb::db_version()
    37483891     *
    37493892     * @param string $db_cap The feature to check for. Accepts 'collation', 'group_concat',
    3750      *                       'subqueries', 'set_charset', 'utf8mb4', or 'utf8mb4_520'.
    3751      * @return int|false Whether the database feature is supported, false otherwise.
     3893     *                       'subqueries', 'set_charset', 'utf8mb4', 'utf8mb4_520',
     3894     *                       or 'identifier_placeholders'.
     3895     * @return bool True when the database feature is supported, false otherwise.
    37523896     */
    37533897    public function has_cap( $db_cap ) {
     
    37833927            case 'utf8mb4_520': // @since 4.6.0
    37843928                return version_compare( $version, '5.6', '>=' );
     3929            case 'identifier_placeholders': // @since 6.1.0, wpdb::prepare() supports identifiers via '%i' - e.g. table/field names.
     3930                return true;
    37853931        }
    37863932
Note: See TracChangeset for help on using the changeset viewer.