Bulk INSERT foreach statement

Question

I want to bulk insert all POST data without having to individually type each name/field. Is the last line that has the mysql INSERT correct? Also I don't have to reprocess mysql_real_escape_string() again for the INSERT correct?

if (is_array($_POST['add']))
   foreach ($_POST['add'] as $key => $value) 
   $_POST['add'][$key] = mysql_real_escape_string(stripslashes($value));

   mysql_query("UPDATE mem SET m_".$key."='".$value."' WHERE m_id=$id");

.... more code

   mysql_query("INSERT INTO meminfo m_".$key." VALUES '".$value."'");

Please don't build SQL by concatenating strings. http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — idstam, Dec 09 '11 at 09:23
@idstam he *have* to concatenate. there is no way to avoid it. that's the very point of the question. — Your Common Sense, Dec 09 '11 at 09:34
Please remember to escape key as well, you never know to what lengths an attacker will go. — Nightwolf, Dec 09 '11 at 13:03

score 1 · Accepted Answer · answered Dec 09 '11 at 09:33

1

This code is injection-prone.

You have to whitelist your keys for protection.
Here is a function to produce SET statement for the mysql queries.

function dbSet($fields, $source = array()) { 
  $set=''; 
  if (!$source) $source = &$_POST; 
  foreach ($fields as $field) { 
    if (isset($source[$field])) { 
      $set.="`$field`='".mysql_real_escape_string($source[$field])."', "; 
    } 
  } 
  return substr($set, 0, -2);  
}

used like this

$fields = explode(" ","name surname lastname address zip fax phone"); 
$query  = "INSERT INTO $table SET ".dbSet($fields,$_POST['add']); 
$fields = array("foo","bar"); 
$query  = "UPDATE $table SET ".dbSet($fields,$_POST['add'])." where id=".intval($id);

answered Dec 09 '11 at 09:33

Your Common Sense

156,878
40
214
345

questions about the $fields var, do I always have to write out all the field names or can I do `m_".$_POST['add']."` – acctman Dec 09 '11 at 18:15
1

if you do $_POST['add'] you will allow an SQL injection. – Your Common Sense Dec 09 '11 at 18:23
Thanks for responding back to my follow-up question and helping make my code more secure. -thanks – acctman Dec 09 '11 at 19:47

score -1 · Answer 2 · answered Dec 09 '11 at 09:58

You can do something like this:

$fields = $values = $set = '';

foreach ($_POST as $key=>$value) {
  $fields .= '`fld_' . $key . '`,';
  $values .= '"' . mysql_real_escape_string($value) . '",';
  $set .= '`fld_' . $key . '` = "'  . $mysql_real_escape_string($value) .  '",';
}

$fields = substr($fields, 0, -1);
$values = substr($values, 0, -1);
$set = substr($set, 0, -1);

$sql_insert = 'INSERT INTO `table` (' . $fields . ') VALUES (' . $values . ');';
$sql_update = 'UPDATE `table` SET ' . $set . ' WHERE `fld_id`=' . $id . ';';

This code isn't tested, I just wrote it from the top of my head, there could be some errors.

Bulk INSERT foreach statement

2 Answers2