UGN Security Forums
My ProfileMember DirectoryLogin
Search our ForumsView our FAQView our Site Rules
View our CalendarView our Active TopicsGo to our Main Page

UGN Security Store
 

Network Sites UGN Security, The GoNix Initiative, Elite Web Gamers, Back of the Web, EveryDay Helper, VNC Web Design & Development
August
Su M Tu W Th F Sa
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
Sponsored Links
Latest Postings
Amazon Gift Card Generator/KeyGen?te
by Gecko666
08/22/14 09:21 AM
Latest Reviews
Topic Options
Rate This Topic
#45751 - 05/31/08 03:02 AM PHP5 OOP - Caching out SQL query results securly
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Sup peoples. I promised another tut on PHP5 caching and I am actually going to do it. If you are like me, you have spent hours shaving precious Milli seconds off queries trying to speed up applications. Maybe you have that one query that hits 10 tables across 3 databases. Damn shame. Meanwhile your applications gets bogged down. Your users complain the site has slowed down since the new features. You know you want to keep the application but the time is killing you.

Or maybe your hoster has said your application take up too many resources. I first looked into caching when Gizmo mentioned it to me. About a year maybe two years ago, we were yucking it up on the tele. I thought, caching... Ole red beard is drinking again. Caching is for desktops. Sigh, seems I was wrong.

While working at the Census Department I coded quite a few LARGE data apps. There are over 300,000,000 people living in the US. So just imagine database table with more rows than 300,000,000 rows. Now we would partition the tables, that helped a lot. We had one kick ass DBA. But in the end, I still was lacking some speed. Enter caching

If you have data you know you can count on being the same for 15 minutes or more in a high use environment... Caching is for you. When I started thinking seriously about caching I layed out some requirements.


  • The dataset being cached had to be secure (no web surfer should be able to directory surf and get them.)
  • The file names had to be unique enough That I could reasonably be sure now accidental overwriting would happen
  • The structure of the save data would have to be easy to work with. (I didn't want to write whole new code just to read and work with cache files)
  • I had to be able to code this thing one time and use it for all apps.
  • It had to document the cache files it created.
  • It had to be flexible and allow me to change a few things.
    • File extensions it uses for the cache files
    • The hashing algarythm it uses, Not all systems use SHA1
    • The time frame a cache is good for.


So with my requirements I set off to code a SQL (or array caching system.)
_________________________
My New site OpenEyes

Top
Sponsored Links
      
#45752 - 05/31/08 03:14 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Below is what your buddy §intå× came up with. As always it is heavily documented. I will cover its uses below.

Php Code:


<?php
/**
* This file houses the cache class.  It will handle the caching of data for sql queries and markup.
*
* This file exists because I belive if you do something more than once you should find a way to 
* abstract it to a single call
* @package	 sqlCache
* @since	6/22/2007 22:10
* @author	 Russell Vance <[email protected]>
* @license	http://www.opensource.org/licenses/lgpl-2.1.php
*/

/**
* This class will generate cache files needed to speed up processing by the other classes
*
* The methods in this class are very powerful and should cut back time spent on sql queries
* @version	1.0.1
*/
class cacheMgr{
	
	/**
	* A numeric value for the hash type you want used in the naming of your files.
	* 1 = md5
	* 2 = sha1
	* The default is 1 md5
	*
	* @access	 public
	* @var		 integer
	*/
	public $hashType;
	
	/**
	* The number of seconds untill the cache file is expired.
	* 1 hrs = 3600
	* 6 hrs = 21600 etc, etc etc...
	*
	* @access	 public
	* @var		 integer
	*/
	public $expierTime;
	
	/**
	* A boolean value that tells the class wether or not to secure the cache file
	*
	* @access	 public
	* @var		boolean
	*/
	public $secure;
	
	/**
	* A string value that tells the the file extension you want for the cache files.  Defaults to '.tmp'
	*
	* @access	 public
	* @var		string
	*/
	public $fileExt;
	
	/**
	 * The full file path to your cache directory
	 * 
	 * @access	public
	 * @var		string
	 */
	public $cachePath;
	
	/**
	* This is the class constructor. Here you can set some options for your cached files.
	*
	* This constructor requires 3 parameters for the class to initialise.  You can see how to initialize 
	* the class in the example below:
	* <code>
	* //Example:
	*
	* $cache	 = new cacheMgr(1, 3600, true, 'php');
	* $cache2	= new cacheMgr('sha1', 86400, false, 'inc');
	*
	* </code>
	* The above code would create two new cacheMgr objects.  
	* The files with the first object created would be called and named with an 
	* md5 hash algorithm, They would expire after 1 hour and they would be made 
	* secure from outside viewers.
	*
	* The the files created from the second object would be named using the sha1 algorithm
	* They would expier after 24 hrs.  They would not be secured.  And they would use the 
	* default file extension.
	* @access	public
	* @param	integer		$hashType	options are 1, 2 or you can use the string identifiers md5, sha1.  <br />1 returns md5 hash named files, 2 returns sha1 hash named files
	* @param	integer		$expierTime	The number of seconds the cahe file is good for.  Defaults to 3600.
	* @param	boolean		$secure		True now direct web access.  False, allow web access to cache files.
	* @param	string		$fileExt	The cache file, file extension to use.
	* @return	void
	*/
	public function __construct($hashType = 1, $expierTime = 3600, $secure, $fileExt = 'tmp', $cachePath){
		$this->hashType		= $hashType;
		$this->expierTime	= (int)$expierTime;
		$this->secure		= (bool)$secure;
		$this->fileExt		= (string)$fileExt;
		$this->cachePath	= (string)$cachePath;
	}	
	
	/**
	* The class destructor
	*
	* Cleans up after the class and frees up memory.  No need to call it.
	* @access	public
	* @param	void
	* @return	void
	*/
	public function __destruct(){
		unset($this->hashType);
		unset($this->expierTime);
		unset($this->secure);
		unset($this->fileExt);
		unset($this->cachePath);
	}
	
	/**
	* This method will return the md5 or sha1 hash for a pain text string.
	*
	* This method exists so I do not have to define both hash methods in every cacheOutxxx method.
	* @access	private
	* @param	string		$plainText	The plain text string to be hashed
	* @return	string		On sucess, return the newly created hash value.  On fail return false.
	*/
	private function getHash($plainText){
		switch($this->hashType){
			case 1 || 'md5':
			default: 
				$newHash	= md5($plainText, false);
				break;
			case 2 || 'sha1':
				$newHash	= sha1($plainText, false);
				break;
		}
		return	$newHash;
	}
	
	/**
	* This method is used to cache out an enumerated array
	*
	* This method will cache out an enumerated array into a php script that can be called.
	* This is usually done to avoid having to do complex queries.  Note this method is only 
	* good for single demensional arrays at this time.
	* @access	public
	* @param	array				$array		An enumerated array to cache out
	* @param	string				$uniqueName	A string value passed in that will not be passed in for another cache.  Usualy $_GET parameters, or query keys from the sqlClass
	* @return	string|bool			On sucess returns the file name created.  On fail returns false.
	*/	
	public function cacheOutArray($array, $uniquieId){
		$uniquieId = str_replace(' ', '_', $uniquieId);
		if(is_array($array)){
			$outString = "\$".$uniquieId." = '".json_encode($array)."';";
		}
		if($fileName	 = $this->setCacheFile($uniquieId,$outString)){
			$retVal	 = $fileName;
		}else{
			$retVal	 = false;
		}
		return $retVal;
	}

	/**
	* This method returns data that has been cached out.
	*
	* This method accepts a unique identifier that would be used to name a cache file, generates
	* a hash of it and check if a file by that name exists.  If it does it will include the file.
	* @access	public
	* @uses		cache::cachePath Used to define the path to the cache directory
	* @uses		DS				A Constant for DIRECTORY_SEPERATOR
	* @param	string	$uniqueId	A unique identifier used to generate a hash value
	* @return	boolean	On sucess returns true.  On fail returns false.
	*/
	public function getCache($fileName){
		$filePath = $this->cachePath;
		if(file_exists($fileName)){
			@include_once($filePath.$fileName);
			return true;
		}else{
			return false;
		}
	}
	
	/**
	* This method returns an array that has been cached out.
	*
	* This method accepts a unique identifier that would be used to name a cache file, generates
	* a hash of it and check if a file by that name exists.  If it does it will include the file 
	* and return the array.
	* @access	public
	* @uses		cache::cachePath Used to define the path to the cache directory
	* @uses		DS				A Constant for DIRECTORY_SEPERATOR
	* @param	string	$uniqueId	A unique identifier used to generate a hash value
	* @return	array|boolean		Returns a stored array object on sucess. On fail returns false.
	*/
	public function getArrayCache($uniquieId){
		$uniquieId = str_replace(' ', '_', $uniquieId);
		$fileName = $this->getHash($uniquieId).'.'.$this->fileExt;
		$filePath = $this->cachePath;
		if(file_exists($filePath.$fileName)){
			include_once($filePath.$fileName);
			//print_r(json_decode($getOptions, true));
			$arr = json_decode( $$uniquieId, true );
			return $arr;
		}else{
			return false;
		}
	}

	/**
	* This method is used to generate a docblock for the cache file
	*
	* This method will auto gen a doc block for the cach file with the time it was created in human readable format
	* and noting the file was created by this class and not a human.  This might help future proofing human error.
	* @access	private
	* @param	void
	* @return	string	Returns a dockblock comment that will go in a head section of a cache file.
	*/
	private function getCacheDocBlock(){
		$tmStmp	 = date('F d, Y - H:i:s T');
		$expires	= date('F d, Y - H:i:s T', mktime(date('H'), date('i'), date('s'), date('m'), date('d')+1, date('Y')));
		$comment	 = "\n/** \n* Cache file created by cacheMgr class \n* \n* You should not edit this file unless you know what you are doing. \n* If you do edit the file and the ads system crashes, just delete \n* this file.  It will be re-built.\n* @version	$tmStmp \n* @expires	$expires \n* @author	CAMS::cacheMgr \n*/";
		return $comment;
	}
	
	/**
	* This method creates a cache file 
	*
	* Takes incoming string data and writes it out to a file.  If class var $secure is true ads a constant 
	* check to the top of the class.
	* @access	 private
	* @uses		cache::cachePath Used to define the path to the cache directory
	* @uses		DS				A Constant for DIRECTORY_SEPERATOR
	* @param	string	$fileName	A md5 or sha1 generated hash and file extension file name.
	* @param	string	$outString	The data that is to be writen out to a file.
	* @return	boolean	On sucess return true.  On fail return false.
	*/
	private function setCacheFile($uniquieId, $outString){
		$uniquieId = str_replace(' ', '_', $uniquieId);
		$fileName	 = $this->getHash($uniquieId) . '.' . $this->fileExt;
		$outString	 = $this->prepStrForOut($outString);
		$cacheFile	= $this->cachePath . $fileName;
		if(file_put_contents($cacheFile, $outString) === false){
			return false;
		}
		return $fileName;
	}
	
	/**
	* This method preps a string that will be cached out with header and footer charaters it will need
	*
	* If the request is to create a secured cache file, a check for a joomla defined constant will be added to 
	* the header.
	* @access	private
	* @param	string	$outString	The string that will be written out to a file
	* @return	string	A new string with header and footer data will be returned.
	*/
	private function prepStrForOut($outString){
		if($this->secure){
		$outHeader	 = "<"."?php ".$this->getCacheDocBlock()." \ndefined( '_VALID_AMS_' ) or die( 'Restricted access' ); \n";
		}else{
			$outHeader	 = "<?php 
						".$this->getCacheDocBlock()." \n";
		}
		$outFooter	 = " \n?".">";
		$outString	= $outHeader . $outString . $outFooter;
		return $outString;
	}
}
?>


_________________________
My New site OpenEyes

Top
#45753 - 05/31/08 03:40 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Ok, to use this you have to re-think how you use MySQL. This isn't a bad thing. You probably code in a procedural style and thats fine. But you probably treat SQL statements like strings, thats not so fine. SQL is a language that can do things to your database that will make baby Jesus cry. It is time to treat SQL like what it is. An object in your code. Enter prepared statements.

http://www.php.net/manual/en/mysqli-stmt.prepare.php

Now you can use mysqli_stmt as a procedural thing but if you are changing, I suggest you use it in an OOP way. What I do now in building my apps is place all SQL into an array writen as prepared statements. When I need them I prepare them and slam the prepared statements into an array for use.

The fuck did he call me you say.... Nah, it isn't as hard as it sounds.

Below is a short example of a function that might store an array of prepared statement ready queries.
Php Code:

function queries($key){
	$queries = array(
				  'quer1' =>	'SELECT * FROM `tableA` WHERE `id` = ?',
				  'quer2' =>	'SELECT * FROM `tableb` WHERE `id` = ?,
				  'quer3' =>	'SELECT
						   `tableb`.`col1`,
						   `tableb`.`col2`, 
						   `tableb`.`col3`, 
						   `tablea`.`col1`, 
						   `tablea`.`col2`  
						  FROM 
							`tablea`, 
							`tableb` 
						  WHERE 
							`tablea`.`id` = ? 
						  AND 
							`tableb`.`id` = ?'
			   );
	if(!array_key_exists($key, $queries)){
		$retVal = false;
	}else{
		$retVal = $queries[$key];
	}
}
 



Now below is how we might use this.
Php Code:


function getTableA_Data($id){
  $key	= 'quer1';
  if($query = queries($key)){
	  //Query is now an object, it runs faster, and I can use it over and over in a loop!!!
	$stmt->prepare($query);
  }else{
	  //handle no query available here
  }

  //This will replace '?' with the value I set.  The "i" means integer.  This is secure.  More so than the old way of doing things
  $stmt->bind_param("i", $id);

  //Execute the query
  $stmt->execute();
  $stmt->bind_result($col1, $col2, $id);
	 WHILE($stmt->fetch()){
	   $rows[] = array(
					'col1' => $col1,
					'col2' => $col2,
					'id'   => $id
				  );
	 }
  //Kill our prepared statement and free system resources
  $stmt->close();
return $rows;
}
 


Now if I wanted a data set out of tablea in the rest of me code I just do this

Php Code:

 $tableaRows = getTableA_Data(3);
 


By the way I would never put all that in a function in real world code. I have centralized the handling of all prepared statements in my own wrapper of sorts. I needed to touch on this to make the caching work.

So as it stands. the queries are stored in an array in a function and are served up ass needed. Each query is handled by its own function. But how do we keep from executing the query every time?


Edited by §intå× (05/31/08 03:54 AM)
_________________________
My New site OpenEyes

Top
#45754 - 05/31/08 04:03 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
The answer is simple. First we instantiate our class.

Php Code:

/**
 * Defaults are 
 * hashing		   MD5 
 * time in seconds   3600
 * secure mode	   NULL 
 * cache dir		 NULL
 */
$cache = new cache( 2,	   //hash type (1) md5 (2) Sha1
					3600,	//Timer seconds
					true,	//secure mode
					'php',   //file extensions
					'/path/to/cache/directory' //Cache dir
				   ); 
 
_________________________
My New site OpenEyes

Top
#45755 - 05/31/08 04:19 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Then we pass it into our functions that will do our queries. THis would be a great use of the registry pattern I posted yesterday.
Php Code:

$reg	= new registry();
$cache  = new cache( 2, 3600, true, 'php', '/path/to/cache/directory' ); 
$reg->set('cache',$cache);


function getTableA_Data($id, $reg){
  if(!$rows	= $reg->get('cache')->getArrayCache('quer1'.$id)){
	 if($query = queries('quer1')){
	  //Query is now an object, it runs faster, and I can use it over and over in a loop!!!
	$stmt->prepare($query);
	 }else{
	  //handle no query available here
	 }

	 //This will replace '?' with the value I set.  The "i" means integer.  This is secure.  More so than the old way of doing things
	 $stmt->bind_param("i", $id);

	 //Execute the query
	 $stmt->execute();

	 //Bind the results to variables
	 $stmt->bind_result($col1, $col2, $id);
	 WHILE($stmt->fetch()){
	   $rows[] = array(
					'col1' => $col1,
					'col2' => $col2,
					'id'   => $id
				  );
	 }
	 //Kill our prepared statement and free system resources
	 $stmt->close();
	 $cache->cacheOutArray($rows, 'quer1'.$id);
	 $rows = $cache->getArrayCache('quer1'.$id);
  }
  return $rows;
}
 


So what just happened here? Well when we first enter the functions we call on the cache class method getArrayCache() to see if out cache file exists. If it does and the cache timer has not expired we return the results. If not we process the query and overwrite any cache file with the same name. The file names are created using the query keys and the parameter values we use in our queries. These are concatenated together and then hashed using either MD5 or SHA1.

Okay you drunks. You have code and examples. Hit me with any and all questions. If you improve it, I am interested in what you did. Please post your modified code.
_________________________
My New site OpenEyes

Top
#45756 - 05/31/08 04:32 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Requirements of this class.



Php Code:

define('_VALID_AMS_', 1);
 


You can change '_VALID_AMS_' but you need to find it in my class and change it there too. The above examples are how I use it and find it useful. Please feel free to experiment with it. The code is released under lgpl to use and learn with. It is hoped it will be useful but no guarantees. Use at your own risk. Please throughly test before using in a production environment.



Edited by §intå× (05/31/08 04:33 AM)
_________________________
My New site OpenEyes

Top
#45761 - 05/31/08 08:16 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
Gremelin Offline

Community Owner
*****

Registered: 02/28/02
Posts: 7192
Loc: Portland, OR; USA
For some reason I still feel like i failed... well, in teaching you how to format your code... you have so many un-needed tabs... i didnt' even look for double+ spaces... bad bad bad...
_________________________
Donate to UGN Security here.
UGN Security, Back of the Web, Elite Web Gamers & VNC Web Design Owner

Top
#45762 - 05/31/08 03:23 PM Re: PHP5 OOP - Caching out SQL query results securly [Re: Gremelin]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Much of my code was altered on posting, but it is well formatted here on my desk top. The class itself was coded a bit ago and should look fine to you. Some of my examples are just spaces and you BBS deciding how many. Can't use tab in this post box.

In my haste to get this out I forgot to post an example of what a cache file might look like.
Php Code:


<?php 
/** 
* Cache file created by cacheMgr class 
* 
* You should not edit this file unless you know what you are doing. 
* If you do edit the file and the ads system crashes, just delete 
* this file.  It will be re-built.
* @version	February 13, 2008 - 23:40:06 EST 
* @expires	February 14, 2008 - 23:40:06 EST 
* @author	CAMS::cacheMgr 
*/ 
defined( '_VALID_AMS_' ) or die( 'Restricted access' ); 
$_core_GetPkgNames = '["default","tkt","dorxad"]'; 




From "defined( '_VALID_AMS_' ) or die( 'Restricted access' ); " up to the top, the cache script generates that. You can alter the comments in the head of the document by editiing the private method "getCacheDocBlock()" in my class.

Also if you wanted to edit the constant name inserted into the cache files edit the "prepStrForOut()" method.


getCacheDocBlock() -line # 218
prepStrForOut($outString) - line 257

Line numbers are no good if you edit the file first.

Public methods you can call in your code are
  • getArrayCache($uniquieId)
  • getCache($fileName)
  • cacheOutArray($array, $uniquieId)


I hope you finde my code decently commented and useful. But I offer no guarantees, use at your own risk.
_________________________
My New site OpenEyes

Top
#45763 - 05/31/08 03:40 PM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
I do not see where I mentioned this above.

Php Code:

$cache->cacheOutArray($rows, 'quer1'.$id);

$rows = $cache->getArrayCache('quer1'.$id);
 


In the first method call "$cache->cacheOutArray($rows, 'quer1'.$id)" '$rows' is the array I am caching and 'quer1'.$id is the name of the query and the value of the parameter I will use in the query. "'quer1'.$id" in my class will be processed into either an md5 or sha1. This hash is then used for a file name of the cache file. This helps ensure the file names will be unique. It will also allow for multiple parameter values to be used so that any query may be cached out.

You can have multiple cache object in your code. Maybe you want to have a cache lasting 15 minutes, a cache lasting 1 hrs, and one lasting a day.

Php Code:

//Cache file created using this object will last only 15 minutes
$cache15min = new cache( 2, 900, true, 'php', '/path/to/cache/directory' );

//Cache file created using this object will last only 1 hrs
$cache1hrs = new cache( 2, 3600, true, 'php', '/path/to/cache/directory' );

//Cache file created using this object will last only 24 hrs
$cache1Day = new cache( 2, 86400, true, 'php', '/path/to/cache/directory' );
 


Then just used the methods I defined with the cache object you want to use.

Php Code:

$cache15min->cacheOutArray($membersOnline, 'memOnline'.$param.$param);

$cache1hrs->cacheOutArray($newsFeed, 'newsRssData'.$param.$param2);

$cache1day->cacheOutArray($staffArray, 'getStaffNames'.$param.$param2.$param3);
 


Edited by §intå× (05/31/08 03:48 PM)
_________________________
My New site OpenEyes

Top

Moderator:  §intå×, Gremelin 
Featured Member
Registered: 08/22/14
Posts: 1
Forum Stats
2148 Members
46 Forums
34005 Topics
69172 Posts

Max Online: 1567 @ 04/25/10 02:20 AM
Top Posters
UGN Security 27167
Gremelin 7192
§intå× 3255
SilentRage 1273
Ice 1146
pergesu 1136
Infinite 1041
jonconley 955
Girlie 908
unreal 860
Newest Members
Gecko666, defghi795767, Devo60, ali, lavos
2147 Registered Users
Who's Online
1 registered (Tim050), 298 Guests and 305 Spiders online.
Key: Admin, Global Mod, Mod
Latest News


Donate
  Get Firefox!
Get FireFox!