Column Allowlist SQL Builder

Code

<?php

/*
 * Copyright (c) 2026 Jeffery L. Paris <jparis@phpog.com>.
 * Free for personal and internal use. Paid project use requires visible credit
 * to Jeffery L. Paris. Corporate use requires a paid license fee unless a
 * separate written license states otherwise.
 */

/**
 * Column Allowlist SQL Builder.
 *
 * Purpose:
 * Builds an UPDATE statement from approved column names only.
 *
 * @param string $table_name Table to update.
 * @param array $allowed_columns Column names allowed to be updated.
 * @param array $submitted_values Submitted field values keyed by column name.
 * @param string $where_column Approved ID or lookup column for the WHERE clause.
 * @return array SQL string, values, and rejected columns.
 */
function ogSnippetColumnAllowlistSqlBuilder(string $table_name, array $allowed_columns, array $submitted_values, string $where_column): array {
	$table_name = trim($table_name);
	$where_column = trim($where_column);
	$allowed_map = array();
	$set_parts = array();
	$values = array();
	$rejected_columns = array();

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1 || preg_match('/^[a-zA-Z0-9_]+$/', $where_column) !== 1) {
		return array(
			'ok' => false,
			'sql' => '',
			'values' => array(),
			'rejected_columns' => array('Invalid table or WHERE column.')
		);
	}

	foreach ($allowed_columns as $column_name) {
		$column_name = trim((string) $column_name);

		if (preg_match('/^[a-zA-Z0-9_]+$/', $column_name) === 1) {
			$allowed_map[$column_name] = true;
		}
	}

	foreach ($submitted_values as $column_name => $value) {
		$column_name = trim((string) $column_name);

		if (isset($allowed_map[$column_name]) === false) {
			$rejected_columns[] = $column_name;
			continue;
		}

		$set_parts[] = '`'.$column_name.'` = ?';
		$values[] = $value;
	}

	if (count($set_parts) === 0) {
		return array(
			'ok' => false,
			'sql' => '',
			'values' => array(),
			'rejected_columns' => $rejected_columns
		);
	}

	$sql = 'UPDATE `'.$table_name.'` SET '.implode(', ', $set_parts).' WHERE `'.$where_column.'` = ?';

	return array(
		'ok' => true,
		'sql' => $sql,
		'values' => $values,
		'rejected_columns' => $rejected_columns
	);
}

$submitted_payload = array(
	'title' => 'Battlestar Damage Report',
	'status' => 'published',
	'is_admin' => '1'
);

$update_plan = ogSnippetColumnAllowlistSqlBuilder('items', array('title', 'status'), $submitted_payload, 'id');
echo 'Rejected columns: '.count($update_plan['rejected_columns']);