ā€œ%1sā€ placeholder in $wpdb->prepare()

  • I have an array variable in my PHP code.
    $id_arr = fn_get_id_array(); // Returns Array, i.e. [23,24,25]

    I need to use this value in my query, like this:
    ... WHERE ID IN(23,24,25)

    If I prepare it using ā€œ%sā€, this is not working:
    ... WHERE ID IN('23,24,25')

    So, I use this:
    $wpdb->query( $wpdb->prepare( "SELECT ... WHERE ID IN(%1s) ", implode(',',$id_arr) ) );

    PCP says:

    WARNING WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder Complex placeholders used for values in the query string in $wpdb->prepare() will NOT be quoted automagically. Found: %1s.

    Should another method be used to prepare the query? I’m waiting for your advice.

    • This topic was modified 5 months, 3 weeks ago by Atakan Au.
    • This topic was modified 5 months, 3 weeks ago by Atakan Au.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator Dion Hulse

    (@dd32)

    Meta Developer

    Hi @atakanau,

    Using %1s is not recommended (or supported by wpdb::prepare()) for this, as it’s actually bypassing the entire purpose of using prepared queries.
    Furthermore %1s is actually saying “Print %s with at least 1 character”, not the same as %1$s which is “The first argument presented as a string”. wpdb::prepare() accepts %1s for backwards compatibility with old code, but it should not be used intentionally.

    This code is the same syntactically as what you’re currently using: (which has no SQLi prevention in place)

    $ids = implode(',',$id_arr);
$wpdb->query( $wpdb->prepare( "SELECT ... WHERE ID IN( $ids ) " ) );

    The proper way to achieve what you want, would be to apply some sanitization to the input before interpolating:

    $ids = implode( ',', array_map( 'intval', $id_arr ) );
$wpdb->get_results( "SELECT ... WHERE ID IN( $ids ) " );

    You’ll find many examples of this in WordPress files, such as WP_Query:

    https://github.com/WordPress/wordpress-develop/blob/e5935547d2faeaedb857f77f878c4d08d2b3389b/src/wp-includes/class-wp-query.php#L2197-L2206

    NOTE: If you’re looking to use strings rathe than IDs, you’d have to do something like this to properly escape each piece:

    $slugs = '"' . implode( '" , "', array_map( 'esc_sql', $slugs ) ) ) ) . '"';
$wpdb->get_results( "SELECT ... WHERE post_name IN( $slugs ) " );

    for example: https://github.com/WordPress/wordpress-develop/blob/e5935547d2faeaedb857f77f878c4d08d2b3389b/src/wp-includes/class-wp-query.php#L2550

    There’s other approaches to this, like using array_fill() to generate the placeholders: https://stackoverflow.com/a/38735186

    There’s work to support list notation with WordPress’s prepared statements in this trac ticket: https://core.trac.www.ads-software.com/ticket/54042

    Thread Starter Atakan Au

    (@atakanau)

    Thank you for your patiently detailed answer.

    This is already unwanted usege:

    $ids = implode(',',$id_arr);
$wpdb->get_results( $wpdb->prepare( "SELECT ... WHERE ID IN( $ids ) " ) );

    [Code#1] PCP is ‘WARNING’ for this sanitization:

    $ids = implode( ',', array_map( 'intval', $id_arr ) );
$wpdb->get_results( $wpdb->prepare( "SELECT ... WHERE ID IN( $ids ) " ) );

    WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    Use placeholders and $wpdb->prepare(); found interpolated variable $ids at “WHERE ID IN( $ids ) ”

    [Code#2] PCP is ‘WARNING’ for this too (stackoverflow):

    $que= implode(',', array_fill(0, count($id_arr), '%s') );
$wpdb->get_results( $wpdb->prepare( "SELECT ... WHERE ID IN( $que ) " ), $id_arr );

    WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    Use placeholders and $wpdb->prepare(); found interpolated variable $que at “WHERE ID IN ( $que ) ”

    [Code#3] ...d : It seems not available yet.

    WordPress.DB.PreparedSQLPlaceholders.UnsupportedPlaceholder
    Unsupported placeholder used in $wpdb->prepare(). Found: “%…d)”.

    None of the codes complete the checks successfully. Am I supposed to ignore the ‘WARNING’s?

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.