問題描述

我正在嘗試用 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 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！