1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Database prepare() function

Discussion in 'Archived Docs' started by Nick, Jul 14, 2009.

  1. Nick

    Nick Well-Known Member

    The prepare() function in ez_sql_core.php is also a custom function, though borrowed from Wordpress. It's used for sanitizing SQL queries and there are some examples here in our plugin documentation.

    There is one significant difference between the Wordpress version and Hotaru's: the ability to pass an array of arguments built on the fly. Instead of the using the usual $db->prepare($sql, $var1, $var2, etc.), we add $sql to the head of $prepare_array followed by an unknown number of $value arguments. That array is then sent to the prepare() function which checks if the first argument is an array. If so, it works with the array elements just as if they had been passed to the function the traditional way.

    Example:

    In /plugins/submit/libs/Post.php:

    PHP:
        /**
         * Gets all the posts from the database
         *
         * @param array $vars - search parameters
         * @param int $limit - no. of rows to retrieve
         * @param bool $all - true to retrieve ALL rows, else default 20
         * @param string $select - the select clause
         * @param string $orderby - the order by clause
         * @return array|false $prepare_array is the prepared SQL statement
         *
         * Example usage: $post->filter(array('post_tags LIKE %s' => '%tokyo%'), 10);
         */    
        
    function filter($vars = array(), $limit 0$all false$select '*'$orderby 'post_date DESC')
        {
             if(!isset(
    $filter)) { $filter ''; }
            
    $prepare_array = array();
            
    $prepare_array[0] = "temp";    // placeholder to be later filled with the SQL query.
            
            
    if (!empty($vars)) {
                
    $filter " WHERE ";
                foreach (
    $vars as $key => $value) {
                    
    $filter .= $key " AND ";    // e.g. " post_tags LIKE %s "
                    
    array_push($prepare_array$value);
                }
                
    $filter rstrtrim($filter"AND ");
            }
            
            if (
    $all == true) {
                
    $limit '';
            } elseif (
    $limit == 0) { 
                
    $limit "LIMIT 20"
            } else { 
                
    $limit "LIMIT " $limit
            }
            
            if (
    $orderby) { $orderby "ORDER BY " $orderby; }
            
            
    $sql "SELECT " $select " FROM " TABLE_POSTS $filter " " $orderby " " $limit;
                    
            
    $prepare_array[0] = $sql;
                    
            
    // $prepare_array needs to be passed to $db->prepare, i.e. $db->get_results($db->prepare($prepare_array));
                    
            
    if ($prepare_array) { return $prepare_array; } else { return false; }
        }
    In /libs/extensions/ezSQL/ez_sql_core.php:

    PHP:
            function prepare($args=null
            {
                
                if (
    is_null$args ))
                    return;
                                
                
    $args func_get_args();
                
                
    // This is a Hotaru hack, enabling args to be built on the fly.
                
    if(is_array($args[0]))
                {
                    
    // See Submit plugin: class.post.php get_posts() for an example.
                    
    $args $args[0];
                }
                            
                
    $query array_shift($args);
                
                
    // in case someone mistakenly already singlequoted it
                
    $query str_replace("'%s'"'%s'$query); 
                
                
    $query str_replace('"%s"''%s'$query); // doublequote unquoting
                
                
    $query str_replace('%s'"'%s'"$query); // quote the strings
                
                
    array_walk($args, array(&$this'escape_by_ref'));
                
                return @
    vsprintf($query$args);
            }
    Notes

    One observation worth noting is that ORDER BY %s doesn't work. That's because the purpose of prepare() is to quote your strings, but field names (e.g. category_name) aren't supposed to be quoted.
     
    Last edited: Sep 23, 2009

Share This Page