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

Question

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

[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.

Have you tried escaping the apostrophe with a backslash (`\'`)? — Barranka, Jul 09 '14 at 20:16

score 1 · Answer 1 · edited Apr 13 '17 at 12:37

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
        )
);

score 0 · Answer 2 · answered Jul 09 '14 at 20:35

you should use mysql_real_escape_string($content) before using it in your query , or even better use prepared statements to make things much safer, currently you are vulnerable to sql injection. If you use prepared statement this issue will be resolved and it will be safer for you as well.

https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks

score 0 · Answer 3 · answered Jul 09 '14 at 20:43

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;
}

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

3 Answers3

UPDATE