How to write a good PHP database insert using an associative array

Question

In PHP, I want to insert into a database using data contained in a associative array of field/value pairs.

Example:

$_fields = array('field1'=>'value1','field2'=>'value2','field3'=>'value3');

The resulting SQL insert should look as follows:

INSERT INTO table (field1,field2,field3) VALUES ('value1','value2','value3');

I have come up with the following PHP one-liner:

mysql_query("INSERT INTO table (".implode(',',array_keys($_fields)).") VALUES (".implode(',',array_values($_fields)).")");

It separates the keys and values of the the associative array and implodes to generate a comma-separated string . The problem is that it does not escape or quote the values that were inserted into the database. To illustrate the danger, Imagine if $_fields contained the following:

$_fields = array('field1'=>"naustyvalue); drop table members; --");

The following SQL would be generated:

INSERT INTO table (field1) VALUES (naustyvalue); drop table members; --;

Luckily, multiple queries are not supported, nevertheless quoting and escaping are essential to prevent SQL injection vulnerabilities.

How do you write your PHP Mysql Inserts?

Note: PDO or mysqli prepared queries aren't currently an option for me because the codebase already uses mysql extensively - a change is planned but it'd take alot of resources to convert?

I would just make sure everything has been validated before added them to the field and value arrays — Phill Pafford
– Phill Pafford, Commented Nov 16, 2009 at 19:10
Watch out: implode() converts NULL to empty string, which then becomes 0 in integer columns — psycho brm
– psycho brm, Commented Dec 6, 2012 at 13:25

Galen · Accepted Answer · 2013-10-25 18:39:46Z

22

The only thing i would change would be to use sprintf for readability purposes

$sql = sprintf(
    'INSERT INTO table (%s) VALUES ("%s")',
    implode(',',array_keys($_fields)),
    implode('","',array_values($_fields))
);
mysql_query($sql);

and make sure the values are escaped.

edited Oct 25, 2013 at 18:39

answered Nov 16, 2009 at 19:12

Galen

30.2k9 gold badges75 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Galen Over a year ago

automatically no, just use php.net/manual/en/function.mysql-real-escape-string.php on each item before insertion

Tom Over a year ago

Wow, this is fantastic. You have added quotes to the values as well as improved the readability. Thanks very much!

Strae Over a year ago

I'll suggest to run array_filter() before, in order to remove the empty values of the array, they are useless but can slower your query.

Marco Demaio Over a year ago

@DaNieL: but wouldn't array_filter() remove also the string "0"? This might not be a good idea.

Strae Over a year ago

@marco: yes it will, the string "0" is considerated false in boolean, so it will be removed by array_filter (without callback). Take care that, depending on the situation, this can be a problem.

Frank Farmer · Accepted Answer · 2009-11-16 19:08:09Z

3

Nothing wrong with that. I do the same.

But make sure you mysql_escape() and quote the values you stick in the query, otherwise you're looking at SQL injection vulnerability.

Alternately, you could use parametrized queries, in which case you can practically pass the array in itself, instead of building a query string.

answered Nov 16, 2009 at 19:08

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

1 Comment

outis Over a year ago

+1 for parametrized queries. Link for reference: us.php.net/manual/en/pdo.prepared-statements.php

blockhead · Accepted Answer · 2009-11-16 19:08:26Z

1

The best practice is either to use an ORM (Doctrine 2.0), an ActiveRecord implementation (Doctrine 1.0, RedBean), or a TableGateway pattern implementation (Zend_Db_Table, Propel). These tools will make your life a lot easier, and handle a lot of the heavy lifting for you, and can help protect you from SQL injections.

Other than that, there's nothing inherently wrong with what you're doing, you just might want to abstract it away into a class or a function, so that you can repeat the functionality in different places.

answered Nov 16, 2009 at 19:08

blockhead

9,7053 gold badges46 silver badges69 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:18:11Z

0

Using the sprintf trick mentioned by Galen in a previous answer, I have come up with the following code:

$escapedfieldValues = array_map(create_function('$e', 'return mysql_real_escape_string(((get_magic_quotes_gpc()) ? stripslashes($e) : $e));'), array_values($_fields));

$sql = sprintf('INSERT INTO table (%s) VALUES ("%s")', implode(',',array_keys($_fields)), implode('","    ',$escapedfieldValues));

mysql_query($sql);

It generates a escaped and quoted insert. It also copes independent of whether magic_quotes_gpc is on or off. The code could be nicer if I used new PHP v5.3.0 anonymous functions but I need it to run on older PHP installations.

This code is a bit longer that the original (and slower) but it is more secure.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Nov 17, 2009 at 17:58

Tom

14.3k16 gold badges68 silver badges81 bronze badges

4 Comments

outis Over a year ago

This doesn't really deserve to be an answer. It should be added to the original question. As for security, your best bet these days is to use prepared queries (aka parametrized queries).

Tom Over a year ago

I agree, but if I add it to the original question, the existing answers won't make so much sense? Should I add it anyway?

outis Over a year ago

Either A) add most of it, except the code (since it's a potential answer) or B) add it in a section title "Update".

Tom Over a year ago

@outis - I chose option A. Thanks for the advice. Now these comments don't make much sense - do we delete them?

Marco Demaio · Accepted Answer · 2012-08-18 14:35:14Z

0

I use this to retrieve the VALUES part of the INSERT. But it might be an absurd way to do things. Comments/suggestions are welcome.

   function arrayToSqlValues($array)
   {
      $sql = "";
      foreach($array as $val)
      {    
         //adding value
         if($val === NULL)
            $sql .= "NULL";
         else
            /*
            useless piece of code see comments
            if($val === FALSE)
               $sql .= "FALSE";
            else
            */
               $sql .= "'" . addslashes($val) . "'";

         $sql .= ", ";
      };

      return "VALUES(" . rtrim($sql, " ,") . ")";
   }

edited Aug 18, 2012 at 14:35

answered Mar 24, 2010 at 18:41

Marco Demaio

34.6k33 gold badges132 silver badges162 bronze badges

3 Comments

Tom Haigh Over a year ago

if $val === true, then this gives you '1'.

Marco Demaio Over a year ago

@TomHaigh: that's correct, it's not a bug. '1' in SQL query is TRUE.

Tom Haigh Over a year ago

in that case why have you done ` $sql .= "FALSE";` for false? Wouldn't it be better to be consistent?

psycho brm · Accepted Answer · 2012-12-06 13:43:06Z

0

There is a problem with NULL (in the accepted answer) values being converted to empty string "". So this is fix, NULL becomes NULL without quotes:

function implode_sql_values($vals)
{
    $s = '';
    foreach ($vals as $v)
        $s .= ','.(($v===NULL)?'NULL':'"'.mysql_real_escape_string($v).'"');

    return substr($s, 1);
}

Usage:

implode_sql_values(array_values( array('id'=>1, 'nick'=>'bla', 'fbid'=>NULL) ));
// =='"1","bla",NULL'

answered Dec 6, 2012 at 13:43

psycho brm

7,6801 gold badge46 silver badges42 bronze badges

Comments

Marc Ruef · Accepted Answer · 2016-08-04 11:30:54Z

0

If you want to enhance your approach and add the possibility for input validation and sanitation, you might want to do this:

function insertarray($table, $arr){
   foreach($arr as $k => $v){
      $col[] = sanitize($k);
      $val[] = "'".sanitize($v)."'";
   }

   query('INSERT INTO '.sanitize($table).' ('.implode(', ', $col).') VALUES ('.implode(', ', $val).')' );
}

answered Aug 4, 2016 at 11:30

Marc Ruef

3,4311 gold badge14 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to write a good PHP database insert using an associative array

7 Answers 7

5 Comments

1 Comment

Comments

4 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

1 Comment

Comments

4 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related