5 # A Simple SQL Database Abstraction Object 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). 12 # Author: Edward Almasy (almasy@axisdata.com) 14 # Part of the AxisPHP library v1.2.5 15 # For more information see http://www.axisdata.com/AxisPHP/ 25 # ---- PUBLIC INTERFACE -------------------------------------------------- 44 $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
46 # save DB access parameter values 47 $this->
DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
48 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
50 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
52 $this->
DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
54 # set memory threshold for cache clearing 55 if (!isset(self::$CacheMemoryThreshold))
57 self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
60 # if we don't already have a connection or DB access parameters were supplied 62 if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
63 || $UserName || $Password || $DatabaseName || $HostName)
65 # open connection to DB server and select database 66 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
68 self::$ConnectionHandles[$HandleIndex] = $this->Handle;
72 # set local connection handle 73 $this->Handle = self::$ConnectionHandles[$HandleIndex];
83 return array(
"DBUserName",
"DBPassword",
"DBHostName",
"DBName");
92 # if we don't already have a database server connection 94 if (!array_key_exists($HandleIndex, self::$ConnectionHandles))
96 # open connection to DB server and select database 99 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
102 catch (Exception $Exception)
104 if (isset(self::$GlobalDBUserName)
105 && isset(self::$GlobalDBPassword)
106 && isset(self::$GlobalDBName))
109 $this->DBPassword = self::$GlobalDBPassword;
110 $this->
DBName = self::$GlobalDBName;
111 $this->
DBHostName = isset(self::$GlobalDBHostName)
112 ? self::$GlobalDBHostName :
"localhost";
113 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
121 self::$ConnectionHandles[$HandleIndex] = $this->Handle;
125 # set local connection handle 126 $this->Handle = self::$ConnectionHandles[$HandleIndex];
139 $UserName, $Password, $HostName =
"localhost")
141 # save default DB access parameters 142 self::$GlobalDBUserName = $UserName;
143 self::$GlobalDBPassword = $Password;
144 self::$GlobalDBHostName = $HostName;
146 # clear any existing DB connection handles 147 self::$ConnectionHandles = array();
156 # save new default DB name 157 self::$GlobalDBName = $DatabaseName;
159 # clear any existing DB connection handles 160 self::$ConnectionHandles = array();
169 # choose config variable to use based on server version number 171 ?
"storage_engine" :
"default_storage_engine";
173 # set storage engine in database 174 $this->
Query(
"SET ".$ConfigVar.
" = ".$Engine);
185 # retrieve version string 186 $Version = $this->
Query(
"SELECT VERSION() AS ServerVer",
"ServerVer");
190 # strip off any build/config suffix 191 $Pieces = explode(
"-", $Version);
192 $Version = array_shift($Pieces);
195 # return version number to caller 209 return mysqli_get_client_info();
219 return mysqli_get_host_info($this->Handle);
259 public static function Caching($NewSetting = NULL)
261 # if cache setting has changed 262 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
265 self::$CachingFlag = $NewSetting;
267 # clear any existing cached results 268 self::$QueryResultCache = array();
271 # return current setting to caller 272 return self::$CachingFlag;
287 if ($NewSetting !== NULL)
289 self::$AdvancedCachingFlag = $NewSetting;
291 return self::$AdvancedCachingFlag;
315 if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
317 $RevisedErrorsToIgnore = array();
318 foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
320 $SqlPattern = preg_replace(
"/\\s+/",
"\\s+", $SqlPattern);
321 $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
323 $ErrorsToIgnore = $RevisedErrorsToIgnore;
325 $this->ErrorsToIgnore = $ErrorsToIgnore;
335 return $this->ErrorIgnored;
351 public function Query($QueryString, $FieldName =
"")
353 # clear flag that indicates whether query error was ignored 354 $this->ErrorIgnored = FALSE;
356 # if caching is enabled 357 if (self::$CachingFlag)
359 # if SQL statement is read-only 360 if ($this->IsReadOnlyStatement($QueryString))
362 # if we have statement in cache 363 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
365 if (self::$QueryDebugOutputFlag)
366 { print(
"DB-C: $QueryString<br>\n"); }
368 # make sure query result looks okay 369 $this->QueryHandle = TRUE;
371 # increment cache hit counter 372 self::$CachedQueryCounter++;
374 # make local copy of results 375 $this->QueryResults = self::$QueryResultCache[$QueryString];
376 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
378 # set flag to indicate that results should be retrieved from cache 379 $this->GetResultsFromCache = TRUE;
383 # execute SQL statement 384 $this->QueryHandle = $this->RunQuery($QueryString);
385 if (!$this->QueryHandle instanceof mysqli_result) {
return FALSE; }
387 # save number of rows in result 388 $this->NumRows = mysqli_num_rows($this->QueryHandle);
390 # if too many rows to cache 391 if ($this->NumRows >= self::$CacheRowsThreshold)
393 # set flag to indicate that query results should not 394 # be retrieved from cache 395 $this->GetResultsFromCache = FALSE;
399 # if we are low on memory 400 if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
402 # clear out all but last few rows from cache 403 self::$QueryResultCache = array_slice(
404 self::$QueryResultCache,
405 (0 - self::$CacheRowsToLeave));
408 # if advanced caching is enabled 409 if (self::$AdvancedCachingFlag)
411 # save tables accessed by query 412 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
413 $this->TablesAccessed($QueryString);
417 if ($this->NumRows > 0)
420 for ($Row = 0; $Row < $this->NumRows; $Row++)
422 $this->QueryResults[$Row] =
423 mysqli_fetch_assoc($this->QueryHandle);
426 # cache query results 427 self::$QueryResultCache[$QueryString] = $this->QueryResults;
431 # clear local query results 432 unset($this->QueryResults);
435 # cache number of rows 436 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
438 # set flag to indicate that query results should be 439 # retrieved from cache 440 $this->GetResultsFromCache = TRUE;
446 # if advanced caching is enabled 447 if (self::$AdvancedCachingFlag)
449 # if table modified by statement is known 450 $TableModified = $this->TableModified($QueryString);
453 # for each cached query 454 foreach (self::$QueryResultCache
455 as $CachedQueryString => $CachedQueryResult)
457 # if we know what tables were accessed 458 if ($CachedQueryResult[
"TablesAccessed"])
460 # if tables accessed include the one we may modify 461 if (in_array($TableModified,
462 $CachedQueryResult[
"TablesAccessed"]))
464 # clear cached query results 465 unset(self::$QueryResultCache[$CachedQueryString]);
470 # clear cached query results 471 unset(self::$QueryResultCache[$CachedQueryString]);
477 # clear entire query result cache 478 self::$QueryResultCache = array();
483 # clear entire query result cache 484 self::$QueryResultCache = array();
487 # execute SQL statement 488 $this->QueryHandle = $this->RunQuery($QueryString);
489 if ($this->QueryHandle === FALSE) {
return FALSE; }
491 # set flag to indicate that query results should not be 492 # retrieved from cache 493 $this->GetResultsFromCache = FALSE;
497 $this->RowCounter = 0;
499 # increment query counter 500 self::$QueryCounter++;
504 # execute SQL statement 505 $this->QueryHandle = $this->RunQuery($QueryString);
506 if ($this->QueryHandle === FALSE) {
return FALSE; }
509 if (($FieldName !=
"") && ($this->QueryHandle !== FALSE))
515 return $this->QueryHandle;
534 $FHandle = fopen($FileName,
"r");
536 # if file open succeeded 537 if ($FHandle !== FALSE)
539 # while lines left in file 542 while (!feof($FHandle))
544 # read in line from file 545 $Line = fgets($FHandle, 32767);
547 # trim whitespace from line 550 # if line is not empty and not a comment 551 if (!preg_match(
"/^#/", $Line)
552 && !preg_match(
"/^--/", $Line)
555 # add line to current query 558 # if line completes a query 559 if (preg_match(
"/;$/", $Line))
563 $Result = $this->
Query($Query);
566 # if query resulted in an error that is not ignorable 567 if ($Result === FALSE)
569 # stop processing queries and set error code 581 # return number of executed queries to caller 592 return $this->ErrMsg;
613 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
614 return self::$DisplayErrors;
623 # if caching is enabled and query was cached 624 if (self::$CachingFlag && $this->GetResultsFromCache)
626 # return cached number of rows to caller 627 return $this->NumRows;
631 # call to this method after an unsuccessful query 632 if (!$this->QueryHandle instanceof mysqli_result)
637 # retrieve number of rows and return to caller 638 return mysqli_num_rows($this->QueryHandle);
649 # call to this method after an unsuccessful query 650 if ($this->QueryHandle === FALSE)
655 # retrieve number of rows and return to caller 656 return mysqli_affected_rows($this->Handle);
666 # if caching is enabled and query was cached 667 if (self::$CachingFlag && $this->GetResultsFromCache)
669 # if rows left to return 670 if ($this->RowCounter < $this->NumRows)
672 # retrieve row from cache 673 $Result = $this->QueryResults[$this->RowCounter];
675 # increment row counter 686 # call to this method after successful query 687 if ($this->QueryHandle instanceof mysqli_result)
689 $Result = mysqli_fetch_assoc($this->QueryHandle);
690 if ($Result === NULL) { $Result = FALSE; }
693 # call to this method after unsuccessful query 700 # return row to caller 712 # assume no rows will be returned 715 # for each available row 717 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
725 # return array of rows to caller 750 if ($IndexFieldName != NULL)
752 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
756 $Array[] = $Record[$FieldName];
773 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
784 return (
int)$this->
Query(
785 "SELECT LAST_INSERT_ID() AS InsertId",
804 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
806 # expand condition if supplied 807 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
809 # if cached values not already loaded 810 if (!isset($CachedRecord))
812 # read cached record from database 813 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
816 # error out if requested column does not exist in specified table 820 if (($RowsFound && !array_key_exists($FieldName, $CachedRecord))
821 || (!$RowsFound && !$this->
FieldExists($TableName, $FieldName)))
823 throw new Exception(
"Column '".$FieldName
824 .
"' not found in table '".$TableName.
"'.");
829 # if new value supplied 832 # error out if we are trying to update a nonexistent record or field 833 if (!count($CachedRecord))
835 throw new Exception(
"No records found when attempting to update" 836 .
" column '".$FieldName.
"' in table '".$TableName.
"'" 837 .(($Condition != NULL)
838 ?
" using condition '".$Condition.
"'" :
"")
843 if (!array_key_exists($FieldName, $CachedRecord))
845 throw new Exception(
"Attempting to update column '".$FieldName
846 .
"', which does not exist in table '".$TableName.
"'.");
850 # update value in database 851 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = " 852 .(($NewValue === NULL) ?
"NULL" :
"'" 853 .mysqli_real_escape_string($this->Handle, $NewValue).
"'")
856 # update value in cached record 857 $CachedRecord[$FieldName] = $NewValue;
860 # return value from cached record to caller 861 return isset($CachedRecord[$FieldName])
862 ? $CachedRecord[$FieldName] : NULL;
882 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
886 $Condition, $CachedRecord);
906 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
910 $Condition, $CachedRecord);
924 public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
925 $ColumnsToExclude = array())
927 # retrieve names of all columns in table 930 # remove columns to be excluded from copy 931 $ColumnsToExclude[] = $IdColumn;
932 $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
934 # normalize destination IDs 935 $DstIds = is_array($DstId) ? $DstId : array($DstId);
936 $DstIds = array_diff($DstIds, array($SrcId));
938 # if there are columns to copy and we have destinations 939 if (count($ColumnsToCopy) && count($DstIds))
941 # construct and execute query to perform copy 942 $Query =
"UPDATE `".$TableName.
"` AS Target" 943 .
" LEFT JOIN `".$TableName.
"` AS Source" 944 .
" ON Source.`".$IdColumn.
"` = '".addslashes($SrcId).
"'";
945 $QuerySets = array();
946 foreach ($ColumnsToCopy as $ColumnName)
948 $QuerySets[] =
"Target.`".$ColumnName.
"` = Source.`".$ColumnName.
"`";
950 $Query .=
" SET ".implode(
", ", $QuerySets);
951 $QueryConditions = array();
952 foreach ($DstIds as $Id)
954 $QueryConditions[] =
"Target.`".$IdColumn.
"` = '".addslashes($DstId).
"'";
956 $Query .=
" WHERE ".implode(
" OR ", $QueryConditions);
957 $this->
Query($Query);
975 $KeyField = NULL, $AvgDataLength = 20)
977 # pick some ballpark values 978 $ChunkSizeAssumedSafe = 100;
979 $QueryLengthAssumedSafe = 10486576; # (1 MB)
981 # exit without doing anything
if there are no values
982 $ValueCount = count($Values);
983 if ($ValueCount == 0)
988 # determine size of array chunk per INSERT statement 989 $NonValueCharCount = 100;
990 if ($ValueCount > $ChunkSizeAssumedSafe)
993 $ValueSegmentLen = $AvgDataLength + 6;
994 if ($KeyField !== NULL)
996 $ValueSegmentLen = $ValueSegmentLen * 2;
998 $ValueChunkSize = $MaxQueryLen / $ValueSegmentLen;
1002 $ValueChunkSize = $ChunkSizeAssumedSafe;
1005 # for each chunk of values 1006 foreach (array_chunk($Values, $ValueChunkSize, TRUE) as $ValueChunk)
1008 # begin building query 1009 $Query =
"INSERT INTO `".$Table.
"` (`".$ValueField.
"`";
1011 # if key field was specified 1012 if ($KeyField !== NULL)
1014 # add key field to query 1015 $Query .=
", `".$KeyField.
"`";
1017 # assemble value segment with keys 1018 $ValueSegFunc =
function($Carry, $Key) use ($ValueChunk)
1020 $Carry .=
"('".addslashes($ValueChunk[$Key]).
"','" 1021 .addslashes($Key).
"'),";
1024 $ValueSegment = array_reduce(array_keys($ValueChunk), $ValueSegFunc);
1028 # assemble value segment 1029 $ValueSegFunc =
function($Carry, $Value)
1031 $Carry .=
"('".addslashes($Value).
"'),";
1034 $ValueSegment = array_reduce($ValueChunk, $ValueSegFunc);
1037 # trim extraneous comma off of value segment 1038 $ValueSegment = substr($ValueSegment, 0, -1);
1040 # add value segment to query 1041 $Query .=
") VALUES ".$ValueSegment;
1043 # double check to make sure query isn't too long 1044 $QueryLen = strlen($Query);
1045 if ($QueryLen > $QueryLengthAssumedSafe)
1047 if (!isset($MaxQueryLen))
1051 if ($QueryLen > $MaxQueryLen)
1053 throw new Exception(
"Maximum query length (" 1054 .$MaxQueryLen.
") exceeded (".$QueryLen.
").");
1059 $this->
Query($Query);
1076 return mysqli_real_escape_string($this->Handle, $String);
1087 $this->
Query(
"-- ".$String);
1097 $this->
Query(
"SHOW TABLES LIKE '".addslashes($TableName).
"'");
1109 $this->
Query(
"DESC ".$TableName);
1110 while ($CurrentFieldName = $this->
FetchField(
"Field"))
1112 if ($CurrentFieldName == $FieldName) {
return TRUE; }
1125 $this->
Query(
"DESC ".$TableName);
1127 return $AllTypes[$FieldName];
1137 $this->
Query(
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" 1138 .
" WHERE TABLE_SCHEMA = '".addslashes($this->
DBName)
1139 .
"' AND TABLE_NAME = '".addslashes($TableName).
"'");
1149 return $this->
Query(
"SHOW VARIABLES LIKE 'max_allowed_packet'",
1160 self::$QueryDebugOutputFlag = $NewSetting;
1170 return self::$QueryCounter;
1181 return self::$CachedQueryCounter;
1191 if (self::$QueryCounter)
1193 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1203 # ---- PRIVATE INTERFACE ------------------------------------------------- 1211 private $QueryHandle;
1212 private $QueryResults;
1213 private $RowCounter;
1215 private $GetResultsFromCache;
1216 private $ErrorIgnored = FALSE;
1217 private $ErrorsToIgnore = NULL;
1218 private $ErrMsg = NULL;
1219 private $ErrNo = NULL;
1221 private static $DisplayErrors = FALSE;
1223 private static $GlobalDBUserName;
1224 private static $GlobalDBPassword;
1225 private static $GlobalDBHostName;
1226 private static $GlobalDBName;
1229 private static $QueryDebugOutputFlag = FALSE;
1230 # flag for whether caching is turned on 1231 private static $CachingFlag = TRUE;
1232 # query result advanced caching flag 1233 private static $AdvancedCachingFlag = FALSE;
1234 # global cache for query results 1235 private static $QueryResultCache = array();
1237 private static $QueryCounter = 0;
1238 private static $CachedQueryCounter = 0;
1239 # database connection link handles 1240 private static $ConnectionHandles = array();
1241 # do not cache queries that return more than this number of rows 1242 private static $CacheRowsThreshold = 250;
1243 # prune the query cache if there is less than this amount of memory free 1244 private static $CacheMemoryThreshold;
1245 # number of rows to leave in cache when pruning 1246 private static $CacheRowsToLeave = 10;
1247 # number of retry attempts to make to connect to database 1248 private static $ConnectRetryAttempts = 3;
1249 # number of seconds to wait between connection retry attempts 1250 private static $ConnectRetryInterval = 5;
1252 # server connection error codes 1254 # through socket '%s
' (%d) 1255 const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on
'%s' (%d)
1259 # connection error codes that may be recoverable 1260 private static $RecoverableConnectionErrors = array(
1261 self::CR_CONNECTION_ERROR,
1274 private static function ConnectAndSelectDB(
1277 $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1280 # if this is not our first connection attempt 1283 # wait for the retry interval 1284 sleep(self::$ConnectRetryInterval);
1287 # attempt to connect to server 1289 $ConnectAttemptsLeft--;
1291 # repeat if we do not have a connection and there are retry attempts 1292 # left and the connection error code indicates a retry may succeed 1295 while (!$Handle && $ConnectAttemptsLeft
1296 && in_array(mysqli_connect_errno(),
1297 self::$RecoverableConnectionErrors));
1300 # throw exception if connection attempts failed 1303 throw new Exception(
"Could not connect to database: " 1304 .mysqli_connect_error().
" (errno: ".mysqli_connect_errno().
")");
1308 $Result = mysqli_select_db($Handle,
$DBName);
1309 if ($Result !== TRUE)
1311 throw new Exception(
"Could not select database: " 1312 .mysqli_error($Handle).
" (errno: ".mysqli_errno($Handle).
")");
1315 # return new connection to caller 1324 private function IsReadOnlyStatement($QueryString)
1326 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1335 private function TableModified($QueryString)
1337 # assume we're not going to be able to determine table 1340 # split query into pieces 1341 $QueryString = trim($QueryString);
1342 $Words = preg_split(
"/\s+/", $QueryString);
1344 # if INSERT statement 1346 if (strtoupper($Words[0]) ==
"INSERT")
1348 # skip over modifying keywords 1349 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1350 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
1351 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1352 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
1357 # next word is table name 1358 $TableName = $Words[$WordIndex];
1360 # else if UPDATE statement 1361 elseif (strtoupper($Words[0]) ==
"UPDATE")
1363 # skip over modifying keywords 1364 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1365 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
1370 # if word following next word is SET 1371 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
1373 # next word is table name 1374 $TableName = $Words[$WordIndex];
1377 # else if DELETE statement 1378 elseif (strtoupper($Words[0]) ==
"DELETE")
1380 # skip over modifying keywords 1381 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1382 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1383 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
1388 # if next term is FROM 1389 if (strtoupper($Words[$WordIndex]) ==
"FROM")
1391 # next word is table name 1393 $TableName = $Words[$WordIndex];
1397 # discard table name if it looks at all suspicious 1400 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
1406 # return table name (or lack thereof) to caller 1416 private function TablesAccessed($QueryString)
1418 # assume we're not going to be able to determine tables 1419 $TableNames = FALSE;
1421 # split query into pieces 1422 $QueryString = trim($QueryString);
1423 $Words = preg_split(
"/\s+/", $QueryString);
1424 $UQueryString = strtoupper($QueryString);
1425 $UWords = preg_split(
"/\s+/", $UQueryString);
1427 # if SELECT statement 1428 if ($UWords[0] ==
"SELECT")
1430 # keep going until we hit FROM or last word 1432 while (($UWords[$WordIndex] !=
"FROM")
1433 && strlen($UWords[$WordIndex]))
1439 if ($UWords[$WordIndex] ==
"FROM")
1441 # for each word after FROM 1443 while (strlen($UWords[$WordIndex]))
1445 # if current word ends with comma 1446 if (preg_match(
"/,$/", $Words[$WordIndex]))
1448 # strip off comma and add word to table name list 1449 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1453 # add word to table name list 1454 $TableNames[] = $Words[$WordIndex];
1456 # if next word is not comma 1458 if ($Words[$WordIndex] !=
",")
1460 # if word begins with comma 1461 if (preg_match(
"/^,/", $Words[$WordIndex]))
1463 # strip off comma (NOTE: modifies $Words array!) 1464 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1466 # decrement index so we start with this word next pass 1471 # stop scanning words (non-basic JOINs not yet handled) 1483 # discard table names if they look at all suspicious 1486 foreach ($TableNames as $Name)
1488 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1490 $TableNames = FALSE;
1496 # return table name (or lack thereof) to caller 1506 private function RunQuery($QueryString)
1508 # log query start time if debugging output is enabled 1509 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1511 # run query against database 1512 $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1514 # print query and execution time if debugging output is enabled 1515 if (self::$QueryDebugOutputFlag)
1517 print
"DB: ".$QueryString.
" [" 1518 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1522 # if query failed and there are errors that we can ignore 1523 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1525 # for each pattern for an error that we can ignore 1526 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1528 # if error matches pattern 1529 $ErrorMsg = mysqli_error($this->Handle);
1530 if (preg_match($SqlPattern, $QueryString)
1531 && preg_match($ErrMsgPattern, $ErrorMsg))
1533 # set return value to indicate error was ignored 1534 $this->QueryHandle = TRUE;
1536 # set internal flag to indicate that an error was ignored 1537 $this->ErrorIgnored = $ErrorMsg;
1539 # stop looking at patterns 1546 if ($this->QueryHandle === FALSE)
1548 # clear stored value for number of rows retrieved 1551 # retrieve error info 1552 $this->ErrMsg = mysqli_error($this->Handle);
1553 $this->ErrNo = mysqli_errno($this->Handle);
1555 # if we are supposed to be displaying errors 1556 if (self::$DisplayErrors)
1559 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1560 .
"</i> (".$this->ErrNo.
")<br/>\n");
1561 print(
"<b>SQL Statement:</b> <i>" 1562 .htmlspecialchars($QueryString).
"</i><br/>\n");
1564 # retrieve execution trace that got us to this point 1565 $Trace = debug_backtrace();
1567 # remove current context from trace 1568 array_shift($Trace);
1570 # make sure file name and line number are available 1571 foreach ($Trace as $Index => $Loc)
1573 if (!array_key_exists(
"file", $Loc))
1575 $Trace[$Index][
"file"] =
"UNKNOWN";
1577 if (!array_key_exists(
"line", $Loc))
1579 $Trace[$Index][
"line"] =
"??";
1583 # determine length of leading path common to all file names in trace 1585 $OurFile = __FILE__;
1587 foreach ($Trace as $Loc)
1589 if ($Loc[
"file"] !=
"UNKNOWN")
1592 $FNameLength = strlen($Loc[
"file"]);
1593 while ($Index < $FNameLength &&
1594 $Loc[
"file"][$Index] == $OurFile[$Index])
1596 $PrefixLen = min($PrefixLen, $Index);
1600 foreach ($Trace as $Loc)
1604 foreach ($Loc[
"args"] as $Arg)
1607 switch (gettype($Arg))
1610 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1619 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1620 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1626 $ArgString .= strtoupper(gettype($Arg));
1630 $ArgString .= get_class($Arg);
1633 case "unknown type":
1634 $ArgString .=
"UNKNOWN";
1639 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1640 $LocString .=
" ";
1641 if (array_key_exists(
"class", $Loc))
1642 { $LocString .= $Loc[
"class"].
"::"; }
1643 $LocString .= $Loc[
"function"].
"(".$ArgString.
")" 1644 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1647 print(
"<b>Trace:</b><br>\n".$LocString);
1650 return $this->QueryHandle;
1657 static private function GetPhpMemoryLimit()
1659 $Str = strtoupper(ini_get(
"memory_limit"));
1660 if (substr($Str, -1) ==
"B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1661 switch (substr($Str, -1))
1664 $MemoryLimit = (int)$Str * 1024;
1668 $MemoryLimit = (int)$Str * 1048576;
1672 $MemoryLimit = (int)$Str * 1073741824;
1676 $MemoryLimit = (int)$Str;
1679 return $MemoryLimit;
1686 static private function GetFreeMemory()
1688 return self::GetPhpMemoryLimit() - memory_get_usage();
1692 # define return values (numerical values correspond to MySQL error codes) 1694 define(
"DB_OKAY", 0);
1695 define(
"DB_ERROR", 1);
1696 define(
"DB_ACCESSDENIED", 2);
1697 define(
"DB_UNKNOWNDB", 3);
1698 define(
"DB_UNKNOWNTABLE", 4);
1699 define(
"DB_SYNTAXERROR", 5);
1700 define(
"DB_DBALREADYEXISTS", 6);
1701 define(
"DB_DBDOESNOTEXIST", 7);
1702 define(
"DB_DISKFULL", 8);
1705 # define value to designate omitted arguments (so DB values can be set to NULL) 1706 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1708 # MySQL error code mapping 1719 # date() format for SQL dates 1720 define(
"DATE_SQL",
"Y-m-d H:i:s");
QueryErrMsg()
Get most recent error message text set by Query().
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
GetServerVersion($FullVersion=FALSE)
Get database server version number.
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
const CR_CONNECTION_ERROR
SetDefaultStorageEngine($Engine)
Set default database storage engine.
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
SQL database abstraction object with smart query caching.
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
const CR_SERVER_GONE_ERROR
DBUserName()
Get name used to connect with database server.
EscapeString($String)
Escape a string that may contain null bytes.
FetchRow()
Get next database row retrieved by most recent query.
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
__construct($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
TableExists($TableName)
Get whether specified table exists.
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Set default login and host info for database server.
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
GetFieldType($TableName, $FieldName)
Get field (column) type.
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
GetColumns($TableName)
Get column (database field) names.
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
DBHostName()
Get host name of system on which database server resides.
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
InsertArray($Table, $ValueField, $Values, $KeyField=NULL, $AvgDataLength=20)
Insert an array of values with a minimum number of INSERT statements.
QueryErrNo()
Get most recent error code set by Query().
GetHostInfo()
Get database connection type and hostname.
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
GetMaxQueryLength()
Get maximum size for query string.
IgnoredError()
Check whether an error was ignored by the most recent query.
DBName()
Get current database name.
CopyValues($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
A convenience function to copy values from one row to another.
LogComment($String)
Peform query that consists of SQL comment statement.
__wakeup()
Restore database connection when unserialized.
static NumQueries()
Get the number of queries that have been run since program execution began.