emailtemplate.it/download/emailtemplate_bridge.php

<?php
declare(strict_types=1);

/**
 * EmailTemplate Bridge – Schema-API für Quellsysteme.
 *
 * Diese Datei kann auf einer geschützten Quelle (z.B. Kundenserver) installiert werden.
 * Sie liefert dem EmailTemplate-System Informationen über verfügbare Tabellen/Spalten,
 * ohne direkten DB-Zugriff von außen zu erlauben.
 *
 * Sicherheit:
 *  - Authentifizierung per statischem Token (per Header oder Query-Parameter).
 *  - Optional können Host/IP-Checks ergänzt werden.
 *
 * Aktionen:
 *  - action=schema (Default)  → Gibt Tabellen inkl. Spaltendefinition zurück.
 *  - action=ping              → Kleiner Health-Check.
 *
 * Hinweise:
 *  - DB-Daten können direkt unten eingetragen oder aus einer separaten Datei geladen werden.
 *  - Der Token sollte für jede Installation eindeutig sein.
 */

$bridgeConfig = [
    'token' => getenv('EMAILTEMPLATE_BRIDGE_TOKEN') ?: 'REPLACE_WITH_SHARED_TOKEN',
    'db' => [
        'dsn'  => getenv('EMAILTEMPLATE_BRIDGE_DSN') ?: 'mysql:host=127.0.0.1;dbname=example;charset=utf8mb4',
        'user' => getenv('EMAILTEMPLATE_BRIDGE_DB_USER') ?: 'root',
        'pass' => getenv('EMAILTEMPLATE_BRIDGE_DB_PASS') ?: '',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ],
    ],
    'tables_allow' => ['users', 'children', 'event_participants', 'events', 'user_points', 'user_points_totals', 'user_profiles', 'forum_posts', 'forum_threads'], // optional whitelist: ['customers', 'orders']
    'setup_hint' => '__SETUP_HINT__',
];

// {{BRIDGE_DB_SETUP}}

$localOverride = __DIR__ . '/emailtemplate.bridge.conf.php';
if (is_file($localOverride)) {
    $override = include $localOverride;
    if (is_array($override)) {
        $bridgeConfig = array_replace_recursive($bridgeConfig, $override);
    }
}

function bridgeRespond($payload, int $status = 200): void
{
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');
    header('Cache-Control: no-store, max-age=0');
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function bridgeRequireToken(array $config): void
{
    $expected = (string)($config['token'] ?? '');
    if ($expected === '') {
        bridgeRespond(['ok' => false, 'error' => 'Bridge token not configured'], 500);
    }

    $provided = null;
    if (!empty($_SERVER['HTTP_AUTHORIZATION']) && stripos($_SERVER['HTTP_AUTHORIZATION'], 'Bearer ') === 0) {
        $provided = trim(substr($_SERVER['HTTP_AUTHORIZATION'], 7));
    } elseif (!empty($_SERVER['HTTP_X_EMAILTEMPLATE_TOKEN'])) {
        $provided = trim($_SERVER['HTTP_X_EMAILTEMPLATE_TOKEN']);
    } elseif (isset($_GET['token'])) {
        $provided = (string)$_GET['token'];
    } elseif (isset($_POST['token'])) {
        $provided = (string)$_POST['token'];
    }

    if (!$provided || !hash_equals($expected, $provided)) {
        bridgeRespond(['ok' => false, 'error' => 'Unauthorized'], 403);
    }
}

function bridgeDb(array $config): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }

    try {
        $pdo = new PDO(
            $config['db']['dsn'],
            $config['db']['user'],
            $config['db']['pass'],
            $config['db']['options']
        );
    } catch (Throwable $e) {
        bridgeRespond(['ok' => false, 'error' => 'DB connection failed', 'detail' => $e->getMessage()], 500);
    }

    return $pdo;
}

function bridge_array_get($data, string $path, $default = null)
{
    if (is_object($data)) {
        $data = (array)$data;
    }
    if (!is_array($data)) {
        return $default;
    }
    $path = trim($path);
    if ($path === '') {
        return $data;
    }
    $segments = array_values(array_filter(array_map('trim', explode('.', $path)), static function ($segment) {
        return $segment !== '';
    }));
    foreach ($segments as $segment) {
        if (is_array($data) && array_key_exists($segment, $data)) {
            $value = $data[$segment];
            if (is_object($value)) {
                $value = (array)$value;
            }
            $data = $value;
        } else {
            return $default;
        }
    }
    return $data;
}

bridgeRequireToken($bridgeConfig);

$action = strtolower((string)($_GET['action'] ?? $_POST['action'] ?? 'schema'));

if ($action === 'ping') {
    bridgeRespond(['ok' => true, 'time' => date(DATE_ATOM)]);
}

if ($action !== 'schema') {
    bridgeRespond(['ok' => false, 'error' => 'Unknown action'], 404);
}

$pdo = bridgeDb($bridgeConfig);

try {
    $dbName = '';
    if (preg_match('/dbname=([^;]+)/i', $bridgeConfig['db']['dsn'], $m)) {
        $dbName = $m[1];
    }

$tablesStmt = $pdo->query('SHOW FULL TABLES');
$tables = [];
$whitelist = [];
if (!empty($bridgeConfig['tables_allow']) && is_array($bridgeConfig['tables_allow'])) {
    foreach ($bridgeConfig['tables_allow'] as $tbl) {
        if (is_string($tbl) && $tbl !== '') {
            $whitelist[strtolower($tbl)] = true;
        }
    }
}
while ($row = $tablesStmt->fetch(PDO::FETCH_NUM)) {
    $tableName = $row[0];
    if ($tableName === null) {
        continue;
    }
    if ($whitelist && empty($whitelist[strtolower($tableName)])) {
        continue;
    }

        $columnsStmt = $pdo->prepare(
            'SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA
             FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table
             ORDER BY ORDINAL_POSITION'
        );
        $columnsStmt->execute([
            ':schema' => $dbName ?: $pdo->query('SELECT DATABASE()')->fetchColumn(),
            ':table'  => $tableName,
        ]);

        $columns = [];
        foreach ($columnsStmt as $col) {
            $columns[] = [
                'name'       => $col['COLUMN_NAME'],
                'type'       => $col['DATA_TYPE'],
                'nullable'   => ($col['IS_NULLABLE'] === 'YES'),
                'default'    => $col['COLUMN_DEFAULT'],
                'key'        => $col['COLUMN_KEY'],
                'extra'      => $col['EXTRA'],
                'placeholder'=> strtoupper($tableName) . '__' . strtoupper($col['COLUMN_NAME']),
            ];
        }

        $tables[] = [
            'name'    => $tableName,
            'columns' => $columns,
        ];
    }

    bridgeRespond([
        'ok'      => true,
        'tables'  => $tables,
        'setup_hint' => $bridgeConfig['setup_hint'] ?? null,
        'fetched' => date(DATE_ATOM),
    ]);
} catch (Throwable $e) {
    bridgeRespond(['ok' => false, 'error' => 'Schema fetch failed', 'detail' => $e->getMessage()], 500);
}