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 "Table ".$TableName.
" does not exist");
807 return (
int)$this->
Query(
808 "SELECT `AUTO_INCREMENT` AS Id FROM INFORMATION_SCHEMA.TABLES " 809 .
"WHERE TABLE_SCHEMA='".addslashes($this->
DBName()).
"' " 810 .
"AND TABLE_NAME = '".addslashes($TableName).
"'",
"Id");
828 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
830 # expand condition if supplied 831 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
833 # if cached values not already loaded 834 if (!isset($CachedRecord))
836 # read cached record from database 837 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
840 # error out if requested column does not exist in specified table 844 if (($RowsFound && !array_key_exists($FieldName, $CachedRecord))
845 || (!$RowsFound && !$this->
FieldExists($TableName, $FieldName)))
847 throw new Exception(
"Column '".$FieldName
848 .
"' not found in table '".$TableName.
"'.");
853 # if new value supplied 856 # error out if we are trying to update a nonexistent record or field 857 if (!count($CachedRecord))
859 throw new Exception(
"No records found when attempting to update" 860 .
" column '".$FieldName.
"' in table '".$TableName.
"'" 861 .(($Condition != NULL)
862 ?
" using condition '".$Condition.
"'" :
"")
867 if (!array_key_exists($FieldName, $CachedRecord))
869 throw new Exception(
"Attempting to update column '".$FieldName
870 .
"', which does not exist in table '".$TableName.
"'.");
874 # update value in database 875 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = " 876 .(($NewValue === NULL) ?
"NULL" :
"'" 877 .mysqli_real_escape_string($this->Handle, $NewValue).
"'")
880 # update value in cached record 881 $CachedRecord[$FieldName] = $NewValue;
884 # return value from cached record to caller 885 return isset($CachedRecord[$FieldName])
886 ? $CachedRecord[$FieldName] : NULL;
906 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
910 $Condition, $CachedRecord);
930 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
934 $Condition, $CachedRecord);
948 public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
949 $ColumnsToExclude = array())
951 # retrieve names of all columns in table 954 # remove columns to be excluded from copy 955 $ColumnsToExclude[] = $IdColumn;
956 $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
958 # normalize destination IDs 959 $DstIds = is_array($DstId) ? $DstId : array($DstId);
960 $DstIds = array_diff($DstIds, array($SrcId));
962 # if there are columns to copy and we have destinations 963 if (count($ColumnsToCopy) && count($DstIds))
965 # construct and execute query to perform copy 966 $Query =
"UPDATE `".$TableName.
"` AS Target" 967 .
" LEFT JOIN `".$TableName.
"` AS Source" 968 .
" ON Source.`".$IdColumn.
"` = '".addslashes($SrcId).
"'";
969 $QuerySets = array();
970 foreach ($ColumnsToCopy as $ColumnName)
972 $QuerySets[] =
"Target.`".$ColumnName.
"` = Source.`".$ColumnName.
"`";
974 $Query .=
" SET ".implode(
", ", $QuerySets);
975 $QueryConditions = array();
976 foreach ($DstIds as $Id)
978 $QueryConditions[] =
"Target.`".$IdColumn.
"` = '".addslashes($DstId).
"'";
980 $Query .=
" WHERE ".implode(
" OR ", $QueryConditions);
981 $this->
Query($Query);
999 $KeyField = NULL, $AvgDataLength = 20)
1001 # pick some ballpark values 1002 $ChunkSizeAssumedSafe = 100;
1003 $QueryLengthAssumedSafe = 10486576; # (1 MB)
1005 # exit without doing anything
if there are no values
1006 $ValueCount = count($Values);
1007 if ($ValueCount == 0)
1012 # determine size of array chunk per INSERT statement 1013 $NonValueCharCount = 100;
1014 if ($ValueCount > $ChunkSizeAssumedSafe)
1017 $ValueSegmentLen = $AvgDataLength + 6;
1018 if ($KeyField !== NULL)
1020 $ValueSegmentLen = $ValueSegmentLen * 2;
1022 $ValueChunkSize = $MaxQueryLen / $ValueSegmentLen;
1026 $ValueChunkSize = $ChunkSizeAssumedSafe;
1029 # for each chunk of values 1030 foreach (array_chunk($Values, $ValueChunkSize, TRUE) as $ValueChunk)
1032 # begin building query 1033 $Query =
"INSERT INTO `".$Table.
"` (`".$ValueField.
"`";
1035 # if key field was specified 1036 if ($KeyField !== NULL)
1038 # add key field to query 1039 $Query .=
", `".$KeyField.
"`";
1041 # assemble value segment with keys 1042 $ValueSegFunc =
function($Carry, $Key) use ($ValueChunk)
1044 $Carry .=
"('".addslashes($ValueChunk[$Key]).
"','" 1045 .addslashes($Key).
"'),";
1048 $ValueSegment = array_reduce(array_keys($ValueChunk), $ValueSegFunc);
1052 # assemble value segment 1053 $ValueSegFunc =
function($Carry, $Value)
1055 $Carry .=
"('".addslashes($Value).
"'),";
1058 $ValueSegment = array_reduce($ValueChunk, $ValueSegFunc);
1061 # trim extraneous comma off of value segment 1062 $ValueSegment = substr($ValueSegment, 0, -1);
1064 # add value segment to query 1065 $Query .=
") VALUES ".$ValueSegment;
1067 # double check to make sure query isn't too long 1068 $QueryLen = strlen($Query);
1069 if ($QueryLen > $QueryLengthAssumedSafe)
1071 if (!isset($MaxQueryLen))
1075 if ($QueryLen > $MaxQueryLen)
1077 throw new Exception(
"Maximum query length (" 1078 .$MaxQueryLen.
") exceeded (".$QueryLen.
").");
1083 $this->
Query($Query);
1100 return mysqli_real_escape_string($this->Handle, $String);
1111 $this->
Query(
"-- ".$String);
1121 $this->
Query(
"SHOW TABLES LIKE '".addslashes($TableName).
"'");
1133 $this->
Query(
"DESC ".$TableName);
1134 while ($CurrentFieldName = $this->
FetchField(
"Field"))
1136 if ($CurrentFieldName == $FieldName) {
return TRUE; }
1149 $this->
Query(
"DESC ".$TableName);
1151 return $AllTypes[$FieldName];
1161 $this->
Query(
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS" 1162 .
" WHERE TABLE_SCHEMA = '".addslashes($this->
DBName)
1163 .
"' AND TABLE_NAME = '".addslashes($TableName).
"'");
1173 return $this->
Query(
"SHOW VARIABLES LIKE 'max_allowed_packet'",
1184 self::$QueryDebugOutputFlag = $NewSetting;
1194 return self::$QueryCounter;
1205 return self::$CachedQueryCounter;
1215 if (self::$QueryCounter)
1217 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1227 # ---- PRIVATE INTERFACE ------------------------------------------------- 1235 private $QueryHandle;
1236 private $QueryResults;
1237 private $RowCounter;
1239 private $GetResultsFromCache;
1240 private $ErrorIgnored = FALSE;
1241 private $ErrorsToIgnore = NULL;
1242 private $ErrMsg = NULL;
1243 private $ErrNo = NULL;
1245 private static $DisplayErrors = FALSE;
1247 private static $GlobalDBUserName;
1248 private static $GlobalDBPassword;
1249 private static $GlobalDBHostName;
1250 private static $GlobalDBName;
1253 private static $QueryDebugOutputFlag = FALSE;
1254 # flag for whether caching is turned on 1255 private static $CachingFlag = TRUE;
1256 # query result advanced caching flag 1257 private static $AdvancedCachingFlag = FALSE;
1258 # global cache for query results 1259 private static $QueryResultCache = array();
1261 private static $QueryCounter = 0;
1262 private static $CachedQueryCounter = 0;
1263 # database connection link handles 1264 private static $ConnectionHandles = array();
1265 # do not cache queries that return more than this number of rows 1266 private static $CacheRowsThreshold = 250;
1267 # prune the query cache if there is less than this amount of memory free 1268 private static $CacheMemoryThreshold;
1269 # number of rows to leave in cache when pruning 1270 private static $CacheRowsToLeave = 10;
1271 # number of retry attempts to make to connect to database 1272 private static $ConnectRetryAttempts = 3;
1273 # number of seconds to wait between connection retry attempts 1274 private static $ConnectRetryInterval = 5;
1276 # server connection error codes 1278 # through socket '%s
' (%d) 1279 const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on
'%s' (%d)
1283 # limits on int variables 1284 # https://dev.mysql.com/doc/refman/5.7/en/integer-types.html 1291 # connection error codes that may be recoverable 1292 private static $RecoverableConnectionErrors = array(
1293 self::CR_CONNECTION_ERROR,
1306 private static function ConnectAndSelectDB(
1309 $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1312 # if this is not our first connection attempt 1315 # wait for the retry interval 1316 sleep(self::$ConnectRetryInterval);
1319 # attempt to connect to server 1321 $ConnectAttemptsLeft--;
1323 # repeat if we do not have a connection and there are retry attempts 1324 # left and the connection error code indicates a retry may succeed 1327 while (!$Handle && $ConnectAttemptsLeft
1328 && in_array(mysqli_connect_errno(),
1329 self::$RecoverableConnectionErrors));
1332 # throw exception if connection attempts failed 1335 throw new Exception(
"Could not connect to database: " 1336 .mysqli_connect_error().
" (errno: ".mysqli_connect_errno().
")");
1340 $Result = mysqli_select_db($Handle,
$DBName);
1341 if ($Result !== TRUE)
1343 throw new Exception(
"Could not select database: " 1344 .mysqli_error($Handle).
" (errno: ".mysqli_errno($Handle).
")");
1347 # return new connection to caller 1356 private function IsReadOnlyStatement($QueryString)
1358 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1367 private function TableModified($QueryString)
1369 # assume we're not going to be able to determine table 1372 # split query into pieces 1373 $QueryString = trim($QueryString);
1374 $Words = preg_split(
"/\s+/", $QueryString);
1376 # if INSERT statement 1378 if (strtoupper($Words[0]) ==
"INSERT")
1380 # skip over modifying keywords 1381 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1382 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
1383 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1384 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
1389 # next word is table name 1390 $TableName = $Words[$WordIndex];
1392 # else if UPDATE statement 1393 elseif (strtoupper($Words[0]) ==
"UPDATE")
1395 # skip over modifying keywords 1396 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1397 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
1402 # if word following next word is SET 1403 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
1405 # next word is table name 1406 $TableName = $Words[$WordIndex];
1409 # else if DELETE statement 1410 elseif (strtoupper($Words[0]) ==
"DELETE")
1412 # skip over modifying keywords 1413 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1414 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1415 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
1420 # if next term is FROM 1421 if (strtoupper($Words[$WordIndex]) ==
"FROM")
1423 # next word is table name 1425 $TableName = $Words[$WordIndex];
1429 # discard table name if it looks at all suspicious 1432 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
1438 # return table name (or lack thereof) to caller 1448 private function TablesAccessed($QueryString)
1450 # assume we're not going to be able to determine tables 1451 $TableNames = FALSE;
1453 # split query into pieces 1454 $QueryString = trim($QueryString);
1455 $Words = preg_split(
"/\s+/", $QueryString);
1456 $UQueryString = strtoupper($QueryString);
1457 $UWords = preg_split(
"/\s+/", $UQueryString);
1459 # if SELECT statement 1460 if ($UWords[0] ==
"SELECT")
1462 # keep going until we hit FROM or last word 1464 while (($UWords[$WordIndex] !=
"FROM")
1465 && strlen($UWords[$WordIndex]))
1471 if ($UWords[$WordIndex] ==
"FROM")
1473 # for each word after FROM 1475 while (strlen($UWords[$WordIndex]))
1477 # if current word ends with comma 1478 if (preg_match(
"/,$/", $Words[$WordIndex]))
1480 # strip off comma and add word to table name list 1481 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1485 # add word to table name list 1486 $TableNames[] = $Words[$WordIndex];
1488 # if next word is not comma 1490 if ($Words[$WordIndex] !=
",")
1492 # if word begins with comma 1493 if (preg_match(
"/^,/", $Words[$WordIndex]))
1495 # strip off comma (NOTE: modifies $Words array!) 1496 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1498 # decrement index so we start with this word next pass 1503 # stop scanning words (non-basic JOINs not yet handled) 1515 # discard table names if they look at all suspicious 1518 foreach ($TableNames as $Name)
1520 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1522 $TableNames = FALSE;
1528 # return table name (or lack thereof) to caller 1538 private function RunQuery($QueryString)
1540 # log query start time if debugging output is enabled 1541 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1543 # run query against database 1544 $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1546 # print query and execution time if debugging output is enabled 1547 if (self::$QueryDebugOutputFlag)
1549 print
"DB: ".$QueryString.
" [" 1550 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1554 # if query failed and there are errors that we can ignore 1555 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1557 # for each pattern for an error that we can ignore 1558 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1560 # if error matches pattern 1561 $ErrorMsg = mysqli_error($this->Handle);
1562 if (preg_match($SqlPattern, $QueryString)
1563 && preg_match($ErrMsgPattern, $ErrorMsg))
1565 # set return value to indicate error was ignored 1566 $this->QueryHandle = TRUE;
1568 # set internal flag to indicate that an error was ignored 1569 $this->ErrorIgnored = $ErrorMsg;
1571 # stop looking at patterns 1578 if ($this->QueryHandle === FALSE)
1580 # clear stored value for number of rows retrieved 1583 # retrieve error info 1584 $this->ErrMsg = mysqli_error($this->Handle);
1585 $this->ErrNo = mysqli_errno($this->Handle);
1587 # if we are supposed to be displaying errors 1588 if (self::$DisplayErrors)
1591 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1592 .
"</i> (".$this->ErrNo.
")<br/>\n");
1593 print(
"<b>SQL Statement:</b> <i>" 1594 .htmlspecialchars($QueryString).
"</i><br/>\n");
1596 # retrieve execution trace that got us to this point 1597 $Trace = debug_backtrace();
1599 # remove current context from trace 1600 array_shift($Trace);
1602 # make sure file name and line number are available 1603 foreach ($Trace as $Index => $Loc)
1605 if (!array_key_exists(
"file", $Loc))
1607 $Trace[$Index][
"file"] =
"UNKNOWN";
1609 if (!array_key_exists(
"line", $Loc))
1611 $Trace[$Index][
"line"] =
"??";
1615 # determine length of leading path common to all file names in trace 1617 $OurFile = __FILE__;
1619 foreach ($Trace as $Loc)
1621 if ($Loc[
"file"] !=
"UNKNOWN")
1624 $FNameLength = strlen($Loc[
"file"]);
1625 while ($Index < $FNameLength &&
1626 $Loc[
"file"][$Index] == $OurFile[$Index])
1628 $PrefixLen = min($PrefixLen, $Index);
1632 foreach ($Trace as $Loc)
1636 foreach ($Loc[
"args"] as $Arg)
1639 switch (gettype($Arg))
1642 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1651 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1652 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1658 $ArgString .= strtoupper(gettype($Arg));
1662 $ArgString .= get_class($Arg);
1665 case "unknown type":
1666 $ArgString .=
"UNKNOWN";
1671 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1672 $LocString .=
" ";
1673 if (array_key_exists(
"class", $Loc))
1674 { $LocString .= $Loc[
"class"].
"::"; }
1675 $LocString .= $Loc[
"function"].
"(".$ArgString.
")" 1676 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1679 print(
"<b>Trace:</b><br>\n".$LocString);
1682 return $this->QueryHandle;
1689 static private function GetPhpMemoryLimit()
1691 $Str = strtoupper(ini_get(
"memory_limit"));
1692 if (substr($Str, -1) ==
"B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1693 switch (substr($Str, -1))
1696 $MemoryLimit = (int)$Str * 1024;
1700 $MemoryLimit = (int)$Str * 1048576;
1704 $MemoryLimit = (int)$Str * 1073741824;
1708 $MemoryLimit = (int)$Str;
1711 return $MemoryLimit;
1718 static private function GetFreeMemory()
1720 return self::GetPhpMemoryLimit() - memory_get_usage();
1724 # define return values (numerical values correspond to MySQL error codes) 1726 define(
"DB_OKAY", 0);
1727 define(
"DB_ERROR", 1);
1728 define(
"DB_ACCESSDENIED", 2);
1729 define(
"DB_UNKNOWNDB", 3);
1730 define(
"DB_UNKNOWNTABLE", 4);
1731 define(
"DB_SYNTAXERROR", 5);
1732 define(
"DB_DBALREADYEXISTS", 6);
1733 define(
"DB_DBDOESNOTEXIST", 7);
1734 define(
"DB_DISKFULL", 8);
1737 # define value to designate omitted arguments (so DB values can be set to NULL) 1738 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1740 # MySQL error code mapping 1751 # date() format for SQL dates 1752 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.
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 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.