Feature #22630
openPrevent SHOW TABLES queries
0%
Description
SHOW TABLES requires reference to INFORMATION_SCHEMA. On our very large database, this kind of query is extremely slow, to the point where it can crash our database. This has happened most recently with litespeed-cache #22618, but it has happened in the past with other plugins: Gravity Forms #15242, #21031; Editoria11y #20828.
A quick search of the plugins directory shows dozens more SHOW TABLES query, all of which could cause similar problems in the right combination of situations.
Unfortunately, I think the time has come when we need to introduce some sort of global solution. I see two types of approaches:
1. Pre-deployment mitigations
We could build sniffs into our CI pipeline that look for SHOW TABLES queries. Then, for each identified item, we either (a) somehow fix the plugin (using hooks if possible, forking the plugin if it's old, sending upstream patches, etc), or we (b) decide it's not a problem because the code path is never or is rarely accessed. This is pretty time-consuming, but it has the benefit of being very targeted.
2. Just-in-time "polyfill"
A more general solution is to write a 'query' filter callback that detects all instances of SHOW TABLES and swaps them out for less destructive queries. For example, $wpdb->get_var( "SHOW TABLES LIKE 'foo'" ); returns 'foo' if the table exists. We can run something like DESCRIBE 'foo' and return the table name if the result is not empty. get_col() queries would need a somewhat more complex treatment. Wildcard queries SHOW TABLES LIKE '%foo%' would likely have to be skipped, and we may discover other instances where special treatment or exclusions are necessary.
The second option is a fairly big hammer, but I wonder if it might be worth it. We could use a similar infrastructure in the future to prevent INFORMATION_SCHEMA queries or other types of problems.
Jeremy and Ray, do you have thoughts on whether it's worth pursuing this, and if one of these approaches (or something else) is preferable?