Collect “where” clause array with “join” using wpdb prepare safely

  • I need to secure a big SQL statement has a lot of conditions in its WHERE clause so I made an array for WHERE clause using $wpdb->prepare properly BUT an error happened while join this array together as a string in the final statement.

    Here is some of my code .. Is that secure enough or it may cause an SQL injection?

    P.S. I try to make another $wpdb->prepare in the last get_row function but the join function made quotes before and after WHERE clause so the statement generates an error.

    foreach( $args as $field => $field_value ) {
    if( ! is_null( $field_value ) ) {            
        switch( $field ) {
            case 'id': {
                $where[] = $wpdb->prepare( 'tbl_names.id = %d', $field_value );
            } break;                    
            case 'name': {
                $where[] = $wpdb->prepare( 'tbl_names.name = %s', $field_value );
            } break;                  
        }
    }
}

// NOT Working - Quotes before and after WHERE
return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$tbl_names} tbl_names WHERE %s", join( ' AND ', $where ) ), ARRAY_A );

// Working Good .. BUT Is it Safe??
return $wpdb->get_row( ( "SELECT * FROM {$tbl_names} tbl_names WHERE " . join( ' AND ', $where ) ), ARRAY_A );

    Any ideas? Thanks

Viewing 3 replies - 1 through 3 (of 3 total)
  • Dion

    (@diondesigns)

    Your second version is safe. You only need to escape the actual values, not the statement itself. In fact, you can optimize the code in your switch clause by sanitizing integers yourself, as follows:

    case 'id': {
	$where[] = 'tbl_names.id = ' . intval($field_value);
} break;
    Thread Starter Oxibug

    (@oxibug)

    @diondesigns No Pal, Your $where array is incorrect a lot of SQL injection could happen there and it’s against the WordPress wpdb docs.

    Thanks for your help anyway ??

    Dion

    (@diondesigns)

    Um…no. Absolutely, positively ZERO sql injection could happen with what I provided. If the $wpdb docs say that, they are WRONG. At best it’s another example of “the sky is falling” that permeates the WordPress documentation. (The Health Check plugin is a case study on how to scare people.)

    $wpbd->prepare() is for those who don’t know how to manually sanitize queries. It can be a godsend for newbies, but it uses a lot of resources, and those who are more advanced will instead manually sanitize their queries. Just like what’s done with every other PHP application on the planet that doesn’t use mysqli_stmt or PDO/mysql. ??

    Maybe someday WordPress will get rid of support for the mysql extension. If that happens, lots of good things can be done to dramatically improve the performance of WordPress. One of them is to use the mysqli_stmt class as a replacement for $wpdb->prepare(). Another is to use transactions when modifying the meta tables. I look forward to that day.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Collect “where” clause array with “join” using wpdb prepare safely’ is closed to new replies.