• (2 Pages)
  • +
  • 1
  • 2

Be Prepared for your Database Going safe with Prepared Statements Rate Topic: ***** 2 Votes

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Posted 10 June 2010 - 08:07 AM

*
POPULAR

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


Is This A Good Question/Topic? 13
  • +

Replies To: Be Prepared for your Database

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Posted 03 March 2011 - 08:24 AM

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 :).
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 11 May 2011 - 08:50 PM

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

Was This Post Helpful? 0
  • +
  • -

#4 GhandiJones  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 54
  • View blog
  • Posts: 174
  • Joined: 17-March 11

Posted 13 May 2011 - 06:58 PM

You rock.

Write a book on OOP and PDO. I will buy it.
Was This Post Helpful? 1
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 04 June 2011 - 05:32 PM

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 :oops:
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 05 September 2011 - 02:52 PM

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?
Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Posted 05 September 2011 - 10:29 PM

View Poste_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.
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 05 September 2011 - 11:08 PM

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...


Was This Post Helpful? 0
  • +
  • -

#9 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Posted 06 September 2011 - 12:02 AM

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)
Was This Post Helpful? 0
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 06 September 2011 - 03:31 AM

View PostDormilich, 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.
Was This Post Helpful? 0
  • +
  • -

#11 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Posted 06 September 2011 - 04:10 AM

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

Was This Post Helpful? 1
  • +
  • -

#12 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 06 September 2011 - 05:26 AM

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 :)
Was This Post Helpful? 0
  • +
  • -

#13 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Posted 06 September 2011 - 06:27 AM

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.
Was This Post Helpful? 0
  • +
  • -

#14 richmund12  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 38
  • Joined: 10-October 09

Posted 03 August 2012 - 08:45 AM

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,
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9491
  • View blog
  • Posts: 35,826
  • Joined: 12-June 08

Posted 03 August 2012 - 08:49 AM

You may want to check this tutorial:introduction-to-pdo
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2