avoid repeated mysql_real_escape_string?

[PHPycho]

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.

[Weedpacket]

Those would be prepared statements. Look up [man]PDO[/man].

[johanafm]

$sql = "SELECT * FROM table WHERE field1='%s' AND field2 ='%s'"; 

This form is used in conjuntion with sprintf. You could create your own wrapper function to deal with escaping arguments based on the sprintf format specifiers, and then passing the result on to sprintf, but it is an inefficient approach and I believe it should be avoided. E.g

function escapeQuery($qry, $args) {
	// Do note that sprintf takes more format specifiers than just %s and %d...
	$p = '#(?<!%)(?:%|%{3}|%{5})(d|s)#';
	if (preg_match_all($p, $qry, $match)) {
		if (($argCount = count($match[1])) != count($args)) {
			error_log('Argument and value count mismatch for ' . $qry . ' with ' . print_r($args,1));
		}
		else {
			for ($i = 0; $i < $argCount; ++$i) {
				switch ($match[1][$i]) {
					case 'd':
						$args[$i] = (int) $args[$i];
						break;
					case 's':
						$args[$i] = mysql_real_escape_string($args[$i]);
						break;
					default:
						error_log('Argument type not recognized ' . $match[1][$i]);
						$args[$i] = mysql_real_escape_string($args[$i]);
				}
			}
		}
		array_unshift($args, $qry);
		return call_user_func_array('sprintf', $args);
	}
}

$qry = "SELECT * FROM table WHERE field1='%s' AND field2=%d AND field3='%%s' and field4='%%%d'";
$args = array("I'm a string", 5, 80);

echo escapeQuery($qry, $args);

The other and better way to go is with prepared statements, using either mysqli or PDO.

If you go down the mysqli road, you will probably want something to automate result parameter binding

$meta = $stmt->result_metadata();
if ($meta) {
	$fields = $meta->fetch_fields();
	$meta->close();

$result = array();
foreach ($fields as $f) {
	$bind[] = &$row[$f->name];
}

call_user_func_array(array(&$stmt, 'bind_result'), $bind);
while($stmt->fetch()) {
	// $k will be the fieldName, $v its value
	foreach($row as $k => $v)

[NogDog]

http://www.webdeveloper.com/forum/showthread.php?t=223127#3

[PHPycho]

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