Page 1 of 1
avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:40 am
by PHPycho
Consider:
Code: Select all
$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>
Code: Select all
$sql = "SELECT * FROM table WHERE field1='%s' AND field2 ='%s'";
$result = custom_query($sql, array($field1_value, $field2_value));
2>
Code: Select all
$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.
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:48 am
by VladSun
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 3:09 pm
by tr0gd0rr
I like using a style like Oracle's named binding:
http://us3.php.net/mysql_real_escape_string#89851 :
Code: Select all
// $sql = "SELECT * FROM users WHERE user = :user AND password = :password";
// mysql_bind($sql, array('user' => $user, 'password' => $password));
// mysql_query($sql);
function mysql_bind(&$sql, $vals) {
foreach ($vals as $name => $val) {
$sql = str_replace(":$name", "'" . mysql_real_escape_string($val) . "'", $sql);
}
}
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 4:31 pm
by VladSun
Looks good, but it can't handle NULL values.
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:23 pm
by tr0gd0rr
True. The version I actually use is much more complex and allows nulls, converts true/false to 1/0, creates IN() for arrays and accepts expression objects. Or of course you can use mysqli and replace the named parameters with ? and do real binding. That is probably ideal for most situations.
Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:39 pm
by VladSun
tr0gd0rr wrote:Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.

Absolutely!
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:43 pm
by VladSun
Code: Select all
$sql = str_replace(":$name", "'" . mysql_real_escape_string($val) . "'", $sql);
This could cause problems if the data itself contains a placeholder string.
Re: avoid repeated mysql_real_escape_string?
Posted: Tue Jan 19, 2010 5:50 pm
by tr0gd0rr
Yes. It also fails if you have two parameters that begin the same such as "user" and "user_id" when "user" comes first in the array. You can do a preg_replace_all() to make sure that you are running through the string only once.
Re: avoid repeated mysql_real_escape_string?
Posted: Wed Jan 20, 2010 4:45 am
by 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.
Code: Select all
//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
Re: avoid repeated mysql_real_escape_string?
Posted: Wed Jan 20, 2010 4:52 am
by Eran
There's not much point in checking for the existence of mysql_real_escape_string() on every escape operation, that function has been available since PHP 4.3 which has been available for more than 7 years now.
Re: avoid repeated mysql_real_escape_string?
Posted: Wed Jan 20, 2010 5:13 am
by VladSun
PHPycho wrote:Please have a look and comment/suggest if there is any rooms for improvements.
VladSun wrote:tr0gd0rr wrote:Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.

Absolutely!
