Database prepare() function

[Nick]

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 (http://hotarucms.org/showpost.php?p=19&postcount=2) 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:


/**
* 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:


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.