[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:
                                                 |  davidbaumwald
     Type:  defect (bug)                         |      Status:  reopened
 Priority:  normal                               |   Milestone:  6.2
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  has-unit-tests early needs-docs      |     Focuses:
  has-patch needs-testing needs-dev-note         |  performance
-------------------------------------------------+-------------------------

Comment (by ironprogrammer):

 == Test Report
 Patch tested: https://github.com/WordPress/wordpress-develop/pull/3724

 === Steps to Test
 ==== Test 1 - `%i` placeholder functionality
 1. Check out the PR or apply the patch for this ticket,
 [https://github.com/WordPress/wordpress-develop/pull/3724 PR 3724].
 2. In your test site's `wp-content` directory, create a `mu-plugins`
 folder if it does not already exist.
 3. In this new folder, create a new PHP file based on
 [https://gist.github.com/ironprogrammer/95e26a03fad6f74685c5ee0772f732c3
 this test plugin gist].
 4. In a browser, open your test site's homepage (the test does not run in
 WP admin).
 5. The test plugin should render content similar to the image provided
 below.
 6. When testing is complete, delete or move the test plugin file out of
 the `mu-plugins` folder.

 ==== Test 2 - regression for [comment:53 this ticket's revert] that was
 addressed in #56933
 1. After removing the test plugin above, in the `mu-plugins` folder,
 create a new PHP file based on
 [https://gist.github.com/hellofromtonya/de5d59a9c42820f3395667883fac3f19
 this other test plugin from #56933] (props hellofromTonya).
 2. In a browser, open your test site's homepage (this test also does not
 run in WP admin).
 3. The test plugin should render a SQL query similar to the one provided
 below.
 4. When testing is complete, delete or move the test plugin out of the
 `mu-plugins` folder.

 === Environment
 - Hardware: MacBook Pro Apple M1 Pro
 - OS: macOS 12.6.2
 - Browser: Safari 16.2
 - Database: MySQL 8.0.27
 - Server: nginx/1.23.3
 - PHP: 7.4.33
 - WordPress: 6.2-alpha-54642-src

 === Actual Results
 - ✅ Using `%i` as a placeholder in `$wpdb->prepare()` statements escapes
 table and field names with backticks.
 - ✅ Performance using `%i` to handle escaping compared with current
 methods like {{{`%1s`}}} appears consistent with previous findings.
 - ✅ Regression test shows the PR retains the fix from
 https://core.trac.wordpress.org/ticket/56933.

 === Supplemental Artifacts
 **Test 1 example plugin output**
 [[Image(https://cldup.com/pv3o2ngWQR.png, 50%)]]

 **Test 2 example plugin output: the search term "hello" in the LIKE clause
 is not quoted (i.e. `}hello{` and not `}'hello'{`)**
 {{{#!sql
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'a' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON(wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'mgmlp_media_folder' and pm.meta_key =
 '_wp_attached_file' and SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{34eb7a3049b8cba3a0981fed3048fc504dff44a188548d73f755f178c269a3f3}hello{34eb7a3049b8cba3a0981fed3048fc504dff44a188548d73f755f178c269a3f3}')
 union all
 (select wp_posts.ID, post_title, wp_mgmlp_folders.folder_id, pm.meta_value
 as attached_file, 'b' as item_type
 from wp_posts
 LEFT JOIN wp_mgmlp_folders ON( wp_posts.ID = wp_mgmlp_folders.post_id)
 LEFT JOIN wp_postmeta AS pm ON (pm.post_id = wp_posts.ID)
 LEFT JOIN wp_users AS us ON (wp_posts.post_author = us.ID)
 where post_type = 'attachment' and pm.meta_key = '_wp_attached_file' and
 SUBSTRING_INDEX(pm.meta_value, '/', -1) like
 '{34eb7a3049b8cba3a0981fed3048fc504dff44a188548d73f755f178c269a3f3}hello{34eb7a3049b8cba3a0981fed3048fc504dff44a188548d73f755f178c269a3f3}')
 order by attached_file
 }}}

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