How to write a good PHP database insert using an a

问题:

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?

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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, " ,") . ")";
   }

回答6:

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'

回答7:

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).')' );
}