[wp-trac] [WordPress Trac] #52506: Add escaping method for table names in SQL queries

[WordPress Trac]

#52506: Add escaping method for table names in SQL queries
--------------------------+------------------------------
 Reporter:  tellyworth    |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:
 Severity:  normal        |  Resolution:
 Keywords:  dev-feedback  |     Focuses:
--------------------------+------------------------------

Comment (by iandunn):

 +1 to this

 ----

 > Developers who make use of wpcs and similar tools inevitably need to
 exclude their queries from sniffer rules because they will otherwise cause
 false positive errors.

 Even that is problematic, since it opens the door for future mistakes.
 Take this for instance:

 {{{
 #!php
 $reimbursements_index = Reimbursements_Dashboard\get_index_table_name();

 // phpcs:ignore WordPressDotOrg.sniffs.DirectDB.UnescapedDBParameter --
 this is safe
 $paid_reimbursements = $wpdb->get_results( "
         SELECT blog_id, request_id, date_paid
         FROM `$reimbursements_index`
         WHERE status = 'wcb-paid'
 " );
 }}}

 That's fine, but another developer could come along 6 months later and
 change it to this:

 {{{
 #!php
 $reimbursements_index = Reimbursements_Dashboard\get_index_table_name();

 // phpcs:ignore WordPressDotOrg.sniffs.DirectDB.UnescapedDBParameter --
 this is safe
 $paid_reimbursements = $wpdb->get_results( "
         SELECT blog_id, request_id, date_paid
         FROM `$reimbursements_index`
         WHERE
                 status = 'wcb-paid' AND
                 name = " . $_GET['name']
 ) );
 }}}

 ----

 > adding an `esc_sql_table_name()` function, and also supporting one of
 the special `prepare()` formatting options

 +1

 To be descriptive, and avoid potential conflicts with future `printf()`
 formats, maybe something like:

 `$wpdb->query( $wpdb->prepare( "SELECT * FROM %table WHERE …", 'foo' ) );`
 becomes
 {{{"SELECT * FROM `foo` WHERE …"}}}

 ----

 > A modification to the `%s` character format that does not add `'` quotes
 when surrounded with backticks

 My instinct is to be a bit leery of that, since it'd be a bit more
 complicated to implement, which could introduce the potential for esoteric
 bypasses. I could be convinced that I'm being too paranoid, though.

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