lib/adodb/adodb-lib.inc.php

   1 <?php
   2 // security - hide paths
   3 if (!defined('ADODB_DIR')) die();
   4
   5 global $ADODB_INCLUDED_LIB;
   6 $ADODB_INCLUDED_LIB = 1;
   7
   8 /*
   9   @version V5.16 26 Mar 2012  (c) 2000-2012 John Lim (jlim#natsoft.com). All rights reserved.
  10   Released under both BSD license and Lesser GPL library license.
  11   Whenever there is any discrepancy between the two licenses,
  12   the BSD license will take precedence. See License.txt.
  13   Set tabs to 4 for best viewing.
  14
  15   Less commonly used functions are placed here to reduce size of adodb.inc.php.
  16 */
  17
  18 function adodb_strip_order_by($sql)
  19 {
  20         $rez = preg_match('/(\sORDER\s+BY\s[^)]*)/is',$sql,$arr);
  21         if ($arr)
  22                 if (strpos($arr[0],'(') !== false) {
  23                         $at = strpos($sql,$arr[0]);
  24                         $cntin = 0;
  25                         for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
  26                                 $ch = $sql[$i];
  27                                 if ($ch == '(') {
  28                                         $cntin += 1;
  29                                 } elseif($ch == ')') {
  30                                         $cntin -= 1;
  31                                         if ($cntin < 0) {
  32                                                 break;
  33                                         }
  34                                 }
  35                         }
  36                         $sql = substr($sql,0,$at).substr($sql,$i);
  37                 } else
  38                         $sql = str_replace($arr[0], '', $sql);
  39         return $sql;
  40  }
  41
  42 if (false) {
  43         $sql = 'select * from (select a from b order by a(b),b(c) desc)';
  44         $sql = '(select * from abc order by 1)';
  45         die(adodb_strip_order_by($sql));
  46 }
  47
  48 function adodb_probetypes(&$array,&$types,$probe=8)
  49 {
  50 // probe and guess the type
  51         $types = array();
  52         if ($probe > sizeof($array)) $max = sizeof($array);
  53         else $max = $probe;
  54
  55
  56         for ($j=0;$j < $max; $j++) {
  57                 $row = $array[$j];
  58                 if (!$row) break;
  59                 $i = -1;
  60                 foreach($row as $v) {
  61                         $i += 1;
  62
  63                         if (isset($types[$i]) && $types[$i]=='C') continue;
  64
  65                         //print " ($i ".$types[$i]. "$v) ";
  66                         $v = trim($v);
  67
  68                         if (!preg_match('/^[+-]{0,1}[0-9\.]+$/',$v)) {
  69                                 $types[$i] = 'C'; // once C, always C
  70
  71                                 continue;
  72                         }
  73                         if ($j == 0) {
  74                         // If empty string, we presume is character
  75                         // test for integer for 1st row only
  76                         // after that it is up to testing other rows to prove
  77                         // that it is not an integer
  78                                 if (strlen($v) == 0) $types[$i] = 'C';
  79                                 if (strpos($v,'.') !== false) $types[$i] = 'N';
  80                                 else  $types[$i] = 'I';
  81                                 continue;
  82                         }
  83
  84                         if (strpos($v,'.') !== false) $types[$i] = 'N';
  85
  86                 }
  87         }
  88
  89 }
  90
  91 function  adodb_transpose(&$arr, &$newarr, &$hdr, &$fobjs)
  92 {
  93         $oldX = sizeof(reset($arr));
  94         $oldY = sizeof($arr);
  95
  96         if ($hdr) {
  97                 $startx = 1;
  98                 $hdr = array('Fields');
  99                 for ($y = 0; $y < $oldY; $y++) {
 100                         $hdr[] = $arr[$y][0];
 101                 }
 102         } else
 103                 $startx = 0;
 104
 105         for ($x = $startx; $x < $oldX; $x++) {
 106                 if ($fobjs) {
 107                         $o = $fobjs[$x];
 108                         $newarr[] = array($o->name);
 109                 } else
 110                         $newarr[] = array();
 111
 112                 for ($y = 0; $y < $oldY; $y++) {
 113                         $newarr[$x-$startx][] = $arr[$y][$x];
 114                 }
 115         }
 116 }
 117
 118 // Force key to upper.
 119 // See also http://www.php.net/manual/en/function.array-change-key-case.php
 120 function _array_change_key_case($an_array)
 121 {
 122         if (is_array($an_array)) {
 123                 $new_array = array();
 124                 foreach($an_array as $key=>$value)
 125                         $new_array[strtoupper($key)] = $value;
 126
 127                 return $new_array;
 128    }
 129
 130         return $an_array;
 131 }
 132
 133 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
 134 {
 135                 if (count($fieldArray) == 0) return 0;
 136                 $first = true;
 137                 $uSet = '';
 138
 139                 if (!is_array($keyCol)) {
 140                         $keyCol = array($keyCol);
 141                 }
 142                 foreach($fieldArray as $k => $v) {
 143                         if ($v === null) {
 144                                 $v = 'NULL';
 145                                 $fieldArray[$k] = $v;
 146                         } else if ($autoQuote && /*!is_numeric($v) /*and strncmp($v,"'",1) !== 0 -- sql injection risk*/ strcasecmp($v,$zthis->null2null)!=0) {
 147                                 $v = $zthis->qstr($v);
 148                                 $fieldArray[$k] = $v;
 149                         }
 150                         if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
 151
 152                         if ($first) {
 153                                 $first = false;
 154                                 $uSet = "$k=$v";
 155                         } else
 156                                 $uSet .= ",$k=$v";
 157                 }
 158
 159                 $where = false;
 160                 foreach ($keyCol as $v) {
 161                         if (isset($fieldArray[$v])) {
 162                                 if ($where) $where .= ' and '.$v.'='.$fieldArray[$v];
 163                                 else $where = $v.'='.$fieldArray[$v];
 164                         }
 165                 }
 166
 167                 if ($uSet && $where) {
 168                         $update = "UPDATE $table SET $uSet WHERE $where";
 169
 170                         $rs = $zthis->Execute($update);
 171
 172
 173                         if ($rs) {
 174                                 if ($zthis->poorAffectedRows) {
 175                                 /*
 176                                  The Select count(*) wipes out any errors that the update would have returned.
 177                                 http://phplens.com/lens/lensforum/msgs.php?id=5696
 178                                 */
 179                                         if ($zthis->ErrorNo()<>0) return 0;
 180
 181                                 # affected_rows == 0 if update field values identical to old values
 182                                 # for mysql - which is silly.
 183
 184                                         $cnt = $zthis->GetOne("select count(*) from $table where $where");
 185                                         if ($cnt > 0) return 1; // record already exists
 186                                 } else {
 187                                         if (($zthis->Affected_Rows()>0)) return 1;
 188                                 }
 189                         } else
 190                                 return 0;
 191                 }
 192
 193         //      print "<p>Error=".$this->ErrorNo().'<p>';
 194                 $first = true;
 195                 foreach($fieldArray as $k => $v) {
 196                         if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
 197
 198                         if ($first) {
 199                                 $first = false;
 200                                 $iCols = "$k";
 201                                 $iVals = "$v";
 202                         } else {
 203                                 $iCols .= ",$k";
 204                                 $iVals .= ",$v";
 205                         }
 206                 }
 207                 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
 208                 $rs = $zthis->Execute($insert);
 209                 return ($rs) ? 2 : 0;
 210 }
 211
 212 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
 213 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
 214                         $size=0, $selectAttr='',$compareFields0=true)
 215 {
 216         $hasvalue = false;
 217
 218         if ($multiple or is_array($defstr)) {
 219                 if ($size==0) $size=5;
 220                 $attr = ' multiple size="'.$size.'"';
 221                 if (!strpos($name,'[]')) $name .= '[]';
 222         } else if ($size) $attr = ' size="'.$size.'"';
 223         else $attr ='';
 224
 225         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
 226         if ($blank1stItem)
 227                 if (is_string($blank1stItem))  {
 228                         $barr = explode(':',$blank1stItem);
 229                         if (sizeof($barr) == 1) $barr[] = '';
 230                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
 231                 } else $s .= "\n<option></option>";
 232
 233         if ($zthis->FieldCount() > 1) $hasvalue=true;
 234         else $compareFields0 = true;
 235
 236         $value = '';
 237     $optgroup = null;
 238     $firstgroup = true;
 239     $fieldsize = $zthis->FieldCount();
 240         while(!$zthis->EOF) {
 241                 $zval = rtrim(reset($zthis->fields));
 242
 243                 if ($blank1stItem && $zval=="") {
 244                         $zthis->MoveNext();
 245                         continue;
 246                 }
 247
 248         if ($fieldsize > 1) {
 249                         if (isset($zthis->fields[1]))
 250                                 $zval2 = rtrim($zthis->fields[1]);
 251                         else
 252                                 $zval2 = rtrim(next($zthis->fields));
 253                 }
 254                 $selected = ($compareFields0) ? $zval : $zval2;
 255
 256         $group = '';
 257                 if ($fieldsize > 2) {
 258             $group = rtrim($zthis->fields[2]);
 259         }
 260 /*
 261         if ($optgroup != $group) {
 262             $optgroup = $group;
 263             if ($firstgroup) {
 264                 $firstgroup = false;
 265                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 266             } else {
 267                 $s .="\n</optgroup>";
 268                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 269             }
 270                 }
 271 */
 272                 if ($hasvalue)
 273                         $value = " value='".htmlspecialchars($zval2)."'";
 274
 275                 if (is_array($defstr))  {
 276
 277                         if (in_array($selected,$defstr))
 278                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 279                         else
 280                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 281                 }
 282                 else {
 283                         if (strcasecmp($selected,$defstr)==0)
 284                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 285                         else
 286                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 287                 }
 288                 $zthis->MoveNext();
 289         } // while
 290
 291     // closing last optgroup
 292     if($optgroup != null) {
 293         $s .= "\n</optgroup>";
 294         }
 295         return $s ."\n</select>\n";
 296 }
 297
 298 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
 299 function _adodb_getmenu_gp(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
 300                         $size=0, $selectAttr='',$compareFields0=true)
 301 {
 302         $hasvalue = false;
 303
 304         if ($multiple or is_array($defstr)) {
 305                 if ($size==0) $size=5;
 306                 $attr = ' multiple size="'.$size.'"';
 307                 if (!strpos($name,'[]')) $name .= '[]';
 308         } else if ($size) $attr = ' size="'.$size.'"';
 309         else $attr ='';
 310
 311         $s = '<select name="'.$name.'"'.$attr.' '.$selectAttr.'>';
 312         if ($blank1stItem)
 313                 if (is_string($blank1stItem))  {
 314                         $barr = explode(':',$blank1stItem);
 315                         if (sizeof($barr) == 1) $barr[] = '';
 316                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
 317                 } else $s .= "\n<option></option>";
 318
 319         if ($zthis->FieldCount() > 1) $hasvalue=true;
 320         else $compareFields0 = true;
 321
 322         $value = '';
 323     $optgroup = null;
 324     $firstgroup = true;
 325     $fieldsize = sizeof($zthis->fields);
 326         while(!$zthis->EOF) {
 327                 $zval = rtrim(reset($zthis->fields));
 328
 329                 if ($blank1stItem && $zval=="") {
 330                         $zthis->MoveNext();
 331                         continue;
 332                 }
 333
 334         if ($fieldsize > 1) {
 335                         if (isset($zthis->fields[1]))
 336                                 $zval2 = rtrim($zthis->fields[1]);
 337                         else
 338                                 $zval2 = rtrim(next($zthis->fields));
 339                 }
 340                 $selected = ($compareFields0) ? $zval : $zval2;
 341
 342         $group = '';
 343                 if (isset($zthis->fields[2])) {
 344             $group = rtrim($zthis->fields[2]);
 345         }
 346
 347         if ($optgroup != $group) {
 348             $optgroup = $group;
 349             if ($firstgroup) {
 350                 $firstgroup = false;
 351                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 352             } else {
 353                 $s .="\n</optgroup>";
 354                 $s .="\n<optgroup label='". htmlspecialchars($group) ."'>";
 355             }
 356                 }
 357
 358                 if ($hasvalue)
 359                         $value = " value='".htmlspecialchars($zval2)."'";
 360
 361                 if (is_array($defstr))  {
 362
 363                         if (in_array($selected,$defstr))
 364                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 365                         else
 366                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 367                 }
 368                 else {
 369                         if (strcasecmp($selected,$defstr)==0)
 370                                 $s .= "\n<option selected='selected'$value>".htmlspecialchars($zval).'</option>';
 371                         else
 372                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 373                 }
 374                 $zthis->MoveNext();
 375         } // while
 376
 377     // closing last optgroup
 378     if($optgroup != null) {
 379         $s .= "\n</optgroup>";
 380         }
 381         return $s ."\n</select>\n";
 382 }
 383
 384
 385 /*
 386         Count the number of records this sql statement will return by using
 387         query rewriting heuristics...
 388
 389         Does not work with UNIONs, except with postgresql and oracle.
 390
 391         Usage:
 392
 393         $conn->Connect(...);
 394         $cnt = _adodb_getcount($conn, $sql);
 395
 396 */
 397 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
 398 {
 399         $qryRecs = 0;
 400
 401          if (!empty($zthis->_nestedSQL) || preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) ||
 402                 preg_match('/\s+GROUP\s+BY\s+/is',$sql) ||
 403                 preg_match('/\s+UNION\s+/is',$sql)) {
 404
 405                 $rewritesql = adodb_strip_order_by($sql);
 406
 407                 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
 408                 // but this is only supported by oracle and postgresql...
 409                 if ($zthis->dataProvider == 'oci8') {
 410                         // Allow Oracle hints to be used for query optimization, Chris Wrye
 411                         if (preg_match('#/\\*+.*?\\*\\/#', $sql, $hint)) {
 412                                 $rewritesql = "SELECT ".$hint[0]." COUNT(*) FROM (".$rewritesql.")";
 413                         } else
 414                                 $rewritesql = "SELECT COUNT(*) FROM (".$rewritesql.")";
 415
 416                 } else if (strncmp($zthis->databaseType,'postgres',8) == 0 || strncmp($zthis->databaseType,'mysql',5) == 0)  {
 417                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
 418                 } else {
 419                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
 420                 }
 421         } else {
 422                 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
 423                 $rewritesql = preg_replace(
 424                                         '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
 425                 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
 426                 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
 427                 // also see http://phplens.com/lens/lensforum/msgs.php?id=12752
 428                 $rewritesql = adodb_strip_order_by($rewritesql);
 429         }
 430
 431         if (isset($rewritesql) && $rewritesql != $sql) {
 432                 if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
 433
 434                 if ($secs2cache) {
 435                         // we only use half the time of secs2cache because the count can quickly
 436                         // become inaccurate if new records are added
 437                         $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
 438
 439                 } else {
 440                         $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
 441                 }
 442                 if ($qryRecs !== false) return $qryRecs;
 443         }
 444         //--------------------------------------------
 445         // query rewrite failed - so try slower way...
 446
 447
 448         // strip off unneeded ORDER BY if no UNION
 449         if (preg_match('/\s*UNION\s*/is', $sql)) $rewritesql = $sql;
 450         else $rewritesql = $rewritesql = adodb_strip_order_by($sql);
 451
 452         if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
 453
 454         if ($secs2cache) {
 455                 $rstest = $zthis->CacheExecute($secs2cache,$rewritesql,$inputarr);
 456                 if (!$rstest) $rstest = $zthis->CacheExecute($secs2cache,$sql,$inputarr);
 457         } else {
 458                 $rstest = $zthis->Execute($rewritesql,$inputarr);
 459                 if (!$rstest) $rstest = $zthis->Execute($sql,$inputarr);
 460         }
 461         if ($rstest) {
 462                         $qryRecs = $rstest->RecordCount();
 463                 if ($qryRecs == -1) {
 464                 global $ADODB_EXTENSION;
 465                 // some databases will return -1 on MoveLast() - change to MoveNext()
 466                         if ($ADODB_EXTENSION) {
 467                                 while(!$rstest->EOF) {
 468                                         adodb_movenext($rstest);
 469                                 }
 470                         } else {
 471                                 while(!$rstest->EOF) {
 472                                         $rstest->MoveNext();
 473                                 }
 474                         }
 475                         $qryRecs = $rstest->_currentRow;
 476                 }
 477                 $rstest->Close();
 478                 if ($qryRecs == -1) return 0;
 479         }
 480         return $qryRecs;
 481 }
 482
 483 /*
 484         Code originally from "Cornel G" <conyg@fx.ro>
 485
 486         This code might not work with SQL that has UNION in it
 487
 488         Also if you are using CachePageExecute(), there is a strong possibility that
 489         data will get out of synch. use CachePageExecute() only with tables that
 490         rarely change.
 491 */
 492 function _adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
 493                                                 $inputarr=false, $secs2cache=0)
 494 {
 495         $atfirstpage = false;
 496         $atlastpage = false;
 497         $lastpageno=1;
 498
 499         // If an invalid nrows is supplied,
 500         // we assume a default value of 10 rows per page
 501         if (!isset($nrows) || $nrows <= 0) $nrows = 10;
 502
 503         $qryRecs = false; //count records for no offset
 504
 505         $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
 506         $lastpageno = (int) ceil($qryRecs / $nrows);
 507         $zthis->_maxRecordCount = $qryRecs;
 508
 509
 510
 511         // ***** Here we check whether $page is the last page or
 512         // whether we are trying to retrieve
 513         // a page number greater than the last page number.
 514         if ($page >= $lastpageno) {
 515                 $page = $lastpageno;
 516                 $atlastpage = true;
 517         }
 518
 519         // If page number <= 1, then we are at the first page
 520         if (empty($page) || $page <= 1) {
 521                 $page = 1;
 522                 $atfirstpage = true;
 523         }
 524
 525         // We get the data we want
 526         $offset = $nrows * ($page-1);
 527         if ($secs2cache > 0)
 528                 $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 529         else
 530                 $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 531
 532
 533         // Before returning the RecordSet, we set the pagination properties we need
 534         if ($rsreturn) {
 535                 $rsreturn->_maxRecordCount = $qryRecs;
 536                 $rsreturn->rowsPerPage = $nrows;
 537                 $rsreturn->AbsolutePage($page);
 538                 $rsreturn->AtFirstPage($atfirstpage);
 539                 $rsreturn->AtLastPage($atlastpage);
 540                 $rsreturn->LastPageNo($lastpageno);
 541         }
 542         return $rsreturn;
 543 }
 544
 545 // Iván Oliva version
 546 function _adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
 547 {
 548
 549         $atfirstpage = false;
 550         $atlastpage = false;
 551
 552         if (!isset($page) || $page <= 1) {      // If page number <= 1, then we are at the first page
 553                 $page = 1;
 554                 $atfirstpage = true;
 555         }
 556         if ($nrows <= 0) $nrows = 10;   // If an invalid nrows is supplied, we assume a default value of 10 rows per page
 557
 558         // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
 559         // the last page number.
 560         $pagecounter = $page + 1;
 561         $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
 562         if ($secs2cache>0) $rstest = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 563         else $rstest = $zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 564         if ($rstest) {
 565                 while ($rstest && $rstest->EOF && $pagecounter>0) {
 566                         $atlastpage = true;
 567                         $pagecounter--;
 568                         $pagecounteroffset = $nrows * ($pagecounter - 1);
 569                         $rstest->Close();
 570                         if ($secs2cache>0) $rstest = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 571                         else $rstest = $zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 572                 }
 573                 if ($rstest) $rstest->Close();
 574         }
 575         if ($atlastpage) {      // If we are at the last page or beyond it, we are going to retrieve it
 576                 $page = $pagecounter;
 577                 if ($page == 1) $atfirstpage = true;    // We have to do this again in case the last page is the same as the first
 578                         //... page, that is, the recordset has only 1 page.
 579         }
 580
 581         // We get the data we want
 582         $offset = $nrows * ($page-1);
 583         if ($secs2cache > 0) $rsreturn = $zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 584         else $rsreturn = $zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 585
 586         // Before returning the RecordSet, we set the pagination properties we need
 587         if ($rsreturn) {
 588                 $rsreturn->rowsPerPage = $nrows;
 589                 $rsreturn->AbsolutePage($page);
 590                 $rsreturn->AtFirstPage($atfirstpage);
 591                 $rsreturn->AtLastPage($atlastpage);
 592         }
 593         return $rsreturn;
 594 }
 595
 596 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false,$force=2)
 597 {
 598         global $ADODB_QUOTE_FIELDNAMES;
 599
 600                 if (!$rs) {
 601                         printf(ADODB_BAD_RS,'GetUpdateSQL');
 602                         return false;
 603                 }
 604
 605                 $fieldUpdatedCount = 0;
 606                 $arrFields = _array_change_key_case($arrFields);
 607
 608                 $hasnumeric = isset($rs->fields[0]);
 609                 $setFields = '';
 610
 611                 // Loop through all of the fields in the recordset
 612                 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
 613                         // Get the field from the recordset
 614                         $field = $rs->FetchField($i);
 615
 616                         // If the recordset field is one
 617                         // of the fields passed in then process.
 618                         $upperfname = strtoupper($field->name);
 619                         if (adodb_key_exists($upperfname,$arrFields,$force)) {
 620
 621                                 // If the existing field value in the recordset
 622                                 // is different from the value passed in then
 623                                 // go ahead and append the field name and new value to
 624                                 // the update query.
 625
 626                                 if ($hasnumeric) $val = $rs->fields[$i];
 627                                 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
 628                                 else if (isset($rs->fields[$field->name])) $val =  $rs->fields[$field->name];
 629                                 else if (isset($rs->fields[strtolower($upperfname)])) $val =  $rs->fields[strtolower($upperfname)];
 630                                 else $val = '';
 631
 632
 633                                 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
 634                                         // Set the counter for the number of fields that will be updated.
 635                                         $fieldUpdatedCount++;
 636
 637                                         // Based on the datatype of the field
 638                                         // Format the value properly for the database
 639                                         $type = $rs->MetaType($field->type);
 640
 641
 642                                         if ($type == 'null') {
 643                                                 $type = 'C';
 644                                         }
 645
 646                                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES)) {
 647                                                 switch (ADODB_QUOTE_FIELDNAMES) {
 648                                                 case 'LOWER':
 649                                                         $fnameq = $zthis->nameQuote.strtolower($field->name).$zthis->nameQuote;break;
 650                                                 case 'NATIVE':
 651                                                         $fnameq = $zthis->nameQuote.$field->name.$zthis->nameQuote;break;
 652                                                 case 'UPPER':
 653                                                 default:
 654                                                         $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;break;
 655                                                 }
 656                                         } else
 657                                                 $fnameq = $upperfname;
 658
 659
 660                 // is_null requires php 4.0.4
 661                 //********************************************************//
 662                 if (is_null($arrFields[$upperfname])
 663                                         || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
 664                     || $arrFields[$upperfname] === $zthis->null2null
 665                     )
 666                 {
 667                     switch ($force) {
 668
 669                         //case 0:
 670                         //    //Ignore empty values. This is allready handled in "adodb_key_exists" function.
 671                         //break;
 672
 673                         case 1:
 674                             //Set null
 675                             $setFields .= $field->name . " = null, ";
 676                         break;
 677
 678                         case 2:
 679                             //Set empty
 680                             $arrFields[$upperfname] = "";
 681                             $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
 682                         break;
 683                                                 default:
 684                         case 3:
 685                             //Set the value that was given in array, so you can give both null and empty values
 686                             if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
 687                                 $setFields .= $field->name . " = null, ";
 688                             } else {
 689                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,$arrFields, $magicq);
 690                             }
 691                         break;
 692                     }
 693                 //********************************************************//
 694                 } else {
 695                                                 //we do this so each driver can customize the sql for
 696                                                 //DB specific column types.
 697                                                 //Oracle needs BLOB types to be handled with a returning clause
 698                                                 //postgres has special needs as well
 699                                                 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname, $fnameq,
 700                                                                                                                   $arrFields, $magicq);
 701                                         }
 702                                 }
 703                         }
 704                 }
 705
 706                 // If there were any modified fields then build the rest of the update query.
 707                 if ($fieldUpdatedCount > 0 || $forceUpdate) {
 708                                         // Get the table name from the existing query.
 709                         if (!empty($rs->tableName)) $tableName = $rs->tableName;
 710                         else {
 711                                 preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
 712                                 $tableName = $tableName[1];
 713                         }
 714                         // Get the full where clause excluding the word "WHERE" from
 715                         // the existing query.
 716                         preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
 717
 718                         $discard = false;
 719                         // not a good hack, improvements?
 720                         if ($whereClause) {
 721                         #var_dump($whereClause);
 722                                 if (preg_match('/\s(ORDER\s.*)/is', $whereClause[1], $discard));
 723                                 else if (preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard));
 724                                 else if (preg_match('/\s(FOR UPDATE.*)/is', $whereClause[1], $discard));
 725                                 else preg_match('/\s.*(\) WHERE .*)/is', $whereClause[1], $discard); # see http://sourceforge.net/tracker/index.php?func=detail&aid=1379638&group_id=42718&atid=433976
 726                         } else
 727                                 $whereClause = array(false,false);
 728
 729                         if ($discard)
 730                                 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
 731
 732                         $sql = 'UPDATE '.$tableName.' SET '.substr($setFields, 0, -2);
 733                         if (strlen($whereClause[1]) > 0)
 734                                 $sql .= ' WHERE '.$whereClause[1];
 735
 736                         return $sql;
 737
 738                 } else {
 739                         return false;
 740         }
 741 }
 742
 743 function adodb_key_exists($key, &$arr,$force=2)
 744 {
 745         if ($force<=0) {
 746                 // the following is the old behaviour where null or empty fields are ignored
 747                 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key])>0);
 748         }
 749
 750         if (isset($arr[$key])) return true;
 751         ## null check below
 752         if (ADODB_PHPVER >= 0x4010) return array_key_exists($key,$arr);
 753         return false;
 754 }
 755
 756 /**
 757  * There is a special case of this function for the oci8 driver.
 758  * The proper way to handle an insert w/ a blob in oracle requires
 759  * a returning clause with bind variables and a descriptor blob.
 760  *
 761  *
 762  */
 763 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false,$force=2)
 764 {
 765 static $cacheRS = false;
 766 static $cacheSig = 0;
 767 static $cacheCols;
 768         global $ADODB_QUOTE_FIELDNAMES;
 769
 770         $tableName = '';
 771         $values = '';
 772         $fields = '';
 773         $recordSet = null;
 774         $arrFields = _array_change_key_case($arrFields);
 775         $fieldInsertedCount = 0;
 776
 777         if (is_string($rs)) {
 778                 //ok we have a table name
 779                 //try and get the column info ourself.
 780                 $tableName = $rs;
 781
 782                 //we need an object for the recordSet
 783                 //because we have to call MetaType.
 784                 //php can't do a $rsclass::MetaType()
 785                 $rsclass = $zthis->rsPrefix.$zthis->databaseType;
 786                 $recordSet = new $rsclass(-1,$zthis->fetchMode);
 787                 $recordSet->connection = $zthis;
 788
 789                 if (is_string($cacheRS) && $cacheRS == $rs) {
 790                         $columns = $cacheCols;
 791                 } else {
 792                         $columns = $zthis->MetaColumns( $tableName );
 793                         $cacheRS = $tableName;
 794                         $cacheCols = $columns;
 795                 }
 796         } else if (is_subclass_of($rs, 'adorecordset')) {
 797                 if (isset($rs->insertSig) && is_integer($cacheRS) && $cacheRS == $rs->insertSig) {
 798                         $columns = $cacheCols;
 799                 } else {
 800                         for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++)
 801                                 $columns[] = $rs->FetchField($i);
 802                         $cacheRS = $cacheSig;
 803                         $cacheCols = $columns;
 804                         $rs->insertSig = $cacheSig++;
 805                 }
 806                 $recordSet = $rs;
 807
 808         } else {
 809                 printf(ADODB_BAD_RS,'GetInsertSQL');
 810                 return false;
 811         }
 812
 813         // Loop through all of the fields in the recordset
 814         foreach( $columns as $field ) {
 815                 $upperfname = strtoupper($field->name);
 816                 if (adodb_key_exists($upperfname,$arrFields,$force)) {
 817                         $bad = false;
 818                         if ((strpos($upperfname,' ') !== false) || ($ADODB_QUOTE_FIELDNAMES)) {
 819                                 switch (ADODB_QUOTE_FIELDNAMES) {
 820                                 case 'LOWER':
 821                                         $fnameq = $zthis->nameQuote.strtolower($field->name).$zthis->nameQuote;break;
 822                                 case 'NATIVE':
 823                                         $fnameq = $zthis->nameQuote.$field->name.$zthis->nameQuote;break;
 824                                 case 'UPPER':
 825                                 default:
 826                                         $fnameq = $zthis->nameQuote.$upperfname.$zthis->nameQuote;break;
 827                                 }
 828                         } else
 829                                 $fnameq = $upperfname;
 830
 831                         $type = $recordSet->MetaType($field->type);
 832
 833             /********************************************************/
 834             if (is_null($arrFields[$upperfname])
 835                 || (empty($arrFields[$upperfname]) && strlen($arrFields[$upperfname]) == 0)
 836                 || $arrFields[$upperfname] === $zthis->null2null
 837                                 )
 838                {
 839                     switch ($force) {
 840
 841                         case 0: // we must always set null if missing
 842                                                         $bad = true;
 843                                                         break;
 844
 845                         case 1:
 846                             $values  .= "null, ";
 847                         break;
 848
 849                         case 2:
 850                             //Set empty
 851                             $arrFields[$upperfname] = "";
 852                             $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,$arrFields, $magicq);
 853                         break;
 854
 855                                                 default:
 856                         case 3:
 857                             //Set the value that was given in array, so you can give both null and empty values
 858                                                         if (is_null($arrFields[$upperfname]) || $arrFields[$upperfname] === $zthis->null2null) {
 859                                                                 $values  .= "null, ";
 860                                                         } else {
 861                                         $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq, $arrFields, $magicq);
 862                                         }
 863                                 break;
 864                         } // switch
 865
 866             /*********************************************************/
 867                         } else {
 868                                 //we do this so each driver can customize the sql for
 869                                 //DB specific column types.
 870                                 //Oracle needs BLOB types to be handled with a returning clause
 871                                 //postgres has special needs as well
 872                                 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname, $fnameq,
 873                                                                                            $arrFields, $magicq);
 874                         }
 875
 876                         if ($bad) continue;
 877                         // Set the counter for the number of fields that will be inserted.
 878                         $fieldInsertedCount++;
 879
 880
 881                         // Get the name of the fields to insert
 882                         $fields .= $fnameq . ", ";
 883                 }
 884         }
 885
 886
 887         // If there were any inserted fields then build the rest of the insert query.
 888         if ($fieldInsertedCount <= 0)  return false;
 889
 890         // Get the table name from the existing query.
 891         if (!$tableName) {
 892                 if (!empty($rs->tableName)) $tableName = $rs->tableName;
 893                 else if (preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName))
 894                         $tableName = $tableName[1];
 895                 else
 896                         return false;
 897         }
 898
 899         // Strip off the comma and space on the end of both the fields
 900         // and their values.
 901         $fields = substr($fields, 0, -2);
 902         $values = substr($values, 0, -2);
 903
 904         // Append the fields and their values to the insert query.
 905         return 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
 906 }
 907
 908
 909 /**
 910  * This private method is used to help construct
 911  * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
 912  * It handles the string construction of 1 column -> sql string based on
 913  * the column type.  We want to do 'safe' handling of BLOBs
 914  *
 915  * @param string the type of sql we are trying to create
 916  *                'I' or 'U'.
 917  * @param string column data type from the db::MetaType() method
 918  * @param string the column name
 919  * @param array the column value
 920  *
 921  * @return string
 922  *
 923  */
 924 function _adodb_column_sql_oci8(&$zthis,$action, $type, $fname, $fnameq, $arrFields, $magicq)
 925 {
 926     $sql = '';
 927
 928     // Based on the datatype of the field
 929     // Format the value properly for the database
 930     switch($type) {
 931     case 'B':
 932         //in order to handle Blobs correctly, we need
 933         //to do some magic for Oracle
 934
 935         //we need to create a new descriptor to handle
 936         //this properly
 937         if (!empty($zthis->hasReturningInto)) {
 938             if ($action == 'I') {
 939                 $sql = 'empty_blob(), ';
 940             } else {
 941                 $sql = $fnameq. '=empty_blob(), ';
 942             }
 943             //add the variable to the returning clause array
 944             //so the user can build this later in
 945             //case they want to add more to it
 946             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
 947         } else if (empty($arrFields[$fname])){
 948             if ($action == 'I') {
 949                 $sql = 'empty_blob(), ';
 950             } else {
 951                 $sql = $fnameq. '=empty_blob(), ';
 952             }
 953         } else {
 954             //this is to maintain compatibility
 955             //with older adodb versions.
 956             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
 957         }
 958         break;
 959
 960     case "X":
 961         //we need to do some more magic here for long variables
 962         //to handle these correctly in oracle.
 963
 964         //create a safe bind var name
 965         //to avoid conflicts w/ dupes.
 966        if (!empty($zthis->hasReturningInto)) {
 967             if ($action == 'I') {
 968                 $sql = ':xx'.$fname.'xx, ';
 969             } else {
 970                 $sql = $fnameq.'=:xx'.$fname.'xx, ';
 971             }
 972             //add the variable to the returning clause array
 973             //so the user can build this later in
 974             //case they want to add more to it
 975             $zthis->_returningArray[$fname] = ':xx'.$fname.'xx';
 976         } else {
 977             //this is to maintain compatibility
 978             //with older adodb versions.
 979             $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq,false);
 980         }
 981         break;
 982
 983     default:
 984         $sql = _adodb_column_sql($zthis, $action, $type, $fname, $fnameq,  $arrFields, $magicq,false);
 985         break;
 986     }
 987
 988     return $sql;
 989 }
 990
 991 function _adodb_column_sql(&$zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq, $recurse=true)
 992 {
 993
 994         if ($recurse) {
 995                 switch($zthis->dataProvider)  {
 996                 case 'postgres':
 997                         if ($type == 'L') $type = 'C';
 998                         break;
 999                 case 'oci8':
1000                         return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $fnameq, $arrFields, $magicq);
1001
1002                 }
1003         }
1004
1005         switch($type) {
1006                 case "C":
1007                 case "X":
1008                 case 'B':
1009                         $val = $zthis->qstr($arrFields[$fname],$magicq);
1010                         break;
1011
1012                 case "D":
1013                         $val = $zthis->DBDate($arrFields[$fname]);
1014                         break;
1015
1016                 case "T":
1017                         $val = $zthis->DBTimeStamp($arrFields[$fname]);
1018                         break;
1019
1020                 case "N":
1021                     $val = $arrFields[$fname];
1022                         if (!is_numeric($val)) $val = str_replace(',', '.', (float)$val);
1023                     break;
1024
1025                 case "I":
1026                 case "R":
1027                     $val = $arrFields[$fname];
1028                         if (!is_numeric($val)) $val = (integer) $val;
1029                     break;
1030
1031                 default:
1032                         $val = str_replace(array("'"," ","("),"",$arrFields[$fname]); // basic sql injection defence
1033                         if (empty($val)) $val = '0';
1034                         break;
1035         }
1036
1037         if ($action == 'I') return $val . ", ";
1038
1039
1040         return $fnameq . "=" . $val  . ", ";
1041
1042 }
1043
1044
1045
1046 function _adodb_debug_execute(&$zthis, $sql, $inputarr)
1047 {
1048         $ss = '';
1049         if ($inputarr) {
1050                 foreach($inputarr as $kk=>$vv) {
1051                         if (is_string($vv) && strlen($vv)>64) $vv = substr($vv,0,64).'...';
1052                         if (is_null($vv)) $ss .= "($kk=>null) ";
1053                         else $ss .= "($kk=>'$vv') ";
1054                 }
1055                 $ss = "[ $ss ]";
1056         }
1057         $sqlTxt = is_array($sql) ? $sql[0] : $sql;
1058         /*str_replace(', ','##1#__^LF',is_array($sql) ? $sql[0] : $sql);
1059         $sqlTxt = str_replace(',',', ',$sqlTxt);
1060         $sqlTxt = str_replace('##1#__^LF', ', ' ,$sqlTxt);
1061         */
1062         // check if running from browser or command-line
1063         $inBrowser = isset($_SERVER['HTTP_USER_AGENT']);
1064
1065         $dbt = $zthis->databaseType;
1066         if (isset($zthis->dsnType)) $dbt .= '-'.$zthis->dsnType;
1067         if ($inBrowser) {
1068                 if ($ss) {
1069                         $ss = '<code>'.htmlspecialchars($ss).'</code>';
1070                 }
1071                 if ($zthis->debug === -1)
1072                         ADOConnection::outp( "<br>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<br>\n",false);
1073                 else if ($zthis->debug !== -99)
1074                         ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1075         } else {
1076                 $ss = "\n   ".$ss;
1077                 if ($zthis->debug !== -99)
1078                         ADOConnection::outp("-----<hr>\n($dbt): ".$sqlTxt." $ss\n-----<hr>\n",false);
1079         }
1080
1081         $qID = $zthis->_query($sql,$inputarr);
1082
1083         /*
1084                 Alexios Fakios notes that ErrorMsg() must be called before ErrorNo() for mssql
1085                 because ErrorNo() calls Execute('SELECT @ERROR'), causing recursion
1086         */
1087         if ($zthis->databaseType == 'mssql') {
1088         // ErrorNo is a slow function call in mssql, and not reliable in PHP 4.0.6
1089
1090                 if($emsg = $zthis->ErrorMsg()) {
1091                         if ($err = $zthis->ErrorNo()) {
1092                                 if ($zthis->debug === -99)
1093                                         ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1094
1095                                 ADOConnection::outp($err.': '.$emsg);
1096                         }
1097                 }
1098         } else if (!$qID) {
1099
1100                 if ($zthis->debug === -99)
1101                                 if ($inBrowser) ADOConnection::outp( "<hr>\n($dbt): ".htmlspecialchars($sqlTxt)." &nbsp; $ss\n<hr>\n",false);
1102                                 else ADOConnection::outp("-----<hr>\n($dbt): ".$sqlTxt."$ss\n-----<hr>\n",false);
1103
1104                 ADOConnection::outp($zthis->ErrorNo() .': '. $zthis->ErrorMsg());
1105         }
1106
1107         if ($zthis->debug === 99) _adodb_backtrace(true,9999,2);
1108         return $qID;
1109 }
1110
1111 # pretty print the debug_backtrace function
1112 function _adodb_backtrace($printOrArr=true,$levels=9999,$skippy=0,$ishtml=null)
1113 {
1114         if (!function_exists('debug_backtrace')) return '';
1115
1116         if ($ishtml === null) $html =  (isset($_SERVER['HTTP_USER_AGENT']));
1117         else $html = $ishtml;
1118
1119         $fmt =  ($html) ? "</font><font color=#808080 size=-1> %% line %4d, file: <a href=\"file:/%s\">%s</a></font>" : "%% line %4d, file: %s";
1120
1121         $MAXSTRLEN = 128;
1122
1123         $s = ($html) ? '<pre align=left>' : '';
1124
1125         if (is_array($printOrArr)) $traceArr = $printOrArr;
1126         else $traceArr = debug_backtrace();
1127         array_shift($traceArr);
1128         array_shift($traceArr);
1129         $tabs = sizeof($traceArr)-2;
1130
1131         foreach ($traceArr as $arr) {
1132                 if ($skippy) {$skippy -= 1; continue;}
1133                 $levels -= 1;
1134                 if ($levels < 0) break;
1135
1136                 $args = array();
1137                 for ($i=0; $i < $tabs; $i++) $s .=  ($html) ? ' &nbsp; ' : "\t";
1138                 $tabs -= 1;
1139                 if ($html) $s .= '<font face="Courier New,Courier">';
1140                 if (isset($arr['class'])) $s .= $arr['class'].'.';
1141                 if (isset($arr['args']))
1142                  foreach($arr['args'] as $v) {
1143                         if (is_null($v)) $args[] = 'null';
1144                         else if (is_array($v)) $args[] = 'Array['.sizeof($v).']';
1145                         else if (is_object($v)) $args[] = 'Object:'.get_class($v);
1146                         else if (is_bool($v)) $args[] = $v ? 'true' : 'false';
1147                         else {
1148                                 $v = (string) @$v;
1149                                 $str = htmlspecialchars(str_replace(array("\r","\n"),' ',substr($v,0,$MAXSTRLEN)));
1150                                 if (strlen($v) > $MAXSTRLEN) $str .= '...';
1151                                 $args[] = $str;
1152                         }
1153                 }
1154                 $s .= $arr['function'].'('.implode(', ',$args).')';
1155
1156
1157                 $s .= @sprintf($fmt, $arr['line'],$arr['file'],basename($arr['file']));
1158
1159                 $s .= "\n";
1160         }
1161         if ($html) $s .= '</pre>';
1162         if ($printOrArr) print $s;
1163
1164         return $s;
1165 }
1166 /*
1167 function _adodb_find_from($sql)
1168 {
1169
1170         $sql = str_replace(array("\n","\r"), ' ', $sql);
1171         $charCount = strlen($sql);
1172
1173         $inString = false;
1174         $quote = '';
1175         $parentheseCount = 0;
1176         $prevChars = '';
1177         $nextChars = '';
1178
1179
1180         for($i = 0; $i < $charCount; $i++) {
1181
1182         $char = substr($sql,$i,1);
1183             $prevChars = substr($sql,0,$i);
1184         $nextChars = substr($sql,$i+1);
1185
1186                 if((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === false) {
1187                         $quote = $char;
1188                         $inString = true;
1189                 }
1190
1191                 elseif((($char == "'" || $char == '"' || $char == '`') && substr($prevChars,-1,1) != '\\') && $inString === true && $quote == $char) {
1192                         $quote = "";
1193                         $inString = false;
1194                 }
1195
1196                 elseif($char == "(" && $inString === false)
1197                         $parentheseCount++;
1198
1199                 elseif($char == ")" && $inString === false && $parentheseCount > 0)
1200                         $parentheseCount--;
1201
1202                 elseif($parentheseCount <= 0 && $inString === false && $char == " " && strtoupper(substr($prevChars,-5,5)) == " FROM")
1203                         return $i;
1204
1205         }
1206 }
1207 */
1208
1209 ?>