php-query-builder

A fluent, zero-dependency SQL query builder for PHP. It builds MySQL-style SQL (backtick-quoted identifiers) as plain strings plus a bindings array, ready to hand to PDO::prepare() / PDOStatement::execute(). The core guarantee: values you pass in are never interpolated into the SQL string — they only ever appear, in order, in the returned bindings array.

Installation

Once published to Packagist:

composer require kasapdev/php-query-builder

Or just require the file directly:

require_once 'src/QueryBuilder.php';

Usage

use Kasapdev\QueryBuilder\QueryBuilder;

// SELECT ...
[$sql, $bindings] = QueryBuilder::table('users')
    ->select(['users.id', 'users.name', 'orders.total'])
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->where('users.active', '=', 1)
    ->andWhere('orders.total', '>', 100)
    ->orWhere('users.role', '=', 'admin')
    ->orderBy('orders.total', 'DESC')
    ->limit(10)
    ->offset(0)
    ->toSql();

// $sql:      "SELECT `users`.`id`, `users`.`name`, `orders`.`total` FROM `users`
//             INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`
//             WHERE `users`.`active` = ? AND `orders`.`total` > ? OR `users`.`role` = ?
//             ORDER BY `orders`.`total` DESC LIMIT ? OFFSET ?"
// $bindings: [1, 100, "admin", 10, 0]

$pdo = new PDO('mysql:host=localhost;dbname=app', $user, $pass);
$stmt = $pdo->prepare($sql);
$stmt->execute($bindings);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// INSERT / UPDATE / DELETE are terminal helpers on the same class:
$qb = QueryBuilder::table('users');

[$sql, $bindings] = $qb->insert('users', ['name' => 'Ada', 'email' => 'ada@example.com']);
$pdo->prepare($sql)->execute($bindings);

// upsert() = INSERT, or UPDATE the existing row if a unique/primary key collides:
[$sql, $bindings] = $qb->upsert('users', ['id' => 7, 'name' => 'Ada', 'hits' => 1]);
$pdo->prepare($sql)->execute($bindings);

[$sql, $bindings] = $qb->update('users', ['name' => 'Grace'], ['id' => 7]);
$pdo->prepare($sql)->execute($bindings);

[$sql, $bindings] = $qb->delete('users', ['id' => 7]);
$pdo->prepare($sql)->execute($bindings);

Conditions can be built up incrementally too — where()/andWhere()/orWhere() all just append to the same query, so you can branch on application logic before compiling:

$query = QueryBuilder::table('users')->select(['id', 'name', 'email']);

if ($onlyActive) {
    $query->where('active', '=', 1);
}

if ($minAge !== null) {
    $query->andWhere('age', '>=', $minAge);
}

[$sql, $bindings] = $query->orderBy('name')->limit(50)->toSql();
$stmt = $pdo->prepare($sql);
$stmt->execute($bindings);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

Why bindings-only matters

$evil = "'; DROP TABLE users; --";
[$sql, $bindings] = QueryBuilder::table('users')->where('name', '=', $evil)->toSql();

// $sql:      "SELECT * FROM `users` WHERE `name` = ?"   <-- no injected SQL, ever
// $bindings: ["'; DROP TABLE users; --"]                <-- the raw value, safely parameterized

This is enforced structurally: the builder never does string concatenation of a value into the SQL text. Every value-producing code path (where, having, insert, update, delete) appends a ? to the SQL and pushes the raw value onto the bindings array instead.

Supported WHERE forms

->where('id', 5)                 // shorthand for where('id', '=', 5)
->where('id', '=', 5)
->where('age', '>=', 18)
->where('id', 'IN', [1, 2, 3])    // expands to IN (?, ?, ?)
->where('id', 'NOT IN', [1, 2])
->where('deleted_at', '=', null) // compiles to IS NULL
->where('deleted_at', '!=', null) // compiles to IS NOT NULL
->where('id', 'IN', $subquery)   // subquery, expands to IN (SELECT ...) — see "Subqueries" below
->andWhere(...)                  // explicit AND
->orWhere(...)                   // explicit OR

Upsert

// If a row with a colliding unique/primary key already exists, refresh every
// given column on it instead of failing the INSERT:
[$sql, $bindings] = QueryBuilder::table('users')->upsert('users', [
    'id' => 7,
    'email' => 'ada@example.com',
    'login_count' => 1,
]);
// INSERT INTO `users` (`id`, `email`, `login_count`) VALUES (?, ?, ?)
// ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `email` = VALUES(`email`), `login_count` = VALUES(`login_count`)

// Pass $updateColumns to only refresh specific columns on conflict (e.g. leave
// login_count alone and just bump the email):
[$sql, $bindings] = QueryBuilder::table('users')->upsert(
    'users',
    ['id' => 7, 'email' => 'ada@example.com', 'login_count' => 1],
    ['email']
);
// ... ON DUPLICATE KEY UPDATE `email` = VALUES(`email`)

This compiles MySQL/MariaDB's INSERT ... ON DUPLICATE KEY UPDATE. Which row counts as a "duplicate" is decided by the table's own unique/primary key constraints, not by anything passed to upsert() — there's no separate $uniqueBy argument because MySQL doesn't need one.

Subqueries

Pass a nested QueryBuilder instance as a where() / andWhere() / orWhere() value and it compiles to a parenthesized (SELECT ...), with the subquery's own bindings merged into the parent's bindings array at exactly the position its placeholders appear in the SQL — the same left-to-right ordering guarantee the library already gives you for a single query, now proven recursively for nested ones:

$bigSpenders = QueryBuilder::table('orders')
    ->select(['user_id'])
    ->where('total', '>', 100);

[$sql, $bindings] = QueryBuilder::table('users')
    ->where('status', '=', 'active')
    ->andWhere('id', 'IN', $bigSpenders)
    ->toSql();

// $sql:      "SELECT * FROM `users` WHERE `status` = ? AND `id` IN (SELECT `user_id` FROM `orders` WHERE `total` > ?)"
// $bindings: ["active", 100]

$stmt = $pdo->prepare($sql);
$stmt->execute($bindings);

Values inside the subquery are still never interpolated into the SQL string — they flow through compileConditions() the same way top-level values do, so the "always parameterize" guarantee holds no matter how deeply queries are nested. A subquery also works as a plain scalar comparison value (where('id', '=', $subquery) compiles to `id` = (SELECT ...)) since it falls out of the same "value is a QueryBuilder" check — but IN (subquery) is the primary use case.

API

QueryBuilder

• QueryBuilder::table(string $table): self — start a query
• select(array $columns): self
• where(string $column, mixed $operator, mixed $value = null): self
• andWhere(...) / orWhere(...) — same signature as where()
• join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self
• leftJoin(...) / rightJoin(...) — same signature as join() minus $type
• orderBy(string $column, string $direction = 'ASC'): self
• groupBy(string ...$columns): self
• having(string $column, mixed $operator, mixed $value = null): self / orHaving(...)
• limit(int $limit): self
• offset(int $offset): self
• toSql(): array → [string $sql, array $bindings]
• insert(string $table, array $data): array → [string $sql, array $bindings]
• upsert(string $table, array $data, array $updateColumns = []): array → [string $sql, array $bindings] — INSERT ... ON DUPLICATE KEY UPDATE; $updateColumns defaults to every column in $data
• update(string $table, array $data, array $where): array → [string $sql, array $bindings]
• delete(string $table, array $where): array → [string $sql, array $bindings]

All identifiers (table names, column names) are quoted with backticks. Dotted identifiers (table.column) are quoted per-segment. Expressions that aren't simple identifiers (containing spaces, parentheses, etc.) are passed through unmodified so you can supply raw expressions like COUNT(*) AS total.

Testing

php tests/run.php

The test suite includes an explicit SQL-injection regression test (a value containing '; DROP TABLE users; --' is asserted to appear only in the bindings array, never in the SQL string) plus end-to-end execution of generated SQL against a real in-memory SQLite database.

License

MIT