[wp-trac] [WordPress Trac] #56764: Support IN and NOT IN data in wpdb::prepare()

[WordPress Trac]

#56764: Support IN and NOT IN data in wpdb::prepare()
-----------------------------+-----------------------------
 Reporter:  sc0ttkclark      |      Owner:  (none)
     Type:  feature request  |     Status:  new
 Priority:  normal           |  Milestone:  Awaiting Review
Component:  Database         |    Version:
 Severity:  normal           |   Keywords:
  Focuses:                   |
-----------------------------+-----------------------------
 == The problem

 Often times I find myself having to work with dynamic conditionals in a
 very roundabout way. The amount of logic and complexity that preparing `IN
 ()` and `NOT IN ()` statements requires can put people off from using
 `wpdb::prepare()` and I believe direct support in `prepare()` would
 encourage more developers to prepare their SQL statements properly.

 Many devs will choose to not use prepare() for those statements that have
 `IN ()` and they will choose to call things like `esc_sql()` and their
 `$wpdb->____()` counterparts. While this makes quick work for writing a
 query, it is not providing a method like `prepare()` all of the
 information to write a clean SQL query that has values properly prepared.

 Here's an example of what someone might do now:

 {{{#!php
 <?php
 /**
  * @var string[] $list_of_strings
  */

 $list_of_strings_escaped = array_map( 'esc_sql', $list_of_strings );

 $list_of_strings_sql = implode( "', '", $list_of_strings_escaped );

 $final_sql = "
     SELECT *
     FROM `{$wpdb->prefix}whatever_table`
     WHERE `whatever_column` IN ( {$list_of_strings_sql} )
 ";
 }}}

 == Using prepare for `IN ()` in core right now

 You can technically use `prepare()` right now with `IN ()` clauses with
 extra logic and smart use of `array_fill()` or manually putting `IN ( %s,
 %s, %s )` into the SQL statement yourself.

 Here's an example of that:

 {{{#!php
 <?php
 /**
  * @var string[] $list_of_strings
  * @var int $int1
  * @var int $int2
  * @var int $int3
  */

 $values_to_prepare = $list_of_strings;

 // Fill an array with the %s placeholder for each value.
 $placeholders = array_fill( 0, count( $list_of_strings ), '%s' );

 // Build the string as comma-separated between each placeholder.
 $placeholder_sql = implode( ', ', $placeholders );

 $values_to_prepare[] = $int1;
 $values_to_prepare[] = $int2;
 $values_to_prepare[] = $int3;

 $sql = "
     SELECT *
     FROM `{$wpdb->prefix}whatever_table`
     WHERE
         `whatever_column` IN ( {$placeholder_sql} )
         OR `whatever_integer_column IN ( %d, %d, %d )
 ";

 $final_sql = $wpdb->prepare( $sql, $values_to_prepare );
 }}}

 == Introducing `%L` placeholders

 With the introduction of `%i` in #52506 it seems that we may finally be
 ready to close up the remaining gaps for `wpdb::prepare()`.

 For my example on what I think we could use here, I'm using the `%L`
 placeholder because lowercase would be ambiguous and the `%L` placeholder
 doesn't appear to conflict with normal `sprintf()` syntax.

 The `%L` usage would default to a comma-separated list of `%s` prepared
 values but you could have a `%Ld` variation that would use a comma-
 separated list of `%d` prepared values too.

 {{{#!php
 <?php
 /**
  * @var string[] $list_of_strings
  * @var int[] $list_of_integers
  */

 $sql = "
     SELECT *
     FROM `{$wpdb->prefix}whatever_table`
     WHERE
         `whatever_column` IN ( %L )
         OR `whatever_integer_column` IN ( %Ld )
 ";

 $final_sql = $wpdb->prepare( $sql, [ $list_of_strings, $list_of_integers ]
 );
 }}}

 In this way, it sees that first placeholder `%L` and uses the
 corresponding value from the first item in the array of prepare values
 passed. It would expand the prepared values automatically.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/56764>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform