JSON to SQL INSERT — PHP source

<?php
/**
 * json-to-sql — Pure JSON → SQL INSERT converter.
 * Language: PHP (8.0+).
 *
 * CosmoDev polyglot showcase port of the `json-to-sql` tool, ported from
 * src/lib/jsonToSql.ts. Display source — part of CosmoDev's polyglot tool
 * pages (https://dev.cosmolabs.org).
 *
 * The converter never throws on user input: malformed JSON is surfaced
 * through Result['error'], matching the TypeScript contract. PHP's
 * json_decode preserves object key order (via stdClass property order), so
 * first-seen column ordering matches the reference implementation.
 */

/**
 * Escape a string for safe inclusion in a single-quoted SQL literal.
 *
 * Single quotes are always doubled; MySQL mode additionally escapes the
 * backslash and the control bytes that terminate a MySQL statement.
 *
 * @param string $s
 * @param string $dialect 'standard' | 'mysql' | 'postgres'
 * @return string
 */
function escapeSqlString(string $s, string $dialect = 'standard'): string {
    // ANSI behaviour everywhere: double the quote to neutralise it.
    $out = str_replace("'", "''", $s);
    if ($dialect === 'mysql') {
        // Backslash is escaped FIRST, so the backslashes introduced by the
        // replacements below are not themselves re-doubled (matches the TS
        // order). Searches target real control bytes (double-quoted); the
        // replacements are literal 2-char backslash sequences (single-quoted).
        $out = str_replace('\\',  '\\\\', $out); // \   -> \\
        $out = str_replace("\0",  '\\0',  $out); // NUL -> \0
        $out = str_replace("\n",  '\\n',  $out); // LF  -> \n
        $out = str_replace("\r",  '\\r',  $out); // CR  -> \r
        $out = str_replace("\x1a",'\\Z',  $out); // SUB -> \Z (ends MySQL input)
    }
    return $out;
}

/**
 * Render a decoded JSON value as a SQL literal.
 *
 * @param mixed $value
 * @param string $dialect
 * @return string
 */
function sqlLiteral(mixed $value, string $dialect): string {
    if ($value === null) {
        return 'NULL';
    }
    if (is_bool($value)) {
        // MySQL's BOOL aliases TINYINT(1); standard/Postgres use TRUE/FALSE.
        return $dialect === 'mysql'
            ? ($value ? '1' : '0')
            : ($value ? 'TRUE' : 'FALSE');
    }
    if (is_int($value) || is_float($value)) {
        // JSON never decodes to NAN/INF, but guard anyway for direct callers.
        return is_finite($value) ? (string)$value : 'NULL';
    }
    if (is_string($value)) {
        return "'" . escapeSqlString($value, $dialect) . "'";
    }
    // Objects (stdClass) / arrays → compact JSON text literal. The two
    // UNESCAPED flags match JavaScript's JSON.stringify: forward slashes and
    // multibyte characters are left untouched.
    $json = json_encode($value, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    return "'" . escapeSqlString($json, $dialect) . "'";
}

/**
 * Quote an identifier per dialect, or leave it bare when quoting is disabled.
 */
function quoteIdent(string $name, string $dialect, bool $quoteIdentifiers): string {
    if (!$quoteIdentifiers) {
        return $name;
    }
    return $dialect === 'mysql'
        ? ('`' . $name . '`')
        : ('"' . $name . '"');
}

/**
 * Reduce an identifier to the safe subset [A-Za-z0-9_], defaulting to "tbl"
 * when nothing usable remains. Runs before quoting, even when quoting is off.
 */
function sanitizeIdent(string $name): string {
    $cleaned = preg_replace('/[^A-Za-z0-9_]/', '_', $name);
    return $cleaned === '' ? 'tbl' : $cleaned;
}

/**
 * Convert a JSON document into a single multi-row INSERT statement.
 *
 * Returns an array shaped like the TypeScript Result: ['ok', 'sql', 'rows',
 * 'error']. A bare JSON object is one row; a JSON array is many. Rows may
 * differ in shape — the column list is the union of every key in first-seen
 * order, and missing values are emitted as NULL.
 *
 * @param string $jsonString
 * @param array{table:string, dialect?:string, quoteIdentifiers?:bool} $opts
 * @return array{ok:bool, sql:string, rows:int, error:?string}
 */
function jsonToInsert(string $jsonString, array $opts): array {
    // Decode objects as stdClass (the default) so we can distinguish JSON
    // objects from JSON arrays unambiguously; property order is preserved.
    $data = json_decode($jsonString);
    if (json_last_error() !== JSON_ERROR_NONE) {
        return ['ok' => false, 'sql' => '', 'rows' => 0, 'error' => json_last_error_msg()];
    }

    // A bare value (object/scalar/null) is wrapped as a single row.
    $rows = is_array($data) ? $data : [$data];
    if (count($rows) === 0) {
        return ['ok' => false, 'sql' => '', 'rows' => 0, 'error' => 'No rows to insert.'];
    }
    foreach ($rows as $r) {
        if (!($r instanceof stdClass)) {
            return ['ok' => false, 'sql' => '', 'rows' => 0, 'error' => 'Rows must be objects.'];
        }
    }

    $dialect          = $opts['dialect'] ?? 'standard';
    $quoteIdentifiers = $opts['quoteIdentifiers'] ?? true;
    $table = quoteIdent(sanitizeIdent($opts['table'] ?? ''), $dialect, $quoteIdentifiers);

    // Union of keys, first-seen order. get_object_vars yields declaration
    // order, which is the document order json_decode established.
    $cols = [];
    foreach ($rows as $r) {
        foreach (array_keys(get_object_vars($r)) as $k) {
            if (!in_array($k, $cols, true)) {
                $cols[] = $k;
            }
        }
    }
    $colList = implode(', ', array_map(
        fn($c) => quoteIdent($c, $dialect, $quoteIdentifiers),
        $cols
    ));

    $valueLists = array_map(function ($r) use ($cols, $dialect) {
        $props = get_object_vars($r);
        $vals = array_map(
            fn($c) => sqlLiteral(array_key_exists($c, $props) ? $props[$c] : null, $dialect),
            $cols
        );
        return '  (' . implode(', ', $vals) . ')';
    }, $rows);

    $sql = "INSERT INTO {$table} ({$colList}) VALUES\n" . implode(",\n", $valueLists) . ';';
    return ['ok' => true, 'sql' => $sql, 'rows' => count($rows), 'error' => null];
}