Tom Tom - 4 months ago 13
PHP Question

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

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?

Answer

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.