Mysqli prepared statements build INSERT query dynamically from array

Question

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.

What I have right now:

// 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();
}

I was try to using it as:

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

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?

Answer 1

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.

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.

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;
}

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)."`";
}

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

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)";
}

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));
}

called like this

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