Page Comparison

Info
Original source (german) - Posting from xmurrix in Community Forum

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.

...

Code Block

language	php
linenumbers	true

// query(string $statement)
$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();

// query(string $statement)
$db->query($sql = 'SELECT * FROM `' . $cfg['tab']['art_lang'] . '`
	WHERE idart = ' . cSecurity::toInteger($idart) . '
	AND idlang = ' . cSecurity::toInteger($idlang) . ';';
$db->query($sql);

// access values
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.

Code Block

language	php
linenumbers	true

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

...

Code Block

language	php
linenumbers	true

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

...

$sql, $values);

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

...

Code Block

language	php
linenumbers	true

$values$sql = array('SELECT * FROM   '`:table_art_lang'lang` => $cfg['tab']['art_lang'],
    WHERE idart = :idart AND idlang = :idlang;';
$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'$sql, $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 new method cDb::prepare() is nearly itentical to cDb::query() with two exceptions

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

...

Code Block

language	php
linenumbers	true

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

...

Code Block

language	php
linenumbers	true

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

...

Code Block

language	php
linenumbers	true

// string prepare(string $statement, array $values) $values)
$sql = 'SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang;';
$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'$sql, $values);
$db->query($sql);

...

Code Block

language	php
linenumbers	true

$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.

...

Versions Compared

Old Version 17

New Version Current

Key

2. Variant