Knowledge Base

Prepared statements

All new code must use prepared statements for any SQL execution.

All SQL queries use prepared statements in the existing codebase after an extensive effort to port all queries.

The new functions ps_query(), ps_array() and ps_value() replace functions sql_query(), sql_array() and sql_value() respectively. They work exactly the same way as the functions they replace except they have a new second parameter, which is used to pass in parameters to use in the SQL.

Using these functions means a prepared statement is used for query execution when parameters are passed also, which is faster and much more secure.

Contrary to the previous functions, parameters passed in must NOT be escaped and quotes must not be used around parameters in SQL.

The new second parameter takes the form of an array which has the values type, parameter, type, parameter and so on, with type being one of:

• i - integer
• d - double
• s - string
• b - BLOB

We will mainly use "i" and "s" in ResourceSpace - we use very few double values and BLOB is for binary only - also rare.

There are also a couple of helper functions that remove some of the potential repetitiveness that can exist with prepared statements. ps_param_insert() simply returns a list of parameter placeholders of the specified number e.g. "?,?,?,?". ps_param_fill() returns an array with the specified type inserted. Both of these together simplify the case when using an array of values in SQL as you'll see in one of the examples below.

Examples

First we have the very simple case of a straightforward query with one parameter.

function delete_all_resource_nodes($resourceid)
   {
   sql_query("DELETE FROM resource_node WHERE resource ='$resourceid';");
   ps_query("DELETE FROM resource_node WHERE resource = ?", array("i",$resourceid)); 
   }

The variable $resourceid should only ever be an integer so we specify the "i" type in the array. Note the lack of escaping in the old code - this function as it stood requires that the value was already escaped, which is bad practice and underlines the reason for moving to prepared statements.

Now for an example with multiple parameters.

sql_query("INSERT INTO node_keyword (node, keyword, position) VALUES ('" . escape_check($node) . "', '" . escape_check($keyword_ref) . "', '" . escape_check($position) . "')");
s_query("INSERT INTO node_keyword (node, keyword, position) VALUES (?, ?, ?)",array("i",$node,"i",$keyword_ref,"i",$position));

Notice that escape_check() has been removed as it's no longer needed - and in fact would be wrong to use as the escape characters would end up in the database.

Sometimes the situation is more complicated with the query being constructed over several lines of code, with if statements to determine if additional sections of SQL are needed. In this case the parameter count would be different depending on which 'if' blocks are executed. Therefore, while constructing the SQL string it's necessary to keep a $parameters array alongside so both remain "in sync".

$query = sprintf('SELECT COUNT(*) AS value FROM node WHERE resource_type_field = \'%s\' AND %s ORDER BY order_by ASC;', escape_check($resource_type_field), (trim($parent)=="") ? 'parent IS NULL' : 'parent = \'' . escape_check($parent) . '\''
       );
       
$query = "SELECT COUNT(*) AS value FROM node WHERE resource_type_field = ?";
$parameters=array("i",$resource_type_field);

if (trim($parent)=="")
      {
      $query.=" AND parent IS NULL ";
      }
else   
      {
      $query.=" AND parent = ? ";
      $parameters=array_merge($parameters,array("i",$parent));
      }
$query.="ORDER BY order_by ASC;";
$nodes_counter = sql_value($query, 0);
$nodes_counter = ps_value($query, $parameters, 0);

Note the line in bold where the parameters array is extended when the parameter is added to the query in the 'else' case. Instead of using array_merge() you might deem it more readable to simply add the extra type and parameter to the array like so:

$parameters[]="i";$parameters[]=$parent;

In the case where the query passes in an array of values, the helper functions come in handy.

$query = "SELECT * FROM node WHERE ref IN ('" . implode('\', \'', $refs) . "')";
return sql_query($query, "schema");

$query = "SELECT * FROM node WHERE ref IN (" . ps_param_insert(count($refs)) . ")";
$parameters = ps_param_fill($refs,"i");
return ps_query($query, $parameters, "schema");

This example uses ps_array() and also shows that the other function parameters work just as they did previously, in this case stating that the results must be cached using the "schema" cache group.

$omitfields      = sql_array("SELECT ref AS `value` FROM resource_type_field WHERE omit_when_copying = 1", "schema");
$omitfields      = ps_array("SELECT ref AS `value` FROM resource_type_field WHERE omit_when_copying = 1", array(), "schema");

Note also that this function doesn't have any parameters. So we pass in an empty array for the parameters section. Internally, ps_query() (called by ps_array()) will use standard SQL execution and not a prepared statement as prepared statements will only work when there are parameters. However we're still going to the effort of moving such queries to the new functions as it shows we've "considered" the function call. It means we can search for sql_value, sql_array, sql_query in the code and be sure we've considered everything when we get no results.

Combining multiple prepared statements

If you need to join multiple prepared statements use the PreparedStatementQuery type.

// src: A.php
$user_filter = new PreparedStatementQuery('u.ref = ?', ['i', 1]);

// src: B.php (the consumer of the $user_filter)
$user = ps_query(
    "SELECT u.username, u.fullname FROM user AS u WHERE u.approved = ? AND {$user_filter->sql}",
    array_merge(['i', $approved], $user_filter->parameters)
);