I think the problem is in the use of the single ‘%’ signs in creating the where clause. I believe that $wpdb->prepare() is trying to use these to insert parameters. Try using this:
$where = '';
foreach($keywords as $keyword) {
// $keyword = esc_like($keyword); - function deprecated?
$where .= " post_title LIKE '%%" . $keyword . "%%' OR post_content LIKE '%%" . $keyword . "%%' OR";
}
From WordPress 4.0 the like_escape() function is deprecated and is replaced by the the esc_like() method:
global $wpdb;
$escaped_string = $wpdb->esc_like( $like_string_to_escape );
@vtxyzzy, yes you are correct. I actually changed it %%% %% like below, and it works.
$where .= " post_title LIKE '%%%" . $keyword . "%%' OR post_content LIKE '%%%" . $keyword . "%%' OR";
@keesiemeijer, so since I’m running 3.9.2 I should use the like_escape() function for the LIKE statement? Doesnt it get properly sanitized within the prepare() function?
Doesnt it get properly sanitized within the prepare() function?
You should only use it for the LIKE text before escaping the sql with esc_sql() or prepare().
like_escape() and $wpdb->esc_like() escape the characters % (percent) and _ (underscore), as they have special meaning in LIKE statements. The output from these functions is not sql safe by itself.
$wpdb->prepare() is generally preferred over esc_sql() because it corrects a few common formatting errors.
Here is a part of the comments for the $wpdb->esc_like() method in wp-includes/wp-db.php
/**
* First half of escaping for LIKE special characters % and _ before preparing for MySQL.
*
* Use this only before wpdb::prepare() or esc_sql(). Reversing the order is very bad for security.
*
* Example Prepared Statement:
* $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 );
*
* Example Escape Chain:
* $sql = esc_sql( $wpdb->esc_like( $input ) );
*
* ...
*/
http://codex.ww.wp.xz.cn/Class_Reference/wpdb/esc_like
https://developer.ww.wp.xz.cn/reference/classes/wpdb/esc_like/
Use $wpdb->esc_like() if the method exists (from WordPress 4.0 and up), else use like_escape().
global $wpdb;
// if else
if ( method_exists( $wpdb, 'esc_like' ) ) {
$keyword = $wpdb->esc_like( $keyword );
} else {
$keyword = like_escape( $keyword );
}
// The same with the ternary operator
$keyword = method_exists( $wpdb, 'esc_like' ) ? $wpdb->esc_like( $keyword ) : like_escape( $keyword );
Here is an example how you could use it in your code:
global $wpdb;
$keywords = $results = array();
$like = '';
// check if $_POST index 'keywords' exists
if ( isset( $_POST['keywords'] ) && $_POST['keywords'] ) {
// Sanitize (strip tags etc.) and place the search keywords in array
$keywords = ( explode( ' ', sanitize_text_field( $_POST['keywords'] ) ) );
}
// no need to use $wpdb->prepare for this part of the query
$where = "SELECT * FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish'";
if ( !empty( $keywords ) ) {
foreach ( $keywords as $keyword ) {
// check if new WP 4.0 method esc_like() exists
$keyword = method_exists( $wpdb, 'esc_like' ) ? $wpdb->esc_like( $keyword ) : like_escape( $keyword );
$keyword = '%' . $keyword . '%';
// prepare the like statement for the current keyword
$like_statement = $wpdb->prepare( " post_title LIKE %s OR post_content LIKE %s OR", $keyword, $keyword );
$like .= $like_statement;
}
// create the full like statenement and remove last 'OR'
$like = ' AND (' . substr( $like , 0, -2 ) . ')';
// get posts
$results = $wpdb->get_results( $where . $like . ' ORDER BY post_date DESC' );
}
if ( !empty( $results ) ) {
// do something with results
var_dump( $results );
} else {
echo 'no posts found';
}
And as a final note, have you tried it with a WP_Query and the search parameter ‘s’.
http://codex.ww.wp.xz.cn/Function_Reference/WP_Query#Search_Parameter
I think WordPress searches in the post_title and post_content as of version 3.7
