Using Sprintf and Sanitizing data automagically

[karnedge]

I'm setting up my own database object and it's rather simple minded because I hate CURD methods everyone seems to use cuz it limits ORDER BY, not to mention it's a lot more typing I think.

Code: [Select]

if(is_array($data)){
foreach($data as $k => $v){
$data[$k] = $this->sanitize($v);
}
}
$query = @sprintf($queryString,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6]);
$this->result = @mysql_query($query,$this->connection);
So, as an example, you would do...
$db->query("SELECT id FROM users WHERE username = '%s' AND password = '%s',array($username,md5($password)));
etc...

I tried doing loops with the data array to count how many keys there are and place them into the sprintf but it hates me doing that...

Anyone know of a better way to do this?

[Harkins]

It looks like you want data to be either scalar or an array. So that's why the weirdness on the first line here:

Code: [Select]

$data = (is_array($data)) ? $arr : Array($data);
$query = vsprintf($query, $data);
$this->result = @mysql_query($query, $this->connection);


[codestryke]

http://community.bbgamezone.net/index.php/topic,2112.msg13349.html#msg13349

[JGadrow]

As a drop-in replacement for the code that you already have, instead of directly calling the function (because you have a variable number of parameters), you need to call it through call_user_func_array ().

So, to convert your code (comments added to explain logic):

Code: [Select]

// sanitize data
if(is_array($data)){
foreach($data as $k => $v){
$data[$k] = $this->sanitize($v);
}
}

// prepend array with known parameters
array_unshift ($data, $queryString);

// dynamically call sprintf
$query = @call_user_func_array ('sprintf', $data);

// perform query
$this->result = @mysql_query($query,$this->connection);

[karnedge]

As a drop-in replacement for the code that you already have, instead of directly calling the function (because you have a variable number of parameters), you need to call it through call_user_func_array ().

So, to convert your code (comments added to explain logic):

Code: [Select]

// sanitize data
if(is_array($data)){
foreach($data as $k => $v){
$data[$k] = $this->sanitize($v);
}
}

// prepend array with known parameters
array_unshift ($data, $queryString);

// dynamically call sprintf
$query = @call_user_func_array ('sprintf', $data);

// perform query
$this->result = @mysql_query($query,$this->connection);

Makari, you are my hero. haha that call_user_func_array() was exactly what I needed. i took out the array_unshift() part and used func_get_args() instead... which means the QueryString is already at the beginning of the array already. Not to mention this allows me to make my Query usage even simplier...

An Update to my Example:
$db->query("SELECT id FROM users WHERE username = '%s' AND password = '%s'",$username,md5($password));

so now you dont even need to do an array() for the second argument, it will automagically get all the data it needs.

Thanks Makari and everyone else!


Here's the code. I think I could have it skip the first array a bit simpler (otherwise it will sanitize the query itself) but my mind isn't in the right place at the moment:

Code: [Select]

$data = func_get_args();
foreach($data as $k => $v){
if($k != 0) $data[$k] = $this->sanitize($v);
}
$query = @call_user_func_array('sprintf',$data);
$this->result = @mysql_query($query,$this->connection);


[karnedge]

Thanks codestryke for the link as well, that class helped remind me of a couple of errors to handle.

Let me know what you think or even if something is vulnerable or susceptible to error.

Code: [Select]

<?php
/**
 * @version 1.0.3
 * @package ctrlGames Framework
 * @copyright Copyright (C) 2009 - ctrlGames, Inc. All rights reserved.
 * @license GNU Affero General Public License, see LICENSE.PHP
 */
 
// no direct access
defined('_CGFW') or die('Restricted Access');

class Database {
private $connection; // Active Connection
public $result; // Results retrieved and saved
public $queryCounter = 0; // Number of queries made
public $totalTime = 0; // Amount of time queries took
private $debug = true; // Debug displays the queries

public function __construct(){
global $error;
$this->connection = @mysql_connect(DB_HOST,DB_USER,DB_PASS);
if(!is_resource($this->connection)) $error->setError('mysql',"Unable to connect to MySQL.");
if(!@mysql_select_db(DB_NAME,$this->connection)){
@mysql_close($this->connection);
$error->setError('mysql',"Unable to select database.");
}
}

/**
 * Queries the database with the string provided
 * @return  void
 */
public function query($queryString){
global $error;
$startTime = $this->getMicroTime();
$this->queryCounter++;
$data = func_get_args();
unset($data[0]);
foreach($data as $k => $v){
$data[$k] = $this->sanitize($v);
}
$query = vsprintf($queryString,$data);
$this->result = @mysql_query($query,$this->connection);
$this->totalTime += $this->getMicroTime() - $startTime;
if(mysql_errno($this->connection)) $error->setError('mysql',mysql_error($this->connection)."<br /><blockquote>$query</blockquote>");
if($this->debug) echo $this->queryCounter." (".round($this->totalTime,6)."ms): $query <br />";
}

/**
 * Results
 * @params $how = 'i' for numeric array, 'a' for associative array, null for both
 * @return  (array) fetch array for Select query
 */
public function getResults($how = null){
switch($how){
case 'i': return @mysql_fetch_row($this->result);
case 'a': return @mysql_fetch_assoc($this->result);
default: return @mysql_fetch_array($this->result);
}
}

/**
 * Affected
 * @return  (int) affected_rows for last Insert, Update or Delete query
 */
public function getAffected(){
return @mysql_affected_rows($this->connection);
}

/**
 * Rows
 * @return  (int) num_rows for last Select query
 */
public function getRows(){
return @mysql_num_rows($this->result);
}

/**
 * Id
 * @return  (int) auto-increment ID for last Insert query
 */
public function getId(){
return @mysql_insert_id($this->connection);
}

/**
 * MicroTime
 * @return  (float) actual time in milliseconds
 */
private function getMicroTime() {
list($usec,$sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}

/**
 * Santizes data to keep out the SQL injections
 * @return  (string) $data
 */
private function sanitize($data){
$data = trim($data);  // Remove whitespace
if(get_magic_quotes_gpc()){ // Stripslashes if magic_quotes_gpc is enabled
$data = stripslashes($data);
} 
return mysql_real_escape_string($data);
}
}
?>
With this you can use it very easily (keep in mind I do have my error handling class in there as well):

$db = new Database;
$db->query("SELECT id FROM users WHERE id = '%s' AND username = '%s'",1,'admin');
$db->query("UPDATE users SET email = '%s' WHERE id = '1'",$newemail);
$db->query("INSERT INTO users(username,password,time) VALUES ('%s','%s','%s')",$username,md5($password),time())

OR you can just only one argument and it wont spazz out:
$db->query("SELECT COUNT(id) FROM users WHERE time > 500");

So now you can use the query without changing your conversion assignments. (ie; %f for float, %u for integers... whatever, i'll probably never use anything other than %s)

If you like it, I'll post it in the Scripts Vault (when I have access ha) or the Articles forum.

[Harkins]

I think vsprintf is a drop-in replacement for sprintf that would allow you to do away with the call_user_func.

[karnedge]

I think vsprintf is a drop-in replacement for sprintf that would allow you to do away with the call_user_func.

Wow, I totally didn't see your post... you're right, that's much cleaner to use that instead so I updated my code and the previous post as well.

added the $queryString back to the arguments and set it up with the following (basically):

Code: [Select]

public function query($queryString){
$data = func_get_args();
unset($data[0]);
foreach($data as $k => $v){
$data[$k] = $this->sanitize($v);
}
$query = vsprintf($queryString,$data);
$this->result = @mysql_query($query,$this->connection);
}

wham, bam, thank you ma'am.

[JGadrow]

I think vsprintf is a drop-in replacement for sprintf that would allow you to do away with the call_user_func.

Yup, I missed the post too Much better solution, calling in that way will make the script perform slightly better since it doesn't need to lookup the function first

Good call, Harkins!

[Harkins]

Rule of thumb for c-like languages: there's always another printf function.