Safe code in Drupal: Work with the database

(P1. Cross-site request forgery; P3. Working with user input )



Drupal provides its own means to access the database.

')

Firstly, it allows not to depend on the type of DBMS used. By the way, at the moment, the layer for MySQL and PostgreeSQL is fully functioning. In the seventh Drupal this list will be expanded by Oracle and SQLite.



Secondly, the database layer helps protect against SQL injections.



The very first function that you should learn about when working with the database is db_query () .



I will begin, perhaps, with an example in the style of which almost all beginning drupallers write:

/**

* 1 -

* $type (, )

*/

$result = db_query( "SELECT nid, title FROM node WHERE type = '$type'" );



$items = array();

while ($row = db_fetch_object($result)) {

$items[] = l($row->title, "node/{$row->nid}" );

}

return theme( 'item_list' , $items);

In this example, several things are wrong at the root.

Table Name Aliases

Table names should be enclosed in curly brackets, and they should also be assigned aliases, which are recommended to be always used when referring to columns. The modified call will look like this:

$result = db_query( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '$type'" );



What will it give us? This will ensure that tables with prefixes are easily processed. That is, if you have all the tables in the database called “pr_node”, “pr_users”, etc., Drupal will automatically substitute the correct prefixes to the tables enclosed in brackets. Specifying pseudonyms will eliminate the need to use curly brackets more than once.

Argument Filtering

There is no filtering of the request arguments. This is a direct path to SQL injection. If the $type is story' UNION SELECT s.sid, s.sid FROM {sessions} s WHERE s.uid = 1/* , then the whole query will be like this:

SELECT n.nid, n.title FROM {node} n WHERE n.type = 'story' UNION SELECT s.sid, s.sid FROM {sessions} s WHERE s.uid = 1/*'



which will allow the fraudster to get hold of the session IDs and, in turn, when creating the correct session cookie, get direct admin access to the site.



To protect against this is quite simple, using the parameterization of the query. When forming a query, Drupal uses the syntax of the sprintf function. In the query string, stubs are inserted, which are replaced by parameters that go separately. In this case, the parameters are tested and screened, so that you can forget about injections using this approach. Here are some examples:

db_query( "SELECT n.nid FROM {node} n WHERE n.nid > %d" , $nid);

db_query( "SELECT n.nid FROM {node} n WHERE n.type = '%s'" , $type);

db_query( "SELECT n.nid FROM {node} n WHERE n.nid > %d AND n.type = '%s'" , $nid, $type);

db_query( "SELECT n.nid FROM {node} n WHERE n.type = '%s' AND n.nid > %d" , $type, $nid);



List of substitutes:

• % d - for integers
• % f - for floating-point numbers, i.e. fractional (floats)
• % s - for strings (however, note that in the query, quotation marks are set around the string)
• % b - binary data (no need to wrap in quotes)
• %% - replaced by% (for example, for LIKE %monkey% )

For IN (... , ... , ...) constructions, use the db_placeholders () function, which will create the necessary sequence of substitutes, using a given array of parameters, for example:

$nids = array(1, 5, 449);

db_query( 'SELECT * FROM {node} n WHERE n.nid IN (' . db_placeholders($nids) . ')' , $nids);



If you are using the Devel module, you have a very simple way of getting final queries for debugging purposes. Just call the db_queryd() function with exactly the same parameters as you call db_query() .

Now, our query will look like this:

$result = db_query( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'" , $type);



Ranking query results

Our example on a large site will display a huge list of nodes. What if we can be limited to just the top ten? The first urge is to use the LIMIT SQL construct, for example

SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s' LIMIT 0, 10



and everything seems to be fine, but on Postgree SQL this code will lead to an error, since with this control server you need to use the OFFSET 0 LIMIT 10 construct. And on some Orakle, the syntax is different again. What to do?



The answer is to use db_query_range () to limit the number of query results. Its use is similar to db_query, except that in after all the arguments, you need to specify two parameters - the number of the first line, and the number of results. Our query will translate into the following:

// 10

$result = db_query_range( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'" , $type, 0, 10);



And lastly, if you still need pagination for everything, use the pager_query () function. It differs from db_query_range() by the presence of just one optional parameter, which you can read about on the documentation page. With this function, flipping pages is as simple as twice two:

/**

* 2 - ,

*/

//

$result = pager_query( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'" , $type, 0, 10);



$items = array();

while ($row = db_fetch_object($result)) {

$items[] = l($row->title, "node/{$row->nid}" );

}



$output = theme( 'item_list' , $items);



//

$output .= theme( 'pager' );



return $output;



As you can see, just two lines of change. The whole routine of picking up the current page, processing, etc. Drupal takes over completely.

The ability to change the request modules

Quite often it makes sense to give other modules the opportunity to influence your request. In Drupal, this is implemented by the link function db_rewrite_sql () , and hook implementations hook_db_rewrite_sql () in modules. Our query will look like this:

$result = pager_query(db_rewrite_sql( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'" , 'n' , 'nid' ), $type, 0, 10);



and here is an example of a hook implementation, so that you have an idea of ​​what is happening:

// ,

function my_module_db_rewrite_sql($query, $primary_table, $primary_field, $args) {

switch ($primary_field) {

case 'nid' :

if ($primary_table == 'n' ) {

$ return [ 'join' ] = "LEFT JOIN {users} u ON $primary_table.uid = u.uid" ;

$ return [ 'where' ] = 'u.login > ' . time() - 60 * 60 * 24;

}

return $ return ;

break ;

}

}



Items returned from the 'join' hook will be attached to our query, 'where' will be added to the list of conditions, and our query after processing will be like this:

SELECT n.nid, n.title FROM {node} n LEFT JOIN {users} u ON n.uid = u.uid WHERE n.type = '%s' AND u.login > 199976743



After that, it will actually go to pager_query() and will be processed as usual.

Final sample code

/**

* 3 - ,

*/

// db_rewrite_sql

$result = pager_query(db_rewrite_sql( "SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'" , 'n' , 'nid' ), $type, 0, 10);



$items = array();

while ($row = db_fetch_object($result)) {

$items[] = l($row->title, "node/{$row->nid}" );

}



$output = theme( 'item_list' , $items);



$output .= theme( 'pager' );



return $output;



* This source code was highlighted with Source Code Highlighter .

useful links

• Database functions
• Drupal SQL Encoding Standards

Articles cycle "Safe Code"

• Cross-site request forgery
• Work with database