Changeset 55151
- Timestamp:
- 01/27/2023 06:47:53 PM (22 months ago)
- Location:
- trunk
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/src/wp-includes/class-wpdb.php
r54950 r55151 656 656 657 657 /** 658 * Backward compatibility, where wpdb::prepare() has not quoted formatted/argnum placeholders. 659 * 660 * This is often used for table/field names (before %i was supported), and sometimes string formatting, e.g. 661 * 662 * $wpdb->prepare( 'WHERE `%1$s` = "%2$s something %3$s" OR %1$s = "%4$-10s"', 'field_1', 'a', 'b', 'c' ); 663 * 664 * But it's risky, e.g. forgetting to add quotes, resulting in SQL Injection vulnerabilities: 665 * 666 * $wpdb->prepare( 'WHERE (id = %1s) OR (id = %2$s)', $_GET['id'], $_GET['id'] ); // ?id=id 667 * 668 * This feature is preserved while plugin authors update their code to use safer approaches: 669 * 670 * $_GET['key'] = 'a`b'; 671 * 672 * $wpdb->prepare( 'WHERE %1s = %s', $_GET['key'], $_GET['value'] ); // WHERE a`b = 'value' 673 * $wpdb->prepare( 'WHERE `%1$s` = "%2$s"', $_GET['key'], $_GET['value'] ); // WHERE `a`b` = "value" 674 * 675 * $wpdb->prepare( 'WHERE %i = %s', $_GET['key'], $_GET['value'] ); // WHERE `a``b` = 'value' 676 * 677 * While changing to false will be fine for queries not using formatted/argnum placeholders, 678 * any remaining cases are most likely going to result in SQL errors (good, in a way): 679 * 680 * $wpdb->prepare( 'WHERE %1$s = "%2$-10s"', 'my_field', 'my_value' ); 681 * true = WHERE my_field = "my_value " 682 * false = WHERE 'my_field' = "'my_value '" 683 * 684 * But there may be some queries that result in an SQL Injection vulnerability: 685 * 686 * $wpdb->prepare( 'WHERE id = %1$s', $_GET['id'] ); // ?id=id 687 * 688 * So there may need to be a `_doing_it_wrong()` phase, after we know everyone can use 689 * identifier placeholders (%i), but before this feature is disabled or removed. 690 * 691 * @since 6.2.0 692 * @var bool 693 */ 694 private $allow_unsafe_unquoted_parameters = true; 695 696 /** 658 697 * Whether to use mysqli over mysql. Default false. 659 698 * … … 764 803 'table_charset', 765 804 'check_current_query', 805 'allow_unsafe_unquoted_parameters', 766 806 ); 767 807 if ( in_array( $name, $protected_members, true ) ) { … … 1364 1404 1365 1405 /** 1406 * Quotes an identifier for a MySQL database, e.g. table/field names. 1407 * 1408 * @since 6.2.0 1409 * 1410 * @param string $identifier Identifier to escape. 1411 * @return string Escaped identifier. 1412 */ 1413 public function quote_identifier( $identifier ) { 1414 return '`' . $this->_escape_identifier_value( $identifier ) . '`'; 1415 } 1416 1417 /** 1418 * Escapes an identifier value without adding the surrounding quotes. 1419 * 1420 * - Permitted characters in quoted identifiers include the full Unicode 1421 * Basic Multilingual Plane (BMP), except U+0000. 1422 * - To quote the identifier itself, you need to double the character, e.g. `a``b`. 1423 * 1424 * @since 6.2.0 1425 * 1426 * @link https://dev.mysql.com/doc/refman/8.0/en/identifiers.html 1427 * 1428 * @param string $identifier Identifier to escape. 1429 * @return string Escaped identifier. 1430 */ 1431 private function _escape_identifier_value( $identifier ) { 1432 return str_replace( '`', '``', $identifier ); 1433 } 1434 1435 /** 1366 1436 * Prepares a SQL query for safe execution. 1367 1437 * … … 1371 1441 * - %f (float) 1372 1442 * - %s (string) 1443 * - %i (identifier, e.g. table/field names) 1373 1444 * 1374 1445 * All placeholders MUST be left unquoted in the query string. A corresponding argument … … 1403 1474 * by updating the function signature. The second parameter was changed 1404 1475 * from `$args` to `...$args`. 1476 * @since 6.2.0 Added `%i` for identifiers, e.g. table or field names. 1477 * Check support via `wpdb::has_cap( 'identifier_placeholders' )`. 1478 * This preserves compatibility with sprintf(), as the C version uses 1479 * `%d` and `$i` as a signed integer, whereas PHP only supports `%d`. 1405 1480 * 1406 1481 * @link https://www.php.net/sprintf Description of syntax. … … 1434 1509 } 1435 1510 1436 // If args were passed as an array (as in vsprintf), move them up.1437 $passed_as_array = false;1438 if ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) ) {1439 $passed_as_array = true;1440 $args = $args[0];1441 }1442 1443 foreach ( $args as $arg ) {1444 if ( ! is_scalar( $arg ) && ! is_null( $arg ) ) {1445 wp_load_translations_early();1446 _doing_it_wrong(1447 'wpdb::prepare',1448 sprintf(1449 /* translators: %s: Value type. */1450 __( 'Unsupported value type (%s).' ),1451 gettype( $arg )1452 ),1453 '4.8.2'1454 );1455 }1456 }1457 1458 1511 /* 1459 1512 * Specify the formatting allowed in a placeholder. The following are allowed: … … 1476 1529 $query = str_replace( "'%s'", '%s', $query ); // Strip any existing single quotes. 1477 1530 $query = str_replace( '"%s"', '%s', $query ); // Strip any existing double quotes. 1478 $query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s. 1479 1480 $query = preg_replace( "/(?<!%)(%($allowed_format)?f)/", '%\\2F', $query ); // Force floats to be locale-unaware. 1481 1482 $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdF]))/", '%%\\1', $query ); // Escape any unescaped percents. 1483 1484 // Count the number of valid placeholders in the query. 1485 $placeholders = preg_match_all( "/(^|[^%]|(%%)+)%($allowed_format)?[sdF]/", $query, $matches ); 1531 1532 // Escape any unescaped percents (i.e. anything unrecognised). 1533 $query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdfFi]))/", '%%\\1', $query ); 1534 1535 // Extract placeholders from the query. 1536 $split_query = preg_split( "/(^|[^%]|(?:%%)+)(%(?:$allowed_format)?[sdfFi])/", $query, -1, PREG_SPLIT_DELIM_CAPTURE ); 1537 1538 $split_query_count = count( $split_query ); 1539 1540 /* 1541 * Split always returns with 1 value before the first placeholder (even with $query = "%s"), 1542 * then 3 additional values per placeholder. 1543 */ 1544 $placeholder_count = ( ( $split_query_count - 1 ) / 3 ); 1545 1546 // If args were passed as an array, as in vsprintf(), move them up. 1547 $passed_as_array = ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) ); 1548 if ( $passed_as_array ) { 1549 $args = $args[0]; 1550 } 1551 1552 $new_query = ''; 1553 $key = 2; // Keys 0 and 1 in $split_query contain values before the first placeholder. 1554 $arg_id = 0; 1555 $arg_identifiers = array(); 1556 $arg_strings = array(); 1557 1558 while ( $key < $split_query_count ) { 1559 $placeholder = $split_query[ $key ]; 1560 1561 $format = substr( $placeholder, 1, -1 ); 1562 $type = substr( $placeholder, -1 ); 1563 1564 if ( 'f' === $type && true === $this->allow_unsafe_unquoted_parameters && str_ends_with( $split_query[ $key - 1 ], '%' ) ) { 1565 1566 /* 1567 * Before WP 6.2 the "force floats to be locale-unaware" RegEx didn't 1568 * convert "%%%f" to "%%%F" (note the uppercase F). 1569 * This was because it didn't check to see if the leading "%" was escaped. 1570 * And because the "Escape any unescaped percents" RegEx used "[sdF]" in its 1571 * negative lookahead assertion, when there was an odd number of "%", it added 1572 * an extra "%", to give the fully escaped "%%%%f" (not a placeholder). 1573 */ 1574 1575 $s = $split_query[ $key - 2 ] . $split_query[ $key - 1 ]; 1576 $k = 1; 1577 $l = strlen( $s ); 1578 while ( $k <= $l && '%' === $s[ $l - $k ] ) { 1579 $k++; 1580 } 1581 1582 $placeholder = '%' . ( $k % 2 ? '%' : '' ) . $format . $type; 1583 1584 --$placeholder_count; 1585 1586 } else { 1587 1588 // Force floats to be locale-unaware. 1589 if ( 'f' === $type ) { 1590 $type = 'F'; 1591 $placeholder = '%' . $format . $type; 1592 } 1593 1594 if ( 'i' === $type ) { 1595 $placeholder = '`%' . $format . 's`'; 1596 // Using a simple strpos() due to previous checking (e.g. $allowed_format). 1597 $argnum_pos = strpos( $format, '$' ); 1598 1599 if ( false !== $argnum_pos ) { 1600 // sprintf() argnum starts at 1, $arg_id from 0. 1601 $arg_identifiers[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 ); 1602 } else { 1603 $arg_identifiers[] = $arg_id; 1604 } 1605 } elseif ( 'd' !== $type && 'F' !== $type ) { 1606 /* 1607 * i.e. ( 's' === $type ), where 'd' and 'F' keeps $placeholder unchanged, 1608 * and we ensure string escaping is used as a safe default (e.g. even if 'x'). 1609 */ 1610 $argnum_pos = strpos( $format, '$' ); 1611 1612 if ( false !== $argnum_pos ) { 1613 $arg_strings[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 ); 1614 } else { 1615 $arg_strings[] = $arg_id; 1616 } 1617 1618 /* 1619 * Unquoted strings for backward compatibility (dangerous). 1620 * First, "numbered or formatted string placeholders (eg, %1$s, %5s)". 1621 * Second, if "%s" has a "%" before it, even if it's unrelated (e.g. "LIKE '%%%s%%'"). 1622 */ 1623 if ( true !== $this->allow_unsafe_unquoted_parameters || ( '' === $format && ! str_ends_with( $split_query[ $key - 1 ], '%' ) ) ) { 1624 $placeholder = "'%" . $format . "s'"; 1625 } 1626 } 1627 } 1628 1629 // Glue (-2), any leading characters (-1), then the new $placeholder. 1630 $new_query .= $split_query[ $key - 2 ] . $split_query[ $key - 1 ] . $placeholder; 1631 1632 $key += 3; 1633 $arg_id++; 1634 } 1635 1636 // Replace $query; and add remaining $query characters, or index 0 if there were no placeholders. 1637 $query = $new_query . $split_query[ $key - 2 ]; 1638 1639 $dual_use = array_intersect( $arg_identifiers, $arg_strings ); 1640 1641 if ( count( $dual_use ) > 0 ) { 1642 wp_load_translations_early(); 1643 1644 $used_placeholders = array(); 1645 1646 $key = 2; 1647 $arg_id = 0; 1648 // Parse again (only used when there is an error). 1649 while ( $key < $split_query_count ) { 1650 $placeholder = $split_query[ $key ]; 1651 1652 $format = substr( $placeholder, 1, -1 ); 1653 1654 $argnum_pos = strpos( $format, '$' ); 1655 1656 if ( false !== $argnum_pos ) { 1657 $arg_pos = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 ); 1658 } else { 1659 $arg_pos = $arg_id; 1660 } 1661 1662 $used_placeholders[ $arg_pos ][] = $placeholder; 1663 1664 $key += 3; 1665 $arg_id++; 1666 } 1667 1668 $conflicts = array(); 1669 foreach ( $dual_use as $arg_pos ) { 1670 $conflicts[] = implode( ' and ', $used_placeholders[ $arg_pos ] ); 1671 } 1672 1673 _doing_it_wrong( 1674 'wpdb::prepare', 1675 sprintf( 1676 /* translators: %s: A list of placeholders found to be a problem. */ 1677 __( 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %s' ), 1678 implode( ', ', $conflicts ) 1679 ), 1680 '6.2.0' 1681 ); 1682 1683 return; 1684 } 1486 1685 1487 1686 $args_count = count( $args ); 1488 1687 1489 if ( $args_count !== $placeholders ) { 1490 if ( 1 === $placeholders && $passed_as_array ) { 1491 // If the passed query only expected one argument, but the wrong number of arguments were sent as an array, bail. 1688 if ( $args_count !== $placeholder_count ) { 1689 if ( 1 === $placeholder_count && $passed_as_array ) { 1690 /* 1691 * If the passed query only expected one argument, 1692 * but the wrong number of arguments was sent as an array, bail. 1693 */ 1492 1694 wp_load_translations_early(); 1493 1695 _doing_it_wrong( … … 1510 1712 /* translators: 1: Number of placeholders, 2: Number of arguments passed. */ 1511 1713 __( 'The query does not contain the correct number of placeholders (%1$d) for the number of arguments passed (%2$d).' ), 1512 $placeholder s,1714 $placeholder_count, 1513 1715 $args_count 1514 1716 ), … … 1520 1722 * return an empty string to avoid a fatal error on PHP 8. 1521 1723 */ 1522 if ( $args_count < $placeholders ) { 1523 $max_numbered_placeholder = ! empty( $matches[3] ) ? max( array_map( 'intval', $matches[3] ) ) : 0; 1724 if ( $args_count < $placeholder_count ) { 1725 $max_numbered_placeholder = 0; 1726 1727 for ( $i = 2, $l = $split_query_count; $i < $l; $i += 3 ) { 1728 // Assume a leading number is for a numbered placeholder, e.g. '%3$s'. 1729 $argnum = (int) substr( $split_query[ $i ], 1 ); 1730 1731 if ( $max_numbered_placeholder < $argnum ) { 1732 $max_numbered_placeholder = $argnum; 1733 } 1734 } 1524 1735 1525 1736 if ( ! $max_numbered_placeholder || $args_count < $max_numbered_placeholder ) { … … 1530 1741 } 1531 1742 1532 array_walk( $args, array( $this, 'escape_by_ref' ) ); 1533 $query = vsprintf( $query, $args ); 1743 $args_escaped = array(); 1744 1745 foreach ( $args as $i => $value ) { 1746 if ( in_array( $i, $arg_identifiers, true ) ) { 1747 $args_escaped[] = $this->_escape_identifier_value( $value ); 1748 } elseif ( is_int( $value ) || is_float( $value ) ) { 1749 $args_escaped[] = $value; 1750 } else { 1751 if ( ! is_scalar( $value ) && ! is_null( $value ) ) { 1752 wp_load_translations_early(); 1753 _doing_it_wrong( 1754 'wpdb::prepare', 1755 sprintf( 1756 /* translators: %s: Value type. */ 1757 __( 'Unsupported value type (%s).' ), 1758 gettype( $value ) 1759 ), 1760 '4.8.2' 1761 ); 1762 1763 // Preserving old behavior, where values are escaped as strings. 1764 $value = ''; 1765 } 1766 1767 $args_escaped[] = $this->_real_escape( $value ); 1768 } 1769 } 1770 1771 $query = vsprintf( $query, $args_escaped ); 1534 1772 1535 1773 return $this->add_placeholder_escape( $query ); … … 3780 4018 * @since 4.1.0 Added support for the 'utf8mb4' feature. 3781 4019 * @since 4.6.0 Added support for the 'utf8mb4_520' feature. 4020 * @since 6.2.0 Added support for the 'identifier_placeholders' feature. 3782 4021 * 3783 4022 * @see wpdb::db_version() 3784 4023 * 3785 4024 * @param string $db_cap The feature to check for. Accepts 'collation', 'group_concat', 3786 * 'subqueries', 'set_charset', 'utf8mb4', or 'utf8mb4_520'. 4025 * 'subqueries', 'set_charset', 'utf8mb4', 'utf8mb4_520', 4026 * or 'identifier_placeholders'. 3787 4027 * @return bool True when the database feature is supported, false otherwise. 3788 4028 */ … … 3829 4069 case 'utf8mb4_520': // @since 4.6.0 3830 4070 return version_compare( $db_version, '5.6', '>=' ); 4071 case 'identifier_placeholders': // @since 6.2.0 4072 /* 4073 * As of WordPress 6.2, wpdb::prepare() supports identifiers via '%i', 4074 * e.g. table/field names. 4075 */ 4076 return true; 3831 4077 } 3832 4078 -
trunk/tests/phpunit/tests/db.php
r54733 r55151 495 495 $this->assertTrue( $wpdb->has_cap( 'group_concat' ) ); 496 496 $this->assertTrue( $wpdb->has_cap( 'subqueries' ) ); 497 $this->assertTrue( $wpdb->has_cap( 'identifier_placeholders' ) ); 497 498 $this->assertTrue( $wpdb->has_cap( 'COLLATION' ) ); 498 499 $this->assertTrue( $wpdb->has_cap( 'GROUP_CONCAT' ) ); 499 500 $this->assertTrue( $wpdb->has_cap( 'SUBQUERIES' ) ); 501 $this->assertTrue( $wpdb->has_cap( 'IDENTIFIER_PLACEHOLDERS' ) ); 500 502 $this->assertSame( 501 503 version_compare( $wpdb->db_version(), '5.0.7', '>=' ), … … 1516 1518 global $wpdb; 1517 1519 1518 if ( $incorrect_usage ) {1520 if ( is_string( $incorrect_usage ) || true === $incorrect_usage ) { 1519 1521 $this->setExpectedIncorrectUsage( 'wpdb::prepare' ); 1520 1522 } … … 1526 1528 // phpcs:ignore WordPress.DB.PreparedSQL 1527 1529 $sql = $wpdb->prepare( $sql, ...$values ); 1528 $this->assertSame( $expected, $sql ); 1530 $this->assertSame( $expected, $sql, 'The expected SQL does not match' ); 1531 1532 if ( is_string( $incorrect_usage ) && array_key_exists( 'wpdb::prepare', $this->caught_doing_it_wrong ) ) { 1533 $this->assertStringContainsString( $incorrect_usage, $this->caught_doing_it_wrong['wpdb::prepare'], 'The "_doing_it_wrong" message does not match' ); 1534 } 1529 1535 } 1530 1536 … … 1535 1541 global $wpdb; 1536 1542 1537 if ( $incorrect_usage ) {1543 if ( is_string( $incorrect_usage ) || true === $incorrect_usage ) { 1538 1544 $this->setExpectedIncorrectUsage( 'wpdb::prepare' ); 1539 1545 } … … 1545 1551 // phpcs:ignore WordPress.DB.PreparedSQL 1546 1552 $sql = $wpdb->prepare( $sql, $values ); 1547 $this->assertSame( $expected, $sql ); 1553 $this->assertSame( $expected, $sql, 'The expected SQL does not match' ); 1554 1555 if ( is_string( $incorrect_usage ) && array_key_exists( 'wpdb::prepare', $this->caught_doing_it_wrong ) ) { 1556 $this->assertStringContainsString( $incorrect_usage, $this->caught_doing_it_wrong['wpdb::prepare'], 'The "_doing_it_wrong" message does not match' ); 1557 } 1548 1558 } 1549 1559 … … 1704 1714 "'{$placeholder_escape}'{$placeholder_escape}s", 1705 1715 ), 1706 array( 1707 "'%'%%s%s", 1708 'hello', 1709 false, 1710 "'{$placeholder_escape}'{$placeholder_escape}s'hello'", 1711 ), 1712 array( 1713 "'%'%%s %s", 1714 'hello', 1715 false, 1716 "'{$placeholder_escape}'{$placeholder_escape}s 'hello'", 1717 ), 1716 1718 1717 /* 1719 1718 * @ticket 56933. 1720 1719 * When preparing a '%%%s%%', test that the inserted value 1721 * is not wrapped in single quotes between the 2 hex values.1720 * is not wrapped in single quotes between the 2 "%". 1722 1721 */ 1722 array( 1723 '%%s %d', 1724 1, 1725 false, 1726 "{$placeholder_escape}s 1", 1727 ), 1728 array( 1729 '%%%s', 1730 'hello', 1731 false, 1732 "{$placeholder_escape}hello", 1733 ), 1734 array( 1735 '%%%%s', 1736 'hello', 1737 false, 1738 "{$placeholder_escape}{$placeholder_escape}s", 1739 ), 1740 array( 1741 '%%%%%s', 1742 'hello', 1743 false, 1744 "{$placeholder_escape}{$placeholder_escape}hello", 1745 ), 1723 1746 array( 1724 1747 '%%%s%%', … … 1728 1751 ), 1729 1752 array( 1753 "'%'%%s%s", 1754 'hello', 1755 false, 1756 "'{$placeholder_escape}'{$placeholder_escape}s'hello'", 1757 ), 1758 array( 1759 "'%'%%s %s", 1760 'hello', 1761 false, 1762 "'{$placeholder_escape}'{$placeholder_escape}s 'hello'", 1763 ), 1764 array( 1730 1765 "'%-'#5s' '%'#-+-5s'", 1731 1766 array( 'hello', 'foo' ), … … 1733 1768 "'hello' 'foo##'", 1734 1769 ), 1770 1771 /* 1772 * Before WP 6.2 the "force floats to be locale-unaware" RegEx didn't 1773 * convert "%%%f" to "%%%F" (note the uppercase F). 1774 * This was because it didn't check to see if the leading "%" was escaped. 1775 * And because the "Escape any unescaped percents" RegEx used "[sdF]" in its 1776 * negative lookahead assertion, when there was an odd number of "%", it added 1777 * an extra "%", to give the fully escaped "%%%%f" (not a placeholder). 1778 */ 1779 array( 1780 '%f OR id = %d', 1781 array( 3, 5 ), 1782 false, 1783 '3.000000 OR id = 5', 1784 ), 1785 array( 1786 '%%f OR id = %d', 1787 array( 5 ), 1788 false, 1789 "{$placeholder_escape}f OR id = 5", 1790 ), 1791 array( 1792 '%%%f OR id = %d', 1793 array( 5 ), 1794 false, 1795 "{$placeholder_escape}{$placeholder_escape}f OR id = 5", 1796 ), 1797 array( 1798 '%%%%f OR id = %d', 1799 array( 5 ), 1800 false, 1801 "{$placeholder_escape}{$placeholder_escape}f OR id = 5", 1802 ), 1803 array( 1804 "WHERE id = %d AND content LIKE '%.4f'", 1805 array( 1, 2 ), 1806 false, 1807 "WHERE id = 1 AND content LIKE '2.0000'", 1808 ), 1809 array( 1810 "WHERE id = %d AND content LIKE '%%.4f'", 1811 array( 1 ), 1812 false, 1813 "WHERE id = 1 AND content LIKE '{$placeholder_escape}.4f'", 1814 ), 1815 array( 1816 "WHERE id = %d AND content LIKE '%%%.4f'", 1817 array( 1 ), 1818 false, 1819 "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}.4f'", 1820 ), 1821 array( 1822 "WHERE id = %d AND content LIKE '%%%%.4f'", 1823 array( 1 ), 1824 false, 1825 "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}.4f'", 1826 ), 1827 array( 1828 "WHERE id = %d AND content LIKE '%%%%%.4f'", 1829 array( 1 ), 1830 false, 1831 "WHERE id = 1 AND content LIKE '{$placeholder_escape}{$placeholder_escape}{$placeholder_escape}.4f'", 1832 ), 1833 array( 1834 '%.4f', 1835 array( 1 ), 1836 false, 1837 '1.0000', 1838 ), 1839 array( 1840 '%.4f OR id = %d', 1841 array( 1, 5 ), 1842 false, 1843 '1.0000 OR id = 5', 1844 ), 1845 array( 1846 '%%.4f OR id = %d', 1847 array( 5 ), 1848 false, 1849 "{$placeholder_escape}.4f OR id = 5", 1850 ), 1851 array( 1852 '%%%.4f OR id = %d', 1853 array( 5 ), 1854 false, 1855 "{$placeholder_escape}{$placeholder_escape}.4f OR id = 5", 1856 ), 1857 array( 1858 '%%%%.4f OR id = %d', 1859 array( 5 ), 1860 false, 1861 "{$placeholder_escape}{$placeholder_escape}.4f OR id = 5", 1862 ), 1863 array( 1864 '%%%%%.4f OR id = %d', 1865 array( 5 ), 1866 false, 1867 "{$placeholder_escape}{$placeholder_escape}{$placeholder_escape}.4f OR id = 5", 1868 ), 1869 1870 /* 1871 * @ticket 52506. 1872 * Adding an escape method for Identifiers (e.g. table/field names). 1873 */ 1874 array( 1875 'SELECT * FROM %i WHERE %i = %d;', 1876 array( 'my_table', 'my_field', 321 ), 1877 false, 1878 'SELECT * FROM `my_table` WHERE `my_field` = 321;', 1879 ), 1880 array( 1881 'WHERE %i = %d;', 1882 array( 'evil_`_field', 321 ), 1883 false, 1884 'WHERE `evil_``_field` = 321;', // To quote the identifier itself, then you need to double the character, e.g. `a``b`. 1885 ), 1886 array( 1887 'WHERE %i = %d;', 1888 array( 'evil_````````_field', 321 ), 1889 false, 1890 'WHERE `evil_````````````````_field` = 321;', 1891 ), 1892 array( 1893 'WHERE %i = %d;', 1894 array( '``evil_field``', 321 ), 1895 false, 1896 'WHERE `````evil_field````` = 321;', 1897 ), 1898 array( 1899 'WHERE %i = %d;', 1900 array( 'evil\'field', 321 ), 1901 false, 1902 'WHERE `evil\'field` = 321;', 1903 ), 1904 array( 1905 'WHERE %i = %d;', 1906 array( 'evil_\``_field', 321 ), 1907 false, 1908 'WHERE `evil_\````_field` = 321;', 1909 ), 1910 array( 1911 'WHERE %i = %d;', 1912 array( 'evil_%s_field', 321 ), 1913 false, 1914 "WHERE `evil_{$placeholder_escape}s_field` = 321;", 1915 ), 1916 array( 1917 'WHERE %i = %d;', 1918 array( 'value`', 321 ), 1919 false, 1920 'WHERE `value``` = 321;', 1921 ), 1922 array( 1923 'WHERE `%i = %d;', 1924 array( ' AND evil_value', 321 ), 1925 false, 1926 'WHERE `` AND evil_value` = 321;', // Won't run (SQL parse error: "Unclosed quote"). 1927 ), 1928 array( 1929 'WHERE %i` = %d;', 1930 array( 'evil_value -- ', 321 ), 1931 false, 1932 'WHERE `evil_value -- `` = 321;', // Won't run (SQL parse error: "Unclosed quote"). 1933 ), 1934 array( 1935 'WHERE `%i`` = %d;', 1936 array( ' AND true -- ', 321 ), 1937 false, 1938 'WHERE `` AND true -- ``` = 321;', // Won't run (Unknown column ''). 1939 ), 1940 array( 1941 'WHERE ``%i` = %d;', 1942 array( ' AND true -- ', 321 ), 1943 false, 1944 'WHERE ``` AND true -- `` = 321;', // Won't run (SQL parse error: "Unclosed quote"). 1945 ), 1946 array( 1947 'WHERE %2$i = %1$d;', 1948 array( '1', 'two' ), 1949 false, 1950 'WHERE `two` = 1;', 1951 ), 1952 array( 1953 'WHERE \'%i\' = 1 AND "%i" = 2 AND `%i` = 3 AND ``%i`` = 4 AND %15i = 5', 1954 array( 'my_field1', 'my_field2', 'my_field3', 'my_field4', 'my_field5' ), 1955 false, 1956 '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). 1957 ), 1958 array( 1959 'WHERE id = %d AND %i LIKE %2$s LIMIT 1', 1960 array( 123, 'field -- ', false ), 1961 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %i and %2$s', 1962 null, // Should be rejected, otherwise the `%1$s` could use Identifier escaping, e.g. 'WHERE `field -- ` LIKE field -- LIMIT 1' (thanks @vortfu). 1963 ), 1964 array( 1965 'WHERE %i LIKE %s LIMIT 1', 1966 array( "field' -- ", "field' -- " ), 1967 false, 1968 "WHERE `field' -- ` LIKE 'field\' -- ' LIMIT 1", // In contrast to the above, Identifier vs String escaping is used. 1969 ), 1970 array( 1971 'WHERE %2$i IN ( %s , %s ) LIMIT 1', 1972 array( 'a', 'b' ), 1973 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %2$i and %s', 1974 null, 1975 ), 1976 array( 1977 'WHERE %1$i = %1$s', 1978 array( 'a', 'b' ), 1979 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s', 1980 null, 1981 ), 1982 array( 1983 'WHERE %1$i = %1$s OR %2$i = %2$s', 1984 array( 'a', 'b' ), 1985 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s, %2$i and %2$s', 1986 null, 1987 ), 1988 array( 1989 'WHERE %1$i = %1$s OR %2$i = %1$s', 1990 array( 'a', 'b' ), 1991 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %1$i and %1$s and %1$s', 1992 null, 1993 ), 1994 ); 1995 } 1996 1997 /** 1998 * The wpdb->allow_unsafe_unquoted_parameters is true (for now), purely for backwards compatibility reasons. 1999 * 2000 * @ticket 52506 2001 * 2002 * @dataProvider data_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property 2003 * 2004 * @covers wpdb::prepare 2005 * 2006 * @param bool $allow Whether to allow unsafe unquoted parameters. 2007 * @param string $sql The SQL to prepare. 2008 * @param array $values The values for prepare. 2009 * @param string $expected The expected prepared parameters. 2010 */ 2011 public function test_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property( $allow, $sql, $values, $expected ) { 2012 global $wpdb; 2013 2014 $default = $wpdb->allow_unsafe_unquoted_parameters; 2015 2016 $property = new ReflectionProperty( $wpdb, 'allow_unsafe_unquoted_parameters' ); 2017 $property->setAccessible( true ); 2018 $property->setValue( $wpdb, $allow ); 2019 2020 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 2021 $actual = $wpdb->prepare( $sql, $values ); 2022 2023 // Reset. 2024 $property->setValue( $wpdb, $default ); 2025 $property->setAccessible( false ); 2026 2027 $this->assertSame( $expected, $actual ); 2028 } 2029 2030 /** 2031 * Data provider for test_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property(). 2032 * 2033 * @return array[] 2034 */ 2035 public function data_prepare_should_respect_the_allow_unsafe_unquoted_parameters_property() { 2036 global $wpdb; 2037 2038 $placeholder_escape = $wpdb->placeholder_escape(); 2039 2040 return array( 2041 2042 'numbered-true-1' => array( 2043 'allow' => true, 2044 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)', 2045 'values' => array( 'field_a', 'string_a', 'field_b', 'string_b' ), 2046 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = string_b)', 2047 ), 2048 'numbered-false-1' => array( 2049 'allow' => false, 2050 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)', 2051 'values' => array( 'field_a', 'string_a', 'field_b', 'string_b' ), 2052 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = \'string_b\')', 2053 ), 2054 'numbered-true-2' => array( 2055 'allow' => true, 2056 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)', 2057 'values' => array( 'field_a', 'string_a', 'field_b', '0 OR EvilSQL' ), 2058 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = 0 OR EvilSQL)', 2059 ), 2060 'numbered-false-2' => array( 2061 'allow' => false, 2062 'sql' => 'WHERE (%i = %s) OR (%3$i = %4$s)', 2063 'values' => array( 'field_a', 'string_a', 'field_b', '0 OR EvilSQL' ), 2064 'expected' => 'WHERE (`field_a` = \'string_a\') OR (`field_b` = \'0 OR EvilSQL\')', 2065 ), 2066 2067 'format-true-1' => array( 2068 'allow' => true, 2069 'sql' => 'WHERE (%10i = %10s)', 2070 'values' => array( 'field_a', 'string_a' ), 2071 'expected' => 'WHERE (` field_a` = string_a)', 2072 ), 2073 'format-false-1' => array( 2074 'allow' => false, 2075 'sql' => 'WHERE (%10i = %10s)', 2076 'values' => array( 'field_a', 'string_a' ), 2077 'expected' => 'WHERE (` field_a` = \' string_a\')', 2078 ), 2079 'format-true-2' => array( 2080 'allow' => true, 2081 'sql' => 'WHERE (%10i = %10s)', 2082 'values' => array( 'field_a', '0 OR EvilSQL' ), 2083 'expected' => 'WHERE (` field_a` = 0 OR EvilSQL)', 2084 ), 2085 'format-false-2' => array( 2086 'allow' => false, 2087 'sql' => 'WHERE (%10i = %10s)', 2088 'values' => array( 'field_a', '0 OR EvilSQL' ), 2089 'expected' => 'WHERE (` field_a` = \'0 OR EvilSQL\')', 2090 ), 2091 2092 'escaped-true-1' => array( 2093 'allow' => true, 2094 'sql' => 'SELECT 9%%%s', 2095 'values' => array( '7' ), 2096 'expected' => "SELECT 9{$placeholder_escape}7", // SELECT 9%7. 2097 ), 2098 'escaped-false-1' => array( 2099 'allow' => false, 2100 'sql' => 'SELECT 9%%%s', 2101 'values' => array( '7' ), 2102 'expected' => "SELECT 9{$placeholder_escape}'7'", // SELECT 9%'7'. 2103 ), 2104 'escaped-true-2' => array( 2105 'allow' => true, 2106 'sql' => 'SELECT 9%%%s', 2107 'values' => array( '7 OR EvilSQL' ), 2108 'expected' => "SELECT 9{$placeholder_escape}7 OR EvilSQL", // SELECT 9%7 OR EvilSQL. 2109 ), 2110 'escaped-false-2' => array( 2111 'allow' => false, 2112 'sql' => 'SELECT 9%%%s', 2113 'values' => array( '7 OR EvilSQL' ), 2114 'expected' => "SELECT 9{$placeholder_escape}'7 OR EvilSQL'", // SELECT 9%'7 OR EvilSQL'. 2115 ), 2116 1735 2117 ); 1736 2118 }
Note: See TracChangeset
for help on using the changeset viewer.