0

I'm calling a function through ajax in my site, and within the function that's being hit I'm taking the data (in this case it's a search term) and putting it into LIKE statements in sql:

$searchkeyword = '';
$prefix_post = $wpdb->prefix. "posts";

if($_POST['search'] != ''){
    
    //get search term from request
    $search = $_POST['search'];
    
    //build sql condition
    $searchkeyword = " AND ((($prefix_post.post_title LIKE '%$search%') OR ($prefix_post.post_content LIKE '%$search%') ))";

    $mysql = $wpdb->prepare("SELECT * FROM TEST_TABLE WHERE USER = 123 $searchkeyword ");
    print_r($mysql);
    die;
}

I noticed it was hanging up when trying to get results so I added the print for the sql alone and noticed it was printing this for the AND conditions:

AND (( ( wp_posts.post_title LIKE
'{a19f0a6c3d866f7270be0f3954cbb2600270400c15f488e112449a8a131701f0}test{a19f0a6c3d866f7270be0f3954cbb2600270400c15f488e112449a8a131701f0}'
)
 OR ( wp_posts.post_content LIKE
'{a19f0a6c3d866f7270be0f3954cbb2600270400c15f488e112449a8a131701f0}test{a19f0a6c3d866f7270be0f3954cbb2600270400c15f488e112449a8a131701f0}'
    )

Why isn't it just showing wp_posts.post_content LIKE 'test'?

Update:

            $search = $_POST['search'];
            $wild = '%';
            $like = $wild . $wpdb->esc_like( $search ) . $wild;
            
            $sql  = $wpdb->prepare( "
                SELECT $prefix_post.ID 
                FROM $prefix_post 
                WHERE (((($prefix_post.post_title LIKE %s) 
                    OR ($prefix_post.post_excerpt LIKE %s) 
                    OR ($prefix_post.post_content LIKE %s)))
                ", $like );
            
            print_r($sql);die;
3
  • the goal of $wpdb->prepare is to safely allow you to insert variables into an SQL query, but you are not providing it any variables to insert, and inserting them yourself directly into the string, this is extremely insecure and dangerous and not how wpdb->prepare is meant to be used, fixing this will also give clues to why your problem is happening. There should be additional parameters for the things you want to insert into the SQL statement, and placeholders in the SQL for where they go
    – Tom J Nowell
    Commented Mar 18, 2022 at 15:22
  • take a look at developer.wordpress.org/reference%2Fclasses%2Fwpdb%2Fprepare%2F
    – Tom J Nowell
    Commented Mar 18, 2022 at 15:23
  • Thanks for the insight @TomJNowell! I am actually not putting strings into the actual code, I did happen to use the table and user number as a more broken down example above, but I will take that approach with things going forward. I just felt like maybe something obvious was happening with the search text
    – Geoff_S
    Commented Mar 18, 2022 at 15:32

1 Answer 1

1

It isn't showing wp_posts.post_content LIKE '%test%' because the wildcards (%) in the prepared statement are converted to placeholder escape strings (like the {a19f0a6c3d866f7270be0f3954cbb2600270400c15f488e112449a8a131701f0} in your post) when wpdb::prepare() calls wpdb::add_placeholder_escape() which "Adds a placeholder escape string, to escape anything that resembles a printf() placeholder", i.e. a placeholder like %s.

But those placeholder escape strings will be changed back to % when the statement is passed to MySQL, so there's no need to worry about those escape strings.

Additionally, make sure to use the correct prepare() syntax — there should be at least one placeholder and a substitution argument (for that placeholder) like so: $wpdb->prepare( "SELECT * FROM foo WHERE bar = %s", 'bar value' ).

Also, you should apply wpdb::esc_like() on the $search value to ensure % in the value are properly escaped.

And you might also want to check out my recent answer here because (I thought) the question there seemed quite similar to your question..

5
  • Thank you for the thorough answer, @SallyCJ. I read through the doc, see my update above, but my sql statement now won't print on the console. Just says no data. Did I do anything wrong there?
    – Geoff_S
    Commented Mar 18, 2022 at 19:31
  • It seems like I did everything you said, but I don't know why I wouldn't be able to now print out the prepared sql query
    – Geoff_S
    Commented Mar 18, 2022 at 20:27
  • "Did I do anything wrong there?" - yes, you did - you used 3 placeholders there (i.e. three instances of %s), so you also need to pass 3 substitution arguments (one for each of those %s), i.e. $like, $like, $like. Also, the WHERE clause wasn't properly closed - and it should instead look like WHERE ( ( ... ) OR ( ... ) OR ( ... ) ). And actually, the $prefix_post could just be removed and use $wpdb->posts instead, just as the $wpdb->postmeta in my answer to the other question.
    – Sally CJ
    Commented Mar 19, 2022 at 4:40
  • PS: The $prefix_post isn't wrong, but I was saying you could simply use $wpdb->posts to reference the posts table (wp_posts). And as for the "Just says no data", if you enabled debugging, you would've seen the exact error messages, so you should enable debugging while developing to ensure your code is errors-free. :)
    – Sally CJ
    Commented Mar 19, 2022 at 14:19
  • 1
    That all makes sense now, thank you so much
    – Geoff_S
    Commented Mar 19, 2022 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.