Mysql real escape string loop multiple variables

Question

Say I want to insert into name, address, city, state, zip values $name, $address Etc.....

How can I run mysql_real_escape_string on each of the variables before inserting. There has got to be a foreach or loop or while method instead of writing out each variable right?

Thanks for the help.

Tom

so if I have

$data = array($address, $city, $name); array_map('mysql_real_escape_string', $data);

and

$columns = "name, address, city, state, zip";

$count = $dbh->exec("INSERT INTO customer($columns) VALUES ($data)");

I get a ton of errors.

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'root'@'localhost' (using password: NO) in /Users/tommyscott45/Sites/experiment/result.php on line 23

now what?

Answer 1

You use sprintf.

For example

$query = sprintf("INSERT into 
                  TABLE name = '%s', address = '%s', city = '%s'",
                 mysqli_escape_string($link, $name), 
                 mysqli_escape_string($link, $address), 
                 mysqli_escape_string($link, $city) );

Or is that not exactly what you were looking for; a way to avoid typing "mysqli_escape_string" over and over again.

Answer 2

This should work.

$data = array($address, $city, $name);
array_map('mysql_real_escape_string', $data);

But you really should not use the mysql extension anymore. Have a look at PDO or mysqli and their support for "prepared statements".

Answer 3

A database error has occurred when trying to invoke mysql_real_escape_string and I see that you're using $dbh->exec() to execute the query. This suggests that you connect to the database with PDO, so you should rather use PDO::quote instead of mysql_real_escape_string.

Moreover, as others have already mentioned, a better way to solve your problem would be to use prepared statements and PDO::prepare.

Answer 4

You have several problems.

First is that you need to assign the output of array_map() to a variable as it doesn't do in-place conversion. Then you need to implode it back to a string.

 $data = "'".implode("', ", array_map('mysql_real_escape_string', $data))."'";

But the bigger problem is that you're hand assembling SQL instead of using a data access layer (unless you're writing one...). A proper data access layer will take the information you're wanting to save, and use its knowledge about where you want to store it to assemble a correct SQL statement, with proper quoting and all.

This is also the impetus behind suggestions to use prepared statements, incidentally, but just using prepared statements is only half of the solution because you would still be assembling SQL statements.