db.php

<?php
/*
    This file is part of WebChess. https://github.com/thorium/webchess
    Copyright 2010 Jonathan Evraire, Rodrigo Flores

    WebChess is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    WebChess is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with WebChess.  If not, see <http://www.gnu.org/licenses/>.
*/

/*
 * Central database access layer.
 *
 * Replaces the old, removed mysql_* extension with PDO and forces the use of
 * prepared statements at every call site so that user input can never be
 * concatenated into SQL (defeating SQL injection).
 *
 * Usage:
 *     $stmt = db_query("SELECT * FROM players WHERE nick = ?", [$nick]);
 *     while ($row = $stmt->fetch()) { ... }
 *
 *     $row    = db_row("SELECT * FROM players WHERE playerID = ?", [$id]);
 *     $rows   = db_all("SELECT nick FROM players");
 *     $value  = db_value("SELECT COUNT(*) FROM games");
 *     $newId  = db_insert_id();
 */

declare(strict_types=1);

/* load settings (which expose the $CFG_* database credentials) */
if (!isset($_CONFIG)) {
    require __DIR__ . '/config.php';
}

/**
 * Returns the shared PDO connection, creating it on first use.
 */
function db(): PDO
{
    static $pdo = null;

    if ($pdo === null) {
        global $CFG_SERVER, $CFG_USER, $CFG_PASSWORD, $CFG_DATABASE;

        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=utf8mb4',
            $CFG_SERVER,
            $CFG_DATABASE
        );

        try {
            $pdo = new PDO($dsn, $CFG_USER, $CFG_PASSWORD, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);
        } catch (PDOException $e) {
            /* Never leak connection details / credentials to the client. */
            error_log('WebChess DB connection failed: ' . $e->getMessage());
            http_response_code(500);
            die(APP_NAME . ' cannot connect to the database. Please check the database settings in your config.');
        }
    }

    return $pdo;
}

/**
 * Runs a parameterized statement and returns the executed PDOStatement.
 *
 * @param array<int|string, mixed> $params
 */
function db_query(string $sql, array $params = []): PDOStatement
{
    try {
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    } catch (PDOException $e) {
        /* Log the real error, show the user a generic message. */
        error_log('WebChess query failed: ' . $e->getMessage() . ' -- SQL: ' . $sql);
        http_response_code(500);
        die(APP_NAME . ' encountered a database error.');
    }
}

/**
 * Fetches a single row as an associative array, or null when there is none.
 *
 * @param array<int|string, mixed> $params
 * @return array<string, mixed>|null
 */
function db_row(string $sql, array $params = []): ?array
{
    $row = db_query($sql, $params)->fetch();
    return $row === false ? null : $row;
}

/**
 * Fetches every row as an array of associative arrays.
 *
 * @param array<int|string, mixed> $params
 * @return array<int, array<string, mixed>>
 */
function db_all(string $sql, array $params = []): array
{
    return db_query($sql, $params)->fetchAll();
}

/**
 * Fetches a single scalar value from the first column of the first row.
 *
 * @param array<int|string, mixed> $params
 * @return mixed|null
 */
function db_value(string $sql, array $params = [])
{
    $value = db_query($sql, $params)->fetchColumn();
    return $value === false ? null : $value;
}

/**
 * Returns the auto-increment id generated by the most recent INSERT.
 */
function db_insert_id(): string
{
    return db()->lastInsertId();
}