Secure DB (khaderhan/secure-db)

A secure-by-default, PDO-first database abstraction layer (DBAL) for native PHP applications (PHP 8.2+).

⚠️ This package is designed for framework-less / native PHP projects.
It does not integrate with Laravel, Symfony, or other framework ORMs.
It is intentionally lightweight, explicit, and framework-independent.

---

Philosophy

Secure DB is intentionally minimal and explicit.

It is not an ORM.

It does not:

• Auto-map entities
• Generate SQL
• Hide SQL from the developer
• Replace full-featured framework database layers

Instead, it:

• Encourages explicit SQL
• Enforces safe parameter handling
• Provides ergonomic helpers
• Preserves full PDO power
• Keeps abstraction thin and predictable

Suitable for:

• Native PHP applications
• Microservices
• SaaS platforms
• Internal tools
• Performance-critical systems

---

Installation

composer require khaderhan/secure-db

Requirements:

• PHP 8.2+
• ext-pdo
• pdo_mysql (MySQL currently supported)

---

Quick Start

use Khaderhan\SecureDb\DB;

$db = DB::connect([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'example',
    'user' => 'root',
    'pass' => '',
    'charset' => 'utf8mb4',
    'dev' => true,
    'strict' => true,
]);

$users = $db->all(
    'SELECT * FROM users WHERE status = :status',
    ['status' => 'active']
);

---

Connection Configuration

Using DSN

$db = DB::connect([
    'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=example;charset=utf8mb4',
    'user' => 'root',
    'pass' => '',
]);

Structured Config

$db = DB::connect([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'example',
    'user' => 'root',
    'pass' => '',
    'charset' => 'utf8mb4',
    'dev' => false,
    'strict' => true,
    'log_queries' => false,
    'query_log_size' => 50,
    'allow_empty_in_list' => false,
]);

---

Core API

query(string $sql, array $params = []): Result

Used for SELECT queries.

exec(string $sql, array $params = []): Result

Used for INSERT / UPDATE / DELETE.

Both return a Result object.

---

Result Object API

• ok(): bool\
• affected(): int\
• insertId(): int|string|null\
• one(): ?array\
• all(): array\
• scalar(int|string $key = 0): mixed\
• column(int|string $key = 0): array\
• pairs(int|string $key = 0, int|string $value = 1): array\
• iterate(): Generator\
• mustOk(): self\
• mustAffect(): self\
• close(): void

Example:

$user = $db->query(
    'SELECT * FROM users WHERE id = :id',
    ['id' => 1]
)->one();

---

Helper Shortcuts

• one()
• all()
• value()
• exists()
• column()
• pairs()

Example:

$count = $db->value('SELECT COUNT(*) FROM users');
$exists = $db->exists(
    'SELECT 1 FROM users WHERE email = :email',
    ['email' => 'test@example.com']
);

---

IN(:list) Expansion

Safely supports array expansion inside IN clauses.

$rows = $db->all(
    'SELECT * FROM users WHERE status IN (:st)',
    ['st' => ['active', 'trial']]
);

Rules:

• Arrays allowed only inside IN (:name) contexts
• Empty array throws InvalidArgumentException by default
• If allow_empty_in_list=true → becomes IN (NULL)

---

Strict Parameter Normalization

Automatically converts:

• bool → int (1/0)
• DateTimeInterface → formatted string
• Allows: string, int, float, null
• Rejects objects/resources (strict mode)

---

Transactions

Manual

$db->begin();
$db->exec(...);
$db->commit();

Automatic

$db->transaction(function(DB $db) {
    $db->exec(...);
    $db->exec(...);
});

Nested transactions use SAVEPOINT internally.

---

Streaming Large Result Sets

foreach ($db->query('SELECT * FROM big_table')->iterate() as $row) {
    process($row);
}

Prevents loading large datasets into memory.

---

Repository Pattern Example

final class UserRepository
{
    public function __construct(private DB $db) {}

    public function findById(int $id): ?array
    {
        return $this->db->one(
            'SELECT * FROM users WHERE id = :id',
            ['id' => $id]
        );
    }

    public function create(string $email): int
    {
        return $this->db->exec(
            'INSERT INTO users (email) VALUES (:email)',
            ['email' => $email]
        )->insertId();
    }
}

---

Performance Notes

• Uses native prepared statements (ATTR_EMULATE_PREPARES=false)
• Streaming via generators
• Optional bounded query logging
• Nested transactions via SAVEPOINT
• utf8mb4 enforced by default

For high-throughput systems:

• Disable dev mode
• Disable query logging
• Use streaming for large results
• Ensure proper DB indexing

---

Security Notes

• Prepared statements protect values, not identifiers
• Always whitelist identifiers before using DB::ident()
• Escape output with htmlspecialchars()
• Use least-privilege database users
• Production mode hides SQL details

---

Error Handling

Throws:

• InvalidArgumentException (parameter validation issues)
• DbException (database-level errors)

Production mode does not leak SQL details.

---

Testing & Static Analysis

Run tests:

vendor/bin/phpunit

Run static analysis:

vendor/bin/phpstan analyse

---

Attribution & Project History

This project was originally inspired by a MySQL database class published by:

David Adams
https://codeshack.io/super-fast-php-mysql-database-class/\ Published: 2020-03-05
License: MIT

David's original class provided a fast and simple mysqli-based implementation and served as a great starting point.

This repository represents a complete architectural redesign and modernization, including:

• Migration from mysqli to PDO
• Strict parameter handling
• Safe IN expansion
• Nested transactions
• Streaming support
• PSR-4 packaging
• PHPUnit + PHPStan verification
• CI pipeline integration

While inspired by the original work, this implementation is now a significantly expanded and refactored production-grade library.

Special thanks to David Adams for the original foundation.

---

License

MIT License