Update several columns with PDO and an explicit allowlist

Prepared statements protect values, not SQL identifiers. Building column names directly from incoming form keys still lets a request choose which fields your application changes.

Choose writable fields in code

This PHP 8 example assumes an existing PDO connection and an example table named example_profiles. It allows only a display name and timezone to change. Rename the fixed schema identifiers to match your application; do not read them from request parameters.

<?php
declare(strict_types=1);

function updateProfile(PDO $db, int $id, array $input): int
{
    $allowed = ['display_name', 'timezone'];
    if ($id < 1 || $input === [] || array_diff(array_keys($input), $allowed)) {
        throw new InvalidArgumentException('Invalid profile update.');
    }

    $set = [];
    $values = ['profile_id' => $id];
    foreach ($input as $column => $value) {
        if (!is_string($value) || trim($value) === '' || strlen($value) > 100) {
            throw new InvalidArgumentException('Invalid field value.');
        }
        if ($column === 'timezone'
            && !in_array($value, DateTimeZone::listIdentifiers(), true)) {
            throw new InvalidArgumentException('Choose a recognised timezone.');
        }
        $set[] = $column . ' = :value_' . $column;
        $values['value_' . $column] = $value;
    }

    $statement = $db->prepare(
        'UPDATE example_profiles SET ' . implode(', ', $set)
        . ' WHERE id = :profile_id'
    );
    $statement->execute($values);
    return $statement->rowCount();
}

// After your authentication, ownership and CSRF checks:
// updateProfile($pdo, 42, ['display_name' => 'Example member']);

Configure the connection to throw exceptions with PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION. On MySQL, prefer native prepares where supported. Keep connection credentials in private configuration and show visitors a generic error—not the SQL or connection details.

The WHERE clause is only part of the permission check

Validate that the current user is allowed to edit this exact record before calling the function. Do not trust a submitted profile ID as proof of ownership. Use a transaction when several related writes must succeed together, and add optimistic locking if concurrent edits must not overwrite each other.

This is an UPDATE. The historical example actually performed an INSERT; use an explicit INSERT INTO (…) VALUES (…) when creating a record. A zero affected-row count can mean “unchanged” as well as “not found”, depending on the driver and configuration.

Reference: PDO prepared statements and their identifier limitations.

Updating and inserting large sets of data into a database (mySQL etc) can be very tedious when the data is linked explicitly to a column name, especially when there are lots of fields.

The below codebyte is a very simple way, using PDO, of assigning and binding your column names to data that is to added or updated.

One typical example is handling a large user form, where easily 10 or more form fields will need to be inserted into the database. With this codebyte you simply just use the $data array to layout and bind the column name to the variable containing corresponding data.

Just replace the $conn variable with the variable of your PDO connection, and the table_name with the table you'd like to update.

<?php
$data = array(
  // Just Examples:
  // 'column name' => 'value',
  'username' => $username,
  'first_name' => $first_name,
  'last_name' => $last_name,
  'sign_up_date' => time()
);

function buildBindedQuery($fields){
  end($fields);
  $lastField = key($fields);
  $bindString = ' ';
  foreach($fields as $field => $data){
    $bindString .= $field . '=:' . $field;
    $bindString .= ($field === $lastField ? ' ' : ',');
  }
  return $bindString;
}

$query = "INSERT INTO table_name SET" . buildBindedQuery($data);
// Replace $conn with the variable of your PDO connection
$result = $conn->prepare($query);
$result->execute($data);
?>