MetaSushi

Imagine a scenario where you have some special data that you wish to query out of either built in ExpressionEngine tables or out of custom tables added by a third-party package.

Usually what most people would do at this point is resort to using the query plugin to obtain data from a custom query. However, I want to show you a better way to achieve the same kind of access to data in internal tables, but in a much safer and more controllable way.

First a little about why the query plugin isn't such a great idea in this situation. Generally, when we need to query tables in a MySQL database, we need to pass in some information that we have been given by the user or at least which has passed through the user's machine in the form of a link in a URL segment, or in a value posted to a form. For instance, you may want to get some custom member information based on a member ID. You could do this using the Query plugin like in the following example.

Imagine we have a template group named account and a template named member_info which performs this custom query. The root URL of our example site site is http://www.example.com/.

<h2>Member Info</h2>
{exp:query sql="SELECT * FROM exp_members WHERE member_id={segment_3}"}
	  Screen Name: {screen_name}<br/>
	  Registered Email Address: {email}<br/>
	  Username: {username}<br/>
	  <a href="{site_url}account/edit/{segment_3}">Edit</a>
{/exp:query}

This query is incredibly dangerous for a number of reasons.

First, imagine that we visited the following URL:

This may not look like a valid URL and you may expect ExpressionEngine to filter out this kind of request, but it is a valid URL and there is no filtering which can stop this. This request would show us all of the member information in the system.

Of course in this case we could just as easily use the current member's logged in ID - and this would be a very good way to fix this particular type of request, however most use of the Query plugin are quite a bit more complicated than this and cannot be so easily patched.

The alternative approach that I would like to recommend is to create a custom plugin that is designed to perform this exact query, and which constructs it not with SQL but instead with the CodeIgniter Active Record class.

The first step to create a new plugin is to create a package directory in the system/expressionengine/third_party folder with the name you wish to give the plugin. For instance, we might want to call this plugin "member_info", in which case we would create this directory, which must be in all lowercase:

Inside of this directory, we then need to create a PHP file with a special prefix so that ExpressionEngine knows that it is a plugin. This filename must also be in all lowercase:

After creating the plugin file, we then put in the boilerplate plugin code. You can use this as the basis of all of your custom querying plugins.

All plugins contain two major items. The first is a $plugin_info array which contains all of the meta data for this plugin. The second is the actual class that contains the plugin's custom logic.

<?php
$plugin_info = array(
    'pi_name'           => 'Example',
    'pi_version'        => '1.0',
    'pi_author'         => 'Your Name',
    'pi_author_url'     => '',
    'pi_description'    => 'Exemplifies plugins',
    'pi_usage'          => <<<USAGE
    How to call this plugin:
    {exp:example:some_query param1="something"}
    {/exp:example:some_query}
USAGE
    );

    class Example {
        public __construct() {
            $this->EE = &get_instance();
        }

        public function some_query() {
            // Get params from $this->EE->TMPL->fetch_param("param");
            // Get the contents of a tag pair as $this->EE->TMPL->tagdata;
            // Return your results as a string
        }
    }

The boilerplate plugin defines a tag named some_query, which you will want to change the name of, delete or add to. The name of your tags is completely up to you, and remember that a plugin can define multiple tags. This is useful to group all of the related custom query code for a particular site into a single file.

To continue our example, we would create the following Member_info plugin in order to replace the unsafe Query plugin call:

<?php
	$plugin_info = array(
        'pi_name'        => 'Member_info',
        'pi_version'        => '1.0',
        'pi_author'        => 'Your Name',
        'pi_author_url'        => '',
        'pi_description'    => 'Gets member information for the supplied ID',
	    'pi_usage'        => <<<USAGE
	How to call this plugin:
	{exp:member_info:query member_id="something"}
	{/exp:member_info:query}
USAGE
	);
    
	class Member_info {
	    public __construct() {
	        $this->EE = &get_instance();
	    }
	
	    public function query() {
	        // Get params fromfetch_param(). Default value is FALSE.
	        $requested_member_id = $this->EE->TMPL->fetch_param("member_id");
	        // Get the contents of the pair tag
            $result = $this->EE->TMPL->tagdata;
    
	        // Variables to be used if we can't find the member
	        $vars = array(
	            'found' => FALSE
	        );
	        if($requested_member_id) {
	            $query = $this->EE->db->where('member_id', $requested_member_id)->get('members');
	            // Check that we have at least one row
	            if($query->num_rows() > 0) {
	                // Replace the default return vars array with the result row
	                $vars = $query->row_array();
	                $vars['found'] = TRUE;
	            }
	            // Check if request is for their own data, if not we can provide less info:
	            $vars['is_current_user'] = $requested_member_id == $this->EE->session->member_id;
	        }
    
	        // Parse results variables into the output
	        $result = $this->EE->TMPL->parse_variables($result, array($row));
    
	        // Return your results as a string
	        return $result;
	    }
	}

Here's how we'll use the plugin:

	<h2>Member Info</h2>
	{exp:member_info:query member_id="{segment_3}"}
	    {if found}
	        Screen Name: {screen_name}<br/>
	        {if is_current_user}
	            Registered Email Address: {email}<br/>
	            Username: {username}<br/>
	            <a href="{site_url}account/edit/{segment_3}">Edit</a>
	        {/if}
	    {if:else}
	        Invalid request.
	    {/if}
	{/exp:member_info:query}

Using a custom plugin with CodeIgniter's Active Record class gives us two main advantages in this example:

1. The contents of the member_id value are automatically escaped for us by the where() method so that we cannot suffer injection attacks. The previous attack URL will simply print out "Invalid request" now.
2. Our query will still work even if the DB prefix is changed since the get() method prefixes it for us automatically (we only passed in "members" rather than "exp_members" like we have to with the query tag).

Other advantages are:

• We can control the data that is queried with much more flexible conditional statements.
• We can cache the data or stop queries if they have been run too often.
• We have more precise control over the output of values.
• We can more easily filter and modify the parameters sent in to query the database.
• We can more easily provide default values (such as date ranges).

Once you get used to this technique it actually becomes just as easy as using the built in query plugin, and provides much more security and flexibility for very little time investment. I'd highly recommend trying it the next time you need to create a custom query of any kind on a project!