[wp-trac] [WordPress Trac] #57149: get_table_from_query() doesn't properly handle a prepared escape_like() table name

[WordPress Trac]

#57149: get_table_from_query() doesn't properly handle a prepared escape_like()
table name
--------------------------+------------------------------
 Reporter:  prettyboymp   |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  multisite
--------------------------+------------------------------

Comment (by johnjamesjacoby):

 Hey @prettyboymp 👋 nice find 🔍 nice patch 🙌

 I am able to reproduce this defect in:
 * Vanilla WordPress wpdb
   * `maybe_create_table()` (not the one in `install-helper.php`)
   * `network_domain_check()`
   * `display_setup_form()`
   * `wp-admin/maint/repair.php`
 * HyperDB
 * LudicrousDB
 * BerlinDB

 Your suggested code change does successfully resolve the problem as you've
 reported it 💎

 ----

 Additional research & testing has revealed some potential tangential
 defects 😬

 1. `\` is a ''legal'' character in MySQL table names:
 {{{
 CREATE TABLE `wp\_users` LIKE `wp_users`;
 CREATE TABLE `wp\\_users` LIKE `wp\_users`;
 CREATE TABLE `wp\\\_users` LIKE `wp\\_users`;
 CREATE TABLE `wp\\\\_users` LIKE `wp\\\_users`;
 CREATE TABLE `wp\_\_\_users` LIKE `wp\\\\_users`;
 CREATE TABLE `wp\_\_\_\_users` LIKE `wp\\\\_users`;
 SHOW TABLES LIKE 'wp%users';
 }}}
 1. WordPress does not list `NO_BACKSLASH_ESCAPES` as an incompatible mode
 in `wpdb`–  #3286
 1. WordPress does not use the `ESCAPE` clause to enforce `\` as the escape
 character
 1. Documentation for
 [https://developer.wordpress.org/reference%2Fclasses%2Fwpdb%2Fprepare%2F/
 wpdb::prepare()] includes the following:
 {{{
 Literal percentage signs (`%`) in the query string must be written as
 `%%`. Percentage wildcards
 (for example, to use in LIKE syntax) must be passed via a substitution
 argument containing
 the complete LIKE string, these cannot be inserted directly in the query
 string.
 Also see wpdb::esc_like().
 }}}
   Interpretation:
   1. Emphasis: **Percentage wildcards** (to use in LIKE syntax) must be
 passed via a substitution argument containing the complete LIKE string,
 these cannot be inserted directly in the query string.
   1. Missing: The docs ''do not'' instruct to use a similar approach for
 **Underscore wildcards**, or really what approach to use for them at all.

 ----

 Conclusions:
   1. Table names & Columns in `SHOW ... LIKE %s` queries
     * ''most likely'' should not use `$wpdb->esc_like()`
     * ''most likely'' should not use `$wpdb->prepare()`
   1. Otherwise, they are mangled with unnecessary slashes
     * ...causing subsequent `$wpdb->get_var()` queries to fail...
     * ...because a table name like `wp_users` gets:
       * esc_like'ed to `wp\_users`
       * prepare'ed to `wp\\_users`
     * even though:
       * `wp_users` is adequately "wild"
       * `wp\_users` is not syntactically what was being queried
       * `wp\\_users` is not syntactically what was being queried
     * and `_` does not need escaping the way `%` does
       * I.E. `\_\_\_` needs to mean `wp\_\_\_users` and cannot mean
 `wp___users`
   1. Instead, ''most likely'', these queries should be concatenated,
 unescaped and unprepared, and manually slashed to accommodate the desired
 matching:
 {{{
 $like = 'wp\\\_\\\_\\\_\\\_users';
 $sql = 'SHOW TABLES LIKE ' . $like;
 $query = $wpdb->get_var( $sql );
 }}}
   In this way, the core `str_replace( '\\_', '_', $maybe[2] )` is
 surprisingly accurate.
   1. Perhaps, we are all doing it wrong everywhere, and a deeper
 conclusion is required? (Additionally plausible is that I've missed a
 mundane detail and all of this is wrong 🌚)

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/57149#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform