February 20, 2009 at 20:18

Safe Code in Drupal: Working with a Database



    (p1. Cross-site request forgery; p3. Working with user input )

    Drupal provides its own tools for accessing the database.

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

    Secondly, the database layer allows you to protect yourself from SQL injection.

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

    I'll start, perhaps, with an example in the style of which almost all novice 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 fundamentally wrong.

    Table Name Aliases



    The name of the tables should be enclosed in curly brackets, and assign aliases to them, which are recommended to always be 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 make it easy to process tables with prefixes. That is, if you have all the tables in the database called “pr_node”, “pr_users”, etc., Drupal will automatically substitute the correct prefixes for the tables in brackets. Specifying aliases in this case eliminates the need to use curly braces more than once.

    Argument Filtering



    Missing query argument filtering. This is a direct path to SQL injection. If the $typevalue turns out to be story' UNION SELECT s.sid, s.sid FROM {sessions} s WHERE s.uid = 1/*, then the entire request will already 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 take control of session identifiers, and in turn, when creating the correct session cookies, get direct admin access to the site.

    Protecting yourself from this is quite simple using query parameterization. When generating a request, Drupal uses the syntax of the sprintf function . In the query line, stubs are inserted, which are replaced by parameters that go separately. At the same time, the parameters are tested and shielded, so 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);


    Substitute List:

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


    For constructions IN (... , ... , ...), use the db_placeholders () function , which will create the desired sequence of substitutes, according to the 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 use the Devel module, you have a very simple way to get the final requests for debugging purposes. Just call the function db_queryd()with exactly the same parameters as you call db_query().


    Now, our request 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 limit ourselves to just the first ten? The first call will use the SQL LIMIT 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 management server, you need to use the construct OFFSET 0 LIMIT 10. And on some Oracle, the syntax is again different. 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 after all the arguments, you need to specify two parameters - the number of the first line, and the number of results. Our request 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 finally, if you still need pagination for everything, use the pager_query () function . It differs from the db_query_range()presence of just one optional parameter, which you can read about on the documentation page. With this function, the output of the page list 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, there are only two lines of changes. The whole routine of picking up the current page, processing, etc. Drupal takes over.

    Ability to change request modules



    Quite often, it makes sense to give other modules the ability to influence your request. In Drupal, this is implemented by a bunch of db_rewrite_sql () functions , and hook_db_rewrite_sql () hook implementations in modules. Our request 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;
     }
    }


    The elements returned from the 'join' hook will be attached to our request, 'where' will be added to the list of conditions, and our request 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, he, in fact, will go in pager_query()and will be processed as usual.

    Final example 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


    Safe Code Articles

    Tags:

    Also popular now: