Be prepared!

I recently revised an old project of mine and found that it was very vulnerable to SQL injection. As you know, prepared statements are the best way to prevent this kind of attack. I long wanted to try this anyway, so I decided to use PDO. Since there were several hundred queries to rewrite and since PDOs are a bit awkward to use I needed something to ease the transition, introducing the psql-function.

Once called, psql will give you

  • the entire result set in a single, two dimensional array if there is one,
  • only the first row as a one dimensional array if you provide the third parameter,
  • the number of affected rows if there isn’t a result set, or
  • the last inserted ID if you provide the fourth argument
  • If the transaction couldn’t be made, false is returned and a error is triggered

Compared to the normal way of issuing a query to the database, psql is rather concise:

// normal
$res = mysql_query('SELECT something FROM somewhere WHERE something = '.mysql_real_escape_string($id)) or die(mysql_error());
$result = mysql_fetch_assoc($res);

// pdo
$dbc = new PDO();
$dbc->prepare('SELECT something FROM somewhere WHERE something =?');
$dbc->bindValue(1,$id);
$dbc->execute();
$result = $stmt->fetch(PDO :: FETCH_ASSOC);

//psql
$result = psql('SELECT something FROM somewhere WHERE something = :id',array(':id'=>$id));
//or, using the shorthand
$result = psql('SELECT something FROM somewhere WHERE something = ?',$id);

psql returns the results as a associative array in a numeric one, like this

array(
    [0]=> array(
        something => some content,
        something_else => some other content,
        ),
    [1]=> array(
        something => some content,
        something_else => some other content,
        )
)

which is very useful if you’re using Smarty.

That also means that you’ll have to loop through the rows differently than you’re may be used to:

Instead of

$res = mysql_query('SELECT something FROM somewhere');
while($result = mysql_fetch_assoc($res)){
    do_stuff($result);
}

you’ll have to write

$res = psql('SELECT something FROM somewhere');
foreach($res AS $result){
    do_stuff($result);
}

Now, if you like this, you can grab yourself a copy and use it right away:

/*
 * Prepares and executes a (simple) prepared statement
 *
 * @param $sql string prepared statement
 * @param $params array params to be bind
 * @param $single_resultset bool if set to 1 psql will give only the first resultset and disregard any other.
 * @return bool, int or array  if transaction fails false, if there is a result set an array of rows, if not just the number of affected rows
 */
function psql($sql, $params = array (), $single_resultset = 0) {
	$dbc = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);

	$stmt = $dbc->prepare($sql);

	if (!is_array($params)) { //this allows a shorthand for single-valued querys
		$params = array (
			1 => $params
		);
	}
	if (isset ($params[0])) { //array is probably numeric and starts at 0 which it shouldn't
		$count = count($params);
		$params_temp = array ();
		for ($i = 1; $i  $val) {
		$stmt->bindValue($name, htmlspecialchars(html_entity_decode($val)));//basic XSS-Protection
	}

	if (!$stmt->execute()) {
		trigger_error('Could not execute statement, errorInfo: ' . join(", ", $stmt->errorInfo()));
		$return = false;
	} else {
		if ($stmt->columnCount() == 0) {
			$rowCount = $stmt->rowCount();
			if ($rowCount == 1) {
				$return = $dbc->lastInsertId();
			} else {
				$return = $stmt->rowCount();
			}
		} else {
			if ($single_resultset) {
				$return = $stmt->fetch(PDO :: FETCH_ASSOC);
			} else {
				$return = array ();
				while ($row = $stmt->fetch(PDO :: FETCH_ASSOC)) {
					$return[] = $row;
				}
			}
		}
	}
	return $return;
}
Note that you’ll have to define DB_HOST, DB_NAME, DB_USERNAME, DB_PASSWORD somewhere with your own database settings and that you’ll have to use PHP 5.1 or higher for PDO to work. Be also aware that you’ll still have to check the data for sanity. Prepared statements protect you against SQL injections, but not against malformed data.

Kudos to Thomas Maetz (german) for the help on this one.