Prepared Insert 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 INSERT statement from allowed fields and values.
 *
 * Unknown fields are ignored. If no allowed values remain, no SQL is produced.
 *
 * @param string $table Database table name.
 * @param array $data Field/value data to insert.
 * @param array $allowed_fields Optional field allowlist.
 * @param array $options Optional allowed_tables list.
 * @return array Prepared INSERT plan.
 */
function ogBuildPreparedInsert($table, $data = 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)) {
		$result['message'] = 'Insert data must be an array.';
		return $result;
	}

	$columns = array();
	$placeholders = 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;
		}
		$columns[] = '`' . $field . '`';
		$placeholders[] = '?';
		$params[] = $value;
		if (is_int($value)) {
			$types .= 'i';
		} elseif (is_float($value)) {
			$types .= 'd';
		} else {
			$types .= 's';
		}
	}

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

	$sql = 'INSERT INTO `' . $table . '` (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $placeholders) . ')';

	$result['success'] = true;
	$result['message'] = 'Prepared INSERT plan built.';
	$result['data'] = array(
		'sql' => $sql,
		'types' => $types,
		'params' => $params,
		'field_count' => count($columns)
	);

	return $result;
}