[Dormilich]

Be Prepared for your Database

you should read Connection to the MYSQL using OOP first.

This article shall introduce you to Prepared Statements, which is (IMO) the ultimate weapon against SQL Injection attacks.

Prepared Statements are a kind of SQL template stored at the server. Once the template is parsed by the SQL engine, you only need to feed the data and the server sends you the appropriate results. This template approach is also the base of its security, bacause SQL injection attack exploit the SQL string by inserting SQL code where the data should be. If you separate SQL syntax and data—there’s nothing left to exploit.

The database class I’m about to introduce works similar to a Singleton class, although it does not fetches anything for you. I use one of the two database classes in PHP, that are able to do Prepared Statements: PDO (the other one is MySQLi, but I personally find PDO more versatile when it comes to fetching data). The good part about PDO is, PDO does all the hard work for you, connecting, closing connections, you don’t need to worry about that. You’re not even restricted to MySQL.

prerequisites:
don’t forget to define and include the DB_SERVER, DB_NAME, DB_USER & DB_PASS constants beforehand and to make sure the classes are loaded.

I uncommented the Exception Logging, which you can read about in Make your PHP-Errors exceptional

<?php

abstract class DB
{
	/**
	 * @var (PDO) $PDO              PDO DB object
	 * @var (array) $PS             PDOStatement object storage array
	 */
	private static $PDO = NULL;
	private static $PS  = array();
	
	/**
	 * close DB connection on script end
	 *
	 * @return (void)
	 */
	function __destruct()
	{
		self::$PDO = NULL;
	}
	
	/**
	 * connect to the DB using PDO in a Singleton like pattern.
	 *
	 * @return (void)
	 * @throws (Exception)          DB connection failed
	 */
	public static function connect()
	{
		if (self::$PDO === NULL)
		{
			try 
			{
				$dsn = 'mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME;
				self::$PDO = new PDO($dsn, DB_USER, DB_PASS);
			}
			catch (PDOException $pdo)
			{
			//	ErrorLog::add($pdo, __METHOD__);
				$emsg = "Failed to connect to database.";
				throw new Exception($emsg, 0, $pdo);
			}
		}
	}

	/**
	 * create the Prepared Statements and put them in an array.
	 *
	 * @param (string) $index      name of the PS
	 * @param (string) $sql        SQL string
	 * @return (bool)              false on PDO Error
	 * @throws (Exception)         array index already occupied
	 */
	public static function prepare(
		$index, 
		$sql
	)
	{
		# do not overwrite an existing Statement
		if (isset(self::$PS[$index])) 
		{
			$emsg = "Index [$index] is already in use.";
			throw new Exception($emsg, 1);
		}
		
		self::connect();
		
		try 
		{
			self::$PS[$index] = self::$PDO->prepare($sql);
		}
		catch (PDOException $pdo)
		{
		//	ErrorLog::add($pdo, __METHOD__);
			return false;
		}
		return true;
	}

	/**
	 * execute a Prepared Statement using the passed parameters.
	 * supported input data types: string, int, bool, null
	 *
	 * @param (string) $index      name of the PS to execute
	 * @param (array) $param       parameters for the query
	 *                             [array()]
	 * @return (mixed)             PDOStatement,
	 *                             false on
	 *                              - connection not available
	 *                              - Prepared Statement not available
	 * @throws (Exception)         Statement not available
	 * @throws (Exception)         parameter could not be passed
	 * @throws (Exception)         error in DB query
	 * @throws (Exception)         something wrong with the result
	 */
	public static function execute(
		$index, 
		Array $param = array()
	)
	{
		if (self::$PDO === NULL or !isset(self::$PS[$index]))
		{
			$emsg = "Index [$index] unavailable.";
			throw new Exception($emsg, 1);
		}
		
		# prepare parameters
		foreach ($param as $key => $val)
		{
			# correct numeric input key
			if (is_int($key)) ++$key;
			
			# get PDO_PARAM_* type
			$type = self::getValueType($val);
			
			# set value
			$chk = self::$PS[$index]->bindValue($key, $val, $type);
			
			# test function return value
			if (!$chk)
			{
				$emsg = "Parameter '$key' in [$index] failed to bind.";
				throw new Exception($emsg, 2);
			}
		}

		try 
		{	# finally execute statement
			$chk = self::$PS[$index]->execute();
		}
		catch (PDOException $pdo)
		{
		//	ErrorLog::add($pdo, __METHOD__);
			
			$emsg = "PDO-Error while executing Prepared Statement [$index].";
			throw new Exception($emsg, 3, $pdo);
		}
		
		if ($chk === false)
		{
			$emsg = "Result Error in Prepared Statement [$index].";
			throw new Exception($emsg, 3);
		}
		
		return self::$PS[$index];
	}

	/**
	 * get a PDO_PARAM_* type for the parameter value. supported types
	 * are: string, integer, boolean & null (blob for PHP 6). 
	 *
	 * @param (mixed) $value       value whose type is to be determined
	 * @return (int)               PDO_PARAM_*
	 * @throws (Exception)         data type is not detectable
	 */
	private static function getValueType(
		$value
	)
	{
		if (is_int($value))
			return PDO::PARAM_INT;
		if (is_string($value) or is_numeric($value)) 
			return PDO::PARAM_STR;
		if (is_bool($value))
			return PDO::PARAM_BOOL;
		if (is_null($value))
			return PDO::PARAM_NULL;
	#	if (is_binary($value)) return PDO::PARAM_LOB; // PHP 6
		
		$emsg = "Datentyp of parameters (" . gettype($value) . ") is currently not supported.";
		throw new Exception($emsg, 1);
	}
}

Some comments about PDO. PDO passes data according to the specified datatype (e.g. string, int). There is a method in the DB class that does that for you, so if you pass "5" (string) to an TINYINT field, it may cause an error. This is important since all data from $_GET, $_POST, etc. are strings!

How would you use this class? An Example:

// a query, which could be used to get user preferences
try
{
    $sql = "
        SELECT `user_pref_color`, `user_pref_bgcolor` 
        FROM   `user_preferences`
        WHERE  `user_name` = :name AND `user_pw_hash` = :pass";

    // prepare statement
    DB::prepare("user_prefs", $sql);

    // user input data
    $name = $_POST['user'];
    $hash = hash('ripemd160', $_POST['password']);

    // query the DB
    $stmt = DB::execute("user_prefs", array(":pass" => $hash, ":name" => $name));

# you see right, there is no input validation. the data are directly passed to the
# DB class. NEVER EVER do that for a standard SQL query, really.

    // the following code comes from PDO handling and I can cover that in another article.
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $row = $stmt->fetch(); 

    // now you have $row['user_pref_color'] and $row['user_pref_bgcolor'] at your service
}
catch (Exception $e)
{
    // notify the user that there was an error, but keep the details to you.
    echo '<p>There was an Error while executing your task. We will handle the problem in due time.</p>';

    // here should follow Exception logging, if you’ve not done it yet.
}

You may have noted that I don’t test explicitly, whether there is a result that I can fetch … I’ve already done that—in the DB::execute() method.

Alltogether this class helps you securing and cleaning up your code.

What remains is to wish you a happy coding.

Dormi

[Jstall]

Great example of a PDO wrapper. I've been thinking about how to do this with added functionality. Right now I am just using a simple singleton that returns the PDO object itself and doesn't do any of the querying and such. I've been wanting to add some functionality and this has given me some ideas.

I found it more difficult to write a PDO wrapper than a mysql_* one as PDO already has so much functionality on it's own.

Another great PDO article from Dormilich. You know you've pretty much become the definitive word when it comes to PDO around here .

[e_i_pi]

Great article and thanks once again for sharing your expertise with PDOs. Every time I read one of your articles, I read again, and again, learning more with each step. Your work is much appreciated

This post has been edited by e_i_pi: 11 May 2011 - 08:51 PM

[GhandiJones]

You rock.

Write a book on OOP and PDO. I will buy it.

[e_i_pi]

Just a quick note here. Placeholders cannot be used for table names or row names. Something like this will not work:

$sql = "SELECT COUNT(*) FROM `:TableName`";
DB::prepare("RowCountQuery", $sql);
$stmt = DB::execute("RowCountQuery", array(":TableName" => "myTable"));

Failure will occur on line 3. I found this out the hard way

[e_i_pi]

Hi Dormilich, I've been using this class for a little while now, and I've noticed that unparameterised PS's that are executed twice produce exceptions. Append this code to the abstract class DB code to see what I mean:

DB::Prepare('a', 'SELECT COUNT(:id) FROM MyTable');
$a1 = DB::Execute('a', array(':id' => 'ID'));
$a2 = DB::Execute('a', array(':id' => 'ID'));  // Runs fine

DB::Prepare('b', 'SELECT COUNT(*) FROM MyTable');
$a1 = DB::Execute('b');
$a2 = DB::Execute('b');  // Produces exception "Result Error in Prepared Statement b"
?>

I realise executing twice in a row like this is counter-intuitive, but in my code it's a Model method that's being called twice (by two different Controllers).

Could it be something to do with closeCursor() not being called anywhere?

[Dormilich]

e_i_pi, on 05 September 2011 - 11:52 PM, said:

Could it be something to do with closeCursor() not being called anywhere?

according to the Manual, yes.

[e_i_pi]

Righto. I've gotten round the problem by flushing the set before execution, like this:

	public static function Execute(
		$index, 
		Array $param = array()
	)
	{
		if (self::$vPDO === NULL or !isset(self::$vPS[$index]))
		{
			$emsg = "Index [$index] unavailable.";
			throw new Exception($emsg, 1);
		}
		
		// Flush out the current index if the statement does not contain bound values
		if (empty($param))
		{
			do {
				self::$vPS[$index]->fetchAll();
			} while (self::$vPS[$index]->nextRowSet());
		}

		...etc...

[Dormilich]

why using nextRowSet()? afaik fetchAll() already fetches all available results (not only the current row).



PS: method names should start lowercase (cf. userland naming guide)

[e_i_pi]

Dormilich, on 06 September 2011 - 01:02 AM, said:

why using nextRowSet()? afaik fetchAll() already fetches all available results (not only the current row).

I've tried it both ways, and it only works with the do...while statement. I got the code from the Manual.

[Dormilich]

well, to be honest, the class is quite old now. the version I use now uses internal counters for fetch and execute, so I would hook in there, if I ever had the problem.

note: the code you referenced is about SQL Stored Procedures. additionally, UPDATE/INSERT/CREATE/ALTER queries do not produce a result set either (not sure if fetching a result set from such a query would cause an error).

This post has been edited by Dormilich: 06 September 2011 - 04:17 AM

[e_i_pi]

Hmm good point. I might have to review the class and come to a thorough understanding of it to get it ready for what I'm going to throw at it. I'm still pretty new to the PDO/PS concept... looks like it's back to the drawing board for a little while

[Dormilich]

the current version (single database) uses 3-4 separate classes (a Factory class for preparing, a class for fetching, a class for execution (of Prepared Statements) and an iterator class) where there is basically an object for each PDOStatement. essentially it works like an extension of the PDO classes to make the handling way easier for 90% of the use cases.

[richmund12]

Hi!


I'm trying to learn about connecting to db using PDO... Can you/anyone direct me to a working link about: Connection to the MYSQL using OOP << It looks like the link/topic is no longer found.

thanks,

[modi123_1]

You may want to check this tutorial:introduction-to-pdo