[SOLVED] Multiple Inserts in one query and mysql_real_escape_string

[Prodigal Son]

Is it possible to mysql_real_escape_string a big long string that I would use to insert?

I.e., lets say I have this:

foreach ($strs as $str) {
  $name = $str['name'];
  $email = $str['email'];
  $insert_q = "('$name', '$email', NOW())";
  $insert .= $insert_q . ',';
}
$insert = substr($insert, 0, -1); //take off last comma
$query = "INSERT INTO table (name, email, time) VALUES " . mysql_real_escape_string($insert);
mysql_query($query);

I have a feeling that's very wrong. How else should I be doing it?

[abouchoud]

hello,

 

here is an example... you can find it on php.net by searching for the mysql_real_escape_string function!!

 

....

....

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

 

    if(!is_resource($link)) {

 

        echo "Échec de la connexion au serveur\n";

        // ... historisation de l'erreur

 

    } else {

 

        // Annule les effets magic_quotes_gpc/magic_quotes_sybase sur ces variables si ON.

 

        if(get_magic_quotes_gpc()) {

            $product_name        = stripslashes($_POST['product_name']);

            $product_description = stripslashes($_POST['product_description']);

        } else {

            $product_name        = $_POST['product_name'];

            $product_description = $_POST['product_description'];

        }

 

        // Faire une requête sécurisée

        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",

                    mysql_real_escape_string($product_name, $link),

                    mysql_real_escape_string($product_description, $link),

                    $_POST['user_id']);        mysql_query($query, $link);

....

....

[Prodigal Son]

hello,

 

here is an example... you can find it on php.net by searching for the mysql_real_escape_string function!!

 

....

....

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

 

    if(!is_resource($link)) {

 

        echo "Échec de la connexion au serveur\n";

        // ... historisation de l'erreur

 

    } else {

 

        // Annule les effets magic_quotes_gpc/magic_quotes_sybase sur ces variables si ON.

 

        if(get_magic_quotes_gpc()) {

            $product_name        = stripslashes($_POST['product_name']);

            $product_description = stripslashes($_POST['product_description']);

        } else {

            $product_name        = $_POST['product_name'];

            $product_description = $_POST['product_description'];

        }

 

        // Faire une requête sécurisée

        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",

                    mysql_real_escape_string($product_name, $link),

                    mysql_real_escape_string($product_description, $link),

                    $_POST['user_id']);        mysql_query($query, $link);

....

....

Yea, but that's just one insert isn't it? I'm doing multiple inserts. Do I just do:

foreach ($strs as $str) {
  $name = mysql_real_escape_string($str['name']);
  $email = mysql_real_escape_string($str['email']);
  $insert_q = "('$name', '$email', NOW())";
  $insert .= $insert_q . ',';
}
$insert = substr($insert, 0, -1); //take off last comma
$query = "INSERT INTO table (name, email, time) VALUES " . mysql_real_escape_string($insert);
mysql_query($query);

Is it ok to use the mysql_real_escape_string at that point, or does it need to be when you write the query?

[discomatt]

No, you want to escape each value. Escaping the finished string will break the query.

[Prodigal Son]

No, you want to escape each value. Escaping the finished string will break the query.

Yea, I figured it would be something like that. Would this work (and is safe)?

foreach ($strs as $str) {
  $name = mysql_real_escape_string($str['name']);
  $email = mysql_real_escape_string($str['email']);
  $insert_q = "('$name', '$email', NOW())";
  $insert .= $insert_q . ',';
}
$insert = substr($insert, 0, -1); //take off last comma
$query = "INSERT INTO table (name, email, time) VALUES " . mysql_real_escape_string($insert);
mysql_query($query);

[abouchoud]

ok

 

No it is completly wrong. This function is not used correctly. What I understand from that function is that, it protects the special caracters that your $insert string variable contains. But, it has not the ability to deconcatenate the string. Plus, the way you inserted the date in to your $insert string variable  is wrong too!

 

let say you have 3 values to enter as the example you provided us:

 

example :

the $insert string variable now contains :

[email protected]:47:58pm,[email protected]:47:[email protected]:47:58pm

 

I don't see how you can separate you values of each record in you $insert string variable.

 

You need, first of, to find a way to save the values like this:

chad&&[email protected]&&08-07-0804:47:58pm,chris&&[email protected]&&08-07-0804:47:58pm,natasha&&[email protected]&&08-07-0804:47:58pm

 

then after that you have to try to insert your values by deconcatinating you $insert string so it match your table column (name, email, time)

 

 

 

[discomatt]

You got the right idea.. but it's a little messy. Try this one

 

$values = array();
foreach( $strs as $str ) {
$name = mysql_real_escape_string($str['name']);
$email = mysql_real_escape_string($str['email']);
$values[] = "('$name', '$email', NOW())";
}

$query = "INSERT INTO table (name, email, time) VALUES " . implode( ',', $values );
mysql_query($query);

 

implode() will save you from that last comma. Personally, I'd do it like this ( to save a bit of memory )

 

foreach( $strs as $key => $str )
$strs[$key] = 
	"('". mysql_real_escape_string($str['name']) ."', ".
	"'". mysql_real_escape_string($str['email']) ."', ".
	"NOW())";

$query = "INSERT INTO table (name, email, time) VALUES " . implode( ',', $strs );
mysql_query($query);

 

This is assuming you won't use $strs later in the script though... but as you can see, no new variables are created, thus saving a little bit of room

[discomatt]

ok

 

No it is completly wrong. This function is not used correctly. What I understand from that function is that, it protects the special caracters that your $insert string variable contains. But, it has not the ability to deconcatenate the string. Plus, the way you inserted the date in to your $insert string variable  is wrong too!

 

let say you have 3 values to enter as the example you provided us:

 

example :

the $insert string variable now contains :

[email protected]:47:58pm,[email protected]:47:[email protected]:47:58pm

 

I don't see how you can separate you values of each record in you $insert string variable.

 

You need, first of, to find a way to save the values like this:

chad&&[email protected]&&08-07-0804:47:58pm,chris&&[email protected]&&08-07-0804:47:58pm,natasha&&[email protected]&&08-07-0804:47:58pm

 

then after that you have to try to insert your values by deconcatinating you $insert string so it match your table column (name, email, time)

 

Huh?

[abouchoud]

... never mind

 

not to familiar with that function....

sorry!!

 

[Prodigal Son]

You got the right idea.. but it's a little messy. Try this one

 

$values = array();
foreach( $strs as $str ) {
$name = mysql_real_escape_string($str['name']);
$email = mysql_real_escape_string($str['email']);
$values[] = "('$name', '$email', NOW())";
}

$query = "INSERT INTO table (name, email, time) VALUES " . implode( ',', $values );
mysql_query($query);

 

implode() will save you from that last comma. Personally, I'd do it like this ( to save a bit of memory )

 

foreach( $strs as $key => $str )
$strs[$key] = 
	"('". mysql_real_escape_string($str['name']) ."', ".
	"'". mysql_real_escape_string($str['email']) ."', ".
	"NOW())";

$query = "INSERT INTO table (name, email, time) VALUES " . implode( ',', $strs );
mysql_query($query);

 

This is assuming you won't use $strs later in the script though... but as you can see, no new variables are created, thus saving a little bit of room

Thanks, very handy.