CWIS Developer Documentation
Database.php
Go to the documentation of this file.
1 <?PHP
2 
3 #
4 # Axis--Database.php
5 # A Simple SQL Database Abstraction Object
6 #
7 # Copyright 1999-2002 Axis Data
8 # This code is free software that can be used or redistributed under the
9 # terms of Version 2 of the GNU General Public License, as published by the
10 # Free Software Foundation (http://www.fsf.org).
11 #
12 # Author: Edward Almasy (almasy@axisdata.com)
13 #
14 # Part of the AxisPHP library v1.2.5
15 # For more information see http://www.axisdata.com/AxisPHP/
16 #
17 
22 class Database
23 {
24 
25  # ---- PUBLIC INTERFACE --------------------------------------------------
26  /*@(*/
28 
41  public function __construct(
42  $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
43  {
44  # save DB access parameter values
45  $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
46  $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
47  $this->DBHostName = $HostName ? $HostName :
48  (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
49  : "localhost");
50  $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
51 
52  # set memory threshold for cache clearing
53  if (!isset(self::$CacheMemoryThreshold))
54  {
55  self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
56  }
57 
58  # if we don't already have a connection or DB access parameters were supplied
59  $HandleIndex = $this->DBHostName.":".$this->DBName;
60  if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
61  || $UserName || $Password || $DatabaseName || $HostName)
62  {
63  # open connection to DB server
64  self::$ConnectionHandles[$HandleIndex] = mysqli_connect(
65  $this->DBHostName, $this->DBUserName,
66  $this->DBPassword)
67  or die("Could not connect to database: ".mysqli_connect_error());
68 
69  # set local connection handle
70  $this->Handle = self::$ConnectionHandles[$HandleIndex];
71 
72  # select DB
73  mysqli_select_db($this->Handle, $this->DBName)
74  or die(mysqli_error($this->Handle));
75  }
76  else
77  {
78  # set local connection handle
79  $this->Handle = self::$ConnectionHandles[$HandleIndex];
80  }
81  }
82 
87  public function __sleep()
88  {
89  return array("DBUserName", "DBPassword", "DBHostName", "DBName");
90  }
94  public function __wakeup()
95  {
96  # open connection to DB server
97  $this->Handle = mysqli_connect(
98  $this->DBHostName, $this->DBUserName, $this->DBPassword)
99  or die("could not connect to database");
100 
101  # select DB
102  mysqli_select_db($this->Handle, $this->DBName)
103  or die(mysqli_error($this->Handle));
104  }
114  public static function SetGlobalServerInfo(
115  $UserName, $Password, $HostName = "localhost")
116  {
117  # save default DB access parameters
118  self::$GlobalDBUserName = $UserName;
119  self::$GlobalDBPassword = $Password;
120  self::$GlobalDBHostName = $HostName;
121 
122  # clear any existing DB connection handles
123  self::$ConnectionHandles = array();
124  }
125 
130  public static function SetGlobalDatabaseName($DatabaseName)
131  {
132  # save new default DB name
133  self::$GlobalDBName = $DatabaseName;
134 
135  # clear any existing DB connection handles
136  self::$ConnectionHandles = array();
137  }
138 
143  public function SetDefaultStorageEngine($Engine)
144  {
145  # choose config variable to use based on server version number
146  $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
147  ? "storage_engine" : "default_storage_engine";
148 
149  # set storage engine in database
150  $this->Query("SET ".$ConfigVar." = ".$Engine);
151  }
152 
159  public function GetServerVersion($FullVersion=FALSE)
160  {
161  # retrieve version string
162  $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
163 
164  if (!$FullVersion)
165  {
166  # strip off any build/config suffix
167  $Pieces = explode("-", $Version);
168  $Version = array_shift($Pieces);
169  }
170 
171  # return version number to caller
172  return $Version;
173  }
174 
183  public function GetClientVersion()
184  {
185  return mysqli_get_client_info();
186  }
187 
193  public function GetHostInfo()
194  {
195  return mysqli_get_host_info($this->Handle);
196  }
197 
203  public function DBHostName()
204  {
205  return $this->DBHostName;
206  }
207 
213  public function DBName()
214  {
215  return $this->DBName;
216  }
217 
223  public function DBUserName()
224  {
225  return $this->DBUserName;
226  }
227 
235  public static function Caching($NewSetting = NULL)
236  {
237  # if cache setting has changed
238  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
239  {
240  # save new setting
241  self::$CachingFlag = $NewSetting;
242 
243  # clear any existing cached results
244  self::$QueryResultCache = array();
245  }
246 
247  # return current setting to caller
248  return self::$CachingFlag;
249  }
250 
261  public static function AdvancedCaching($NewSetting = NULL)
262  {
263  if ($NewSetting !== NULL)
264  {
265  self::$AdvancedCachingFlag = $NewSetting;
266  }
267  return self::$AdvancedCachingFlag;
268  }
269 
289  public function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
290  {
291  if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
292  {
293  $RevisedErrorsToIgnore = array();
294  foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
295  {
296  $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
297  $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
298  }
299  $ErrorsToIgnore = $RevisedErrorsToIgnore;
300  }
301  $this->ErrorsToIgnore = $ErrorsToIgnore;
302  }
303 
309  public function IgnoredError()
310  {
311  return $this->ErrorIgnored;
312  }
313 
314  /*@)*/ /* Setup/Initialization */ /*@(*/
316 
327  public function Query($QueryString, $FieldName = "")
328  {
329  # clear flag that indicates whether query error was ignored
330  $this->ErrorIgnored = FALSE;
331 
332  # if caching is enabled
333  if (self::$CachingFlag)
334  {
335  # if SQL statement is read-only
336  if ($this->IsReadOnlyStatement($QueryString))
337  {
338  # if we have statement in cache
339  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
340  {
341  if (self::$QueryDebugOutputFlag)
342  { print("DB-C: $QueryString<br>\n"); }
343 
344  # make sure query result looks okay
345  $this->QueryHandle = TRUE;
346 
347  # increment cache hit counter
348  self::$CachedQueryCounter++;
349 
350  # make local copy of results
351  $this->QueryResults = self::$QueryResultCache[$QueryString];
352  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
353 
354  # set flag to indicate that results should be retrieved from cache
355  $this->GetResultsFromCache = TRUE;
356  }
357  else
358  {
359  # execute SQL statement
360  $this->QueryHandle = $this->RunQuery($QueryString);
361  if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
362 
363  # save number of rows in result
364  $this->NumRows = mysqli_num_rows($this->QueryHandle);
365 
366  # if too many rows to cache
367  if ($this->NumRows >= self::$CacheRowsThreshold)
368  {
369  # set flag to indicate that query results should not
370  # be retrieved from cache
371  $this->GetResultsFromCache = FALSE;
372  }
373  else
374  {
375  # if we are low on memory
376  if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
377  {
378  # clear out all but last few rows from cache
379  self::$QueryResultCache = array_slice(
380  self::$QueryResultCache,
381  (0 - self::$CacheRowsToLeave));
382  }
383 
384  # if advanced caching is enabled
385  if (self::$AdvancedCachingFlag)
386  {
387  # save tables accessed by query
388  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
389  $this->TablesAccessed($QueryString);
390  }
391 
392  # if rows found
393  if ($this->NumRows > 0)
394  {
395  # load query results
396  for ($Row = 0; $Row < $this->NumRows; $Row++)
397  {
398  $this->QueryResults[$Row] =
399  mysqli_fetch_assoc($this->QueryHandle);
400  }
401 
402  # cache query results
403  self::$QueryResultCache[$QueryString] = $this->QueryResults;
404  }
405  else
406  {
407  # clear local query results
408  unset($this->QueryResults);
409  }
410 
411  # cache number of rows
412  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
413 
414  # set flag to indicate that query results should be
415  # retrieved from cache
416  $this->GetResultsFromCache = TRUE;
417  }
418  }
419  }
420  else
421  {
422  # if advanced caching is enabled
423  if (self::$AdvancedCachingFlag)
424  {
425  # if table modified by statement is known
426  $TableModified = $this->TableModified($QueryString);
427  if ($TableModified)
428  {
429  # for each cached query
430  foreach (self::$QueryResultCache
431  as $CachedQueryString => $CachedQueryResult)
432  {
433  # if we know what tables were accessed
434  if ($CachedQueryResult["TablesAccessed"])
435  {
436  # if tables accessed include the one we may modify
437  if (in_array($TableModified,
438  $CachedQueryResult["TablesAccessed"]))
439  {
440  # clear cached query results
441  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
442  }
443  }
444  else
445  {
446  # clear cached query results
447  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
448  }
449  }
450  }
451  else
452  {
453  # clear entire query result cache
454  self::$QueryResultCache = array();
455  }
456  }
457  else
458  {
459  # clear entire query result cache
460  self::$QueryResultCache = array();
461  }
462 
463  # execute SQL statement
464  $this->QueryHandle = $this->RunQuery($QueryString);
465  if ($this->QueryHandle === FALSE) { return FALSE; }
466 
467  # set flag to indicate that query results should not be
468  # retrieved from cache
469  $this->GetResultsFromCache = FALSE;
470  }
471 
472  # reset row counter
473  $this->RowCounter = 0;
474 
475  # increment query counter
476  self::$QueryCounter++;
477  }
478  else
479  {
480  # execute SQL statement
481  $this->QueryHandle = $this->RunQuery($QueryString);
482  if ($this->QueryHandle === FALSE) { return FALSE; }
483  }
484 
485  if (($FieldName != "") && ($this->QueryHandle != FALSE))
486  {
487  return $this->FetchField($FieldName);
488  }
489  else
490  {
491  return $this->QueryHandle;
492  }
493  }
494 
507  public function ExecuteQueriesFromFile($FileName)
508  {
509  # open file
510  $FHandle = fopen($FileName, "r");
511 
512  # if file open succeeded
513  if ($FHandle !== FALSE)
514  {
515  # while lines left in file
516  $Query = "";
517  $QueryCount = 0;
518  while (!feof($FHandle))
519  {
520  # read in line from file
521  $Line = fgets($FHandle, 32767);
522 
523  # trim whitespace from line
524  $Line = trim($Line);
525 
526  # if line is not empty and not a comment
527  if (!preg_match("/^#/", $Line)
528  && !preg_match("/^--/", $Line)
529  && strlen($Line))
530  {
531  # add line to current query
532  $Query .= " ".$Line;
533 
534  # if line completes a query
535  if (preg_match("/;$/", $Line))
536  {
537  # run query
538  $QueryCount++;
539  $Result = $this->Query($Query);
540  $Query = "";
541 
542  # if query resulted in an error that is not ignorable
543  if ($Result === FALSE)
544  {
545  # stop processing queries and set error code
546  $QueryCount = NULL;
547  break;
548  }
549  }
550  }
551  }
552 
553  # close file
554  fclose($FHandle);
555  }
556 
557  # return number of executed queries to caller
558  return $QueryCount;
559  }
560 
566  public function QueryErrMsg()
567  {
568  return $this->ErrMsg;
569  }
570 
576  public function QueryErrNo()
577  {
578  return $this->ErrNo;
579  }
580 
587  public static function DisplayQueryErrors($NewValue = NULL)
588  {
589  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
590  return self::$DisplayErrors;
591  }
592 
597  public function NumRowsSelected()
598  {
599  # if caching is enabled and query was cached
600  if (self::$CachingFlag && $this->GetResultsFromCache)
601  {
602  # return cached number of rows to caller
603  return $this->NumRows;
604  }
605  else
606  {
607  # call to this method after an unsuccessful query
608  if (!$this->QueryHandle instanceof mysqli_result)
609  {
610  return 0;
611  }
612 
613  # retrieve number of rows and return to caller
614  return mysqli_num_rows($this->QueryHandle);
615  }
616  }
617 
623  public function NumRowsAffected()
624  {
625  # call to this method after an unsuccessful query
626  if (!$this->QueryHandle instanceof mysqli_result)
627  {
628  return 0;
629  }
630 
631  # retrieve number of rows and return to caller
632  return mysqli_affected_rows($this->Handle);
633  }
634 
640  public function FetchRow()
641  {
642  # if caching is enabled and query was cached
643  if (self::$CachingFlag && $this->GetResultsFromCache)
644  {
645  # if rows left to return
646  if ($this->RowCounter < $this->NumRows)
647  {
648  # retrieve row from cache
649  $Result = $this->QueryResults[$this->RowCounter];
650 
651  # increment row counter
652  $this->RowCounter++;
653  }
654  else
655  {
656  # return nothing
657  $Result = FALSE;
658  }
659  }
660  else
661  {
662  # call to this method after successful query
663  if ($this->QueryHandle instanceof mysqli_result)
664  {
665  $Result = mysqli_fetch_assoc($this->QueryHandle);
666  if ($Result === NULL) { $Result = FALSE; }
667  }
668 
669  # call to this method after unsuccessful query
670  else
671  {
672  $Result = FALSE;
673  }
674  }
675 
676  # return row to caller
677  return $Result;
678  }
679 
686  public function FetchRows($NumberOfRows = NULL)
687  {
688  # assume no rows will be returned
689  $Result = array();
690 
691  # for each available row
692  $RowsFetched = 0;
693  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
694  && ($Row = $this->FetchRow()))
695  {
696  # add row to results
697  $Result[] = $Row;
698  $RowsFetched++;
699  }
700 
701  # return array of rows to caller
702  return $Result;
703  }
704 
721  public function FetchColumn($FieldName, $IndexFieldName = NULL)
722  {
723  $Array = array();
724  while ($Record = $this->FetchRow())
725  {
726  if ($IndexFieldName != NULL)
727  {
728  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
729  }
730  else
731  {
732  $Array[] = $Record[$FieldName];
733  }
734  }
735  return $Array;
736  }
737 
746  public function FetchField($FieldName)
747  {
748  $Record = $this->FetchRow();
749  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
750  }
751 
758  public function LastInsertId()
759  {
760  return (int)$this->Query(
761  "SELECT LAST_INSERT_ID() AS InsertId",
762  "InsertId");
763  }
764 
779  public function UpdateValue(
780  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
781  {
782  # expand condition if supplied
783  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
784 
785  # read cached record from database if not already loaded
786  if (!isset($CachedRecord))
787  {
788  $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
789  $CachedRecord = $this->FetchRow();
790  }
791 
792  # if new value supplied
793  if ($NewValue !== DB_NOVALUE)
794  {
795  # update value in database
796  $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
797  .(($NewValue === NULL) ? "NULL" : "'"
798  .mysqli_real_escape_string($this->Handle, $NewValue)."'")
799  .$Condition);
800 
801  # update value in cached record
802  $CachedRecord[$FieldName] = $NewValue;
803  }
804 
805  # return value from cached record to caller
806  return isset($CachedRecord[$FieldName])
807  ? $CachedRecord[$FieldName] : NULL;
808  }
809 
826  public function UpdateIntValue(
827  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
828  {
829  return $this->UpdateValue($TableName, $FieldName,
830  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
831  $Condition, $CachedRecord);
832  }
833 
850  public function UpdateFloatValue(
851  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
852  {
853  return $this->UpdateValue($TableName, $FieldName,
854  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
855  $Condition, $CachedRecord);
856  }
857 
858  /*@)*/ /* Data Manipulation */ /*@(*/
860 
869  public function EscapeString($String)
870  {
871  return mysqli_real_escape_string($this->Handle, $String);
872  }
873 
880  public function LogComment($String)
881  {
882  $this->Query("-- ".$String);
883  }
884 
890  public function TableExists($TableName)
891  {
892  $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
893  return $this->NumRowsSelected() ? TRUE : FALSE;
894  }
895 
902  public function FieldExists($TableName, $FieldName)
903  {
904  $this->Query("DESC ".$TableName);
905  while ($CurrentFieldName = $this->FetchField("Field"))
906  {
907  if ($CurrentFieldName == $FieldName) { return TRUE; }
908  }
909  return FALSE;
910  }
911 
918  public function GetFieldType($TableName, $FieldName)
919  {
920  $this->Query("DESC ".$TableName);
921  $AllTypes = $this->FetchColumn("Type", "Field");
922  return $AllTypes[$FieldName];
923  }
924 
930  public static function QueryDebugOutput($NewSetting)
931  {
932  self::$QueryDebugOutputFlag = $NewSetting;
933  }
934 
940  public static function NumQueries()
941  {
942  return self::$QueryCounter;
943  }
944 
951  public static function NumCacheHits()
952  {
953  return self::$CachedQueryCounter;
954  }
955 
961  public static function CacheHitRate()
962  {
963  if (self::$QueryCounter)
964  {
965  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
966  }
967  else
968  {
969  return 0;
970  }
971  }
972 
973  /*@)*/ /* Miscellaneous */
974 
975  # ---- PRIVATE INTERFACE -------------------------------------------------
976 
977  protected $DBUserName;
978  protected $DBPassword;
979  protected $DBHostName;
980  protected $DBName;
981 
982  private $Handle;
983  private $QueryHandle;
984  private $QueryResults;
985  private $RowCounter;
986  private $NumRows;
987  private $GetResultsFromCache;
988  private $ErrorIgnored = FALSE;
989  private $ErrorsToIgnore = NULL;
990  private $ErrMsg = NULL;
991  private $ErrNo = NULL;
992 
993  private static $DisplayErrors = FALSE;
994 
995  private static $GlobalDBUserName;
996  private static $GlobalDBPassword;
997  private static $GlobalDBHostName;
998  private static $GlobalDBName;
999 
1000  # debug output flag
1001  private static $QueryDebugOutputFlag = FALSE;
1002  # flag for whether caching is turned on
1003  private static $CachingFlag = TRUE;
1004  # query result advanced caching flag
1005  private static $AdvancedCachingFlag = FALSE;
1006  # global cache for query results
1007  private static $QueryResultCache = array();
1008  # stats counters
1009  private static $QueryCounter = 0;
1010  private static $CachedQueryCounter = 0;
1011  # database connection link handles
1012  private static $ConnectionHandles = array();
1013  # do not cache queries that return more than this number of rows
1014  private static $CacheRowsThreshold = 250;
1015  # prune the query cache if there is less than this amount of memory free
1016  private static $CacheMemoryThreshold;
1017  # number of rows to leave in cache when pruning
1018  private static $CacheRowsToLeave = 10;
1019 
1025  private function IsReadOnlyStatement($QueryString)
1026  {
1027  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1028  }
1029 
1036  private function TableModified($QueryString)
1037  {
1038  # assume we're not going to be able to determine table
1039  $TableName = FALSE;
1040 
1041  # split query into pieces
1042  $QueryString = trim($QueryString);
1043  $Words = preg_split("/\s+/", $QueryString);
1044 
1045  # if INSERT statement
1046  $WordIndex = 1;
1047  if (strtoupper($Words[0]) == "INSERT")
1048  {
1049  # skip over modifying keywords
1050  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1051  || (strtoupper($Words[$WordIndex]) == "DELAYED")
1052  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1053  || (strtoupper($Words[$WordIndex]) == "INTO"))
1054  {
1055  $WordIndex++;
1056  }
1057 
1058  # next word is table name
1059  $TableName = $Words[$WordIndex];
1060  }
1061  # else if UPDATE statement
1062  elseif (strtoupper($Words[0]) == "UPDATE")
1063  {
1064  # skip over modifying keywords
1065  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1066  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1067  {
1068  $WordIndex++;
1069  }
1070 
1071  # if word following next word is SET
1072  if (strtoupper($Words[$WordIndex + 1]) == "SET")
1073  {
1074  # next word is table name
1075  $TableName = $Words[$WordIndex];
1076  }
1077  }
1078  # else if DELETE statement
1079  elseif (strtoupper($Words[0]) == "DELETE")
1080  {
1081  # skip over modifying keywords
1082  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1083  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1084  || (strtoupper($Words[$WordIndex]) == "QUICK"))
1085  {
1086  $WordIndex++;
1087  }
1088 
1089  # if next term is FROM
1090  if (strtoupper($Words[$WordIndex]) == "FROM")
1091  {
1092  # next word is table name
1093  $WordIndex++;
1094  $TableName = $Words[$WordIndex];
1095  }
1096  }
1097 
1098  # discard table name if it looks at all suspicious
1099  if ($TableName)
1100  {
1101  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1102  {
1103  $TableName = FALSE;
1104  }
1105  }
1106 
1107  # return table name (or lack thereof) to caller
1108  return $TableName;
1109  }
1110 
1117  private function TablesAccessed($QueryString)
1118  {
1119  # assume we're not going to be able to determine tables
1120  $TableNames = FALSE;
1121 
1122  # split query into pieces
1123  $QueryString = trim($QueryString);
1124  $Words = preg_split("/\s+/", $QueryString);
1125  $UQueryString = strtoupper($QueryString);
1126  $UWords = preg_split("/\s+/", $UQueryString);
1127 
1128  # if SELECT statement
1129  if ($UWords[0] == "SELECT")
1130  {
1131  # keep going until we hit FROM or last word
1132  $WordIndex = 1;
1133  while (($UWords[$WordIndex] != "FROM")
1134  && strlen($UWords[$WordIndex]))
1135  {
1136  $WordIndex++;
1137  }
1138 
1139  # if we hit FROM
1140  if ($UWords[$WordIndex] == "FROM")
1141  {
1142  # for each word after FROM
1143  $WordIndex++;
1144  while (strlen($UWords[$WordIndex]))
1145  {
1146  # if current word ends with comma
1147  if (preg_match("/,$/", $Words[$WordIndex]))
1148  {
1149  # strip off comma and add word to table name list
1150  $TableNames[] = substr($Words[$WordIndex], 0, -1);
1151  }
1152  else
1153  {
1154  # add word to table name list
1155  $TableNames[] = $Words[$WordIndex];
1156 
1157  # if next word is not comma
1158  $WordIndex++;
1159  if ($Words[$WordIndex] != ",")
1160  {
1161  # if word begins with comma
1162  if (preg_match("/^,/", $Words[$WordIndex]))
1163  {
1164  # strip off comma (NOTE: modifies $Words array!)
1165  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1166 
1167  # decrement index so we start with this word next pass
1168  $WordIndex--;
1169  }
1170  else
1171  {
1172  # stop scanning words (non-basic JOINs not yet handled)
1173  break;
1174  }
1175  }
1176  }
1177 
1178  # move to next word
1179  $WordIndex++;
1180  }
1181  }
1182  }
1183 
1184  # discard table names if they look at all suspicious
1185  if ($TableNames)
1186  {
1187  foreach ($TableNames as $Name)
1188  {
1189  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1190  {
1191  $TableNames = FALSE;
1192  break;
1193  }
1194  }
1195  }
1196 
1197  # return table name (or lack thereof) to caller
1198  return $TableNames;
1199  }
1200 
1207  private function RunQuery($QueryString)
1208  {
1209  # log query start time if debugging output is enabled
1210  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1211 
1212  # run query against database
1213  $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1214 
1215  # print query and execution time if debugging output is enabled
1216  if (self::$QueryDebugOutputFlag)
1217  {
1218  print "DB: ".$QueryString." ["
1219  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1220  ."s]"."<br>\n";
1221  }
1222 
1223  # if query failed and there are errors that we can ignore
1224  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1225  {
1226  # for each pattern for an error that we can ignore
1227  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1228  {
1229  # if error matches pattern
1230  $ErrorMsg = mysqli_error($this->Handle);
1231  if (preg_match($SqlPattern, $QueryString)
1232  && preg_match($ErrMsgPattern, $ErrorMsg))
1233  {
1234  # set return value to indicate error was ignored
1235  $this->QueryHandle = TRUE;
1236 
1237  # set internal flag to indicate that an error was ignored
1238  $this->ErrorIgnored = $ErrorMsg;
1239 
1240  # stop looking at patterns
1241  break;
1242  }
1243  }
1244  }
1245 
1246  # if query failed
1247  if ($this->QueryHandle === FALSE)
1248  {
1249  # clear stored value for number of rows retrieved
1250  $this->NumRows = 0;
1251 
1252  # retrieve error info
1253  $this->ErrMsg = mysqli_error($this->Handle);
1254  $this->ErrNo = mysqli_errno($this->Handle);
1255 
1256  # if we are supposed to be displaying errors
1257  if (self::$DisplayErrors)
1258  {
1259  # print error info
1260  print("<b>SQL Error:</b> <i>".$this->ErrMsg
1261  ."</i> (".$this->ErrNo.")<br/>\n");
1262  print("<b>SQL Statement:</b> <i>"
1263  .htmlspecialchars($QueryString)."</i><br/>\n");
1264 
1265  # retrieve execution trace that got us to this point
1266  $Trace = debug_backtrace();
1267 
1268  # remove current context from trace
1269  array_shift($Trace);
1270 
1271  # make sure file name and line number are available
1272  foreach ($Trace as $Index => $Loc)
1273  {
1274  if (!array_key_exists("file", $Loc))
1275  {
1276  $Trace[$Index]["file"] = "UNKNOWN";
1277  }
1278  if (!array_key_exists("line", $Loc))
1279  {
1280  $Trace[$Index]["line"] = "??";
1281  }
1282  }
1283 
1284  # determine length of leading path common to all file names in trace
1285  $LocString = "";
1286  $OurFile = __FILE__;
1287  $PrefixLen = 9999;
1288  foreach ($Trace as $Loc)
1289  {
1290  if ($Loc["file"] != "UNKNOWN")
1291  {
1292  $Index = 0;
1293  $FNameLength = strlen($Loc["file"]);
1294  while ($Index < $FNameLength &&
1295  $Loc["file"][$Index] == $OurFile[$Index])
1296  { $Index++; }
1297  $PrefixLen = min($PrefixLen, $Index);
1298  }
1299  }
1300 
1301  foreach ($Trace as $Loc)
1302  {
1303  $Sep = "";
1304  $ArgString = "";
1305  foreach ($Loc["args"] as $Arg)
1306  {
1307  $ArgString .= $Sep;
1308  switch (gettype($Arg))
1309  {
1310  case "boolean":
1311  $ArgString .= $Arg ? "TRUE" : "FALSE";
1312  break;
1313 
1314  case "integer":
1315  case "double":
1316  $ArgString .= $Arg;
1317  break;
1318 
1319  case "string":
1320  $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1321  .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1322  break;
1323 
1324  case "array":
1325  case "resource":
1326  case "NULL":
1327  $ArgString .= strtoupper(gettype($Arg));
1328  break;
1329 
1330  case "object":
1331  $ArgString .= get_class($Arg);
1332  break;
1333 
1334  case "unknown type":
1335  $ArgString .= "UNKNOWN";
1336  break;
1337  }
1338  $Sep = ",";
1339  }
1340  $Loc["file"] = substr($Loc["file"], $PrefixLen);
1341  $LocString .= "&nbsp;&nbsp;";
1342  if (array_key_exists("class", $Loc))
1343  { $LocString .= $Loc["class"]."::"; }
1344  $LocString .= $Loc["function"]."(".$ArgString.")"
1345  ." - ".$Loc["file"].":".$Loc["line"]
1346  ."<br>\n";
1347  }
1348  print("<b>Trace:</b><br>\n".$LocString);
1349  }
1350  }
1351  return $this->QueryHandle;
1352  }
1353 
1358  static private function GetPhpMemoryLimit()
1359  {
1360  $Str = strtoupper(ini_get("memory_limit"));
1361  if (substr($Str, -1) == "B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1362  switch (substr($Str, -1))
1363  {
1364  case "K": $MemoryLimit = (int)$Str * 1024; break;
1365  case "M": $MemoryLimit = (int)$Str * 1048576; break;
1366  case "G": $MemoryLimit = (int)$Str * 1073741824; break;
1367  default: $MemoryLimit = (int)$Str; break;
1368  }
1369  return $MemoryLimit;
1370  }
1371 
1376  static private function GetFreeMemory()
1377  {
1378  return self::GetPhpMemoryLimit() - memory_get_usage();
1379  }
1380 }
1381 
1382 # define return values (numerical values correspond to MySQL error codes)
1383 // @codingStandardsIgnoreStart (to silence warning about multiple spaces)
1384 define("DB_OKAY", 0);
1385 define("DB_ERROR", 1);
1386 define("DB_ACCESSDENIED", 2);
1387 define("DB_UNKNOWNDB", 3);
1388 define("DB_UNKNOWNTABLE", 4);
1389 define("DB_SYNTAXERROR", 5);
1390 define("DB_DBALREADYEXISTS", 6);
1391 define("DB_DBDOESNOTEXIST", 7);
1392 define("DB_DISKFULL", 8);
1393 // @codingStandardsIgnoreEnd
1394 
1395 # define value to designate omitted arguments (so DB values can be set to NULL)
1396 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1397 
1398 # MySQL error code mapping
1400  1045 => DB_ACCESSDENIED,
1401  1049 => DB_UNKNOWNDB,
1402  1046 => DB_UNKNOWNTABLE,
1403  1064 => DB_SYNTAXERROR,
1404  1007 => DB_DBALREADYEXISTS, # ? (not sure)
1405  1008 => DB_DBDOESNOTEXIST, # ? (not sure)
1406  1021 => DB_DISKFULL, # ? (not sure)
1407  );
1408 
1409 # date() format for SQL dates
1410 define("DATE_SQL", "Y-m-d H:i:s");
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:566
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:235
__sleep()
Definition: Database.php:87
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:159
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:130
const DB_ACCESSDENIED
Definition: Database.php:1386
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:143
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:507
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:826
SQL database abstraction object with smart query caching.
Definition: Database.php:22
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
Definition: Database.php:289
DBUserName()
Get name used to connect with database server.
Definition: Database.php:223
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:869
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:640
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:758
const DB_SYNTAXERROR
Definition: Database.php:1389
__construct($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
Definition: Database.php:41
TableExists($TableName)
Get whether specified table exists.
Definition: Database.php:890
const DB_DISKFULL
Definition: Database.php:1392
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Definition: Database.php:114
$APDBErrorCodeMappings
Definition: Database.php:1399
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
Definition: Database.php:183
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:918
const DB_NOVALUE
Definition: Database.php:1396
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:597
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:686
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:930
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:327
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:746
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:902
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:951
const DB_UNKNOWNTABLE
Definition: Database.php:1388
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:721
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:203
const DB_DBDOESNOTEXIST
Definition: Database.php:1391
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:623
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:850
const DB_DBALREADYEXISTS
Definition: Database.php:1390
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:576
const DB_UNKNOWNDB
Definition: Database.php:1387
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:193
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:779
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:261
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:587
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:961
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:309
DBName()
Get current database name.
Definition: Database.php:213
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:880
__wakeup()
Restore database connection when unserialized.
Definition: Database.php:94
static NumQueries()
Get the number of queries that have been run since program execution began.
Definition: Database.php:940