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

associative-array, php, sql

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?

Best Solution

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.