Mysqli 准备语句从数组动态构建 INSERT 查询

问题描述

我正在尝试用 PHP(不是 OOP)开发我的函数，以创建一个 CRUD.目标是对任何表使用相同的功能，但我已经陷入第一个.不知道该怎么做.

I'm trying to develop my functions in PHP (not OOP), to create a CRUD. The goal is to use the same function to any table, but I got stuck already in the first one. Can't figure how to do this.

我现在拥有的:

// function to avoid injections
function validate($link, $field){
    $valid = mysqli_real_escape_string($link, $field);
    $valid = strip_tags($valid);
    return $valid;
}

// validate input of array
function sqlWithArray($link,$array){
    $return = array();
    foreach($array as $field=>$val){
        $return[$field] = "'".validate($link, $val)."'";
    }
    return $return;
}

// Multi insert to any table
function InsertDB($link, $table, array $args){
    $rows = sqlWithArray($link,$args);
    $keys = "(".implode(array_keys($args)," ,").")";
    $values = " VALUES (".implode(array_values($args),", ").")";
    $query = "INSERT INTO $table $keys $values";

    return $link->execute();
}

我尝试将其用作:

InsertDB($link, "test_table", $args); //$args is an array

但我不断收到以下错误:

But I keep getting the following error:

PHP Fatal error:  Uncaught Error: Call to undefined method mysqli::execute() in includesfunctions.php:37

我的 37 行是空的，但 36 和 38 如下:

My 37 line is empty, but 36 and 38 are the following:

$query = "INSERT INTO $table $keys $values";

return $link->execute();

我在这里做错了什么?

推荐答案

拥有这样的功能是一个好主意本身.它表明你是一个内心深处的程序员，而不仅仅是一个像乐高人偶一样用现成的积木编写 PHP 的修补匠.这样的功能可以极大地改进您的代码.

Having such a function is a good idea per se. It indicates that you are a programmer in your heart, not just a tinkerer that writes PHP from ready made blocks like a Lego figure. Such a function can greatly improve your code.

权力越大，责任越大.通过表和字段名称，这样的函数是SQL 注入的持续危险.你应该注意这一点.更不用说它应该使用准备好的语句为数据正确实现.

But with great power comes great responsibility. Such a function is a constant danger of SQL injection, through table and field names. You should take care of that. Not to mention it should be properly implemented using prepared statements for the data.

首先，您需要一个通用函数来使用查询和参数数组执行任意 MySQL 查询.我有一个简单的 mysqli 辅助函数给你.执行所有准备好的查询将是一个基本功能:

First of all, you will need a general purpose function to execute an arbitrary MySQL query using a query and an array of parameters. I have a simple mysqli helper function for you. It will be a basic function to execute all prepared queries:

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

现在我们可以开始动态构建 SQL 查询了.为此，我们需要一个可以转义标识符的函数

Now we can start constructing the SQL query dynamically. For this we will need a function that would escape identifiers

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

它会使标识符安全，至少在您使用 Unocode 时是这样.

It will make identifiers safe, at least as long as you are using Unocode.

现在我们可以继续创建正确的 SQL 字符串.我们需要创建一个带有占位符的 SQL，如下所示:

Now we can proceed to creation of the correct SQL string. We will need to create an SQL with placeholders, like this:

INSERT INTO `staff` (`name`,`occupation`) VALUES (?,?)

所以让我们编写一个函数来创建这样的查询

So let's write a function that would create a query like this

function create_insert_query($table, $keys)
{
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    return "INSERT INTO $table ($fields) VALUES ($placeholders)";
}

最后我们可以编写期待已久的 crud 函数:

And finally we can write the long-sought crud function:

function crud_insert($conn, $table, $data) {
    $sql = create_insert_query($table, array_keys($data));
    prepared_query($conn, $sql, array_values($data));
}

这样称呼

$args = ['name' => "D'Artagnan", "occupation" => 'musketeer'];
crud_insert($link, "test_table", $args);

这篇关于Mysqli 准备语句从数组动态构建 INSERT 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！