commit 5bfe441eba2b4efc241aafdaab89d1ba22ccfb45
parent 5993a151e12f665bd9b1127219c52e4b2317883b
Author: Jan Dankert <devnull@localhost>
Date: Fri, 6 Jan 2017 22:51:33 +0100
Refactoring des Datenbank-Layers: Viele Vereinfachungen. Parameternamen dürfen nur noch 1x pro Query vorkommen. Es wird aktuell nur der PDO-Treiber unterstützt!
Diffstat:
10 files changed, 291 insertions(+), 580 deletions(-)
diff --git a/auth/DatabaseAuth.class.php b/auth/DatabaseAuth.class.php
@@ -22,10 +22,10 @@ class DatabaseAuth implements Auth
$authdb = new DB( $authDbConf );
- $sql = $db->sql( $conf['security']['authdb']['sql'] );
+ $sql = $authdb->sql( $conf['security']['authdb']['sql'] );
$sql->setString('username',$user );
$sql->setString('password',$password);
- $row = $authdb->getRow( $sql );
+ $row = $sql->getRow();
$ok = !empty($row);
// noch nicht implementiert: $authdb->close();
diff --git a/config/config-default.php b/config/config-default.php
@@ -166,7 +166,26 @@ $conf['countries']['XH']='Xhosa';
$conf['countries']['YO']='Yoruba';
$conf['countries']['ZH']='Chinese';
$conf['database'] = array();
-$conf['database']['default']='';
+$conf['database']['defaults']=array();
+$conf['database']['defaults']['prefix' ]= '';
+$conf['database']['defaults']['suffix' ]= '';
+$conf['database']['defaults']['enabled' ] = false;
+$conf['database']['defaults']['comment' ] = '';
+$conf['database']['defaults']['type' ] = 'pdo';
+$conf['database']['defaults']['dsn' ] = 'mysql:localhost';
+$conf['database']['defaults']['user' ] = '';
+$conf['database']['defaults']['password' ] = '';
+$conf['database']['defaults']['host' ] = '';
+$conf['database']['defaults']['database' ] = '';
+$conf['database']['defaults']['base64' ] = false;
+$conf['database']['defaults']['persistent' ] = true;
+$conf['database']['defaults']['charset' ] = 'UTF-8';
+$conf['database']['defaults']['connection_sql'] = '';
+$conf['database']['defaults']['cmd' ] = '';
+$conf['database']['defaults']['prepare' ] = true;
+$conf['database']['defaults']['transaction'] = true;
+$conf['database']['defaults']['update' ] = array();
+$conf['database']['defaults']['auto_update'] = true;
$conf['date'] = array();
$conf['date']['format'] = array();
$conf['date']['format']['SHORT']= "";
diff --git a/db/Database.class.php b/db/Database.class.php
@@ -1,33 +1,29 @@
<?php
-//
-// +----------------------------------------------------------------------+
-// | PHP version 4.0 |
-// +----------------------------------------------------------------------+
-// | Copyright (c) 1997-2001 The PHP Group |
-// +----------------------------------------------------------------------+
-// | This source file is subject to version 2.02 of the PHP license, |
-// | that is bundled with this package in the file LICENSE, and is |
-// | available at through the world-wide-web at |
-// | http://www.php.net/license/2_02.txt. |
-// | If you did not receive a copy of the PHP license and are unable to |
-// | obtain it through the world-wide-web, please send a note to |
-// | license@php.net so we can mail you a copy immediately. |
-// +----------------------------------------------------------------------+
-// | Authors: Stig Bakken <ssb@fast.no> |
-// | Jan Dankert <phpdb@jandankert.de> |
-// +----------------------------------------------------------------------+
-//
-
-// This is the database abstraction layer. This class was inspired by the
-// PHP-Pear-DB package. Thanks to its developers.
+// OpenRat Content Management System
+// Copyright (C) 2002-2006 Jan Dankert, jandankert@jandankert.de
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
+
/**
* Darstellung einer Datenbank-Verbindung.
+ *
* Fuer die echten DB-Aufrufe werden die entsprechenden
* Methoden des passenden Clients aufgerufen.
*
- * Diese Klasse stammt urspruenglich aus dem PHP-Pear-DB-Projekt und unterliegt
- * daher auch der PHP-licence.
+ * Diese Klasse stammt urspruenglich aus dem PHP-Pear-DB-Projekt, wurde hier aber intensiv veraendert.
*
* @author Jan Dankert
* @package openrat.database
@@ -82,32 +78,13 @@ class DB
* Erwartet die Datenbank-Konfiguration als Parameter.
*
* @param Array Konfiguration der Verbindung
- * @return Status 'true' wenn Verbindung erfolgreich aufgebaut.
+ * @param boolean admin Wenn es eine Admin-DB-Verbindung werden soll, die auch DDL ausfuehren darf
*/
- public function DB( $conf,$admin=false )
+ public function DB( $dbconf,$admin=false )
{
- $defaultConf = array( 'prefix' => '',
- 'suffix' => '',
- 'enabled' => true,
- 'comment' => '',
- 'type' => 'mysqli',
- 'user' => '',
- 'password' => '',
- 'host' => '',
- 'database' => '',
- 'base64' => false,
- 'persistent' => true,
- 'charset' => 'UTF-8',
- 'connection_sql' => '',
- 'cmd' => '',
- 'prepare' => true,
- 'transaction' => true,
- 'update' => array(),
- 'auto_update' => true
- );
+ global $conf;
- $this->available = false;
- $this->conf = $conf + $defaultConf;
+ $this->conf = $dbconf + $conf['database']['defaults']; // linksstehender Operator hat Priorität!
if ( $admin )
{
@@ -116,8 +93,6 @@ class DB
$this->conf = $this->conf['update'] + $this->conf; // linksstehender Operator hat Priorität!
}
$this->connect();
-
- return $this->available;
}
@@ -166,7 +141,10 @@ class DB
if ( ! empty($this->conf['connection_sql']) )
{
$cmd = $this->conf['connection_sql'];
- $ok = $this->client->query($cmd);
+
+ $sql = $this->sql($cmd);
+
+ $ok = $sql->execute();
if ( ! $ok )
{
@@ -221,7 +199,7 @@ class DB
public function sql( $sql )
{
- return new Statement( $sql,$this->client,$this->id);
+ return new Statement( $sql,$this->client,$this->conf);
}
}
diff --git a/db/DbUpdate.class.php b/db/DbUpdate.class.php
@@ -28,12 +28,12 @@ class DbUpdate
{
if ( $installVersion > 2 )
{
- $sql = new Sql('INSERT INTO {t_version} (id,version,status,installed) VALUES( {id},{version},{status},{time} )',$db->id);
+ $sql = $db->sql('INSERT INTO {{version}} (id,version,status,installed) VALUES( {id},{version},{status},{time} )',$db->id);
$sql->setInt('id' , $installVersion);
$sql->setInt('version', $installVersion);
$sql->setInt('status' , OR_DB_STATUS_UPDATE_PROGRESS);
$sql->setInt('time' , time() );
- $db->query( $sql );
+ $sql->query( $sql );
$db->commit();
}
@@ -46,11 +46,11 @@ class DbUpdate
if ( $installVersion > 2 )
{
- $sql = new Sql('UPDATE {t_version} SET status={status},installed={time} WHERE version={version}',$db->id);
+ $sql = $db->sql('UPDATE {{version}} SET status={status},installed={time} WHERE version={version}',$db->id);
$sql->setInt('status' , OR_DB_STATUS_UPDATE_SUCCESS);
$sql->setInt('version', $installVersion);
$sql->setInt('time' , time() );
- $db->query( $sql );
+ $sql->query( $sql );
$db->commit();
}
}
@@ -67,14 +67,14 @@ class DbUpdate
private function afterUpdate( $db )
{
// Benutzer zählen.
- $sql = new Sql('SELECT COUNT(*) From {t_user}',$db->id);
- $countUsers = $db->getOne( $sql );
+ $sql = $db->sql('SELECT COUNT(*) From {{user}}',$db->id);
+ $countUsers = $sql->getOne( $sql );
// Wenn noch kein Benutzer vorhanden, dann einen anlegen.
if ( $countUsers == 0 )
{
- $sql = new Sql("INSERT INTO {t_user} (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Account for administration tasks.','default',1)",$db->id);
- $db->query( $sql );
+ $sql = $db->sql("INSERT INTO {{user}} (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Account for administration tasks.','default',1)",$db->id);
+ $sql->query( $sql );
$db->commit();
}
}
@@ -83,26 +83,26 @@ class DbUpdate
private function getDbVersion( $db )
{
- $sql = new Sql('SELECT 1 FROM {t_version}',$db->id);
- $versionTableExists = $db->testQuery( $sql );
+ $sql = $db->sql('SELECT 1 FROM {{version}}',$db->id);
+ $versionTableExists = $sql->testQuery();
if ( $versionTableExists )
{
// Prüfen, ob die vorherigen Updates fehlerfrei sind.
- $sql = new Sql(<<<SQL
- SELECT COUNT(*) FROM {t_version} WHERE STATUS=0
+ $sql = $db->sql(<<<SQL
+ SELECT COUNT(*) FROM {{version}} WHERE STATUS=0
SQL
,$db->id);
- $countErrors = $db->getOne($sql);
+ $countErrors = $sql->getOne($sql);
if ( $countErrors > 0 )
Http::serverError('Database error','there are dirty versions (means: versions with status 0), see table VERSION for details.');
// Aktuelle Version ermitteln.
- $sql = new Sql(<<<SQL
- SELECT MAX(version) FROM {t_version}
+ $sql = $db->sql(<<<SQL
+ SELECT MAX(version) FROM {{version}}
SQL
,$db->id);
- $version = $db->getOne($sql);
+ $version = $sql->getOne($sql);
if ( is_numeric($version) )
return $version; // Aktuelle Version.s
@@ -113,8 +113,8 @@ SQL
}
else
{
- $sql = new Sql('SELECT 1 FROM {t_project}',$db->id);
- $projectTableExists = $db->testQuery( $sql );
+ $sql = $db->sql('SELECT 1 FROM {{project}}',$db->id);
+ $projectTableExists = $sql->testQuery();
if ( $projectTableExists )
// Entspricht dem Stand vor Einführung der automatischen Migration.
diff --git a/db/Sql.class.php b/db/Sql.class.php
@@ -0,0 +1,115 @@
+<?php
+// OpenRat Content Management System
+// Copyright (C) 2002-2006 Jan Dankert, jandankert@jandankert.de
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
+
+
+
+/**
+ * SQL-Anweisung.<br>
+ * <br>
+ * Darstellen eines SQL-Statements incl. Methoden zum Fuellen von
+ * Platzhaltern im SQL-Befehl.<br>
+ * <br>
+ * Beispiel<br>
+ * <pre>
+ * // Neues Objekt erzeugen mit SQL-Anweisung
+ * $sql = $db->sql('SELECT * FROM xy WHERE id={uid} AND name={name}');
+ *
+ * // Parameter f�llen
+ * $sql->setInt ('uid' ,1 );
+ * $sql->setString('name','peter');
+ *
+ * // Fertige SQL-Anweisung verwenden
+ * $xy->execute( $sql->query );
+ * </pre>
+ * <br>
+ * Ziele dieser Klasse sind:<br>
+ * - Schreiben einfacher SQL-Anweisungen ohne Stringverarbeitung<br>
+ * - Verhindern von SQL-Injection.<br>
+ * <br>
+ *
+ * @author Jan Dankert, $Author$
+ * @version $Revision$
+ * @package openrat.services
+ */
+
+class Sql
+{
+ /**
+ * SQL-Anweisung.
+ */
+ var $query;
+
+ /**
+ * Ein 1-dimensionales Array mit den Positionen der Parameter.<br>
+ * <br>
+ * Beispiel:<br>
+ * <pre>
+ *
+ * Array
+ * (
+ * [lid] => 16
+ * [oid] => 24
+ * )
+ * </pre>
+ */
+ var $param = array();
+
+
+ /**
+ * Erzeugt ein SQL-Objekt und analysiert die SQL-Anfrage.
+ */
+ function Sql( $query = '' )
+ {
+ $this->parseSourceQuery( $query );
+ }
+
+
+ /**
+ * Die SQL-Anfrage wird auf Parameter untersucht.
+ */
+ function parseSourceQuery( $query )
+ {
+ Logger::debug( 'SQL-query: '.$query);
+
+ while( true ) // Schleife wird solange durchlaufen, solange Parameter gefunden werden.
+ {
+ $posKlLinks = strpos($query,'{');
+ $posKlRechts = strpos($query,'}');
+
+ if ( $posKlLinks === false || $posKlRechts === false )
+ break; // Schleife abbrechen, wenn kein Parameter mehr gefunden wird.
+
+ $nameParam = substr($query,$posKlLinks+1,$posKlRechts-$posKlLinks-1); // Name Parameter
+
+ if ( isset($this->param[$nameParam ]))
+ throw new RuntimeException( 'Parameter '.$nameParam.' in Query mehrfach vorhanden.' );
+
+ $this->param[$nameParam] = $posKlLinks;
+
+ $query = substr($query,0,$posKlLinks).substr($query,$posKlRechts+1);
+ }
+
+ $this->query = $query;
+
+ }
+
+
+}
+
+
+?>+
\ No newline at end of file
diff --git a/db/Statement.class.php b/db/Statement.class.php
@@ -1,33 +1,24 @@
<?php
-//
-// +----------------------------------------------------------------------+
-// | PHP version 4.0 |
-// +----------------------------------------------------------------------+
-// | Copyright (c) 1997-2001 The PHP Group |
-// +----------------------------------------------------------------------+
-// | This source file is subject to version 2.02 of the PHP license, |
-// | that is bundled with this package in the file LICENSE, and is |
-// | available at through the world-wide-web at |
-// | http://www.php.net/license/2_02.txt. |
-// | If you did not receive a copy of the PHP license and are unable to |
-// | obtain it through the world-wide-web, please send a note to |
-// | license@php.net so we can mail you a copy immediately. |
-// +----------------------------------------------------------------------+
-// | Authors: Stig Bakken <ssb@fast.no> |
-// | Jan Dankert <phpdb@jandankert.de> |
-// +----------------------------------------------------------------------+
-//
-
-// This is the database abstraction layer. This class was inspired by the
-// PHP-Pear-DB package. Thanks to its developers.
+// OpenRat Content Management System
+// Copyright (C) 2002-2006 Jan Dankert, jandankert@jandankert.de
+//
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
+
/**
- * Darstellung einer Datenbank-Verbindung.
- * Fuer die echten DB-Aufrufe werden die entsprechenden
- * Methoden des passenden Clients aufgerufen.
- *
- * Diese Klasse stammt urspruenglich aus dem PHP-Pear-DB-Projekt und unterliegt
- * daher auch der PHP-licence.
+ * Darstellung einer Datenbank-Abfrage.
*
* @author Jan Dankert
* @package openrat.database
@@ -50,6 +41,13 @@ class Statement
*/
var $client;
+
+ /**
+ * Datenbank-Konfiguration
+ * @var Array
+ */
+ var $conf;
+
/**
* Kontruktor.
* Erwartet die Datenbank-Konfiguration als Parameter.
@@ -57,15 +55,22 @@ class Statement
* @param Array Konfiguration der Verbindung
* @return Status 'true' wenn Verbindung erfolgreich aufgebaut.
*/
- public function Statement( $sql, $client,$dbid )
+ public function Statement( $sql, $client,$conf )
{
// Tabellen-Praefixe ergaenzen.
- global $conf;
- $sql = str_replace('{{',$conf['database'][$dbid]['prefix'],$sql);
- $sql = str_replace('}}',$conf['database'][$dbid]['suffix'],$sql);
-
+ $this->conf = $conf;
$this->client = $client;
- $this->sql = new Sql( $sql,$dbid );
+
+ $sql = str_replace('{{',$conf['prefix'],$sql);
+ $sql = str_replace('}}',$conf['suffix'],$sql);
+
+ $this->sql = new Sql( $sql );
+
+ // Vorbereitete Datenbankabfrage ("Prepared Statement")
+ $this->client->clear();
+
+ // Statement an die Datenbank schicken
+ $this->client->prepare( $this->sql->query,$this->sql->param );
}
@@ -90,22 +95,12 @@ class Statement
*/
public function execute( )
{
- // Vorbereitete Datenbankabfrage ("Prepared Statement")
- $this->client->clear();
-
- // Statement an die Datenbank schicken
- $this->client->prepare( $this->sql->raw,$this->sql->param );
-
- // Einzelne Parameter an die Anfrage binden
- foreach ($this->sql->param as $name=>$unused)
- $this->client->bind($name,$this->sql->data[$name]);
-
// Ausfuehren...
$result = $this->client->query($this->sql);
if ( $result === FALSE )
{
- throw new Exception( 'Database error: '.$this->client->error);
+ throw new RuntimeException( 'Database error: '.$this->client->error);
}
return $result;
@@ -146,14 +141,6 @@ class Statement
{
$result = $this->query();
- if ( $result === FALSE )
- {
- $this->error = $this->client->error;
-
- Logger::warn('Database error: '.$this->error);
- Http::serverError('Database Error',$this->error);
- }
-
$row = $this->client->fetchRow( $result,0 );
$this->client->freeResult($result);
@@ -198,8 +185,10 @@ class Statement
* @param Boolean $force_array
* @return Array
*/
- public function &getAssoc( $force_array = false )
+ public function &getAssoc()
{
+ $force_array = false;
+
$results = array();
$result = $this->query();
@@ -210,24 +199,20 @@ class Statement
if ( empty($row) )
break;
+ $keys = array_keys($row);
+ $key1 = $keys[0];
+ $id = $row[$key1];
+
if ( count($row) > 2 || $force_array )
{
- // FIXME: Wird offenbar nie ausgeführt.
- $row = $res->fetchRow($i);
-
- $keys = array_keys($row);
- $key1 = $keys[0];
-
unset( $row[$key1] );
- $results[ $row[$key1] ] = $row;
+ $results[ $id ] = $row;
}
else
{
- $keys = array_keys($row);
- $key1 = $keys[0];
$key2 = $keys[1];
- $results[ $row[$key1] ] = $row[$key2];
+ $results[ $id ] = $row[$key2];
}
}
@@ -281,7 +266,6 @@ class Statement
}
-
/**
* Setzt eine Ganzzahl als Parameter.<br>
*
@@ -290,7 +274,7 @@ class Statement
*/
function setInt( $name,$value )
{
- $this->sql->setInt($name, $value);
+ $this->client->bind( $name, (int)$value );
}
@@ -303,7 +287,7 @@ class Statement
*/
function setString( $name,$value )
{
- $this->sql->setString($name, $value);
+ $this->client->bind( $name, (string)$value );
}
@@ -319,7 +303,8 @@ class Statement
{
if ( $value )
$this->setInt( $name,1 );
- else $this->setInt( $name,0 );
+ else
+ $this->setInt( $name,0 );
}
@@ -331,7 +316,7 @@ class Statement
*/
function setNull( $name )
{
- $this->sql->setNull($name);
+ $this->client->bind( $name, null );
}
diff --git a/db/driver/pdo.class.php b/db/driver/pdo.class.php
@@ -42,9 +42,9 @@ class DB_pdo
*/
var $error;
- var $prepared = false;
-
var $lowercase = false;
+
+ var $params;
function connect( $conf )
@@ -58,16 +58,22 @@ class DB_pdo
$options = array();
foreach( $conf as $c )
- if ( substr($c,0,7) == 'option_' )
+ if ( is_string($c) && substr($c,0,7) == 'option_' )
$options[substr($c,8)] = $conf[$c];
-
+
+ if ( $conf['persistent'])
+ $options[ PDO::ATTR_PERSISTENT ] = true;
+
+ if ( !$conf['prepare'])
+ $options[ PDO::ATTR_EMULATE_PREPARES ] = true;
+
+ $options[ PDO::ERRMODE_EXCEPTION ] = true;
+ $options[ PDO::ATTR_DEFAULT_FETCH_MODE ] = PDO::FETCH_ASSOC;
+
$this->connection = new PDO($url, $user, $pw, $options);
if ( !is_object($this->connection) )
- {
- $this->error = "Could not connect to database on host $host. ".PDO::errorInfo();
- return false;
- }
+ throw new OpenRatException( 'DATABASE_ERROR_CONNECTION',"Could not connect to database on host $host. ".PDO::errorInfo() );
return true;
}
@@ -84,41 +90,20 @@ class DB_pdo
function query($query)
{
- if ( $this->prepared )
- {
- $ar = array();
-
- foreach( $query->data as $val )
- $ar[] = $val['value'];
- $erg = $this->stmt->execute( $ar );
-
- if ( $erg === false )
- {
- die( 'Could not execute prepared statement "'.$query->query.'" with values "'.implode(',',$ar).'": '.implode('/',$this->connection->errorInfo()) );
- }
-
- return $this->stmt;
- }
- else
+ $erg = $this->stmt->execute();
+
+ if ( $erg === false )
{
- $this->result = $this->connection->query($query);
-
- if ( ! $this->result )
- {
- $this->error = 'Database error: '.implode('/',$this->connection->errorInfo());
- return FALSE;
- }
- return $this->result;
+ throw new RuntimeException( 'Could not execute prepared statement "'.$query->src.'": '.implode('/',$this->stmt->errorInfo()) );
}
+
+ return $this->stmt;
}
function fetchRow( $result, $rownum )
{
- if ( $this->prepared )
- $row = $this->stmt->fetch( PDO::FETCH_ASSOC );
- else
- $row = $this->result->fetch( PDO::FETCH_ASSOC );
+ $row = $this->stmt->fetch( PDO::FETCH_ASSOC );
if ( is_array($row) && $this->lowercase )
$row = array_change_key_case($row);
@@ -135,21 +120,23 @@ class DB_pdo
function prepare( $query,$param)
{
+ $this->params = $param;
$offset = 0;
- foreach( $param as $pos)
+ foreach( $param as $name=>$pos)
{
- foreach( $pos as $posx )
- {
- $posx += $offset++;
- $query = substr($query,0,$posx).'?'.substr($query,$posx);
- }
+ $name = ':'.$name;
+ $pos += $offset;
+ $query = substr($query,0,$pos).$name.substr($query,$pos);
+
+ $offset = $offset + strlen($name);
}
- $this->prepared = true;
+ Logger::debug('PDO: SQL-before-preparation: '.$query);
+
$this->stmt = $this->connection->prepare($query);
if ( $this->stmt === false )
- die( 'Database error: '.implode('/',$this->connection->errorInfo()) );
+ throw new OpenRatException('ERROR_DATABASE_CONNECTION','Could not prepare statement: '.$query.' Cause: '.implode('/',$this->connection->errorInfo()) );
}
@@ -157,7 +144,20 @@ class DB_pdo
function bind( $param,$value )
{
- $this->params[$param] = &$value;
+ $name = ':'.$param;
+
+ if ( is_string($value) )
+ $type = PDO::PARAM_STR;
+ elseif( is_int($value))
+ $type = PDO::PARAM_INT;
+ elseif( is_null($value))
+ $type = PDO::PARAM_NULL;
+ else
+ throw new RuntimeException( 'Unknown type' );
+
+ $this->stmt->bindValue($name,$value,$type);
+
+ Logger::debug('PDO: SQL-Binding of parameter '.$name);
}
@@ -186,7 +186,14 @@ class DB_pdo
*/
function rollback()
{
- $this->connection->rollBack();
+ try
+ {
+ $this->connection->rollBack();
+ }
+ catch ( PDOException $e )
+ {
+ // Kommt vor, wenn keine Transaktion existiert.
+ }
}
@@ -196,7 +203,6 @@ class DB_pdo
*/
function clear()
{
- $this->prepared = false;
$this->params = array();
}
diff --git a/db/include.inc.php b/db/include.inc.php
@@ -2,6 +2,7 @@
require_once( OR_DBCLASSES_DIR."Database.class.php" );
require_once( OR_DBCLASSES_DIR."Statement.class.php" );
+require_once( OR_DBCLASSES_DIR."Sql.class.php" );
require_once( OR_DBCLASSES_DIR."driver/postgresql.class.php" );
require_once( OR_DBCLASSES_DIR."driver/mysql.class.php" );
if (version_compare(PHP_VERSION, '5.0.0', '>'))
diff --git a/functions/db.inc.php b/functions/db.inc.php
@@ -17,69 +17,6 @@
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
-// Namen der Datenbanktabellen in Variablen schreiben
-//
-require( OR_SERVICECLASSES_DIR.'Sql.class.php');
-
-/**
- * Liefert alle Tabellennamen zur aktuellen Datenbankverbindung.
- *
- * @param int $dbid
- * @return Array Schlüssel=log. Tabellenname, Werte=Phys. Tabellennamen
- */
-function table_names( $dbid )
-{
- $t = array();
- global $conf;
- global $SESS;
-
- if ( empty($dbid) )
- {
- $db = Session::getDatabase();
- if ( is_object( $db ) )
- {
- $conf_db_prefix = $db->conf['prefix'];
- $conf_db_suffix = $db->conf['suffix'];
- }
- else
- {
- $conf_db_prefix = '';
- $conf_db_suffix = '';
- }
- }
- else
- {
- $conf_db_prefix = config('database',$dbid,'prefix');
- $conf_db_suffix = config('database',$dbid,'suffix');
- }
-
- foreach( array(
- 'element',
- 'template',
- 'templatemodel',
- 'projectmodel',
- 'page',
- 'language',
- 'value',
- 'user',
- 'usergroup',
- 'project',
- 'group',
- 'folder',
- 'file',
- 'acl',
- 'object',
- 'name',
- 'link',
- 'version'
- ) as $tname )
- $t['t_'.$tname] = $conf_db_prefix.$tname.$conf_db_suffix;
-
- return $t;
-}
-
-
-
/**
* Liefert die Datenbankverbindung fuer die aktuelle Sitzung.
*
diff --git a/util/Sql.class.php b/util/Sql.class.php
@@ -1,330 +0,0 @@
-<?php
-// OpenRat Content Management System
-// Copyright (C) 2002-2006 Jan Dankert, jandankert@jandankert.de
-//
-// This program is free software; you can redistribute it and/or
-// modify it under the terms of the GNU General Public License
-// as published by the Free Software Foundation; either version 2
-// of the License, or (at your option) any later version.
-//
-// This program is distributed in the hope that it will be useful,
-// but WITHOUT ANY WARRANTY; without even the implied warranty of
-// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
-//
-// You should have received a copy of the GNU General Public License
-// along with this program; if not, write to the Free Software
-// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
-
-
-
-/**
- * SQL-Anweisung.<br>
- * <br>
- * Darstellen eines SQL-Statements incl. Methoden zum Fuellen von
- * Platzhaltern im SQL-Befehl.<br>
- * <br>
- * Beispiel<br>
- * <pre>
- * // Neues Objekt erzeugen mit SQL-Anweisung
- * $sql = $db->sql('SELECT * FROM xy WHERE id={uid} AND name={name}');
- *
- * // Parameter f�llen
- * $sql->setInt ('uid' ,1 );
- * $sql->setString('name','peter');
- *
- * // Fertige SQL-Anweisung verwenden
- * $xy->execute( $sql->query );
- * </pre>
- * <br>
- * Ziele dieser Klasse sind:<br>
- * - Schreiben einfacher SQL-Anweisungen ohne Stringverarbeitung<br>
- * - Verhindern von SQL-Injection.<br>
- * <br>
- *
- * @author Jan Dankert, $Author$
- * @version $Revision$
- * @package openrat.services
- */
-
-class Sql
-{
- /**
- * Urspr�ngliche SQL-Anweisung.
- */
- var $src = '';
-
- /**
- * Auszuf�hrende Abfrage.
- */
- var $query = '';
-
- var $raw = '';
-
-
- /**
- * Zwischenspeicher f�r Parameterwerte.
- */
- var $data = Array();
-
- /**
- * Ein 2-dimensionales Array mit den Positionen der Parameter.<br>
- * <br>
- * Beispiel:<br>
- * <pre>
- *
- * Array
- * (
- * [lid] => Array
- * (
- * [0] => 65
- * [1] => 81
- * )
- * [oid] => Array
- * (
- * [0] => 123
- * )
- * )
- * </pre>
- * In der ersten Dimension sind die Parameter vorhanden, jeder Parameter hat eine Liste von Positionen, an denen er steht.<br>
- * Ein Parameter kann n�mlich mehrfach vorkommen!
- */
- var $param = array();
-
-
- var $dbid = '';
-
-
- /**
- * Erzeugt ein SQL-Objekt und analysiert die SQL-Anfrage.
- */
- function Sql( $query = '', $dbid='' )
- {
- $this->dbid = $dbid;
- $this->parseSourceQuery( $query );
-
- $this->data = array();
- }
-
-
-
- /**
- * Die SQL-Anfrage wird auf Parameter untersucht.
- */
- function parseSourceQuery( $query )
- {
- $this->src = $query; // Wir merken uns die Ur-Abfrage, evtl. f�r Fehlermeldungen interessant.
-
- while( true ) // Schleife wird solange durchlaufen, solange Parameter gefunden werden.
- {
- $posKlLinks = strpos($query,'{');
- $posKlRechts = strpos($query,'}');
-
- if ( $posKlLinks === false )
- break; // Schleife abbrechen, wenn kein Parameter mehr gefunden wird.
-
- $nameParam = substr($query,$posKlLinks+1,$posKlRechts-$posKlLinks-1); // Name Parameter
-
- if ( !isset($this->param[$nameParam ]))
- $this->param [$nameParam ] = array();
-
- $this->param[$nameParam ][] = $posKlLinks;
-
- $query = substr($query,0,$posKlLinks).substr($query,$posKlRechts+1);
- }
-
- $this->query = $query;
-// $merkeParam = $this->param;
-
- // Tabellennamen in die Platzhalter setzen.
- // Dies ist noch OpenRat-spezifisch und sollte bei einer sauberen Abstraktion woanders gemacht werden. Aber wo?
- foreach( table_names($this->dbid) as $t=>$name )
- {
- $this->setParam($t,$name,false );
-
- unset( $this->param[$t] );
-
- }
-
- $this->raw = $this->query;
- //$this->param = $merkeParam;
-
- }
-
-
-
- /**
- * Setzt eine neue SQL-Abfrage.<br>
- * Bereits vorhandene Parameter werden automatisch wieder gesetzt.
- */
- function setQuery( $query = '' )
- {
- $this->parseSourceQuery( $query );
-
- // Bereits vorhande Parameter setzen.
- foreach( $this->data as $name=>$data )
- {
- if ( $data['type']=='string' ) $this->setString ($name,$data['value'] );
- if ( $data['type']=='int' ) $this->setInt ($name,$data['value'] );
- if ( $data['type']=='null' ) $this->setNull ($name );
- }
- }
-
-
-
- /**
- * Setzt einen Parameter.<br>
- * Diese Methode sollte nur intern aufgerufen werden!<br>
- *
- * @param name Name des Parameters
- * @param value Inhalt
- * @param dieIfUnknown wenn <code>true</code> und Parameter unbekannt, dann Abbruch.
- * @access private
- */
- private function setParam( $name,$value,$dieIfUnknown=true)
- {
-
- // Nett gemeint, f�hrt aber aktuell zu Fehlern, weil an vielen Stellen zu viele Parameter gef�llt werden.
- // Daher erstmal deaktiviert.
- // if ( !isset($this->param[$name]) )
- // {
- // if ( $dieIfUnknown )
- // die("parameter '$name' unknown. SQL=".$this->src);
- // else
- // return;
- // }
-
- if ( !isset($this->param[$name]) )
- return; // Parameter nicht vorhanden.
-
- if ( is_array($this->param[$name]) )
- {
- foreach( $this->param[$name] as $idx=>$xyz )
- {
- $pos = $this->param[$name][$idx];
-
- $this->query = substr( $this->query,0,$pos ).$value.substr( $this->query,$pos );
-
- foreach( $this->param as $pn=>$par)
- {
- foreach( $par as $i=>$p )
- {
- if ( $p > $pos )
- $this->param[$pn][$i]=$p+strlen($value);
- }
- }
-
- }
- }
- }
-
-
-
- /**
- * Setzt einen Parameter.<br>
- * Der Typ des Parameters wird automatisch ermittelt.<br>
- *
- * @param name Name des Parameters
- * @param value Inhalt
- */
- function setVar( $name,$value )
- {
- if ( is_string($value) )
- $this->setString( $name,$value );
-
- if ( is_null($value) )
- $this->setNull( $name );
-
- if ( is_int($value) )
- $this->setInt( $name,$value );
- }
-
-
-
- /**
- * Setzt eine Ganzzahl als Parameter.<br>
- *
- * @param name Name des Parameters
- * @param value Inhalt
- */
- function setInt( $name,$value )
- {
- $this->data[ $name ] = array( 'type'=>'int','value'=>(int)$value );
- }
-
-
-
- /**
- * Setzt eine Zeichenkette als Parameter.<br>
- *
- * @param name Name des Parameters
- * @param value Inhalt
- */
- function setString( $name,$value )
- {
- $this->data[ $name ] = array( 'type'=>'string','value'=>$value );
- }
-
-
-
- /**
- * Setzt einen bool'schen Wert als Parameter.<br>
- * Ist der Parameterwert wahr, dann wird eine 1 gesetzt. Sonst 0.<br>
- *
- * @param name Name des Parameters
- * @param value Inhalt
- */
- function setBoolean( $name,$value )
- {
- if ( $value )
- $this->setInt( $name,1 );
- else $this->setInt( $name,0 );
- }
-
-
-
- /**
- * Setzt einen Parameter auf den Wert <code>null</code>.<br>
- *
- * @param name Name des Parameters
- */
- function setNull( $name )
- {
- $this->data[ $name ] = array( 'type'=>'null' );
- }
-
-
-
- /**
- * Ermittelt die fertige SQL-Anfrage.<br>
- * Alias zu #getQuery()
- */
- function &query()
- {
- return $this->getQuery();
- }
-
-
-
- /**
- * Ermittelt die fertige SQL-Anfrage.
- * @param Name einer Funktion, die eine Zeichenkette f�r die
- * Datenbank schuetzt. Dies kann je nach verwendetem RDBMS
- * unterschiedlich sein, daher diese Funktionsreferenz.
- */
- function &getQuery( $escape_function )
- {
- // Bereits gesetzte Parameter setzen.
- foreach( $this->data as $name=>$data )
- {
- if ( $data['type']=='string' ) $this->setParam($name,"'".$escape_function($data['value'])."'" );
- elseif ( $data['type']=='int' ) $this->setParam($name,(int)$data['value'] );
- elseif ( $data['type']=='null' ) $this->setParam($name,'NULL' );
- }
-
- return $this->query;
- }
-}
-
-
-?>-
\ No newline at end of file
