Adding quotes to array values in php

Question

The following script is used to implode values from a multidimensional array and insert into the mysql table. The table contains field data types of varchar and decimal. Since varchar type requires quotes and to avoid selectively placing quotes, I would like to put all values in quotes. How to implement it?

$values = array();
foreach ($data as $rowValues) {
    foreach ($rowValues as $key => $rowValue) {
            }

    $values[] = "(" . implode(', ', $rowValues) . ",'".$date."')";
}

$query = "INSERT INTO mem (memno,loan,subsc,intst, date)
          VALUES " . implode (', ', $values);
$result=mysql_query($query) or die();

I want the sql like this

INSERT INTO mem (memno,loan,subsc,intst, date)
values('value1', 'value2', 'valu3','value4','value5')

you could simply do this $values[] = "('" . implode("', '", $rowValues) . "','".$date."')"; but make sure your count of values ($rowValues) same as the count of table fields :) — dakshina11, Mar 10 '15 at 06:26
And make sure you are validating these inputs before running the query — dakshina11, Mar 10 '15 at 06:32
Please reenter your comment as answer, so that I can accept it — mansoondreamz, Mar 10 '15 at 07:14
See http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query for a PDO example. — blablabla, Mar 10 '15 at 08:46

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Don't use user input to build SQL strings - thats how you get SQL injection attacks.

XKCD - Exploits of a mom

Instead use a prepared statement:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

$stmt = $mysqli->prepare("
    INSERT INTO mem( memno, loan, subsc, intst, date )
        VALUES (?, ?, ?, ?, ?);
");
$stmt->bind_param('sssss', $memno, $loan, $subsc, intst, $date);

edit in response to the comment:

Dynamically binding an array of columns is pretty easy with PDO.

$db =new PDO("mysql:host=localhost;dbname=database;","root","");

/**
* @param PDO $db
* @param string $table - the table to insert into
* @param array $columns - which columns do we want to insert into
* @param array $data - a key/value array of the data we want to insert
* @return bool
*/
function insert_into($db, $table, array $columns, array $data) {
    $rows = implode(', ', $fields);
    $placeholders = array_map ( function($key){ return ":$key" }, $fields); 
    $placeholders = implode(', ', $fields);
    
    $sql = "
        INSERT INTO $table ($fields)
        VALUES ($placeholders);
    ";

    $stmt = $db->prepare($sql);

    foreach( $fields as $field) {
        $stmt->bindParam(":$field", $data[$field]);
    }

    return $sth->execute();
}

$inserted = insertInto(
    $db
    'mem',
    array("memno", "loan", "subsc", "intst", "date"),
    $data
);

Notice that the columns to insert are defined separately. If I had used:

array_keys($data);

It would lead to a mass assigment vulnerability if $data comes from user input and is not whitelisted.

You can accomplish the same thing with mysqli but its a bit trickier.

I have not used prepared statement earlier. If you can re-frame my script given in the question, it would be helpful. Because in your answer memno, loan, subsc are given as variables. You can easily see from the script that I implode an array to get values — mansoondreamz, Mar 10 '15 at 07:24
Added an example of how to dynamically assign from an array. — max, Mar 11 '15 at 16:50

ImDrPatil · Answer 2 · 2015-03-10T08:24:18.577

If $rowValues array is as below then you can do like this also.

$rowValues = array(
  "memno"=>"a",
  "loan"=>"b",
  "subsc"=>"c",
  "intst"=>"d"
);

$fldStr = array();
$valStr = array();
foreach($rowValues as $key=>$val) {
  array_push($fldStr, $key);
  $v2 = "'" . $val . "'";
  array_push($valStr, $v2);
}
array_push($fldStr, "date");
array_push($valStr, "'" . $date . "'");

$flds = implode(", ", $fldStr);
$vals = implode(", ", $valStr);

$query = "INSERT INTO mem ($flds) values($vals)";

Adding quotes to array values in php

2 Answers2

edit in response to the comment: