Support IN and NOT IN data in wpdb::prepare()

[sc0ttkclark]

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
/**
 * @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
/**
 * @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
/**
 * @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.

[sc0ttkclark]

Duplicate of #54042.

Looks like this is covered in a better way via https://core.trac.wordpress.org/ticket/54042#comment:36