...

View Full Version : avoid repeated mysql_real_escape_string?



PHPycho
01-19-2010, 11:40 AM
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.

JAY6390
01-19-2010, 01:00 PM
If you're comfortable with sprintf consider using my mressf function (mysql_real_escape_string and sprintf in one)


function mressf()
{
$args = func_get_args();
if (count($args) < 2)
return false;
$query = array_shift($args);
$args = array_map('mysql_real_escape_string', $args);
array_unshift($args, $query);
$query = call_user_func_array('sprintf', $args);
return $query;
}Then use it like you would normally

$query = mressf("SELECT * FROM table WHERE field1="%s" AND field2="%s", $field1_value, $field2_value);
It will automatically escape $field1_value and $field2_value for you before inserting them into the query

Dormilich
01-19-2010, 01:08 PM
above approach is free of SQL injection
almost free, there are still ways around that.


$query = mressf("SELECT * FROM table WHERE field1="%s" AND field2="%s", $field1_value, $field2_value);
should throw an "unexpected T_STRING" error due to the " around %s, which close the string.

PHPycho
01-20-2010, 10:22 AM
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

Dormilich
01-20-2010, 11:03 AM
if you want to use Prepared Statements (which is awesome and even more secure that mysql_real_escape_string()), why don’t you use PHP’s built-in DB classes (PDO (http://php.net/pdo), MySQLi (http://php.net/mysqli)), that already provide this functionality?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum