PHP developers often struggle with database queries that break at runtime due to type mismatches and SQL injection vulnerabilities. This guide shows how to build type-safe database queries that catch errors early and protect your applications.
What Are Type-Safe Database Queries?
Type-safe database queries ensure data types match between your PHP code and database schema at compile time or through strict runtime checks. This prevents common issues like:
- SQL injection attacks
- Type conversion errors
- Runtime database exceptions
- Data corruption from type mismatches
Why Type Safety Matters in Database Operations
Security Benefits:
- Prevents SQL injection through parameterized queries
- Validates input data types before database operations
- Reduces attack surface area
Development Benefits:
- Catches errors during development, not production
- Improves code maintainability
- Provides better IDE autocompletion
- Reduces debugging time
Method 1: Using PDO with Prepared Statements
PDO provides the foundation for type-safe queries in PHP.
Basic Setup
<?php
class DatabaseConnection
{
private PDO $pdo;
public function __construct(string $dsn, string $username, string $password)
{
$this->pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false
]);
}
public function getPdo(): PDO
{
return $this->pdo;
}
}
Type-Safe Query Builder
class TypeSafeQueryBuilder
{
private PDO $pdo;
private array $bindings = [];
private string $query = '';
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
public function select(string $table, array $columns = ['*']): self
{
$this->query = 'SELECT ' . implode(', ', $columns) . ' FROM ' . $table;
return $this;
}
public function where(string $column, mixed $value, string $operator = '='): self
{
$placeholder = ':' . str_replace('.', '_', $column) . '_' . count($this->bindings);
if (empty($this->getWhereClause())) {
$this->query .= ' WHERE ';
} else {
$this->query .= ' AND ';
}
$this->query .= "{$column} {$operator} {$placeholder}";
$this->bindings[$placeholder] = $this->validateAndTypecast($value);
return $this;
}
private function validateAndTypecast(mixed $value): mixed
{
return match(gettype($value)) {
'integer' => (int) $value,
'double' => (float) $value,
'boolean' => (bool) $value,
'string' => trim((string) $value),
'NULL' => null,
default => throw new InvalidArgumentException('Unsupported data type')
};
}
private function getWhereClause(): string
{
$wherePos = strpos($this->query, ' WHERE ');
return $wherePos !== false ? substr($this->query, $wherePos) : '';
}
public function execute(): array
{
$stmt = $this->pdo->prepare($this->query);
$stmt->execute($this->bindings);
return $stmt->fetchAll();
}
}
Usage Example
$db = new DatabaseConnection('mysql:host=localhost;dbname=myapp', 'user', 'pass');
$builder = new TypeSafeQueryBuilder($db->getPdo());
// Type-safe query execution
$users = $builder
->select('users', ['id', 'email', 'created_at'])
->where('age', 25)
->where('status', 'active')
->execute();
Method 2: Using Doctrine DBAL
Doctrine DBAL provides advanced type mapping and query building capabilities.
Installation
composer require doctrine/dbal
Configuration
<?php
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
$config = new Configuration();
$connectionParams = [
'dbname' => 'mydb',
'user' => 'user',
'password' => 'secret',
'host' => 'localhost',
'driver' => 'pdo_mysql',
];
$connection = DriverManager::getConnection($connectionParams, $config);
Type-Safe Operations
use Doctrine\DBAL\Types\Types;
class UserRepository
{
private Connection $connection;
public function __construct(Connection $connection)
{
$this->connection = $connection;
}
public function findUserById(int $userId): ?array
{
$qb = $this->connection->createQueryBuilder();
return $qb
->select('u.id', 'u.email', 'u.created_at')
->from('users', 'u')
->where('u.id = :userId')
->setParameter('userId', $userId, Types::INTEGER)
->executeQuery()
->fetchAssociative() ?: null;
}
public function updateUser(int $userId, string $email, DateTime $updatedAt): bool
{
$qb = $this->connection->createQueryBuilder();
return $qb
->update('users')
->set('email', ':email')
->set('updated_at', ':updatedAt')
->where('id = :userId')
->setParameter('userId', $userId, Types::INTEGER)
->setParameter('email', $email, Types::STRING)
->setParameter('updatedAt', $updatedAt, Types::DATETIME_MUTABLE)
->executeStatement() > 0;
}
}
Method 3: Creating Custom Type Validators
Build your own validation layer for maximum control.
Input Validator Class
class DatabaseInputValidator
{
private array $rules = [];
public function addRule(string $field, string $type, bool $nullable = false): self
{
$this->rules[$field] = ['type' => $type, 'nullable' => $nullable];
return $this;
}
public function validate(array $data): array
{
$validated = [];
foreach ($this->rules as $field => $rule) {
$value = $data[$field] ?? null;
if ($value === null && !$rule['nullable']) {
throw new InvalidArgumentException("Field {$field} cannot be null");
}
if ($value !== null) {
$validated[$field] = $this->castToType($value, $rule['type'], $field);
} else {
$validated[$field] = null;
}
}
return $validated;
}
private function castToType(mixed $value, string $type, string $field): mixed
{
return match($type) {
'int', 'integer' => $this->castToInt($value, $field),
'float', 'double' => $this->castToFloat($value, $field),
'string' => $this->castToString($value, $field),
'bool', 'boolean' => $this->castToBool($value, $field),
'email' => $this->validateEmail($value, $field),
'date' => $this->castToDate($value, $field),
default => throw new InvalidArgumentException("Unknown type: {$type}")
};
}
private function castToInt(mixed $value, string $field): int
{
if (!is_numeric($value)) {
throw new InvalidArgumentException("Field {$field} must be numeric");
}
return (int) $value;
}
private function castToFloat(mixed $value, string $field): float
{
if (!is_numeric($value)) {
throw new InvalidArgumentException("Field {$field} must be numeric");
}
return (float) $value;
}
private function castToString(mixed $value, string $field): string
{
return trim((string) $value);
}
private function castToBool(mixed $value, string $field): bool
{
return filter_var($value, FILTER_VALIDATE_BOOLEAN);
}
private function validateEmail(mixed $value, string $field): string
{
$email = filter_var($value, FILTER_VALIDATE_EMAIL);
if ($email === false) {
throw new InvalidArgumentException("Field {$field} must be a valid email");
}
return $email;
}
private function castToDate(mixed $value, string $field): DateTime
{
try {
return new DateTime($value);
} catch (Exception $e) {
throw new InvalidArgumentException("Field {$field} must be a valid date");
}
}
}
Usage with Custom Validator
class SafeUserOperations
{
private PDO $pdo;
private DatabaseInputValidator $validator;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
$this->validator = new DatabaseInputValidator();
}
public function createUser(array $userData): int
{
$validator = (new DatabaseInputValidator())
->addRule('email', 'email')
->addRule('age', 'int')
->addRule('name', 'string')
->addRule('is_active', 'bool');
$validated = $validator->validate($userData);
$stmt = $this->pdo->prepare(
'INSERT INTO users (email, age, name, is_active) VALUES (?, ?, ?, ?)'
);
$stmt->execute([
$validated['email'],
$validated['age'],
$validated['name'],
$validated['is_active']
]);
return $this->pdo->lastInsertId();
}
}
Best Practices for Type-Safe Database Queries
1. Always Use Prepared Statements
- Prevents SQL injection
- Enables type binding
- Improves performance through query caching
2. Validate Input Early
// Good: Validate before database operation
public function updateUser(int $id, array $data): bool
{
$this->validateUserData($data);
return $this->executeUpdate($id, $data);
}
// Bad: No validation
public function updateUser(int $id, array $data): bool
{
return $this->executeUpdate($id, $data);
}
3. Use Specific Parameter Types
// Good: Explicit type binding
$stmt->bindValue(':age', $age, PDO::PARAM_INT);
$stmt->bindValue(':email', $email, PDO::PARAM_STR);
// Bad: Generic binding
$stmt->bindValue(':age', $age);
$stmt->bindValue(':email', $email);
4. Handle Null Values Properly
public function bindNullableValue(PDOStatement $stmt, string $param, mixed $value, int $type): void
{
if ($value === null) {
$stmt->bindValue($param, null, PDO::PARAM_NULL);
} else {
$stmt->bindValue($param, $value, $type);
}
}
Common Pitfalls to Avoid
1. Trusting User Input
// Dangerous
$sql = "SELECT * FROM users WHERE id = {$_GET['id']}";
// Safe
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([(int) $_GET['id']]);
2. Ignoring Type Conversion Errors
// Bad: Silent type conversion
$age = (int) $_POST['age']; // "abc" becomes 0
// Good: Explicit validation
if (!is_numeric($_POST['age'])) {
throw new InvalidArgumentException('Age must be numeric');
}
$age = (int) $_POST['age'];
3. Not Handling Database Exceptions
try {
$result = $stmt->execute($params);
} catch (PDOException $e) {
error_log('Database error: ' . $e->getMessage());
throw new DatabaseOperationException('Failed to execute query');
}
Testing Type-Safe Queries
Create comprehensive tests for your database operations:
class UserRepositoryTest extends PHPUnit\Framework\TestCase
{
public function testCreateUserWithValidData(): void
{
$userData = [
'email' => 'test@example.com',
'age' => 25,
'name' => 'John Doe',
'is_active' => true
];
$userId = $this->userRepo->createUser($userData);
$this->assertIsInt($userId);
$this->assertGreaterThan(0, $userId);
}
public function testCreateUserWithInvalidEmail(): void
{
$this->expectException(InvalidArgumentException::class);
$userData = [
'email' => 'invalid-email',
'age' => 25,
'name' => 'John Doe',
'is_active' => true
];
$this->userRepo->createUser($userData);
}
}
Conclusion
Type-safe database queries in PHP require discipline but provide significant benefits in security, maintainability, and reliability. Start with prepared statements, add validation layers, and consider using established libraries like Doctrine DBAL for complex applications.
The investment in type safety pays dividends through fewer runtime errors, improved security, and more maintainable code. Begin implementing these patterns gradually in your existing projects and make them standard practice for new development.
Remember: type safety isn’t just about preventing errors—it’s about building robust applications that handle data predictably and securely.
