PHP security and typecasting

[Jonob]

I am using MySQL as a backend to a flex project, with PHP+AMFPHP as the interface.

 

From a security point of view, I am in the process of typecasting variables that are passed from flex to my php services. For example, if its a get (i.e. sql SELECT) function against an _id field, then I just do something simple like the following, before passing it into my sql statement:

(int)$company_id;

 

For strings, I use something like:

function clean_string($string) 
{
trim($string);
escapeshellarg($string);

// Stripslashes if magic quotes is on
if (get_magic_quotes_gpc()) 
{
	$string = stripslashes($string);
}

// Clean if not integer
if (!is_numeric($string) || $string[0] == '0')
{
	$string = mysql_real_escape_string($string);
}
return $string;
}

 

My question:

In many cases, I am running INSERT queries based on an array of variables that is passed to PHP. Should I use the clean_string() function on each individual string variable, or can I run it on the whole constructed $sql string (i.e. after all the variables have been passed into the $sql string, but before its been executed)?

[Mark Baker]

Individual fields/variables.

 

If you run it against a valid statement like

INSERT INTO tableName (fieldName1,fieldName2) VALUES ('string1','string2');

you'll end up escaping the valid/essential strings around your values to insert

[rhodesa]

no...you have to clean the data....you don't want to clean the entire SQL statement as that will cause errors. to make things easier, you can try something like array_map() or array_walk()

[RussellReal]

rhodesa is right, array_map or array_walk

 

what those functions do is loop through an array, and apply a user callback function to the values and inside you can change the values then returns the new array

 

for example:

 

<?php
  function awalk($value) {
    return mysql_real_escape_string($value);
  }

  // $array will be your array that you want to escape all of them
  $array = array_map("awalk",$array);
?>

[rhodesa]

using the original posted code, it would be:

 

$clean_array = array_map('clean_string',$array);

[Jonob]

Thanks for the replies.

 

The vast majority of my array variables are either int or float, so I would typecast those individually, such as

(int)$data['company_id'];
(float)$data['some_value'];

 

I fully understand the array-walk or array_map, but I think its less resource intensive for me to just 'clean' the strings individually, since very few of the variables are going to be string type.

clean_string($data['description']);

 

For arrays that have a higher proportion of strings, then I would certainly use array_walk or array_map - thanks for the advice 

[Daniel0]

Another option is to use prepared statements:

 

// $db is an instance of PDO

$stmt = $db->prepare('INSERT INTO people (name, phone_number) VALUES(:name, :phone_number)');

$stmt->execute(array('name' => 'Daniel', 'phone_number' => 1234));
$stmt->execute(array('name' => 'John Doe', 'phone_number' => 4321));

// OR

$stmt = $db->prepare('INSERT INTO people (name, phone_number) VALUES(?, ?)');

$stmt->execute(array('Daniel', 1234));
$stmt->execute(array('John Doe', 4321));

 

MySQLi has a syntax for this as well, but I don't use PDO.