SkyDiablo SQL Query Builder

A PHP library for building SQL queries with named parameters that converts them to positional parameters for database drivers.

Features

• Convert named parameters (:varname) to positional parameters (?)
• Handle duplicate parameter occurrences correctly
• Validate parameter completeness
• Extract parameter names from queries
• Type-safe and well-documented

Installation

Add this library to your project:

composer require skydiablo/sql-query-builder

Usage

Basic Example

use SkyDiablo\SqlQueryBuilder\QueryBuilder;

// Your query with named parameters
$query = "SELECT * FROM users WHERE name = :name AND age > :age";

// Your parameters as associative array
$parameters = [
    'name' => 'John Doe',
    'age' => 25
];

// Convert to positional parameters
$result = QueryBuilder::build($query, $parameters);

// Get the processed query and parameters
$processedQuery = $result->getQuery(); // "SELECT * FROM users WHERE name = ? AND age > ?"
$orderedParams = $result->getParameters(); // ['John Doe', 25]

// Use with your database driver
$stmt = $pdo->prepare($processedQuery);
$stmt->execute($orderedParams);

Handling Duplicate Parameters

The library correctly handles duplicate parameter occurrences:

$query = "SELECT * FROM users WHERE (name = :name OR username = :name) AND age > :age";
$parameters = [
    'name' => 'John Doe',
    'age' => 25
];

$result = QueryBuilder::build($query, $parameters);
// Result: "SELECT * FROM users WHERE (name = ? OR username = ?) AND age > ?"
// Parameters: ['John Doe', 'John Doe', 25]

Parameter Validation

Check if all required parameters are provided:

$query = "SELECT * FROM users WHERE name = :name AND age > :age";
$parameters = ['name' => 'John Doe']; // Missing 'age'

// This will throw an exception
try {
    $result = QueryBuilder::build($query, $parameters);
} catch (\InvalidArgumentException $e) {
    echo $e->getMessage(); // "Missing parameter 'age' in parameters array"
}

// Or validate before building
if (QueryBuilder::validateParameters($query, $parameters)) {
    $result = QueryBuilder::build($query, $parameters);
} else {
    echo "Missing parameters!";
}

Extract Parameter Names

Get all parameter names used in a query:

$query = "SELECT * FROM users WHERE name = :name AND age > :age AND city = :city";
$paramNames = QueryBuilder::getParameterNames($query);
// Result: ['name', 'age', 'city']

Complex Example

$query = "
    SELECT u.*, p.title 
    FROM users u 
    LEFT JOIN profiles p ON u.id = p.user_id 
    WHERE u.name LIKE :name 
    AND u.age BETWEEN :min_age AND :max_age 
    AND u.status = :status
    ORDER BY u.created_at DESC 
    LIMIT :limit
";

$parameters = [
    'name' => '%John%',
    'min_age' => 18,
    'max_age' => 65,
    'status' => 'active',
    'limit' => 10
];

$result = QueryBuilder::build($query, $parameters);

// Use with PDO
$stmt = $pdo->prepare($result->getQuery());
$stmt->execute($result->getParameters());
$users = $stmt->fetchAll();

API Reference

QueryBuilder Class

build(string $query, array $parameters = []): QueryResult

Converts a query with named parameters to a query with positional parameters.

Parameters:

• $query (string): SQL query with named parameters (e.g., :varname)
• $parameters (array): Associative array of parameter values

Returns: QueryResult object

Throws: \InvalidArgumentException if a named parameter is missing

validateParameters(string $query, array $parameters): bool

Validates that all named parameters in a query have corresponding values.

Parameters:

• $query (string): SQL query with named parameters
• $parameters (array): Associative array of parameter values

Returns: bool - True if all parameters are provided

getParameterNames(string $query): array

Extracts all named parameter names from a query.

Parameters:

• $query (string): SQL query with named parameters

Returns: array - Array of parameter names

QueryResult Class

getQuery(): string

Returns the processed SQL query with positional parameters.

getParameters(): array

Returns the ordered array of parameter values.

getParameterCount(): int

Returns the number of parameters.

hasParameters(): bool

Returns true if the query has any parameters.

Security

This library helps prevent SQL injection by:

1. Converting named parameters to positional parameters
2. Ensuring parameter values are properly separated from the SQL query
3. Maintaining the correct order of parameters

Always use prepared statements with your database driver for maximum security.

Requirements

• PHP 8.0 or higher

License

MIT