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 vulnerabilities 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 provides 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 SQL LIKE statements.
This must be used when the LIKE condition involves a ‘untrusted’ 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 ‘untrusted’.
$wpdb->prepare( "SELECT * FROM 'table' WHERE 'column' like %s AND 'field' = %d", '%something' . $wpdb->esc_like( $var ), 1337 );