如何使用关联数组编写良好的PHP数据库插入 [英] How to write a good PHP database insert using an associative array

问题描述

在PHP中，我想使用字段/值对的关联数组中包含的数据插入数据库.

In PHP, I want to insert into a database using data contained in a associative array of field/value pairs.

示例:

$_fields = array('field1'=>'value1','field2'=>'value2','field3'=>'value3');

生成的SQL插入应如下所示:

The resulting SQL insert should look as follows:

INSERT INTO table (field1,field2,field3) VALUES ('value1','value2','value3');

我想出了以下PHP一线代码:

I have come up with the following PHP one-liner:

mysql_query("INSERT INTO table (".implode(',',array_keys($_fields)).") VALUES (".implode(',',array_values($_fields)).")");

它将关联数组和implodes的键和值分开，以产生一个逗号分隔的字符串.问题在于它不会转义或引用插入数据库的值.为了说明这种危险，想象一下$_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; --");

将生成以下SQL:

INSERT INTO table (field1) VALUES (naustyvalue); drop table members; --;

幸运的是，不支持多个查询，但是引用和转义对于防止SQL注入漏洞至关重要.

Luckily, multiple queries are not supported, nevertheless quoting and escaping are essential to prevent SQL injection vulnerabilities.

如何编写PHP Mysql插入?

注意:PDO或mysqli准备好的查询目前不是我的选择，因为代码库已广泛使用mysql-已计划进行更改，但需要大量资源进行转换?

推荐答案

我唯一要更改的就是出于可读性目的而使用sprintf

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.

这篇关于如何使用关联数组编写良好的PHP数据库插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！