Raw SQL Query Builder

Raw SQL Query Builder ~ the Swiss-army knife of raw SQL queries

Introduction

We already have some great tools when working with managed or abstracted database layers like ORM's and Doctrine DBAL. And most ORM's allow you to write and execute raw SQL queries when you require greater/custom flexibility or functionality they don't provide.

However, what tools do you have when working with the plain text strings of raw/native SQL queries? You have lots of string concatenations, implode(), PDO::prepare, PDO::quote, sprintf (for the brave) and mysqli::real_escape_string (because the first version wasn't real enough or the name long enough).

The One Ring to rule them all, One Ring to bind them

Introducing the 'Raw SQL Query Builder' (SQLQB); combining all the functionality of having placeholders like ?, :id, %s, %d; with an ORM style 'fluent interface' (methods return $this for method-chaining) and much more.

It's the glue that sits between $sql = '...'; and $db->query($sql). The part where you have to concatenate, 'escape', 'quote', 'prepare' and 'bind' values in a raw SQL query string.

This is not an ORM or replacement for an ORM, it's the tool you use when you need to create a raw SQL query string with the convenience of placeholders. It doesn't 'prepare' or 'execute' your queries exactly like PDO::prepare does; but it does support the familiar syntax of using ? or :id as placeholders. It also supports a subset of sprintf's %s / %d syntax.

In addition, it supports inserting 'raw' strings (without quotes or escapes) with @; eg. sql('dated = @', 'NOW()'), even replacing column or table names as well as auto-implode()ing arrays with [] eg. sql('WHERE id IN ([])', $array')

echo sql('SELECT * FROM @ WHERE @ = ? OR name IN ([?]) OR id IN ([]) AND created = @',
		'users', 'name', 'Trevor', ['Tom', 'Dick', 'Harry'], [1, 2, 3], 'NOW()');

No need for escaping, no quotes, no array handling and no concatenations ...

Output:

SELECT * FROM users WHERE name = "Trevor" OR name IN ("Tom", "Dick", "Harry") OR id IN (1, 2, 3) AND created = NOW()

Description

SQLQB is essentially just a glorified string wrapper targeting SQL query strings with multiple ways to do the same thing, depending on your personal preference or coding style (supports multiple naming conventions, has camelCase and snake_case function names, or you can write statements in the constructor). Designed to be 100% Multibyte-capable (UTF-8, depending on your mb_internal_encoding() setting, all functions use mb_* internally), supports ANY database (database connection is optional, it's just a string concatenator, write the query for your database/driver your own way) and supports ANY framework (no framework required or external dependencies), light-weight (one variable) but feature rich, stateless (doesn't know anything about the query, doesn't parse or validate the query), write in native SQL language with zero learning curve (only knowledge of SQL syntax) and functionality that is targeted to rapidly write, design, test, build, develop and prototype raw/native SQL query strings. You can build entire SQL queries or partial SQL fragments or even non-SQL strings.

History

I got the initial inspiration for this code when reading about the MyBatis SQL Builder Class; and it's dedicated to the few; but proud developers that love the power and flexibility of writing native SQL queries! With great power ...

It was originally designed to bridge the gap between ORM query builders and native SQL queries; by making use of a familiar ORM-style 'fluent interface', but keeping the syntax as close to SQL as possible.

Speed and Safety

This library is not designed for speed of execution or to be 100000% safe from SQL injections, it WILL however do a better job than manually escaping strings yourself; but only real 'prepared statements' offer protection from SQL injections; however they add a lot more complexity and many restrictions. In reality, it's almost impossible to write an entire website using only real/true prepared statements, so you'll invariably have to write many 'unprepared' statements; and that is where this class can help you; by writing safer 'unprepared' statements! It will 'quote' and 'escape' strings, detect the correct data type to use; but it doesn't do syntax checking, syntax parsing, query/syntax validation etc. The main task is to replace your placeholders with the corresponding data, with the ability to auto-detect the data type.

To simplify the complex

This class isn't particularly useful or necessary for small/static queries like 'SELECT * FROM users WHERE id = ' . $id;

But it really starts to shine when your SQL query gets larger and more complex; really shining on INSERT and UPDATE queries. The larger the query, the greater the benefit; that is what it was designed to do. All the complexity and tedious work of 'escaping', 'quoting' and concatenating strings is eliminated by simply putting ? where you want the variable, this library takes care of the rest.

So when you find yourself dealing with 'object-relational impedance mismatch'; because you have a database of 400+ tables, 6000+ columns/fields, one table with 156 data fields, 10 tables with over 100 fields, 24 tables with over 50 fields, 1000+ varchar/char fields as I have; just remember this library was designed to help reduce some of that complexity! Especially still having (semi-)readable queries when you come back to them in a few months or years makes it a joy to use.

Limitations of 'real' prepared statements

One of the limitations is that you cannot do this: WHERE ? = ? which you can in this class, another limitation is that you basically cannot use NULL values (there are workarounds). Also, you cannot use dynamic column/table/field names, such as SELECT ? FROM ?, all of which you can with this class; anything you can do in your $db->query($sql) you can do here!

Install

Composer

composer require twister/sql

manually

/* composer.json */
	"require": {
		"php": ">=5.6",
		"twister/sql": "*"
	}

or from GIT

https://github.com/twister-php/sql

Requirements (similar to Laravel):

PHP 5.6+ (for ...$args syntax)
Multibyte mb_* extension

Hello World

echo sql('Hello @', 'World');

Hello World

echo sql('Hello ?', 'World');

Hello "World"

Hello SQL World

echo sql('SELECT ?, ?, ?, ?, @', 1, "2", null, 'Hello World', 'NOW()');

SELECT 1, 2, NULL, "Hello World", NOW()

Note: 'numeric' values (like the "2" above) are not quoted (even when they are string values). PHP null values become SQL NULL values.

More Examples

echo sql('?, ?, ?, ?, ?, ?, ?', 4, '5', "Trevor's", 'NOW()', true, false, null);

4, 5, "Trevor\'s", "NOW()", 1, 0, NULL, 

"NOW()" is an SQL function that will not be executed, use @ for raw output strings

echo sql('@, @, @, @, @, @, @', 4, "5", "Trevor's", 'NOW()', true, false, null);

4, 5, Trevor's, NOW(), 1, 0, NULL

"Trevor's" is not escaped with @ and will produce an SQL error

Fluent Style

echo sql()->select('u.id', 'u.name', 'a.*')
          ->from('users u')
            ->leftJoin('accounts a ON a.user_id = u.id AND a.overdraft >= ?', 5000)
          ->where('a.account = ? OR u.name = ? OR a.id IN ([])', 'BST002', 'foobar', [1, 2, 3])
          ->orderBy('u.name DESC')
	  ->limit(5, 10);

SELECT u.id, u.name, a.*
FROM users u
  LEFT JOIN accounts a ON a.user_id = u.id AND a.overdraft >= 5000
WHERE a.account = "BST002" OR u.name = "foobar" OR a.id IN (1, 2, 3)
ORDER BY u.name DESC
LIMIT 5, 10

Queries include additional whitespace for formatting and display purposes, which can be removed by calling Sql::singleLineStatements(). SQL keywords can be made lower-case by calling Sql::lowerCaseStatements()

Other features

Arrays:

echo sql('WHERE id IN ([])', [1, 2, 3]);

WHERE id IN (1, 2, 3)

echo sql('WHERE name IN ([?])', ['joe', 'john', 'james']);

WHERE name IN ("joe", "john", "james")

echo sql('WHERE id = :id OR name = :name OR dob = :dob:raw', ['id' => 5, 'name' => 'Trevor', 'dob' => 'NOW()']);

WHERE id = 5 OR name = "Trevor" OR dob = NOW()

Range:

echo sql('WHERE id IN (1..?) OR id IN (?..?)', 3, 6, 8);

WHERE id IN (1, 2, 3) OR id IN (6, 7, 8)

Text filters:

eg. trim, pack (merge internal whitespace) & crop to 20 characters

echo sql('SET description = %s:pack:trim:crop:20', "Hello     World's   Greatest");

SET description = "Hello World\'s Greate"

Beginners guide

There are two main ways to write your queries; either use the constructor like an sprintf function (eg. sql('?', $value)), or use the 'fluent interface' (method chaining) by calling sql()->select(...)->from(...)->where(...) etc.

fluent interface

The general idea of is very simple; when you call a function, it essentially just appends the function/statement name (eg. select(...), from(...), where(...)) (with some extra whitespace) to the internal $sql string variable, and retur