How to SQL query by post title in wordpress when title has an apostrophe

1 Views

I’m creating a shortcode in wordpress where the user can use the post title or slug of a post to pull information from a post. Everything works well except for when a post’s title has an apostrophe in it.

An example of the shortcode that works with the post slug is

Read More
[card]hunters-mark[/card]

When I use the post title and the title has a apostrophe it doesn’t work. 

[card]Hunter's Mark[/card]

When I use the shortcode with the title of a post that doesn’t contain a apostrophe everyting works so the issue is with the apostrophe. The code I use to get the post id is 

$sql="select * from $wpdb->posts where (post_title='$content' or post_name='$content' ) and post_type='cards' and post_status='publish' limit 0,1";
$my_posts = $wpdb->get_results($sql);
if( $my_posts ) {
    $card_id = $my_posts[0]->ID;
}

Edit:

So what is weird is that when I try to output everything by using 

`$data=strpos($content,"'");
var_dump($data);
$content=str_replace("'", "'", $content);`

It is displaying strpos(“Hunter’s Mark”,”‘”)=false

So it is saying that there is no ‘ even though there is, and and I check the database and the post title is showing exactly how I have it in the shortcode.

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. Apparently you cannot autoescape quotes. You need to do that yourself:

    $sqlContent = mysqli_real_escape_string($content);

    I would also advise using curly brackets for variables.

    $sql="select * from {$wpdb->posts} where (post_title='{$sqlContent}' or post_name='{$sqlContent}' ) and post_type='cards' and post_status='publish' limit 0,1";

$my_posts = $wpdb->get_results($sql);
...

    UPDATE

    You can do it another (safer) way:

    $wpdb->get_results( 
    $wpdb->prepare( 
        "SELECT * FROM {$wpdb->posts} WHERE
         (post_title=%s OR post_name=%s)
         AND post_type='cards' AND post_status='publish'
         LIMIT 0,1",
             $content,
             %content
        )
);

  3. You should use the $wpdb->prepare() method to correctly insert and escape your variables – it protects against SQL injections as well. Using %s in your SQL will indicate that you want to use a string, otherwise a %d would be used for a digit. It’s also recommended to use curly braces around {$wpdb->posts}. Since you are just looking for a single row you might want to use get_row() instead of get_results() as well. If you just want the ID you should use get_var() with SELECT ID.

    global $wpdb;
// set up the SQL statement
$sql = "SELECT * FROM {$wpdb->posts} WHERE ( post_title=%s OR post_name=%s ) AND post_type='cards' AND post_status='publish' LIMIT 0,1";
// replace %s with $content
$query = $wpdb->prepare( $sql, $content, $content );
// query for results
$my_post = $wpdb->get_row( $query );
// did we get a result?
if ( ! empty( $my_post ) ) {
    // get the ID
    $card_id = $my_post->ID;
}