Mysqli prepares语句从数组动态构建INSERT查询 [英] Mysqli prepareds statements build INSERT query dynamically from array

问题描述

我正在尝试使用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 includes\functions.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();

我在这里做错了什么?有人可以指出我正确的方向吗?

What I'm doing wrong here? Can someone point me to the right direction?

推荐答案

具有这样的功能本身就是一个好主意.它表明您是一个内心的程序员，而不仅仅是从Lego图这样的现成块编写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 prepares语句从数组动态构建INSERT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！