WordPress PHP, why esc_like method is needed for LIKE type of query?-CodePudding

So I'm left a little confused why on WordPress there is a method like esc_like(): https://developer.wordpress.org/reference/classes/wpdb/esc_like/ The advice is that parameter that will be used by LIKE must be escaped with esc_like() to make it safe, like this:

$wild = '%';
$find = 'only 43% of planets';
$like = $wild . $wpdb->esc_like( $find ) . $wild;
$sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like);

But why:

$sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like);

Wouldn't be enough in this case to properly prepare that LIKE parameter? I don't understand where it could go wrong witout using esc_like and is it really that something can cause vulnerability if used without that esc_like method.

To my understanding $wpdb->prepare should be enough, but lately learning about this I'm not sure.

So the main question is, does it impose very serious security risk if I use it without $wpdb->esc_like() method?

CodePudding user response：

You're right that for preventing SQL injection you have to use a query parameter.

The purpose of "escaping" a string for LIKE isn't the same purpose as preventing SQL injection.

The % and _ characters are wildcards in a LIKE expression. But what if you want to match those characters literally?

SQL provides a solution:

https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like

To test for literal instances of a wildcard character, precede it by the escape character.

The default escape character is \.

CodePudding user response：

WordPress uses a unique and incompletely documented scheme for doing $wpdb->prepare() processing. Display some SQL prepared that way and you'll see what I mean -- long uniqueid strings appear, and are later straightened out during the ->execute() phase.

->esc_like() is part of that scheme. And, of course a lot of this is in aid of injection prevention.