Database

Secure DB against SQL injections

All code related to database MUST rely on prepared statements to pass parameters to a SQL query.

Example of DataAccessObject:

declare(strict_types=1);

namespace Tuleap\Git;

use Tuleap\DB\DataAccessObject;
use ParagonIE\EasyDB\EasyStatement;

class RepositoryDao extends DataAccessObject
{
    public function searchByName(int $project_id, string $name) : array
    {
        $sql = 'SELECT *
                FROM plugin_git_repositories
                WHERE project_id = ? AND name = ?';

        return $this->getDB()->run($sql, $project_id, $name);
    }

    public function searchByProjectIDs(array $project_ids) : array
    {
        $project_ids_in_condition = EasyStatement::open()->in('?*', $project_ids);

        $sql = 'SELECT *
                FROM plugin_git_repositories
                WHERE project_id IN ($project_ids_in_condition)';

        return $this->getDB()->safeQuery($sql, $project_ids_in_condition->values());
    }
}

Note

You might find existing code using the \DataAccessObject class or db_*() functions, in that case you will need to use the dedicated escaping methods (\DataAccessObject::quoteSmart, \DataAccessObject::escapeInt, db_es and db_ei). The usage of these deprecated interfaces should be avoided.

Database structure change with ForgeUpgrade

Each version of Tuleap is likely to differ from the next one on many levels including in it’s database structure. To manage this, ForgeUpgrade? has inbuilt internal functionality akin to that of commonly used tools such as dbdeploy or MIGRATEdb. Whereas the latter use sql and xml scripts to describe each database change, ForgeUpgrade? uses php scripts.

The upgrading of the database happens when the above command is run:

$> /usr/lib/forgeupgrade/bin/forgeupgrade --config=/etc/tuleap/forgeupgrade/config.ini update

Database scripts

• The scripts are located within the db/mysql/updates/yyyy/ directory of each plugin and of the Tuleap core, e.g. /path/to/tuleap/cardwall/db/mysql/updates/2012/

• Each script is php file that begins with the Enalean license and contains a single class.

• The class name is structured as follows: byyyyMMddhhmm_description_of_change_being_made and MUST extend the class “ForgeUpgrade?_Bucket”.

Where:

• yyyy is the year;

• MM the month;

• dd the day and so on.

The “b” is not symbolic of anything and must always be the first letter in the class name.

Sample script

<?php
/**
 * Copyright (c) Enalean, 2018. All Rights Reserved.
 * ....
 */

 declare(strict_types=1);

class b201806051455_add_cardwall_on_top_table extends ForgeUpgrade_Bucket // @phpcs:ignore
{
    public function description()
    {
        return <<<EOT
        Add table to store trackers that enable cardwall on top of them
        EOT;
    }

    public function preUp()
    {
        $this->db = $this->getApi('ForgeUpgrade_Bucket_Db');
    }

    public function up()
    {
        $sql = "CREATE TABLE IF NOT EXISTS plugin_cardwall_on_top(
                  tracker_id int(11) NOT NULL PRIMARY KEY
                )";
        $this->db->createTable('plugin_cardwall_on_top', $sql);
    }

    public function postUp()
    {
        if (! $this->db->tableNameExists('plugin_cardwall_on_top')) {
            throw new ForgeUpgrade_Bucket_Exception_UpgradeNotCompleteException('plugin_cardwall_on_top table is missing');
        }
    }
}

When creating a new script, the only methods you generally need to change are description() and up().