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?

Answer 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.