Re: any security issues with this mysql_update function?

On Fri, Jan 13, 2012 at 7:52 PM, Haluk Karamete <halukkaramete@gmail.com> wrote: > 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 v„ü…•þN|´kqÚ > Åý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; >        } > > } > Use MySQLi library and simplify your life [1]. Best regards, Tommy [1] http://php.net/class.mysqli and http://php.net/class.mysqli-stmt and http://php.net/class.mysqli-result