How To Prepare SQL Query Dynamically (column Names Too) Avoiding SQL Injection

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

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

Solution 2:

I noticed you included the mysqli tag on your question, so assuming your database is MySQL and you are using the native MySQL functions, then you can do something like this:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
mysqli_stmt_execute($stmt);

And yes, I ripped that straight out of the PHP manual page on mysqli_stmt_bind_param.