WordPress – How to use LIKE in WPDB PREPARE SQL statements

In WordPress the WPDB class is available for interacting with the database, for example reading or writing data.

The PREPARE method is almost always used along with the WPDB.

PREPARE helps protects from SQL injection vunerabilities by ensuring variables are what they’re meant to be (strings or digits).

It requires you to write the SQL statements with %s (for a string) or %d (for a digit) in place of a variable, then providing the variable as a parameter.

For example:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' = %s AND 'field' = %d", 'something', 1337 );

The problem with wildcard characters

By using the % character in PREPARE it takes on a completely different meaning which can cause problems when it comes to using it as a wild card in SQL LIKE statements.

For example, this would not work:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like '%something' AND 'field' = %d", 1337 );

Because PREPARE will try to replace %s in the SQL statement.

There are two ways to get around this –

Escape %s with a second %

Note: with this method you need to add quotes around the condition.

For example:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like '%%something' AND 'field' = %d", 1337 );

Pass the like condition as a parameter

For example:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like %s AND 'field' = %d", '%something', 1337 );

This is my prefered method as it allows you to add in variables, for example:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like %s AND 'field' = %d", '%something' . $var, 1337 );

Escaping like conditions

As an added level of protection, WPDB also has a method called ESC_LIKE that will escape characters that will break a SQL LIKE statement.

This must be used when the LIKE condition involves a ‘dirty’ variable – for example one provided by a user.

The ESC_LIKE method sanitises the variable by escaping the % and _ characters inside the variable.

For example, if $var was ‘dirty’.

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like %s AND 'field' = %d", '%something' . $wpdb->esc_like( $var ), 1337 );