• Home page
• Blog
• php prepared (parameterized) statements into arrays

php prepared (parameterized) statements into arrays

After a recent security audit of one of our sites we had to do a but of recoding. One particular issue was with sql injection into form fields

As a result I've written a couple of functions that extract database data into arrays from prepared or parametirised queries using the following functions

• mysqli_prepare
• mysqli_stmt_result_metadata
• mysqli_stmt_bind_param
• mysqli_stmt_execute
• mysqli_stmt_bind_result
• mysqli_stmt_fetch
• mysqli_stmt_close

If this page gets any hits, comments or feedback I'll add some more detail.

		
	
	dbConnect ($dbA);
	
	$unsafeInput = $_POST['input_field'];
	$userArray = $dbA->retrieveAllRecordsFromPreparedQuery ("select * from users where name like ? or address like ?", array ("%".$unsafeInput."%", "%".$unsafeInput."%"));
	print_r ($userArray); // use the array here

	$unsafeInput_1 = $_POST['input_field_1'];
	$unsafeInput_2 = $_POST['input_field_2'];
	$unsafeInput_3 = $_POST['input_field_3'];
	$userArray = $dbA->retrieveAllRecordsFromPreparedQuery ("select * from students where forename like ? or surname like ? or postcode like ?", array ("%".$unsafeInput_1."%", "%".$unsafeInput_2."%", "%".$unsafeInput_3."%");
	print_r ($userArray); // use the array here

	function dbConnect(&$dbA) {
		global $dbHost,$dbUser,$dbPassword,$dbName;
		
		$dbA = new dbAccess();		
		$dbStatus = $dbA-> connect($dbHost,$dbUser,$dbPassword,$dbName);
		
		if ($dbStatus == false && $showErrorOnFail==true) {
			exit;
		}
		return ($dbStatus);
	}

	class dbAccess {
		function connect($sql_host_name,$sql_username,$sql_password,$sql_database_name) {
			$this->currentDatabase = $sql_database_name;
			$this->resID = @mysqli_connect($sql_host_name,$sql_username,$sql_password);
			if ($this->resID == FALSE) {
				$this->lastError = "Could not connect to mySQL server";
				return FALSE;
			} else {
				if (@mysqli_select_db($this->resID, $sql_database_name)) {
					return TRUE;
				} else {
					return @mysqli_query($this->resID,"create database $sql_database_name");
					return FALSE;
				}
			}
		}
		
		function retrieveAllRecordsFromPreparedQuery ($sql_query, $params){
			if ($preparedStmt = mysqli_prepare($this->resID, $sql_query)) {	
				$paramPattern =  $this->getPreparedParamPattern ($params);				
				call_user_func_array('mysqli_stmt_bind_param', array_merge (array($preparedStmt, $paramPattern), $params));
				mysqli_stmt_execute($preparedStmt);
				
				$meta = mysqli_stmt_result_metadata($preparedStmt);				
				$fieldNames = array();
				while ($field = mysqli_fetch_field($meta)) {
				   $fieldNames[] = $field->name;
				}
				$resultRow = array($preparedStmt);
				foreach($fieldNames as $value) {
					$resultRow[] = &$$value;
				}
				call_user_func_array('mysqli_stmt_bind_result', $resultRow);
				$result = array();
				while(mysqli_stmt_fetch($preparedStmt)) {
					$thisRow = array();
					foreach($fieldNames as $value) {
						$thisRow[$value] = $$value;
					}
					$result[] = $thisRow;
				}
				mysqli_stmt_close($preparedStmt);
				return $result;
			} else {
				return "ERROR";
			}
		}

		function getPreparedParamPattern ($params){	
			$paramPattern = "";			
			foreach ($params as $thisParam) {					
				if (is_string($thisParam)) {
					$paramPattern .= "s";
				} elseif (is_int($thisParam)) {
					$paramPattern .= "i";
				} elseif (is_float($thisParam)) {
					$paramPattern .= "d";
				} else {
					die("invalid prepare type");
				}
			}
			return $paramPattern;
		}
	}
		

Please rate this article

Click the stars below to give this article a mark out of 10

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 5 / 10

Related Articles

Solving 'This page contains both secure and nonsecure items' errors
21 Jul 2011 | Posted by: Paul Whittington
Having trouble getting rid of 'this page contains both secure and nonsecure items' errors?
PHP upload progress bar
7 Apr 2010 | Posted by: Paul Whittington
Creating a php upload progress bar in PHP is notoriously difficult...

Post your comments...

We would really appreciate any comments or additions that you have. Include a link in your comment and if we think your comment is appropriate we will publish it. If found this article in any way useful we would really appreciate you bookmarking the page with any of the social bookmarking links provided.

Sitemap  |  Terms and Conditions Copyright © 2010 - 2012 Populate Ltd. All Rights Reserved.
Website Design & Development by Populate.it | Company No. 7364755