Sanitize

By default, the QueryBuilder will identify the type of values passed in build methods and will apply sanitization to them.

Build Method	Documentation
join()	Read more.
where()	Read more.
keywords()	Read more.

Supported Values

The following value types will be detected and sanitization will be applied:

Type	Sanitize Method
null	none, Database null value is used.
int	intval()
float	floatval()
string	sanitize_text_field()
array	The array will be looped recursively, each value within will be sanitize independently.

Custom Sanitization

The where() and join() build methods allow for custom sanitize callbacks to be specified. See example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key'   => 'b.id',
            'value' => $b_id,
            'sanitize_callback' => 'custom_sanitize_callback',
        ],
    ] )
    ->where( [
        'a.id' => [
            'value' => $a_id,
            'sanitize_callback' => 'custom_sanitize_callback',
        ],
    ] );

Remove Sanitization

The where() and join() build methods allow for sanitization to be removed. See example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key'   => 'b.id',
            'value' => $b_id,
            'sanitize_callback' => false,
        ],
    ] )
    ->where( [
        'a.id' => [
            'value' => $a_id,
            'sanitize_callback' => false,
        ],
    ] );

NOTE: It is recommended to remove sanitization when using raw statements.

Like Statement Sanitization

If using LIKE comparison operator in queries, it will require to use $wpdb->esc_like() sanitization. The builder has a couple of private sanitization methods that will help with this.

Using esc_like()

Use _builder_esc_like as sanitize_callback parameter, like in the following example:

$builder->select( 'a.id' )
    ->select( 'a.name' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => $search,
            'sanitize_callback' => '_builder_esc_like',
        ],
    ] );

With wildcards

To use esc_like() and append a wildcard at the beginning of the value use _builder_esc_like_wild_value, like in the following example:

$builder->select( 'a.id' )
    ->select( 'a.name' )
    ->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => $search,
            'sanitize_callback' => '_builder_esc_like_wild_value',
        ],
    ] );

In the example above, if $value has a value of 'test', the resulting query would be:

SELECT a.id,a.name FROM a WHERE a.name LIKE '%test'

To use esc_like() and append a wildcard at the end of the value use _builder_esc_like_value_wild, like in the following example:

$builder->select( 'a.id' )
    ->select( 'a.name' )
    ->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => $search,
            'sanitize_callback' => '_builder_esc_like_value_wild',
        ],
    ] );

And to use esc_like() and append a wildcard at both ends of the value use _builder_esc_like_wild_wild, like in the following example:

$builder->select( 'a.id' )
    ->select( 'a.name' )
    ->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => $search,
            'sanitize_callback' => '_builder_esc_like_wild_wild',
        ],
    ] );

Wildcard placeholders

Use the placeholder {%} to indicate what should be considered a wildcard in the value, for example:

$builder->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => 'text{%}value',
            'sanitize_callback' => '_builder_esc_like',
        ],
    ] );

Define custom wildcards using the array key wildcard, for example:

$builder->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => 'text{wild}value',
            'sanitize_callback' => '_builder_esc_like',
            'wildcard' => '{wild}',
        ],
    ] );

Take into consideration that using % as the wildcard placeholder may create conflicts:

$search_text = 'discount at %5';
$builder->from( 'a' )
    ->where( [
        'a.name' => [
            'operator' => 'LIKE',
            'value' => str_replace( ' ', '%', $search_value ),
            'sanitize_callback' => '_builder_esc_like',
            'wildcard' => '%',
        ],
    ] );

The example above conflicts and will return a wrong result since the wildcard is part of the search string; to avoid the above, is recommended to stick with the default wildcard.