Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 13 Next »

With CONTENIDO 4.9.0 some core components were refactored. This also affected the class cDb (formerly known as DB_Contenido) and its parent classes. Some code was ported to PHP 5, functions were extended or simply added. This results in much more secure and easier abstraction compared with its predecessor. This article introduces some of the changes that were made.

query()

The method cDb::query() became much more fexible and now can be called in different variants with different parameter lists.

1. Variant

This is the plain old syntax that is already supported in CONTENIDO 4.8.x.

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();

// query(string $statement)
$db->query('SELECT * FROM `' . $cfg['tab']['art_lang'] . '` WHERE idart = ' . cSecurity::toInteger($idart) . ' AND idlang = ' . cSecurity::toInteger($idlang));
if ($db->nextRecord()) {
    echo $db->f('title');
}

2. Variant

An alternative is to call cDb::query() with multiple parameters, where the first is the SQL statement in which subsequent parameters will be embedded. The embedding will be performed in a way similar to formatting values with a format string.

// query(string $statement [, mixed $args [, mixed $... ]])
$db->query('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);

In this example the method will be called with four parameters. Please assure that for each formatting instruction (e.g. %s) exactly one parameter with its value is given.

  • %s will be substituted by $cfg['tab']['art_lang']
  • the 1st %d will be substituted by $idart
  • the 2nd %d will be substituted by $idlang

Using this variant strings don't have to be manually escaped (cSecurity::escapeDB()) and integer values don't have to be casted (cSecurity::toInteger()) anymore. This will be assured automatically and the code will become more concise and more readable.

3. Variant

Another variant is to call cDb::query() with exactly two parameters, the SQL statement as in the second variant and, as second parameter, an array containing all values that should be used for substitution.

// query(string $statement, array $values)
$values = array($cfg['tab']['art_lang'], $idart, $idlang);
$db->query('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $values);

Compared the the second variant, all values will just be passed together as an array but there is no difference in the behaviour.

4. Variant

Eventually you can choose to use "named parameters". This variant also takes exactly two parameters, the SQL statement as in the second variant and, as second parameter, an associative array containing all values that should be used for substitution.

$values = array(
    'table_art_lang' => $cfg['tab']['art_lang'],
    'idart' => cSecurity::toInteger($idart),
    'idlang' => cSecurity::toInteger($idlang)
);
$db->query('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang', $values);

Using this variant integer valus should be casted cause the format string contains no formatting instruction like %d. Though strings still don't have to be escaped.

The tokens start with a colon and equal the arrays keys.

"Named parameters" should not be confused with prepared statements. Database driver that support prepared statements parse the SQL statement once and reuse this on consecutive uses whereas this version perform the substitution on every call!

prepare()

The new method cDb::prepare() is nearly itentical to cDb::query() with two exceptions

  1. Whilq cDb::query() executes the statement, cDb::prepare() just returns the prepared statement.
  2. cDb::prepare() cannot be called with a single parameter, i.e. the SQL statement.

Occasionally you don't wanr to execute an SQL statement in place, but prepare it, e.g. for logging purposes. This is where cDb::prepare() is handy.

1. Variant

The first is the SQL statement in which subsequent parameters will be embedded. The embedding will be performed in a way similar to formatting values with a format string.

// string prepare(string $statement [, mixed $args [, mixed $... ]])
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);

2. Variant

Call with exactly two parameters, the SQL statement as in the first variant and, as second parameter, an array containing all values that should be used for substitution.

// string prepare(string $statement, array $values)
$values = array($cfg['tab']['art_lang'], $idart, $idlang);
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $values);
$db->query($sql);

3. Variant

Call with exactly two parameters with "named parameters". The first parameter is the SQL statement as in the first variant and, as second parameter, an associative array containing all values that should be used for substitution.

// string prepare(string $statement, array $values)
$values = array(
    'table_art_lang' => $cfg['tab']['art_lang'],
    'idart' => (int) $idart,
    'idlang' => (int) $idlang
);
$sql = $db->prepare('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang', $values);
$db->query($sql);

insert()

The new method insert() can be used to create a new record in a given database table. It is an alternative to the manual creation of an INSERT statement.

The manual creation of an INSERT statement usually looks something like this:

$idcode = 123;
$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = "INSERT INTO ".$cfg["tab"]["code"]." (idcode, idcatart, code, idlang, idclient)
        VALUES (".cSecurity::toInteger($idcode).", ".cSecurity::toInteger($idcatart).",
        '".cSecurity::escapeDB($code, $db)."', ".cSecurity::toInteger($idlang).",
        ".cSecurity::toInteger($idclient).")";
$db->query($sql);

The method insert() is an alternative. The first parameter is the database table name whereas the second parameter is an associative array with array keys as column names and array values as record values.

// bool insert(string $tablename, array $fields)

$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";

$fields = array(
    'idcatart' => (int) $idcatart,
    'idlang' => (int) $idlang,
    'idclient' => (int) $idclient,
    'code' => $code,
);

$cfg = cRegistry::getConfig();

$db = cRegistry::getDb();
$result = $db->insert($cfg['tab']['code'], $fields);

Integer values should be casted whereas strings will be escaped automatically.

The observant reader might have noticed that no idcode was defined. From CONTENIDO 4.9 on the sequence table for managing IDs was removed. It's no more neccessary to retrieve the next ID via $db->nextid() as ID columns are now defined as AUTOINCREMENT and MySQL is responsible for the determination of IDs.

buildInsert()

While insert() creates an INSERT statement and executes it immediatly, buildInsert() will just create and return the statement. This is usefull if the statement should be used otherwise (e.g. logged) before being executed.

// string buildInsert(string $tablename, array $fields)

$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";

$fields = array(
    'idcatart' => (int) $idcatart,
    'idlang' => (int) $idlang,
    'idclient' => (int) $idclient,
    'code' => $code,
);

$cfg = cRegistry::getConfig();

$db = cRegistry::getDb();
$sql = $db->buildInsert($cfg['tab']['code'], $fields);
$result = $db->query($sql);

update()

The new method update() is used to update an existing record. It's an alternative to the manual creation of an UPDATE statement.

The manual creation of an UPDATE statement usually looks something like this:

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = "UPDATE ".$cfg["tab"]["code"]." SET code = '".cSecurity::escapeDB($code, $db)."'
        WHERE idcode = " . cSecurity::toInteger($idcode);
$db->query($sql);

The method update() is an alternative. The first parameter is the database table name whereas the second parameter is an associative array with array keys as column names and array values as record values. Furthermore there is an associative array as third parameter containing the WHERE conditions.

// bool update(string $tablename, array $fields, array $where)

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";

$fields = array(
    'code' => $code,
);
$where = array(
    'idcode' => (int) $idcode
);
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$result = $db->update($cfg['tab']['code'], $fields, $where);

Integer values should be casted whereas strings will be escaped automatically.

If the third parameter contains several conditions these will be ANDed.

buildUpdate()

Während update() die Anweisung zusammenbaut und auch gleich ausführt, liefert buildUpdate() die zusammengebaute Anweisung zurück.

// string buildUpdate(string $tablename, array $fields, array $where)

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";

$fields = array(
    'code' => $code,
);
$where = array(
    'idcode' => (int) $idcode
);

$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->buildUpdate($cfg['tab']['code'], $fields, $where);
$result = $db->query($sql);

toArray()

Dei neue Funktion toArray() liefert den aktuellen Datensatz, als Array zurück. Dabei ist es möglich den Datensatz als assoziatives und/oder indexiertes Array zurückzuliefern. Per default wird ein assoziatives Array zurückgeliefert.

// string toArray(string $fetchmode)

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);
if ($db->nextRecord()) {
    $assocRs = $db->toArray(); // oder mit Parameter cDbDriverHandler::FETCH_ASSOC
    echo "<pre>\$assocRs: " . print_r($assocRs, true) . "<pre>";

    $indexedRs = $db->toArray(cDbDriverHandler::FETCH_NUMERIC);
    echo "<pre>\$indexedRs: " . print_r($indexedRs, true) . "<pre>";

    $bothdRs = $db->toArray(cDbDriverHandler::FETCH_BOTH);
    echo "<pre>\$bothdRs: " . print_r($bothdRs, true) . "<pre>";
}

toObject()

Dei neue Funktion toObject() liefert den aktuellen Datensatz, als Objekt zurück das eine Instanz von stdClass ist (Standard Klasse in PHP). Die Eigenschaften des Objekts entsprechen den Feldnamen der Tabelle.

// string toObject(string $fetchmode)

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);
if ($db->nextRecord()) {
    $rs = $db->toObject();
    echo "<pre>\$rs: " . print_r($rs, true) . "<pre>";

    echo "<pre>idartlang: " . $rs->idartlang . "<pre>";
    echo "<pre>title: " . $rs->title . "<pre>";
    echo "<pre>author: " . $rs->author . "<pre>";
}

escape()

Mit der neuen Funktion escape() kann man Variablen vom Typ String bei Bedarf escapen. Diese Funktion ist eine Alternative zu cSecurity::escapeDB().

// string escape(string $str)

$db = cRegistry::getDb();

$code = "<html>... more code n' fun ...</html>";

$escapedCode = $db->escape($code);

// Zuvor war dies folgendermaßen möglich

$db = cRegistry::getDb();

$code = "<html>... more code n' fun ...</html>";

$escapedCode = cSecurity::escapeDB($code, $db);

Das waren die wichtigsten Neuerungen in den Datenbank-Klassen cDb und Eltern-Klassen. Entwickler haben nun mehr Möglichkeiten, um SQL-Anweisungen zu generieren, zum Teil ist das Erstellen der SQL-Anweisungen einfacher sowie lesbarer geworden und der Sicherheitsaspekt wurde zum Teil in die DB-Adapter verlagert.

  • No labels