utf8/nucleus/libs/sql/pdo.php

   1 <?php
   2
   3 /*
   4  * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
   5  * Copyright (C) 2002-2011 The Nucleus Group
   6  *
   7  * This program is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU General Public License
   9  * as published by the Free Software Foundation; either version 2
  10  * of the License, or (at your option) any later version.
  11  * (see nucleus/documentation/index.html#license for more info)
  12  */
  13 /**
  14  * @license http://nucleuscms.org/license.txt GNU General Public License
  15  * @copyright Copyright (C) 2002-2011 The Nucleus Group
  16  * @version $Id$
  17  */
  18
  19 /*
  20  * complete sql_* wrappers for mysql functions
  21  *
  22  * functions moved from globalfunctions.php: sql_connect, sql_disconnect, sql_query
  23  */
  24
  25
  26 $MYSQL_CONN = 0;
  27 global $SQL_DBH;
  28 $SQL_DBH = NULL;
  29
  30 if (!function_exists('sql_fetch_assoc'))
  31 {
  32 /**
  33  * Errors before the database connection has been made
  34  */
  35         function startUpError($msg, $title) {
  36                 if (!defined('_CHARSET')) {
  37                         define('_CHARSET', 'UTF-8');
  38                 }
  39                 if (!defined('_HTML_XML_NAME_SPACE_AND_LANG_CODE')) {
  40                         define('_HTML_XML_NAME_SPACE_AND_LANG_CODE', 'xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us"');
  41                 }
  42                 sendContentType('text/html','',_CHARSET);
  43                 ?>
  44 <html <?php echo _HTML_XML_NAME_SPACE_AND_LANG_CODE; ?>>
  45         <head><meta http-equiv="Content-Type" content="text/html; charset=<?php echo _CHARSET?>" />
  46         <title><?php echo htmlspecialchars($title,ENT_QUOTES)?></title></head>
  47         <body>
  48                 <h1><?php echo htmlspecialchars($title,ENT_QUOTES)?></h1>
  49                 <?php echo $msg?>
  50         </body>
  51 </html>
  52 <?php
  53                 exit;
  54         }
  55
  56 /**
  57  * Connects to mysql server
  58  */
  59         function sql_connect_args($mysql_host = 'localhost', $mysql_user = '', $mysql_password = '', $mysql_database = '') {
  60                 global $MYSQL_HANDLER;
  61
  62                 try {
  63                         if (strpos($mysql_host,':') === false) {
  64                                 $host = $mysql_host;
  65                                 $port = '';
  66                                 $portnum = '';
  67                         }
  68                         else {
  69                                 list($host,$port) = explode(":",$mysql_host);
  70                                 if (isset($port)) {
  71                                         $portnum = $port;
  72                                         $port = ';port='.trim($port);
  73                                 }
  74                                 else {
  75                                         $port = '';
  76                                         $portnum = '';
  77                                 }
  78                         }
  79
  80                         switch ($MYSQL_HANDLER[1]) {
  81                                 case 'sybase':
  82                                 case 'dblib':
  83                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
  84                                         else $port = '';
  85                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
  86                                 break;
  87                                 case 'mssql':
  88                                         if (is_numeric($portnum)) $port = ','.intval($portnum);
  89                                         else $port = '';
  90                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
  91                                 break;
  92                                 case 'oci':
  93                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
  94                                         else $port = '';
  95                                         $DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$mysql_database, $mysql_user, $mysql_password);
  96                                 break;
  97                                 case 'odbc':
  98                                         if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
  99                                         else $port = '';
 100                                         $DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$mysql_database.';PROTOCOL=TCPIP;UID='.$mysql_user.';PWD='.$mysql_password);
 101
 102                                 break;
 103                                 case 'pgsql':
 104                                         if (is_numeric($portnum)) $port = ';port='.intval($portnum);
 105                                         else $port = '';
 106                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
 107                                 break;
 108                                 case 'sqlite':
 109                                 case 'sqlite2':
 110                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 111                                         else $port = '';
 112                                         $DBH = new PDO($MYSQL_HANDLER[1].':'.$mysql_database, $mysql_user, $mysql_password);
 113                                 break;
 114                                 default:
 115                                         //mysql
 116                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
 117                                 break;
 118                         }
 119
 120
 121
 122                 } catch (PDOException $e) {
 123                         $DBH =NULL;
 124                         startUpError('<p>a1 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 125                 }
 126 //echo '<hr />DBH: '.print_r($DBH,true).'<hr />';
 127                 return $DBH;
 128         }
 129
 130 /**
 131  * Connects to mysql server
 132  */
 133         function sql_connect() {
 134                 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
 135                 $SQL_DBH = NULL;
 136                 try {
 137                         if (strpos($MYSQL_HOST,':') === false) {
 138                                 $host = $MYSQL_HOST;
 139                                 $port = '';
 140                         }
 141                         else {
 142                                 list($host,$port) = explode(":",$MYSQL_HOST);
 143                                 if (isset($port)) {
 144                                         $portnum = $port;
 145                                         $port = ';port='.trim($port);
 146                                 }
 147                                 else {
 148                                         $port = '';
 149                                         $portnum = '';
 150                                 }
 151                         }
 152
 153                         switch ($MYSQL_HANDLER[1]) {
 154                                 case 'sybase':
 155                                 case 'dblib':
 156                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 157                                         else $port = '';
 158                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 159                                 break;
 160                                 case 'mssql':
 161                                         if (is_numeric($portnum)) $port = ','.intval($portnum);
 162                                         else $port = '';
 163                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 164                                 break;
 165                                 case 'oci':
 166                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 167                                         else $port = '';
 168                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 169                                 break;
 170                                 case 'odbc':
 171                                         if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
 172                                         else $port = '';
 173                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$MYSQL_DATABASE.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
 174
 175                                 break;
 176                                 case 'pgsql':
 177                                         if (is_numeric($portnum)) $port = ';port='.intval($portnum);
 178                                         else $port = '';
 179                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 180                                 break;
 181                                 case 'sqlite':
 182                                 case 'sqlite2':
 183                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 184                                         else $port = '';
 185                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 186                                 break;
 187                                 default:
 188                                         //mysql
 189                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 190                                 break;
 191                         }
 192
 193                         //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 194
 195 // <add for garble measure>
 196                         if (strpos($MYSQL_HANDLER[1], 'mysql') === 0) {
 197                                 $resource = $SQL_DBH->query("show variables LIKE 'character_set_database'");
 198                                 $resource->bindColumn('Value', $charset);
 199                                 $resource->fetchAll();
 200                                 $SQL_DBH->exec("SET CHARACTER SET " . $charset);
 201                         }
 202 // </add for garble measure>*/
 203                 } catch (PDOException $e) {
 204                         $SQL_DBH = NULL;
 205                         startUpError('<p>a2 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 206                 }
 207 //              echo '<hr />DBH: '.print_r($SQL_DBH,true).'<hr />';
 208                 $MYSQL_CONN &= $SQL_DBH;
 209                 return $SQL_DBH;
 210
 211         }
 212
 213 /**
 214  * disconnects from SQL server
 215  */
 216         function sql_disconnect(&$dbh=NULL) {
 217                 global $SQL_DBH;
 218                 if (is_null($dbh)) $SQL_DBH = NULL;
 219                 else $dbh = NULL;
 220         }
 221
 222         function sql_close(&$dbh=NULL) {
 223                 global $SQL_DBH;
 224                 if (is_null($dbh)) $SQL_DBH = NULL;
 225                 else $dbh = NULL;
 226         }
 227
 228 /**
 229  * executes an SQL query
 230  */
 231         function sql_query($query,$dbh=NULL) {
 232                 global $SQLCount,$SQL_DBH;
 233                 $SQLCount++;
 234 //echo '<hr />SQL_DBH: ';
 235 //print_r($SQL_DBH);
 236 //echo '<hr />DBH: ';
 237 //print_r($dbh);
 238 //echo '<hr />';
 239 //echo $query.'<hr />';
 240                 if (is_null($dbh)) $res = $SQL_DBH->query($query);
 241                 else $res = $dbh->query($query);
 242                 if ($res->errorCode() != '00000') {
 243                         $errors = $res->errorInfo();
 244                         print("SQL error with query $query: " . $errors[0].'-'.$errors[1].' '.$errors[2] . '<p />');
 245                 }
 246
 247                 return $res;
 248         }
 249
 250 /**
 251  * executes an SQL error
 252  */
 253         function sql_error($dbh=NULL)
 254         {
 255                 global $SQL_DBH;
 256                 if (is_null($dbh)) $error = $SQL_DBH->errorInfo();
 257                 else $error = $dbh->errorInfo();
 258                 if ($error[0] != '00000') {
 259                         return $error[0].'-'.$error[1].' '.$error[2];
 260                 }
 261                 else return '';
 262         }
 263
 264 /**
 265  * executes an SQL db select
 266  */
 267         function sql_select_db($db,&$dbh=NULL)
 268         {
 269                 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
 270 //echo '<hr />'.print_r($dbh,true).'<hr />';
 271 //exit;
 272                 if (is_null($dbh)) {
 273                         try {
 274                                 $SQL_DBH = NULL;
 275                                 list($host,$port) = explode(":",$MYSQL_HOST);
 276                                 if (isset($port)) {
 277                                         $portnum = $port;
 278                                         $port = ';port='.trim($port);
 279                                 }
 280                                 else {
 281                                         $port = '';
 282                                         $portnum = '';
 283                                 }
 284                                 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.trim($host).$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 285                                 //$SQL_DBH = sql_connect();
 286                                 switch ($MYSQL_HANDLER[1]) {
 287                                         case 'sybase':
 288                                         case 'dblib':
 289                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 290                                                 else $port = '';
 291                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 292                                         break;
 293                                         case 'mssql':
 294                                                 if (is_numeric($portnum)) $port = ','.intval($portnum);
 295                                                 else $port = '';
 296                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 297                                         break;
 298                                         case 'oci':
 299                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 300                                                 else $port = '';
 301                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 302                                         break;
 303                                         case 'odbc':
 304                                                 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
 305                                                 else $port = '';
 306                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$db.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
 307
 308                                         break;
 309                                         case 'pgsql':
 310                                                 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
 311                                                 else $port = '';
 312                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 313                                         break;
 314                                         case 'sqlite':
 315                                         case 'sqlite2':
 316                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 317                                                 else $port = '';
 318                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 319                                         break;
 320                                         default:
 321                                                 //mysql
 322                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 323                                         break;
 324                                 }
 325                                 return 1;
 326                         } catch (PDOException $e) {
 327                                 startUpError('<p>a3 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 328                                 return 0;
 329                         }
 330                 }
 331                 else {
 332                         if ($dbh->exec("USE $db") !== false) return 1;
 333                         else return 0;
 334                 }
 335         }
 336
 337 /**
 338  * executes an SQL real escape
 339  */
 340         function sql_real_escape_string($val,$dbh=NULL)
 341         {
 342                 return addslashes($val);
 343         }
 344
 345 /**
 346  * executes an PDO::quote() like escape, ie adds quotes arround the string and escapes chars as needed
 347  */
 348         function sql_quote_string($val,$dbh=NULL) {
 349                 global $SQL_DBH;
 350                 if (is_null($dbh))
 351                         return $SQL_DBH->quote($val);
 352                 else
 353                         return $dbh->quote($val);
 354         }
 355
 356 /**
 357  * executes an SQL insert id
 358  */
 359         function sql_insert_id($dbh=NULL)
 360         {
 361                 global $SQL_DBH;
 362                 if (is_null($dbh))
 363                         return $SQL_DBH->lastInsertId();
 364                 else
 365                         return $dbh->lastInsertId();
 366         }
 367
 368 /**
 369  * executes an SQL result request
 370  */
 371         function sql_result($res, $row = 0, $col = 0)
 372         {
 373                 $results = array();
 374                 if (intval($row) < 1) {
 375                         $results = $res->fetch(PDO::FETCH_BOTH);
 376                         return $results[$col];
 377                 }
 378                 else {
 379                         for ($i = 0; $i < intval($row); $i++) {
 380                                 $results = $res->fetch(PDO::FETCH_BOTH);
 381                         }
 382                         $results = $res->fetch(PDO::FETCH_BOTH);
 383                         return $results[$col];
 384                 }
 385         }
 386
 387 /**
 388  * frees sql result resources
 389  */
 390         function sql_free_result($res)
 391         {
 392                 $res = NULL;
 393                 return true;
 394         }
 395
 396 /**
 397  * returns number of rows in SQL result
 398  */
 399         function sql_num_rows($res)
 400         {
 401                 return $res->rowCount();
 402         }
 403
 404 /**
 405  * returns number of rows affected by SQL query
 406  */
 407         function sql_affected_rows($res)
 408         {
 409                 return $res->rowCount();
 410         }
 411
 412 /**
 413  * Get number of fields in result
 414  */
 415         function sql_num_fields($res)
 416         {
 417                 return $res->columnCount();
 418         }
 419
 420 /**
 421  * fetches next row of SQL result as an associative array
 422  */
 423         function sql_fetch_assoc($res)
 424         {
 425                 $results = array();
 426                 $results = $res->fetch(PDO::FETCH_ASSOC);
 427                 return $results;
 428         }
 429
 430 /**
 431  * Fetch a result row as an associative array, a numeric array, or both
 432  */
 433         function sql_fetch_array($res)
 434         {
 435                 $results = array();
 436                 $results = $res->fetch(PDO::FETCH_BOTH);
 437                 return $results;
 438         }
 439
 440 /**
 441  * fetches next row of SQL result as an object
 442  */
 443         function sql_fetch_object($res)
 444         {
 445                 $results = NULL;
 446                 $results = $res->fetchObject();
 447                 return $results;
 448         }
 449
 450 /**
 451  * Get a result row as an enumerated array
 452  */
 453         function sql_fetch_row($res)
 454         {
 455                 $results = array();
 456                 $results = $res->fetch(PDO::FETCH_NUM);
 457                 return $results;
 458         }
 459
 460 /**
 461  * Get column information from a result and return as an object
 462  */
 463         function sql_fetch_field($res,$offset = 0)
 464         {
 465                 $results = array();
 466                 $obj = NULL;
 467                 $results = $res->getColumnMeta($offset);
 468                 foreach($results as $key=>$value) {
 469                         $obj->$key = $value;
 470                 }
 471                 return $obj;
 472         }
 473
 474 /**
 475  * Get current system status (returns string)
 476  */
 477         function sql_stat($dbh=NULL)
 478         {
 479                 //not implemented
 480                 global $SQL_DBH;
 481                 if (is_null($dbh))
 482                         return '';
 483                 else
 484                 return '';
 485         }
 486
 487 /**
 488  * Returns the name of the character set
 489  */
 490         function sql_client_encoding($dbh=NULL)
 491         {
 492                 //not implemented
 493                 global $SQL_DBH;
 494                 if (is_null($dbh))
 495                         return '';
 496                 else
 497                         return '';
 498         }
 499
 500 /**
 501  * Get SQL client version
 502  */
 503         function sql_get_client_info()
 504         {
 505                 global $SQL_DBH;
 506                 return $SQL_DBH->getAttribute(constant("PDO::ATTR_CLIENT_VERSION"));
 507         }
 508
 509 /**
 510  * Get SQL server version
 511  */
 512         function sql_get_server_info($dbh=NULL)
 513         {
 514                 global $SQL_DBH;
 515                 if (is_null($dbh))
 516                         return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 517                 else
 518                         return $dbh->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 519         }
 520
 521 /**
 522  * Returns a string describing the type of SQL connection in use for the connection or FALSE on failure
 523  */
 524         function sql_get_host_info($dbh=NULL)
 525         {
 526                 global $SQL_DBH;
 527                 if (is_null($dbh))
 528                         return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
 529                 else
 530                         return $dbh->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
 531         }
 532
 533 /**
 534  * Returns the SQL protocol on success, or FALSE on failure.
 535  */
 536         function sql_get_proto_info($dbh=NULL)
 537         {
 538                 //not implemented
 539                 global $SQL_DBH;
 540                 if (is_null($dbh))
 541                         return false;
 542                 else
 543                         return false;
 544         }
 545
 546 /**
 547  * Get the name of the specified field in a result
 548  */
 549         function sql_field_name($res, $offset = 0)
 550         {
 551                 $column = $res->getColumnMeta($offset);
 552                 if ($column) {
 553                         return $column['name'];
 554                 }
 555                 return false;
 556         }
 557
 558 /**************************************************************************
 559         Unimplemented mysql_* functions
 560
 561 # mysql_ data_ seek (maybe useful)
 562 # mysql_ errno (maybe useful)
 563 # mysql_ fetch_ lengths (maybe useful)
 564 # mysql_ field_ flags (maybe useful)
 565 # mysql_ field_ len (maybe useful)
 566 # mysql_ field_ name (maybe useful)
 567 # mysql_ field_ seek (maybe useful)
 568 # mysql_ field_ table (maybe useful)
 569 # mysql_ field_ type (maybe useful)
 570 # mysql_ info (maybe useful)
 571 # mysql_ list_ processes (maybe useful)
 572 # mysql_ ping (maybe useful)
 573 # mysql_ set_ charset (maybe useful, requires php >=5.2.3 and mysql >=5.0.7)
 574 # mysql_ thread_ id (maybe useful)
 575
 576 # mysql_ db_ name (useful only if working on multiple dbs which we do not do)
 577 # mysql_ list_ dbs (useful only if working on multiple dbs which we do not do)
 578
 579 # mysql_ pconnect (probably not useful and could cause some unintended performance issues)
 580 # mysql_ unbuffered_ query (possibly useful, but complicated and not supported by all database drivers (pdo))
 581
 582 # mysql_ change_ user (deprecated)
 583 # mysql_ create_ db (deprecated)
 584 # mysql_ db_ query (deprecated)
 585 # mysql_ drop_ db (deprecated)
 586 # mysql_ escape_ string (deprecated)
 587 # mysql_ list_ fields (deprecated)
 588 # mysql_ list_ tables (deprecated)
 589 # mysql_ tablename (deprecated)
 590
 591 *******************************************************************/
 592
 593         /**
 594           * for JP installer only
 595           */
 596         function at_sql_query($query,$dbh=NULL) {
 597                 global $SQLCount,$SQL_DBH;
 598                 $SQLCount++;
 599                 if (is_null($dbh)) $res = $SQL_DBH->query($query);
 600                 else $res = $dbh->query($query);
 601 /*              if ($res->errorCode() != '00000') {
 602                         $errors = $res->errorInfo();
 603                         print("SQL error with query $query: " . $errors[0].'-'.$errors[1].' '.$errors[2] . '<p />');
 604                 }
 605 */
 606                 return $res;
 607         }
 608 }
 609 ?>