Safe Table Exporter

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 safe table export plan using allowlisted table and column names.
 *
 * The function returns a query plan only. The caller handles permissions, execution,
 * streaming, and file delivery.
 *
 * @param string $table Table to export.
 * @param array $columns Columns to export.
 * @param array $filters Optional equality filters.
 * @param array $options Allowed tables/columns, limit, and order settings.
 * @return array Safe export query plan.
 */
function ogExportTableRowsSafely($table, $columns = array(), $filters = 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 (!is_array($options)) {
		$options = array();
	}
	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;
		}
	}

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

	$select_columns = array();
	foreach ((array)$columns as $column) {
		$column = trim((string)$column);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $column)) {
			continue;
		}
		if (!empty($allowed_columns) && !in_array($column, $allowed_columns, true)) {
			continue;
		}
		$select_columns[] = '`' . $column . '`';
	}

	if (empty($select_columns)) {
		$result['message'] = 'At least one export column is required.';
		return $result;
	}

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

	$sql = 'SELECT ' . implode(', ', $select_columns) . ' FROM `' . $table . '`';
	if (!empty($where_parts)) {
		$sql .= ' WHERE ' . implode(' AND ', $where_parts);
	}

	if (!empty($options['limit'])) {
		$limit = (int)$options['limit'];
		if ($limit > 0) {
			$sql .= ' LIMIT ' . $limit;
		}
	}

	$result['success'] = true;
	$result['message'] = 'Safe export query plan built.';
	$result['data'] = array(
		'sql' => $sql,
		'types' => $types,
		'params' => $params,
		'columns' => $select_columns
	);

	return $result;
}