poor-mans-parameterized-queries.php

<?php
	class MySQLException extends Exception 
	{
		private $errNo;
		private $errStr;

		public function __construct($message = null, $errNo = null, $errStr = null)
		{
			$this->message = $message;
			$this->errNo = $errNo;
			$this->errStr = $errStr;
		}

		public function getErrorNumber()
		{
			return $this->errNo;
		}

		public function getErrorString()
		{
			return $this->errStr;
		}

		public function isMissingTable($table)
		{
			return $this->getErrorNumber() == MySQLDatabase::ER_NO_SUCH_TABLE && strpos($this->getErrorString(), ".$table'") !== false;
		}
	};
	
	class MySQLDatabase {
		public function replaceParameters($sql, $parameters, $splitMulti = false, $stripComments = false)
		{
			$multiResult = Array();
			$newSql = '';
			$IN_WS = 0; //whitespace
			$IN_STRS = 1; //string single-quoted
			$IN_STRD = 2; //string double-quoted
			$IN_IDBQ = 3; //identifier back-qouted
			$IN_SL_COMMENT = 4; //single-line comment
			$IN_ML_COMMENT = 5; //multi-line comment
			$IN_WORD = 6; //inside a unqouted identifier or reservered word
			$IN_PARAM = 7; //parameter - the cool part
			$state = $IN_WS;
			$paramName = '';
			for ($i = 0; $i <= strlen($sql); $i++)
			{
				$ignoreChar = false;
				$chr = @$sql[$i];
				$chrnext = @$sql[$i+1];
				switch ($state)
				{
					case $IN_WS:
					case $IN_WORD:
						if ($chr == "'")
							$state = $IN_STRS;
						else if ($chr == '"')
							$state = $IN_STRD;
						else if ($chr == '`')
							$state = $IN_IDBQ;
						else if ($chr == '#')
						{
							$state = $IN_SL_COMMENT;
							if ($stripComments)
								$ignoreChar = true;
						}
						else if ($chr == '-' && $chrnext == '-')
						{
							$state = $IN_SL_COMMENT;
							if ($stripComments)
								$ignoreChar = true;
							else
								$newSql .= '-';
							$i++;
						}	
						else if ($chr == '/' && $chrnext == '*')
						{
							$state = $IN_ML_COMMENT;
							if ($stripComments)
								$ignoreChar = true;
							else
								$newSql .= '/';
							$i++;
						}
						else if ($chr == ':' && $parameters != null)
						{
							$state = $IN_PARAM;
							$paramName = ':';
							$ignoreChar = true;
						}
						else if ($chr == ';' && $splitMulti)
						{
							$multiResult[] = $newSql;
							$newSql = '';
							$ignoreChar = true;
						}
						break;
					case $IN_STRS:
						if ($chr == '\\' && $chrnext == "'")
						{
							$newSql .= '\\';
							$i++;
						}
						else if ($chr == "'")
							$state = $IN_WS;
						break;
					case $IN_STRD:
						if ($chr == '\\' && $chrnext == '"')
						{
							$newSql .= '\\';
							$i++;
						}
						else if ($chr == '"')
							$state = $IN_WS;
						break;
					case $IN_IDBQ:
						//TODO: can the backtick be escaped inside a backtick escaped identifier name?
						if ($chr == '`')
							$state = $IN_WS;
						break;
					case $IN_SL_COMMENT:
						if ($chr == "\r" || $chr == "\n")
							$state = $IN_WS;
						else if ($stripComments)
							$ignoreChar = true;
						break;
					case $IN_ML_COMMENT:
						if ($stripComments)
							$ignoreChar = true;
						if ($chr == '*' && $chrnext == '/')
						{
							if (!$stripComments)
								$newSql .= '*';
							$state = $IN_WS;
							$i++;
						}
						break;
					case $IN_PARAM:
						if (!ctype_alnum($chr) && $chr != '_')
						{
							if (!array_key_exists($paramName, $parameters))
								throw new MySQLException("Parameterized query referenced undefined parameter '$paramName'");
							$newSql .= $this->escapeRecursive($parameters[$paramName]);
							$state = $IN_WS;
							$i--; //reprocess the character
						}
						else
							$paramName .= $chr;
						$ignoreChar = true;
						break;

				}
				if (!$ignoreChar)
					$newSql .= @$sql[$i];
			}
			if ($splitMulti)
			{
				if (trim($newSql) != '')
					$multiResult[] = $newSql;
				return $multiResult;
			}
			else
				return $newSql;
		}

		public function execute($thisSQL, $parameters = null)
		{
			if ($parameters != null)
				$thisSQL = $this->replaceParameters($thisSQL, $parameters);
			/* Making query and return the result-handle */
			$newResult = @mysql_query($thisSQL, $this->Connection);
			if (!$newResult)
				$this->Error('Query failed: '.mysql_error($this->Connection), mysql_errno($this->Connection), mysql_error($this->Connection));
			if (is_resource($newResult))
				$this->Results[] = $newResult;
			return $newResult;
		}
		
		public function executeMulti($sql, $parameters = null)
		{
			$results = Array();
			$queries = $this->replaceParameters($sql, $parameters, true, true);
			foreach ($queries as $query)
			{
				$query = trim($query);
				if ($query == '')
					$results[] = null;				
				else if (preg_match('/^\s*USE\s+(\w+|`(.+?)`)\s*;?\s*$/isD', $query, $matches))
				{
					if (isset($matches[2]))
						$db = $matches[2];
					else
						$db = $matches[1];
					if (!mysql_select_db($db, $this->Connection))
						$this->Error('Query failed: '.mysql_error($this->Connection), mysql_errno($this->Connection), mysql_error($this->Connection));
					$results[] = null;
				}
				else if (preg_match('/^\s*INSERT/i', $query))
				{
					$this->execute($query, $parameters);
					$results[] = $this->insertId();
				}
				else
					$results[] = $this->execute($query, $parameters); 
			}
			return $results;
		}

		public function escape($str)
		{
			return mysql_real_escape_string($str, $this->Connection);
		}

		public function escapeRecursive($value)
		{
			if (!is_array($value))
			{
				if (is_null($value))
					return 'NULL';
				else if (ctype_digit($value) || is_int($value))
					return $value;
				else
					return "'".$this->escape($value)."'";
			}
			$retval = '';
			$first = true;
			foreach ($value as $v)
			{
				if (!$first)
					$retval .= ', ';
				else
					$first = false;
				$retval .= $this->escapeRecursive($v);
			}
			return "($retval)";
		}
	}