Prepared Update 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.
 */

/**
 * Builds a prepared UPDATE statement from a change set and explicit WHERE rules.
 *
 * The function refuses to build an UPDATE without at least one SET field and one
 * WHERE rule. This prevents unbounded update plans.
 *
 * @param string $table Database table name.
 * @param array $data Fields to update.
 * @param array $where Explicit WHERE rules keyed by field name.
 * @param array $allowed_fields Optional update-field allowlist.
 * @param array $options Optional allowed tables and allowed where fields.
 * @return array Prepared UPDATE plan.
 */
function ogBuildPreparedUpdate($table, $data = array(), $where = array(), $allowed_fields = array(), $options = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	$table = trim((string)$table);
	if (empty($table) || !preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
		$result['message'] = 'Invalid table name.';
		return $result;
	}

	if (!empty($options['allowed_tables']) && is_array($options['allowed_tables'])) {
		if (!in_array($table, $options['allowed_tables'], true)) {
			$result['message'] = 'Table is not allowlisted.';
			return $result;
		}
	}

	if (!is_array($data) || empty($data)) {
		$result['message'] = 'Update data must not be empty.';
		return $result;
	}

	if (!is_array($where) || empty($where)) {
		$result['message'] = 'WHERE rules are required for UPDATE plans.';
		return $result;
	}

	$set_parts = array();
	$params = array();
	$types = '';

	foreach ($data as $field => $value) {
		$field = trim((string)$field);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
			continue;
		}
		if (!empty($allowed_fields) && !in_array($field, $allowed_fields, true)) {
			continue;
		}
		$set_parts[] = '`' . $field . '` = ?';
		$params[] = $value;
		if (is_int($value)) {
			$types .= 'i';
		} elseif (is_float($value)) {
			$types .= 'd';
		} else {
			$types .= 's';
		}
	}

	if (empty($set_parts)) {
		$result['message'] = 'No allowed update fields were supplied.';
		return $result;
	}

	$allowed_where_fields = array();
	if (!empty($options['allowed_where_fields']) && is_array($options['allowed_where_fields'])) {
		$allowed_where_fields = $options['allowed_where_fields'];
	}

	$where_parts = array();
	foreach ($where as $field => $value) {
		$field = trim((string)$field);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
			continue;
		}
		if (!empty($allowed_where_fields) && !in_array($field, $allowed_where_fields, true)) {
			continue;
		}
		$where_parts[] = '`' . $field . '` = ?';
		$params[] = $value;
		if (is_int($value)) {
			$types .= 'i';
		} elseif (is_float($value)) {
			$types .= 'd';
		} else {
			$types .= 's';
		}
	}

	if (empty($where_parts)) {
		$result['message'] = 'No allowed WHERE fields were supplied.';
		return $result;
	}

	$sql = 'UPDATE `' . $table . '` SET ' . implode(', ', $set_parts) . ' WHERE ' . implode(' AND ', $where_parts);

	$result['success'] = true;
	$result['message'] = 'Prepared UPDATE plan built.';
	$result['data'] = array(
		'sql' => $sql,
		'types' => $types,
		'params' => $params,
		'set_count' => count($set_parts),
		'where_count' => count($where_parts)
	);

	return $result;
}