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 5 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' => (int) $idart,
    'idlang' => (int) $idlang
);
$db->query('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang', $values);

 

 

Bei dieser Version ist zu beachten, dass man integer Werte sicherheitshalber in ein integer castet, da die SQL-Anweisung keine Formatierungs-Anweisungen wie %d enthält. Strings muss man nicht escapen, dies funktioniert wie zuvor.
Die Platzhalter haben einen vorangestellten Doppelunkt ":" und sind ansonsten identisch mit den Schlüsseln im assoziativen Array.
Die Verwendung von "named Parametern" sollte nicht prepared Statements verwechselt werden. DB Treiber, die prepared Statements unterstützen, parsen die SQL-Anweisung einmal und verwenden die geparste Version für den mehrfachen Einsatz. Diese Version führt die Ersetzungen jedes Mal neu aus.


  • No labels