How to prepare SQL query dynamically (column names too) avoiding SQL injection

Question

I recently learned about SQL Injection and the PHP recommendation to avoid it, using prepare() and bind_param(). Now, I want to prepare SQL queries dynamically, adding both column names and values.

I usted to do it like this, having the name field of the HTML input with the same name as the MySQL database column.

    <input type="text" name="firstname" >
    <input type="text" name="lastname" >

And the, create the SQL query dynamically using mysqli.

    // Extract values from POST
    $parameters = $_POST;
    // Organize the values in two strings
    foreach ($parameters as $id => $value) {
        $fields = $fields . "`" . $id . "`,";
        $values = $values . "'" . $value . "',"; 

        /*e.g.
            $fields = `firstname`,`lastname`
            $values = 'John','Wick'
        */
    }

    // Write into the database
    $sql = "INSERT INTO `user` ($fields) VALUES ($values)";

    /*e.g.
        INSERT INTO `user` (`firstname`,`lastname`) VALUES ('John','Wick')
    */

I would like to know if there is a way to do this using prepare() and bind_param() to avoid SQL injection, may be adding adding some data-type="s" to the HTML input tag or if there is a better, more best-practices, way to do it.

Answer 1

You can use bound parameters only for an element that would be a constant value — a quoted string, a quoted datetime, or a numeric literal.

You can't use a parameter placeholder for anything else in SQL, like column names, table names, lists of values, SQL keywords or expressions, or other syntax.

If you need to make column names dynamic, the only option is to validate them against a list of known columns.

$columns_in_user_table = [
  'userid'=>null,
  'username'=>'',
  'firstname'=>'',
  'lastname'=>''
];
// Extract values from POST, but only those that match known columns
$parameters = array_intersect_key($_POST, $columns_in_user_table);
// Make sure no columns are missing; assign default values as needed
$parameters = array_merge($columns_in_user_table, $parameters);

If you use PDO instead of mysqli, you can skip the binding. Just use named parameters, and pass your associative array of column-value pairs directly to execute():

$columns = [];
$placeholders = [];
foreach ($parameters as $col => $value) {
    $columns[] = "`$col`";
    $placeholders[] = ":$col";
}
$column_list = implode($columns, ',');
$placeholder_list = implode($placeholders, ',');

// Write into the database
$sql = "INSERT INTO `user` ($column_list) VALUES ($placeholder_list)";

$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);