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 -------------------------------------------------- 49 # save DB access parameter values 50 $this->
DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
51 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
53 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
55 $this->
DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
57 # set memory threshold for cache clearing 58 if (!isset(self::$CacheMemoryThreshold))
60 self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
63 # if we don't already have a connection or DB access parameters were supplied 65 if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
66 || $UserName || $Password || $DatabaseName || $HostName)
68 # open connection to DB server and select database 69 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
71 self::$ConnectionHandles[$HandleIndex] = $this->Handle;
75 # set local connection handle 76 $this->Handle = self::$ConnectionHandles[$HandleIndex];
86 return array(
"DBUserName",
"DBPassword",
"DBHostName",
"DBName");
95 # if we don't already have a database server connection 97 if (!array_key_exists($HandleIndex, self::$ConnectionHandles))
99 # open connection to DB server and select database 102 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
105 catch (Exception $Exception)
107 if (isset(self::$GlobalDBUserName)
108 && isset(self::$GlobalDBPassword)
109 && isset(self::$GlobalDBName))
112 $this->DBPassword = self::$GlobalDBPassword;
113 $this->
DBName = self::$GlobalDBName;
114 $this->
DBHostName = isset(self::$GlobalDBHostName)
115 ? self::$GlobalDBHostName :
"localhost";
116 $this->Handle = self::ConnectAndSelectDB($this->
DBHostName,
124 self::$ConnectionHandles[$HandleIndex] = $this->Handle;
128 # set local connection handle 129 $this->Handle = self::$ConnectionHandles[$HandleIndex];
142 $UserName, $Password, $HostName =
"localhost")
144 # save default DB access parameters 145 self::$GlobalDBUserName = $UserName;
146 self::$GlobalDBPassword = $Password;
147 self::$GlobalDBHostName = $HostName;
149 # clear any existing DB connection handles 150 self::$ConnectionHandles = array();
159 # save new default DB name 160 self::$GlobalDBName = $DatabaseName;
162 # clear any existing DB connection handles 163 self::$ConnectionHandles = array();
172 # choose config variable to use based on server version number 174 ?
"storage_engine" :
"default_storage_engine";
176 # set storage engine in database 177 $this->
Query(
"SET ".$ConfigVar.
" = ".$Engine);
188 # retrieve version string 189 $Version = $this->
Query(
"SELECT VERSION() AS ServerVer",
"ServerVer");
193 # strip off any build/config suffix 194 $Pieces = explode(
"-", $Version);
195 $Version = array_shift($Pieces);
198 # return version number to caller 212 return mysqli_get_client_info();
222 return mysqli_get_host_info($this->Handle);
262 public static function Caching($NewSetting = NULL)
264 # if cache setting has changed 265 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
268 self::$CachingFlag = $NewSetting;
270 # clear any existing cached results 271 self::$QueryResultCache = array();
274 # return current setting to caller 275 return self::$CachingFlag;
290 if ($NewSetting !== NULL)
292 self::$AdvancedCachingFlag = $NewSetting;
294 return self::$AdvancedCachingFlag;
317 $ErrorsToIgnore, $NormalizeWhitespace = TRUE)
319 if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
321 $RevisedErrorsToIgnore = array();
322 foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
324 $SqlPattern = preg_replace(
"/\\s+/",
"\\s+", $SqlPattern);
325 $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
327 $ErrorsToIgnore = $RevisedErrorsToIgnore;
329 $this->ErrorsToIgnore = $ErrorsToIgnore;
339 return $this->ErrorIgnored;
355 public function Query($QueryString, $FieldName =
"")
357 # clear flag that indicates whether query error was ignored 358 $this->ErrorIgnored = FALSE;
360 # if caching is enabled 361 if (self::$CachingFlag)
363 # if SQL statement is read-only 364 if ($this->IsReadOnlyStatement($QueryString))
366 # if we have statement in cache 367 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
369 if (self::$QueryDebugOutputFlag)
370 { print(
"DB-C: $QueryString<br>\n"); }
372 # make sure query result looks okay 373 $this->QueryHandle = TRUE;
375 # increment cache hit counter 376 self::$CachedQueryCounter++;
378 # make local copy of results 379 $this->QueryResults = self::$QueryResultCache[$QueryString];
380 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
382 # set flag to indicate that results should be retrieved from cache 383 $this->GetResultsFromCache = TRUE;
387 # execute SQL statement 388 $this->QueryHandle = $this->RunQuery($QueryString);
389 if (!$this->QueryHandle instanceof mysqli_result) {
return FALSE; }
391 # save number of rows in result 392 $this->NumRows = mysqli_num_rows($this->QueryHandle);
394 # if too many rows to cache 395 if ($this->NumRows >= self::$CacheRowsThreshold)
397 # set flag to indicate that query results should not 398 # be retrieved from cache 399 $this->GetResultsFromCache = FALSE;
403 # if we are low on memory 404 if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
406 # clear out all but last few rows from cache 407 self::$QueryResultCache = array_slice(
408 self::$QueryResultCache,
409 (0 - self::$CacheRowsToLeave));
412 # if advanced caching is enabled 413 if (self::$AdvancedCachingFlag)
415 # save tables accessed by query 416 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
417 $this->TablesAccessed($QueryString);
421 if ($this->NumRows > 0)
424 for ($Row = 0; $Row < $this->NumRows; $Row++)
426 $this->QueryResults[$Row] =
427 mysqli_fetch_assoc($this->QueryHandle);
430 # cache query results 431 self::$QueryResultCache[$QueryString] = $this->QueryResults;
435 # clear local query results 436 unset($this->QueryResults);
439 # cache number of rows 440 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
442 # set flag to indicate that query results should be 443 # retrieved from cache 444 $this->GetResultsFromCache = TRUE;
450 # if advanced caching is enabled 451 if (self::$AdvancedCachingFlag)
453 # if table modified by statement is known 454 $TableModified = $this->TableModified($QueryString);
457 # for each cached query 458 foreach (self::$QueryResultCache
459 as $CachedQueryString => $CachedQueryResult)
461 # if we know what tables were accessed 462 if ($CachedQueryResult[
"TablesAccessed"])
464 # if tables accessed include the one we may modify 465 if (in_array($TableModified,
466 $CachedQueryResult[
"TablesAccessed"]))
468 # clear cached query results 469 unset(self::$QueryResultCache[$CachedQueryString]);
474 # clear cached query results 475 unset(self::$QueryResultCache[$CachedQueryString]);
481 # clear entire query result cache 482 self::$QueryResultCache = array();
487 # clear entire query result cache 488 self::$QueryResultCache = array();
491 # execute SQL statement 492 $this->QueryHandle = $this->RunQuery($QueryString);
493 if ($this->QueryHandle === FALSE) {
return FALSE; }
495 # set flag to indicate that query results should not be 496 # retrieved from cache 497 $this->GetResultsFromCache = FALSE;
501 $this->RowCounter = 0;
503 # increment query counter 504 self::$QueryCounter++;
508 # execute SQL statement 509 $this->QueryHandle = $this->RunQuery($QueryString);
510 if ($this->QueryHandle === FALSE) {
return FALSE; }
513 if (($FieldName !=
"") && ($this->QueryHandle !== FALSE))
519 return $this->QueryHandle;
540 $FHandle = fopen($FileName,
"r");
542 # if file open succeeded 543 if ($FHandle !== FALSE)
545 # while lines left in file 547 while (!feof($FHandle))
549 # read in line from file 550 $Line = fgets($FHandle, 32767);
552 # trim whitespace from line 555 # if line is not empty and not a comment 556 if (!preg_match(
"/^#/", $Line)
557 && !preg_match(
"/^--/", $Line)
560 # add line to current query 563 # if line completes a query 564 if (preg_match(
"/;$/", $Line))
568 $Result = $this->
Query($Query);
571 # if query resulted in an error that is not ignorable 572 if ($Result === FALSE)
574 # stop processing queries and set error code 586 # return number of executed queries to caller 597 return $this->ErrMsg;
618 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
619 return self::$DisplayErrors;
628 # if caching is enabled and query was cached 629 if (self::$CachingFlag && $this->GetResultsFromCache)
631 # return cached number of rows to caller 632 return $this->NumRows;
636 # call to this method after an unsuccessful query 637 if (!$this->QueryHandle instanceof mysqli_result)
642 # retrieve number of rows and return to caller 643 return mysqli_num_rows($this->QueryHandle);
654 # call to this method after an unsuccessful query 655 if ($this->QueryHandle === FALSE)
660 # retrieve number of rows and return to caller 661 return mysqli_affected_rows($this->Handle);
671 # if caching is enabled and query was cached 672 if (self::$CachingFlag && $this->GetResultsFromCache)
674 # if rows left to return 675 if ($this->RowCounter < $this->NumRows)
677 # retrieve row from cache 678 $Result = $this->QueryResults[$this->RowCounter];
680 # increment row counter 691 # call to this method after successful query 692 if ($this->QueryHandle instanceof mysqli_result)
694 $Result = mysqli_fetch_assoc($this->QueryHandle);
695 if ($Result === NULL) { $Result = FALSE; }
698 # call to this method after unsuccessful query 705 # return row to caller 718 # assume no rows will be returned 721 # for each available row 723 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
731 # return array of rows to caller 756 if ($IndexFieldName != NULL)
758 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
762 $Array[] = $Record[$FieldName];
779 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
790 return (
int)$this->
Query(
791 "SELECT LAST_INSERT_ID() AS InsertId",
810 "Table ".$TableName.
" does not exist");
813 return (
int)$this->
Query(
814 "SELECT `AUTO_INCREMENT` AS Id FROM INFORMATION_SCHEMA.TABLES " 815 .
"WHERE TABLE_SCHEMA='".addslashes($this->
DBName()).
"' " 816 .
"AND TABLE_NAME = '".addslashes($TableName).
"'",
"Id");
840 # expand condition if supplied 841 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
843 # if cached values not already loaded 844 if (!isset($CachedRecord))
846 # read cached record from database 847 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
850 # error out if requested column does not exist in specified table 854 if (($RowsFound && !array_key_exists($FieldName, $CachedRecord))
855 || (!$RowsFound && !$this->
FieldExists($TableName, $FieldName)))
857 throw new Exception(
"Column '".$FieldName
858 .
"' not found in table '".$TableName.
"'.");
863 # if new value supplied 866 # error out if we are trying to update a nonexistent record or field 867 if (!count($CachedRecord))
869 throw new Exception(
"No records found when attempting to update" 870 .
" column '".$FieldName.
"' in table '".$TableName.
"'" 871 .(($Condition != NULL)
872 ?
" using condition '".$Condition.
"'" :
"")
877 if (!array_key_exists($FieldName, $CachedRecord))
879 throw new Exception(
"Attempting to update column '".$FieldName
880 .
"', which does not exist in table '".$TableName.
"'.");
884 # update value in database 885 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = " 886 .(($NewValue === NULL) ?
"NULL" :
"'" 887 .mysqli_real_escape_string($this->Handle, $NewValue).
"'")
890 # update value in cached record 891 $CachedRecord[$FieldName] = $NewValue;
894 # return value from cached record to caller 895 return isset($CachedRecord[$FieldName])
896 ? $CachedRecord[$FieldName] : NULL;
924 $Condition, $CachedRecord);
952 $Condition, $CachedRecord);
966 public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
967 $ColumnsToExclude = array())
969 # retrieve names of all columns in table 972 # remove columns to be excluded from copy 973 $ColumnsToExclude[] = $IdColumn;
974 $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
976 # normalize destination IDs 977 $DstIds = is_array($DstId) ? $DstId : array($DstId);
978 $DstIds = array_diff($DstIds, array($SrcId));
980 # if there are columns to copy and we have destinations 981 if (count($ColumnsToCopy) && count($DstIds))
983 # construct and execute query to perform copy 984 $Query =
"UPDATE `".$TableName.
"` AS Target" 985 .
" LEFT JOIN `".$TableName.
"` AS Source" 986 .
" ON Source.`".$IdColumn.
"` = '".addslashes($SrcId).
"'";
987 $QuerySets = array();
988 foreach ($ColumnsToCopy as $ColumnName)
990 $QuerySets[] =
"Target.`".$ColumnName.
"` = Source.`".$ColumnName.
"`";
992 $Query .=
" SET ".implode(
", ", $QuerySets);
993 $QueryConditions = array();
994 foreach ($DstIds as $Id)
996 $QueryConditions[] =
"Target.`".$IdColumn.
"` = '".addslashes($DstId).
"'";
998 $Query .=
" WHERE ".implode(
" OR ", $QueryConditions);
999 $this->
Query($Query);
1017 $KeyField = NULL, $AvgDataLength = 20)
1019 # pick some ballpark values 1020 $ChunkSizeAssumedSafe = 100;
1021 $QueryLengthAssumedSafe = 10486576; # (1 MB)
1023 # exit without doing anything
if there are no values
1024 $ValueCount = count($Values);
1025 if ($ValueCount == 0)
1030 # determine size of array chunk per INSERT statement 1031 $NonValueCharCount = 100;
1032 if ($ValueCount > $ChunkSizeAssumedSafe)
1035 $ValueSegmentLen = $AvgDataLength + 6;
1036 if ($KeyField !== NULL)
1038 $ValueSegmentLen = $ValueSegmentLen * 2;
1040 $ValueChunkSize = $MaxQueryLen / $ValueSegmentLen;
1044 $ValueChunkSize = $ChunkSizeAssumedSafe;
1047 # for each chunk of values 1048 foreach (array_chunk($Values, $ValueChunkSize, TRUE) as $ValueChunk)
1050 # begin building query 1051 $Query =
"INSERT INTO `".$Table.
"` (`".$ValueField.
"`";
1053 # if key field was specified 1054 if ($KeyField !== NULL)
1056 # add key field to query 1057 $Query .=
", `".$KeyField.
"`";
1059 # assemble value segment with keys 1060 $ValueSegFunc =
function($Carry, $Key) use ($ValueChunk)
1062 $Carry .=
"('".addslashes($ValueChunk[$Key]).
"','" 1063 .addslashes($Key).
"'),";
1066 $ValueSegment = array_reduce(array_keys($ValueChunk), $ValueSegFunc);
1070 # assemble value segment 1071 $ValueSegFunc =
function($Carry, $Value)
1073 $Carry .=
"('".addslashes($Value).
"'),";
1076 $ValueSegment = array_reduce($ValueChunk, $ValueSegFunc);
1079 # trim extraneous comma off of value segment 1080 $ValueSegment = substr($ValueSegment, 0, -1);
1082 # add value segment to query 1083 $Query .=
") VALUES ".$ValueSegment;
1085 # double check to make sure query isn't too long 1086 $QueryLen = strlen($Query);
1087 if ($QueryLen > $QueryLengthAssumedSafe)
1089 if (!isset($MaxQueryLen))
1093 if ($QueryLen > $MaxQueryLen)
1095 throw new Exception(
"Maximum query length (" 1096 .$MaxQueryLen.
") exceeded (".$QueryLen.
").");
1101 $this->
Query($Query);
1118 return mysqli_real_escape_string($this->Handle, $String);
1129 $this->
Query(
"-- ".$String);
1139 $this->
Query(
"SHOW TABLES LIKE '".addslashes($TableName).
"'");
1151 $this->
Query(
"DESC ".$TableName);
1152 while ($CurrentFieldName = $this->
FetchField(
"Field"))
1154 if ($CurrentFieldName == $FieldName) {
return TRUE; }
1167 $this->
Query(
"DESC ".$TableName);
1169 return $AllTypes[$FieldName];
1179 $this->
Query(
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" 1180 .
" WHERE TABLE_SCHEMA = '".addslashes($this->
DBName)
1181 .
"' AND TABLE_NAME = '".addslashes($TableName).
"'");
1191 return (
int)static::ServerSystemVariable(
"max_allowed_packet");
1201 self::$QueryDebugOutputFlag = $NewSetting;
1211 return self::$QueryCounter;
1222 return self::$CachedQueryCounter;
1232 if (self::$QueryCounter)
1234 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1250 return (
int)static::ServerSystemVariable(
"long_query_time", intval($NewValue));
1255 # ---- PRIVATE INTERFACE ------------------------------------------------- 1263 private $QueryHandle;
1264 private $QueryResults;
1265 private $RowCounter;
1267 private $GetResultsFromCache;
1268 private $ErrorIgnored = FALSE;
1269 private $ErrorsToIgnore = NULL;
1270 private $ErrMsg = NULL;
1271 private $ErrNo = NULL;
1273 private static $DisplayErrors = FALSE;
1275 private static $GlobalDBUserName;
1276 private static $GlobalDBPassword;
1277 private static $GlobalDBHostName;
1278 private static $GlobalDBName;
1281 private static $QueryDebugOutputFlag = FALSE;
1282 # flag for whether caching is turned on 1283 private static $CachingFlag = TRUE;
1284 # query result advanced caching flag 1285 private static $AdvancedCachingFlag = FALSE;
1286 # global cache for query results 1287 private static $QueryResultCache = array();
1289 private static $QueryCounter = 0;
1290 private static $CachedQueryCounter = 0;
1291 # database connection link handles 1292 private static $ConnectionHandles = array();
1293 # do not cache queries that return more than this number of rows 1294 private static $CacheRowsThreshold = 250;
1295 # prune the query cache if there is less than this amount of memory free 1296 private static $CacheMemoryThreshold;
1297 # number of rows to leave in cache when pruning 1298 private static $CacheRowsToLeave = 10;
1299 # number of retry attempts to make to connect to database 1300 private static $ConnectRetryAttempts = 3;
1301 # number of seconds to wait between connection retry attempts 1302 private static $ConnectRetryInterval = 5;
1304 # server connection error codes 1306 # through socket '%s
' (%d) 1307 const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on
'%s' (%d)
1311 # limits on int variables 1312 # https://dev.mysql.com/doc/refman/5.7/en/integer-types.html 1319 # connection error codes that may be recoverable 1320 private static $RecoverableConnectionErrors = array(
1321 self::CR_CONNECTION_ERROR,
1334 private static function ConnectAndSelectDB(
1337 $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1340 # if this is not our first connection attempt 1343 # wait for the retry interval 1344 sleep(self::$ConnectRetryInterval);
1347 # attempt to connect to server 1349 $ConnectAttemptsLeft--;
1351 # repeat if we do not have a connection and there are retry attempts 1352 # left and the connection error code indicates a retry may succeed 1355 while (!$Handle && $ConnectAttemptsLeft
1356 && in_array(mysqli_connect_errno(),
1357 self::$RecoverableConnectionErrors));
1360 # throw exception if connection attempts failed 1363 throw new Exception(
"Could not connect to database: " 1364 .mysqli_connect_error().
" (errno: ".mysqli_connect_errno().
")");
1368 $Result = mysqli_select_db($Handle,
$DBName);
1369 if ($Result !== TRUE)
1371 throw new Exception(
"Could not select database: " 1372 .mysqli_error($Handle).
" (errno: ".mysqli_errno($Handle).
")");
1375 # return new connection to caller 1384 private function IsReadOnlyStatement($QueryString)
1386 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1395 private function TableModified($QueryString)
1397 # assume we're not going to be able to determine table 1400 # split query into pieces 1401 $QueryString = trim($QueryString);
1402 $Words = preg_split(
"/\s+/", $QueryString);
1404 # if INSERT statement 1406 if (strtoupper($Words[0]) ==
"INSERT")
1408 # skip over modifying keywords 1409 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1410 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
1411 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1412 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
1417 # next word is table name 1418 $TableName = $Words[$WordIndex];
1420 # else if UPDATE statement 1421 elseif (strtoupper($Words[0]) ==
"UPDATE")
1423 # skip over modifying keywords 1424 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1425 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
1430 # if word following next word is SET 1431 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
1433 # next word is table name 1434 $TableName = $Words[$WordIndex];
1437 # else if DELETE statement 1438 elseif (strtoupper($Words[0]) ==
"DELETE")
1440 # skip over modifying keywords 1441 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1442 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1443 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
1448 # if next term is FROM 1449 if (strtoupper($Words[$WordIndex]) ==
"FROM")
1451 # next word is table name 1453 $TableName = $Words[$WordIndex];
1457 # discard table name if it looks at all suspicious 1460 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
1466 # return table name (or lack thereof) to caller 1476 private function TablesAccessed($QueryString)
1478 # assume we're not going to be able to determine tables 1479 $TableNames = FALSE;
1481 # split query into pieces 1482 $QueryString = trim($QueryString);
1483 $Words = preg_split(
"/\s+/", $QueryString);
1484 $UQueryString = strtoupper($QueryString);
1485 $UWords = preg_split(
"/\s+/", $UQueryString);
1487 # if SELECT statement 1488 if ($UWords[0] ==
"SELECT")
1490 # keep going until we hit FROM or last word 1492 while (($UWords[$WordIndex] !=
"FROM")
1493 && strlen($UWords[$WordIndex]))
1499 if ($UWords[$WordIndex] ==
"FROM")
1501 # for each word after FROM 1503 while (strlen($UWords[$WordIndex]))
1505 # if current word ends with comma 1506 if (preg_match(
"/,$/", $Words[$WordIndex]))
1508 # strip off comma and add word to table name list 1509 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1513 # add word to table name list 1514 $TableNames[] = $Words[$WordIndex];
1516 # if next word is not comma 1518 if ($Words[$WordIndex] !=
",")
1520 # if word begins with comma 1521 if (preg_match(
"/^,/", $Words[$WordIndex]))
1523 # strip off comma (NOTE: modifies $Words array!) 1524 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1526 # decrement index so we start with this word next pass 1531 # stop scanning words (non-basic JOINs not yet handled) 1543 # discard table names if they look at all suspicious 1546 foreach ($TableNames as $Name)
1548 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1550 $TableNames = FALSE;
1556 # return table name (or lack thereof) to caller 1566 private function RunQuery($QueryString)
1568 # log query start time if debugging output is enabled 1569 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1571 # run query against database 1572 $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1574 # print query and execution time if debugging output is enabled 1575 if (self::$QueryDebugOutputFlag)
1577 print
"DB: ".$QueryString.
" [" 1578 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1582 # if query failed and there are errors that we can ignore 1583 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1585 # for each pattern for an error that we can ignore 1586 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1588 # if error matches pattern 1589 $ErrorMsg = mysqli_error($this->Handle);
1590 if (preg_match($SqlPattern, $QueryString)
1591 && preg_match($ErrMsgPattern, $ErrorMsg))
1593 # set return value to indicate error was ignored 1594 $this->QueryHandle = TRUE;
1596 # set internal flag to indicate that an error was ignored 1597 $this->ErrorIgnored = $ErrorMsg;
1599 # stop looking at patterns 1606 if ($this->QueryHandle === FALSE)
1608 # clear stored value for number of rows retrieved 1611 # retrieve error info 1612 $this->ErrMsg = mysqli_error($this->Handle);
1613 $this->ErrNo = mysqli_errno($this->Handle);
1615 # if we are supposed to be displaying errors 1616 if (self::$DisplayErrors)
1619 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1620 .
"</i> (".$this->ErrNo.
")<br/>\n");
1621 print(
"<b>SQL Statement:</b> <i>" 1622 .htmlspecialchars($QueryString).
"</i><br/>\n");
1624 # retrieve execution trace that got us to this point 1625 $Trace = debug_backtrace();
1627 # remove current context from trace 1628 array_shift($Trace);
1630 # make sure file name and line number are available 1631 foreach ($Trace as $Index => $Loc)
1633 if (!array_key_exists(
"file", $Loc))
1635 $Trace[$Index][
"file"] =
"UNKNOWN";
1637 if (!array_key_exists(
"line", $Loc))
1639 $Trace[$Index][
"line"] =
"??";
1643 # determine length of leading path common to all file names in trace 1645 $OurFile = __FILE__;
1647 foreach ($Trace as $Loc)
1649 if ($Loc[
"file"] !=
"UNKNOWN")
1652 $FNameLength = strlen($Loc[
"file"]);
1653 while ($Index < $FNameLength &&
1654 $Loc[
"file"][$Index] == $OurFile[$Index])
1656 $PrefixLen = min($PrefixLen, $Index);
1660 foreach ($Trace as $Loc)
1664 foreach ($Loc[
"args"] as $Arg)
1667 switch (gettype($Arg))
1670 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1679 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1680 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1686 $ArgString .= strtoupper(gettype($Arg));
1690 $ArgString .= get_class($Arg);
1693 case "unknown type":
1694 $ArgString .=
"UNKNOWN";
1699 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1700 $LocString .=
" ";
1701 if (array_key_exists(
"class", $Loc))
1702 { $LocString .= $Loc[
"class"].
"::"; }
1703 $LocString .= $Loc[
"function"].
"(".$ArgString.
")" 1704 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1707 print(
"<b>Trace:</b><br>\n".$LocString);
1710 return $this->QueryHandle;
1717 static private function GetPhpMemoryLimit()
1719 $Str = strtoupper(ini_get(
"memory_limit"));
1720 if (substr($Str, -1) ==
"B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1721 switch (substr($Str, -1))
1724 $MemoryLimit = (int)$Str * 1024;
1728 $MemoryLimit = (int)$Str * 1048576;
1732 $MemoryLimit = (int)$Str * 1073741824;
1736 $MemoryLimit = (int)$Str;
1739 return $MemoryLimit;
1746 static private function GetFreeMemory()
1748 return self::GetPhpMemoryLimit() - memory_get_usage();
1760 static private function ServerSystemVariable($VarName, $NewValue = NULL)
1769 if ($NewValue !== NULL)
1771 if (is_string($NewValue))
1773 $NewValue =
"'".addslashes($NewValue).
"'";
1775 $DB->Query(
"SET ".$VarName.
" = ".$NewValue);
1778 return $DB->Query(
"SHOW VARIABLES LIKE '".addslashes($VarName).
"'",
1783 # define value to designate omitted arguments (so DB values can be set to NULL) 1784 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
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.
GetNextInsertId($TableName)
For tables that have an AUTO_INCREMENT column, get the next value that will be assigned.
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 SlowQueryThreshold($NewValue=NULL)
Get/set current threshold for what is considered a "slow" SQL query.
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.
const MEDIUMINT_MAX_VALUE
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.