any security issues with this mysql_update function?
| From: | Haluk Karamete | Date: | Sat, 14 Jan 2012 03:52:27 +0000 |
| Subject: | any security issues with this mysql_update function? | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-316295@lists.php.net to get a copy of this message | ||
I wrote a function "sql_update" which takes a $db_name, a $table_name,
a $where and finally a $data array where data is authored by using an
associative array which allows easy the pairing of field names and
field values.
This is how I build the data array;
$data = array(
'FirstName' => 'John',
'LastName' => "Smith",
'Age' => 90,
);
and this is how I call the function
sql_update("blueprint2012","test_table","where PersonID=1",$data);
And this does it for me, does it very easy and convenient,
but I've got a concern...
If you kindly take a look at the function that does the work
"sql_update" posted below, therein you will see a
"mysql_real_escape_string" being used in an array_map operation.
The question is would simply having "mysql_real_escape_string" in
there will protect me from a SQLInjection? Is it that good?
Or do you think this kind of stuff should be handled before the
function is called at $data building time?
This approach of course would then nullify the need of using
mysql_real_escape_string within the below function.
I'm inclining towards the idea that the below function *should* just
assume that the data is safe ( and therefore not use
"mysql_real_escape_string" ) and that before I call the function, I
should take care of the SQLInjection stuff more transparently, so that
$data is safe and sound as far as both sqlinjection and htmlencode
against XSS.
But then again, if mysql_real_escape_string does the job well and good
enough, why worry?
what say you?
function sql_update($db_name,$table_name,$where,$data)
{
//dies out if something wrong.
//returns $the_number_of_records_effected, if any
//following 3 lines take care of the connection
bp_conn($db_name,$db_server,$db_username,$db_pass);
$link = mysql_connect($db_server, $db_username, $db_pass) or
die(mysql_error());
mysql_select_db($db_name, $link) or die(mysql_error());
$values = array_map('mysql_real_escape_string', array_values($data));
$keys = array_keys($data);
$i=-1;
$string = "SET ";
foreach ($keys as $item)
{
$i++;
$string = $string . "
" . $item . "='" . $values[$i] .
"', ";
}
//echo "[" . $string . "]";
// [SET FirstName='John', LastName='Smith',
Age='90', ]
$string = bp_cutthelast($string,2) . " " . $where;
//echo "[" . $string . "]";
// [SET FirstName='John', LastName='Smith',
Age='90']
$update_sql_statement = 'UPDATE '.$table_name. " " . $string;
//echo $update_sql_statement;
//outputs UPDATE test_table SET FirstName='John',
LastName='Smith', Age='90' where PersonID=1
if (mysql_query($update_sql_statement,$link ))
{
return mysql_affected_rows ($link);
mysql_close($link);
}
else
{
echo "error SQL FAILS " . mysql_error();
mysql_close($link) ;
die;
return null;
}
}