Implement a Secure WordPress Search

The built-in WordPress search is widely regarded as a disaster by many bloggers.  For that reason, many opt for who use a Google custom search or one of their own design.  Rolling your own is especially advantageous for WordPress sites that include a lot of custom post types.  In this follow-up to the Querying the WordPress Database and Working with WordPress Result Sets tutorials, we’ll build a query that searches by post_title and taxonomy metadata.

Taxonomies Explained

In WordPress, a "taxonomy" is a grouping mechanism for certain posts such as links or custom post types.  The names for the different groupings in a taxonomy are called terms. Using groupings of animals as an example, we might call one group "birds", and another group "fish". "Fish" and "birds" are terms in our “Animals” taxonomy.

In the purposes of our search, we’ll assume that we have a custom post_type called “restaurant”.  Each restaurant can have one or more “cuisines”.  For example, the restaurant “Bob’s Burger’s” might list “burgers”, “fast food”, and “American” as its taxonomy terms.

This relationship is maintained by three tables:

• wp_term_relationships
• wp_term_taxonomy
• wp_terms

These tables are shown below, along with the tables they are linked to, wp_posts and wp_links:

The relationship is maintained by three tables

Constructing the Basic Query

Using the above diagram as our guide, we can join all of the relevant taxonomy tables to wp_posts using LEFT JOINs so that a lack of taxonomies won’t cause a restaurant to be omitted. 

SELECT p.ID,
        p.post_title AS name,
        t.name AS cuisines   
 FROM wp_posts p
   LEFT JOIN wp_postmeta pm ON(p.ID = pm.post_id)
   LEFT JOIN wp_term_relationships tr ON(p.ID = tr.object_id)
   LEFT JOIN wp_term_taxonomy tt ON(tr.term_taxonomy_id = tt.term_taxonomy_id)
   LEFT JOIN wp_terms t ON(tt.term_id = t.term_id)
 WHERE p.post_type   = 'restaurant'
 AND   p.post_status = 'publish'
 AND   tt.taxonomy   = 'cuisines'
 ORDER BY p.post_title ASC;

Here’s a sampling of the data produced by the above SELECT statement:

Sanitizing User Input

All data passed to SQL queries should be SQL-escaped before the query is executed to protect against SQL injection attacks. Don’t assume that the application code will sanitize user inputs before getting to you.  To be on the safe side, use the WordPress prepare() method.  It cleans up user input using sprintf()-like and vsprintf()-like syntax. 

A member of the $wpdb object, prepare() requires a minimum of 2 arguments - the query and at least one value parameter:

<?php $sql=$wpdb->prepare('query', value_parameter[, value_parameter... ]); ?> 

The query parameter for prepare() accepts sprintf()-like placeholders defined by the percentage symbol (%), followed by a lowercase letter for the data type.  These include %s for string, %d for integer and %f for float.  The function takes care of quoting string values so there is no need to enclose your %d, %f, and %s placeholders within quotes in the query string.

Passing Value Parameters to $wpdb->prepare()

Flexibility is the order of the day when it comes to the prepare() method.  It really has to be, when you consider the types and number of parameters that it has to work with.  In order to do that, the value_parameters may be supplied using either the PHP sprintf() or vsprintf() style: sprintf() accepts a variable number of parameters while vsprintf() accepts an array containing the values.

Let’s take a look at each style in turn, by making the restaurant name and taxonomy terms searchable.

Using the sprintf() style, we would pass a total of three parameters to prepare(): the query string and two value_parameters:

$restaurant_name_user_input = $_GET[
'restaurant_name'];

$search_results = $wpdb->get_results( $wpdb->prepare( 
   "
     SELECT p.ID,
            p.post_title AS name,
            t.name AS cuisines   
     FROM wp_posts p
       LEFT JOIN wp_postmeta pm 
            ON(p.ID = pm.post_id)
            AND pm.meta_key   = 'restaurant_base_name'      
       LEFT JOIN wp_term_relationships tr ON(p.ID = tr.object_id)
       LEFT JOIN wp_term_taxonomy tt ON(tr.term_taxonomy_id = tt.term_taxonomy_id)
      LEFT JOIN wp_terms t ON(tt.term_id = t.term_id)
     WHERE p.post_type   = 'restaurant'
     AND   p.post_status = 'publish'
     AND   tt.taxonomy   = 'cuisines'
     AND  (p.post_title  LIKE %s
        OR pm.meta_value LIKE %s)
     ORDER BY p.post_title ASC;
   ", 
       '%' . $restaurant_name_user_input . '%',
       '%' . $restaurant_name_user_input . '%'
 ) );

Note that prepare() does not remove the ‘%’ character from strings as it is database safe.

The vsprintf() version of the function is similar to the first one, except that value_parameters are passed via an array:

$search_results = $wpdb->get_results( $wpdb->prepare( 
   "
     SELECT p.ID,
            p.post_title AS name,
            t.name AS cuisines   
     ...
     ORDER BY p.post_title ASC;
   ", 
     array(
       '%' . $restaurant_name_user_input . '%',
       '%' . $restaurant_name_user_input . '%'
     )
 ) );

Here are the first several rows for a $restaurant_name_user_input of ‘Cote’:

Displaying One Row per Restaurant

As described in Why You Should be using the MySQL GROUP_CONCAT Function, the GROUP_CONCAT() function is a great way to combine related group data into one row. 

To use it with our query, all we need to do is group the results by post ID and apply GROUP_CONCAT() to the taxonomy terms.  Here is the relevant SQL with changes highlighted in red:

SELECT p.ID,
        p.post_title AS name,
        GROUP_CONCAT(DISTINCT t.name) AS cuisines
 FROM wp_posts p
 ...
 WHERE p.post_type   = 'restaurant'
 AND   p.post_status = 'publish'
 AND   tt.taxonomy   = 'cuisines'
 AND  (p.post_title  LIKE %s
    OR pm.meta_value LIKE %s)
 GROUP BY p.ID
 ORDER BY p.post_title ASC;

Presto, one restaurant per row!

Conclusion

For WordPress sites that include a lot of custom post types, knowing how to construct queries that join several WordPress tables is a useful skill to have.

See all articles by Rob Gravelle