Auto escaping for sql?

Consider:

$sql = "SELECT * FROM table WHERE field1='".mysql_real_escape_string($field1_value)."' AND field2 ='".mysql_real_escape_string($field2_value)."'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
//fetching goes here..
}

above approach is free of SQL injection but has tedious job of writing mysql_real_escape_string() for every field values.
I would like to know the good approach for auto escaping,so that i don’t have to write those escaping manually.
I have seen some styles like:
1>

$sql = "SELECT * FROM table WHERE field1='%s' AND field2 ='%s'";
$result = custom_query($sql, array($field1_value, $field2_value));

2>

$sql = "SELECT * FROM table WHERE field1=? AND field2 =?"; //without using quotes which will be auto detected & quoted accordingly
$result = custom_query($sql, array($field1_value, $field2_value));

Note: above doesn’t uses prepared statement.

what will the custom_query() function look like ?
anybody has used similar function?

Thanks in advance for the valueable suggestion.

I’d argue that it takes the same amount of time to switch to pdo as switch to some custom escaping/binding function.

Why don’t you use prepared statements?

If you have some legacy code, then open it and read it. But if this is theory, it belongs in the waste bin.

Sorry no prepared statement. As the server doesn’t have mysqli, pdo extension.
Any idea?

Get a new host. Or a new server admin.

If this is legacy code, then you can read it and find out what is going on, if not, then you are trying to design an app for 5 years ago.

is mysql extension is dead from PHP5 onwards?

It’s not dead, just no longer packaged with php (for licensing reasons mainly mainly).

PHPycho, you know about prepared statements. If your server isn’t up to scratch, then you need to fix that. You aren’t a noob that read some tutorial and went off on the wrong tangent. If your host doesn’t support mysqli or pdo then they fail … hard.

Yes you can support placeholders manually.
Write a parser and substitute placeholder with value. this way can be more flexible than native ones.

I see no big deal with mysql_real_escape_string though.
When we have large amount of fields, we can use automated processing with an array.
When we need to use only one or two fields, I see no problem with

$field1_value=dbq($field1_value);
$field2_value=dbq($field2_value);
$sql = "SELECT * FROM table WHERE field1=$field_value AND field2 =$field2_value";

or sprintf use.

Are you using php4?

I have made one such utility regarding automatic sql escaping:
Please have a look and comment/suggest if there is any rooms for improvements.

//utility functions
function escape($input){
	if(is_array($input)){			
		return array_map('escape', $input);		
	}else{			
		$input = trim($input);	
		switch (gettype($input)){
			case 'string'	:	
				$input = "'".escapeString($input)."'";
				break;
			case 'boolean'	:	
				$input = ($input === FALSE) ? 0 : 1;
				break;
			default			:	
				$input = ($input === NULL) ? 'NULL' : $input;
				break;
		}			
		return $input;
	}		
}

function escapeString($input){
	if(function_exists('mysql_real_escape_string')){	
		$input = mysql_real_escape_string($input);
	}elseif (function_exists('mysql_escape_string')) {
		$input = mysql_escape_string($input);
	}else{
		$input = addslashes($input); 
	}
	return $input;
}

function prepareBindSql($sql, $binds){
	if (false === strpos($sql, '?'))	{
		return $sql;
	}
	
	if ( ! is_array($binds)){
		$binds = array($binds);
	}
	
	foreach ($binds as $val){
		$val = escape($val);
				
		// Just in case the replacement string contains the bind
		// character should be temporarily replaced by a marker
		$val = str_replace('?', '{%bind_marker%}', $val);
		$sql = preg_replace("#".preg_quote('?', '#')."#", str_replace('$', '\\$', $val), $sql, 1);
	}

	return str_replace('{%bind_marker%}', '?', $sql);
}

function customQuery($sql, $binds = false){
	if(false !== $binds){
		$sql = prepareBindSql($sql, $binds);
	}
	return mysql_query($sql);	
}

//usage:
$sql = "SELECT * FROM table WHERE int_field = ? AND string_field = ?";
$result = customQuery($sql, array($int_field_value, $string_field_value)); //this will auto escape & quote according to data types

Thanks

It appears, that if your binding is not a string, null or Boolean you don’t apply any escaping whatsoever assuming it is an int.

Too slack for my liking, although it does look similar to CodeIgniters implementation, not that this adds merit. :smiley:

Yes exactly, the code looks similar to codeigniter as the main concept is from it.
What are the things that seem missing in:

function escape($input){
    if(is_array($input)){            
        return array_map('escape', $input);        
    }else{            
        $input = trim($input);    
        switch (gettype($input)){
            case 'string'    :    
                $input = "'".escapeString($input)."'";
                break;
            case 'boolean'    :    
                $input = ($input === FALSE) ? 0 : 1;
                break;
            default            :    
                $input = ($input === NULL) ? 'NULL' : $input;
                break;
        }            
        return $input;
    }        
}

2 faults I see in this code: is_array() and gettype()
What’s the use of the word “Array” in your SQL query? :wink:
And gettype will always return “string” for every variable from outside. Because they’re always of string type.

And one improvement.
I don’t think that long sets of “name=?, date=?, price=?” are usable.
For the insert and update queries it is better to use separate function. Which takes as arguments table name, an associative array with data and an array with allowed field names. Last one can be omitted and fetched from the table automatically.

Suppose:

$sql = "SELECT * FROM table WHERE int_field = ? AND string_field = ?";
$result = customQuery($sql, array((int)$int_field_value, (string)$string_field_value));

then i think gettype will work.

escape() is made for general purpose too. so that we can recursively escape data.
for example:

escape($_POST);
escape($_GET);

Thanks

then i think gettype will work.

Yes, it will. but you added another matter. And made thing more complicated, with intention to make it ease :slight_smile:
It is very common pitfall. With some more of such patches you’ll end up with “smarty”, which does the same as original code but with different syntax.
I still in doubts for this function. If you want to support placeholders of different types, it is better to make named ones. like ?d for numbers and and ?a for arrays (which one can be extremely useful)

escape() is made for general purpose too.

do not mix matters. And escape($_POST); is just wrong by design. SQL function shouldn’t be applied to HTTP array

Reinvent the wheel, I say. Lets start by making it round!

is my above code is not good(better)?
if not, that’s why i have posted the code here for refinements.

it’s not the matter of reinventing the wheel. you shouldn’t forget the phrase"Necessity is the mother of invention. "

Thanks

Off Topic:

You know, I actually commend you trying to learn this.

Sure, you’re re-hashing old techniques which have been replaced with better implementaions, but knowing the pitfalls and the reason(s) these approaches have been left behind matters.

It matters a lot.

Good for you, and long may it continue I say. :cool:

I would like to mention some points.
“comparison is done among similar things. like an apple can’t be compared with mango,
but an apple from one country can be considered with that apple from different country.”

Same rule applies in my case here;)
I am asking about the code refinement and you are showing me the another path.
Yes that’s good to show the path to those who lost their path but not to those who knows which path is for what.
I agree that PDO, mysqli are the next generation techniques but we shouldn’t forget the client too which are actually GOD for programmers.

It obviously takes a lot of time just to switch the mysql implementation to pdo or mysqli for an existing framework that is running in most of the sites.

Hope if someone share some idea regarding on my auto escaping functionality for refinement(The code is mentioned above)

Thanks

It’s actually taken me a relatively short amount of time to switch to using prepared sql statements.

I started by using my own database class to ease some of the burdon of performing the queries. This allowed me to abstract away the requests (query, numRows, fetch) as well as some of the error-checking work:


$expiry = '';
$sql = 'SELECT Expiry from Person WHERE ID = ' . intval($personId);
$result = $db->query($sql);
if ($db->numRows($result) > 0) {
    $row = $db->fetch($result);
    $expiry = $row['Expiry'];
}
return $expiry;

When checking out PEAR’s MDB2 class I realised that the whole process was very similar to what I’m already doing. I just needed to pass in a different $db object and with a few tweaks (the result now provides fetching methods) we’re away:


$expiry = '';
$sql = 'SELECT Expiry from Person WHERE ID = ' . $db->quote($personId, 'integer');
$result = $db->query($sql);
if ($result->numRows() > 0) {
    $row = $result->fetchRow());
    $expiry = $row['Expiry'];
}
return $expiry;

I could even get rid of the $row part by replacing those two lines with just:


$expiry = $result(fetchOne());

But those are only normal queries. What about prepared data objects? The prepare/execute part of the process was a little bit different, but the rest remained the same:


$expiry = '';
$sth = $db->prepare(
    'SELECT Expiry from Person WHERE ID = ?',
    array('integer'),
    MDB2_PREPARE_RESULT
);
$result = $sth->execute($personId);
if ($result->numRows() > 0) {
    $expiry = $result->fetchOne());
}
return $expiry;

Then I realised that the database object provides a handy method to do all of this with one call to a single method. The standard SQL query would be:


return $db->queryOne(
    'SELECT Expiry from Person WHERE ID = ' . $db->quote($personId, 'integer')
    , array('timestamp') // result type
);

And the prepared version of the code would be:


$db->loadModule('Extended');
return $db->extended->getOne(
    'SELECT Expiry from Person WHERE ID = ?',
    array('timestamp'), // returned type
    array($personId), // params
    array('integer') // param type
);