如何使用关联数组编写好的PHP数据库插件

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

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

Example:

例：

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

The resulting SQL insert should look as follows:

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

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

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

我想出了以下PHP单行代码：

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包含以下内容：

$_fields = array('field1'=>"naustyvalue); drop table members; --");

The following SQL would be generated:

将生成以下SQL：

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.

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

How do you write your PHP Mysql Inserts?

你如何编写PHP Mysql插件？

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?

注意：PDO或mysqli准备的查询目前不是我的选项，因为代码库已经广泛使用了mysql - 计划进行更改但是需要很多资源才能进行转换？

7 个解决方案

#1

The only thing i would change would be to use sprintf for readability purposes

我唯一想改变的就是使用sprintf来实现可读性

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

并确保值被转义。

#2

Nothing wrong with that. I do the same.

没有错。我也一样。

But make sure you mysql_escape() and quote the values you stick in the query, otherwise you're looking at SQL injection vulnerability.

但请确保你使用mysql_escape（）并引用你在查询中粘贴的值，否则你会看到SQL注入漏洞。

Alternately, you could use parametrized queries, in which case you can practically pass the array in itself, instead of building a query string.

或者，您可以使用参数化查询，在这种情况下，您实际上可以自己传递数组，而不是构建查询字符串。

#3

The best practice is either to use an ORM (Doctrine 2.0), an ActiveRecord implementation (Doctrine 1.0, RedBean), or a TableGateway pattern implementation (Zend_Db_Table, Propel). These tools will make your life a lot easier, and handle a lot of the heavy lifting for you, and can help protect you from SQL injections.

最佳实践是使用ORM（Doctrine 2.0），ActiveRecord实现（Doctrine 1.0，RedBean）或TableGateway模式实现（Zend_Db_Table，Propel）。这些工具将使您的生活更轻松，并为您处理大量繁重的工作，并有助于保护您免受SQL注入。

Other than that, there's nothing inherently wrong with what you're doing, you just might want to abstract it away into a class or a function, so that you can repeat the functionality in different places.

除此之外，你正在做的事情本身没有任何错误，你可能只想把它抽象成一个类或一个函数，这样你就可以在不同的地方重复这些功能。

#4

Using the sprintf trick mentioned by Galen in a previous answer, I have come up with the following code:

使用Galen在之前的回答中提到的sprintf技巧，我提出了以下代码：

$escapedfieldValues = array_map(create_function('$e', 'return mysql_real_escape_string(((get_magic_quotes_gpc()) ? stripslashes($e) : $e));'), array_values($_fields));

$sql = sprintf('INSERT INTO table (%s) VALUES ("%s")', implode(',',array_keys($_fields)), implode('","    ',$escapedfieldValues));

mysql_query($sql);

It generates a escaped and quoted insert. It also copes independent of whether magic_quotes_gpc is on or off. The code could be nicer if I used new PHP v5.3.0 anonymous functions but I need it to run on older PHP installations.

它生成一个转义和引用的插入。它还可以独立于magic_quotes_gpc是打开还是关闭。如果我使用新的PHP v5.3.0匿名函数，代码可能更好，但我需要它在较旧的PHP安装上运行。

This code is a bit longer that the original (and slower) but it is more secure.

此代码比原始代码（和更慢）稍长，但更安全。

#5

I use this to retrieve the VALUES part of the INSERT. But it might be an absurd way to do things. Comments/suggestions are welcome.

我用它来检索INSERT的VALUES部分。但这可能是一种荒谬的做事方式。欢迎提出意见/建议。

   function arrayToSqlValues($array)
   {
      $sql = "";
      foreach($array as $val)
      {    
         //adding value
         if($val === NULL)
            $sql .= "NULL";
         else
            /*
            useless piece of code see comments
            if($val === FALSE)
               $sql .= "FALSE";
            else
            */
               $sql .= "'" . addslashes($val) . "'";

         $sql .= ", ";
      };

      return "VALUES(" . rtrim($sql, " ,") . ")";
   }

#6

There is a problem with NULL (in the accepted answer) values being converted to empty string "". So this is fix, NULL becomes NULL without quotes:

将NULL（在接受的答案中）值转换为空字符串“”存在问题。所以这是修复，NULL变为NULL而没有引号：

function implode_sql_values($vals)
{
    $s = '';
    foreach ($vals as $v)
        $s .= ','.(($v===NULL)?'NULL':'"'.mysql_real_escape_string($v).'"');

    return substr($s, 1);
}

Usage:

用法：

implode_sql_values(array_values( array('id'=>1, 'nick'=>'bla', 'fbid'=>NULL) ));
// =='"1","bla",NULL'

#7

If you want to enhance your approach and add the possibility for input validation and sanitation, you might want to do this:

如果您想增强您的方法并增加输入验证和卫生的可能性，您可能希望这样做：

function insertarray($table, $arr){
   foreach($arr as $k => $v){
      $col[] = sanitize($k);
      $val[] = "'".sanitize($v)."'";
   }

   query('INSERT INTO '.sanitize($table).' ('.implode(', ', $col).') VALUES ('.implode(', ', $val).')' );
}

#1