DHTMLSQL 2.0

A secure, typed and lightweight MySQL database wrapper built on PHP's mysqli extension.

• Native prepared statements — no manual escaping
• Typed exceptions instead of exit()
• Independent immutable result objects per query
• Fluent query builders with validated identifiers
• First-class transaction support
• Transport-independent pagination (no $_GET inside the library)
• Stream-based import/export (no header() calls from the library)
• PSR-4 autoloading, declare(strict_types=1) everywhere, PHP 8.4+

Upgrading from 1.x? Read docs/migration/upgrade-from-1.x.md.

---

Requirements

• PHP ^8.4 with the mysqli extension (mysqlnd driver recommended)
• MySQL 5.7+ or MariaDB 10.4+

---

Installation

composer require dhtml/dhtmlsql

---

Connection

use Dhtml\DhtmlSql\DhtmlSql;
use Dhtml\DhtmlSql\Connection\ConnectionConfig;

// From environment variables (recommended — never hard-code credentials)
$db = DhtmlSql::create(ConnectionConfig::fromEnvironment());

// Or explicit named arguments
$db = DhtmlSql::create(new ConnectionConfig(
    host:     '127.0.0.1',
    database: 'mydb',
    username: 'myuser',
    password: (string) getenv('DB_PASSWORD'),
));

Required environment variables (copy .env.example and fill in values):

DB_HOST=127.0.0.1
DB_DATABASE=mydb
DB_USERNAME=myuser
DB_PASSWORD=secret

---

Raw Prepared Queries

$result = $db->execute(
    'SELECT id, email FROM users WHERE status = ? AND role = ?',
    ['active', 'admin'],
);

foreach ($result as $row) {
    echo htmlspecialchars($row['email'], ENT_QUOTES, 'UTF-8') . PHP_EOL;
}

$row = $db->execute('SELECT * FROM users WHERE id = ?', [42])->fetchOne();

---

Query Builder (SELECT)

$rows = $db->selectFrom('users')
    ->columns(['id', 'email', 'status'])
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(25)
    ->fetchAll();

IN clause — array is auto-expanded to the correct number of ? placeholders:

$rows = $db->execute(
    'SELECT * FROM users WHERE id IN (?)',
    [[1, 2, 3]],
)->fetchAll();

---

INSERT

$id = $db->insertInto('users', [
    'email'  => 'alice@example.com',
    'status' => 'active',
])->lastInsertId();

Bulk INSERT:

$db->insertBulkInto('users',
    columns: ['email', 'status'],
    rows: [
        ['bob@example.com',   'active'],
        ['carol@example.com', 'inactive'],
    ],
);

INSERT IGNORE / Upsert:

$db->insertIgnoreInto('users', ['email' => 'alice@example.com']);

use Dhtml\DhtmlSql\Query\Expression;

$db->upsertInto(
    'users',
    insertValues: ['email' => 'dave@example.com', 'login_count' => 0],
    updateValues: ['login_count' => Expression::increment(1)],
);

---

UPDATE

$db->updateTable('users')
   ->set(['status' => 'inactive'])
   ->where('id', '=', 42)
   ->run();

// Atomic increment
$db->updateTable('stats')
   ->set(['views' => Expression::increment(1)])
   ->where('page_id', '=', 7)
   ->run();

---

DELETE

$db->deleteFrom('sessions')
   ->where('expires_at', '<', new DateTimeImmutable())
   ->run();

---

Transactions

use Dhtml\DhtmlSql\Contract\ConnectionInterface;

$db->transaction(function (ConnectionInterface $conn): void {
    $conn->execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [50, 1]);
    $conn->execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [50, 2]);
});
// Commits on success; rolls back automatically on any exception.

---

Pagination

The library never reads $_GET. Supply the current page from your application:

$page   = max(1, (int) ($_GET['page'] ?? 1));

$result = $db->paginate(
    query: 'SELECT * FROM articles WHERE status = ?',
    params: ['published'],
    page: $page,
    perPage: 20,
);

echo "Page {$result->currentPage} of {$result->totalPages} ({$result->totalItems} total)" . PHP_EOL;

foreach ($result->items as $row) {
    echo $row['title'] . PHP_EOL;
}

// Render navigation links in your application layer:
if ($result->previousPage() !== null) {
    echo "<a href='?page={$result->previousPage()}'>&laquo; Previous</a> ";
}
if ($result->nextPage() !== null) {
    echo "<a href='?page={$result->nextPage()}'>Next &raquo;</a>";
}

---

Error Handling

use Dhtml\DhtmlSql\Exception\ConnectionException;
use Dhtml\DhtmlSql\Exception\QueryException;

try {
    $db = DhtmlSql::create(ConnectionConfig::fromEnvironment());
    $db->execute('SELECT * FROM nonexistent_table');
} catch (ConnectionException $e) {
    // Credentials are never in exception messages
    error_log('Connection failed: ' . $e->getMessage());
} catch (QueryException $e) {
    error_log('Query failed: ' . $e->getSqlTemplate()); // SQL template, not bound values
}

---

Import / Export

// Import a trusted SQL file
$db->importer()->import('/var/backups/schema.sql');

// Import with path traversal protection
$db->importer()->importWithinDirectory('backup.sql', '/var/backups/');

// Export entire database to a file
$db->exporter()->exportToFile('/tmp/backup.sql');

// Export to a stream (for download handling in the application layer)
$stream = fopen('php://temp', 'w+');
$db->exporter()->export($stream, ['users', 'orders']);
rewind($stream);
$sql = stream_get_contents($stream);
fclose($stream);

Export is designed for small databases and development use. Use mysqldump for production backups.

---

Schema Helpers

$schema = $db->schema();

if (!$schema->tableExists('migrations')) {
    $db->execute('CREATE TABLE migrations (...)');
}

$schema->truncateTable('cache');
$schema->optimizeOverheadTables();

---

Native MySQLi Escape Hatch

// For operations the library does not yet cover
$mysqli = $db->nativeConnection();

---

Security

See SECURITY.md for the full security model and vulnerability disclosure process.

Key points:

• Every user-supplied value is bound via native mysqli::prepare() + bind_param().
• SQL identifiers are validated against a strict pattern and backtick-quoted separately.
• Exceptions never include raw bound parameter values.
• Passwords are marked #[\SensitiveParameter] and never appear in stack traces or logs.

---

Running Tests

# Unit tests (no database required)
composer test:unit

# Integration tests (export DB_HOST, DB_DATABASE, DB_USERNAME, DB_PASSWORD)
composer test:integration

# Static analysis — PHPStan level 8
composer analyse

# Coding style
composer style:check

# All quality checks
composer quality

---

Migrating from 1.x

See docs/migration/upgrade-from-1.x.md for the full API mapping table and before/after examples.

A drop-in compatibility facade is available:

use Dhtml\DhtmlSql\Legacy\DHTMLSQL;

$db = DHTMLSQL::connect(
    getenv('DB_HOST'), getenv('DB_USERNAME'),
    getenv('DB_PASSWORD'), getenv('DB_DATABASE'),
);

---

Author

Anthony Ogundipe (dhtml) — https://github.com/dhtml

---

Licence

MIT — see LICENCE.