匹配SQL注入预防和wpdb->准备

Question

I'm trying to optimize SQL from sql injects using $wpdb->prepare();

This query doesn't return anything. Can anyone see why or have a more elegant alternative. Also is it necessary to escape the search terms in the MATCH AGAINST statement.

$search_terms = "example search" // input post
// remove single quotes from search terms
$search_terms = str_replace("'","",$search_terms);

$sql = "SELECT
    post.ID,
    post.post_author,
    post.post_date,
    post.post_title,
    LEFT(post.post_content, 240) As post_content,
    post.post_name,
    post.post_type,
    post.comment_count,
    post.comment_status,
    MATCH (post_search.post_content,post_search.post_title) AGAINST (%s IN BOOLEAN MODE) AS score
FROM wp_posts post
LEFT JOIN wp_term_relationships term_rel
    ON post.ID = term_rel.object_id
LEFT JOIN wp_term_taxonomy term_tax
    ON term_rel.term_taxonomy_id = term_tax.term_taxonomy_id
LEFT JOIN wp_terms terms
    ON term_tax.term_id = terms.term_id
LEFT JOIN wp_posts_fulltext_search post_search 
    ON post.ID=post_search.post_id
WHERE MATCH(post_search.post_content,post_search.post_title) AGAINST (%s IN BOOLEAN MODE) && 
    post.post_status = 'publish' && 
    post.post_type = 'post' && 
    (term_tax.description != '' && 
    term_tax.description NOT LIKE '%sample%') 
GROUP BY post.ID
ORDER BY score DESC
LIMIT 0,20";

$results = $wpdb->get_results( $wpdb->prepare( $sql, $search_terms ) );

The "As score" is used to return more relevant results to the top. Just to reiterate I'm more concerned about the SQL inject, MATCH AGAINST and wordpress prepare function.

Answer 1

Don't ever use this code:

// remove single quotes from search terms 
$search_terms = str_replace("'","",$search_terms); 

It's broken, because it does not take MySQL's encoding into account.

If you want to escape, use this instead:

$search_terms = mysql_real_escape_string($search_terms);

But beware!, this will only escape values, not table names and not field names.
If you want manipulate the field or table names in the query the rules are:

1. Don't do it you are asking for SQL-injection trouble.
2. Don't do it with PDO either, you're still running into SQL-injection problems.
3. Read the answer by Pekka to my question: How to prevent SQL injection with dynamic tablenames?

However
No escaping is needed, because you're using a prepared statement.