How to write a good PHP database insert using an associative array
- by Tom
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?
