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();
$sql // query(string $statement)
$db->query(= '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', $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.
Occasionally you don't wanr want to execute an SQL statement in place, but prepare it, e.g. for logging purposes. This is where cDb::prepare() is handy.
...
| 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)
$values$sql = array($cfg['tab''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'$sql, $values);
$db->query($sql); |
...
| Code Block |
|---|
| language | php |
|---|
| linenumbers | true |
|---|
|
// string prepare(string $statement, array $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', $values);
$db->query($sql);$sql, $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.
...
| 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
(' VALUES (".cSecurity:. 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.
...
| Code Block |
|---|
| language | php |
|---|
| linenumbers | true |
|---|
|
// 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>";
} |
...
| Code Block |
|---|
| language | php |
|---|
| linenumbers | true |
|---|
|
// string escape(string $str)
$db$escapedCode = cRegistry::getDb();
$code = ->escape("<html>... more code n' fun ...</html>";
$escapedCode = $db->escape($code);
// Zuvorold warstyle dies folgendermaßen möglich
$dbexcaping
$escapedCode = cRegistrycSecurity::getDb();
$code = escapeDB("<html>... more code n' fun ...</html>";
$escapedCode = cSecurity, cRegistry::escapeDB($code, $dbgetDb()); |
These were the most important changes of the database class cDb and its predecessors. Developers now have more options to create SQL statements. 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 verlagertThis leads to easier and much more readable code.