Dbal allows you to escape and build safe SQL query. It provides these powerful parameter modifiers:
Modifier | Type | Description |
---|---|---|
%s , %?s , %s[] , %...s[] |
string | not nullable, nullable, array of |
%i , %?i , %i[] , %...i[] |
integer | not nullable, nullable, array of |
%f , %?f , %f[] , %...f[] |
float | not nullable, nullable, array of |
%b , %?b , %b[] , %...b[] |
boolean | not nullable, nullable, array of |
%dt , %?dt , %dt[] , %...dt[] |
datetime | not nullable, nullable, array of read more about datetime handling; using wrong modifier may damage your data |
%ldt , %?ldt , %ldt[] , %...ldt[] |
local datetime | datetime without timezone conversion read more about datetime handling; using wrong modifier may damage your data |
%di , %?di , %di[] , %...di[] |
date interval | DateInterval instance |
%blob , %?blob , %blob[] |
binary string | not nullable, nullable, array of |
%json , %?json , %json[] , %...json[] |
any | not nullable, nullable, array of |
%any |
any value | |
%_like , %like_ , %_like_ |
string | like left, like right, like both sides |
All modifiers require an argument of the specific data type - e.g. %f
accepts only floats and integers.
$connection->query('id = %i AND name IN (%?s, %?s)', 1, NULL, 'foo'); // `id` = 1 AND name IN (NULL, 'foo') $connection->query('name LIKE %_like_', $query); // escapes query and adds % to both sides // name LIKE '%escaped query expression%'
Array modifiers are able to process array of the required type. The basic []
suffix syntax denotes such array. This way Dbal also adds wrapping parenthesis. You may want to omit them for more complex SQL. To do so, use a “spread” variant of array operator – add three dots after the %
character.
$connection->query('WHERE id IN %i[]', [1, 3, 4]); // WHERE `id` IN (1, 3, 4) $connection->query('WHERE [roles.privileges] ?| ARRAY[%...s[]]', ['backend', 'frontend']); // WHERE "roles"."privileges" ?| ARRAY['backend', 'frontend']
Other available modifiers:
Modifier | Description |
---|---|
%and |
AND condition |
%or |
OR condition |
%multiOr |
OR condition with multiple conditions in pairs |
%values , %values[] |
expands array for INSERT clause, multi insert |
%set |
expands array for SET clause |
%table , %table[] |
escapes string as table name, may contain a database or schema name separated by a dot; surrounding parentheses are not added to %table[] modifier; %table supports also processing a Nextras\Dbal\Platforms\Data\Fqn instance. |
%column , %column[] |
escapes string as column name, may contain a database name, schema name or asterisk (* ) separated by a dot; surrounding parentheses are not added to %column[] modifier; |
%ex |
expands array as processor arguments |
%raw |
inserts string argument as is |
%% |
escapes to single % (useful in date_format() , etc.) |
[[ , ]] |
escapes to single [ or ] (useful when working with array, etc.) |
Let’s examine %and
and %or
behavior. If array key is numeric and its value is an array, value is expanded with %ex
modifier. (See below.)
$connection->query('%and', [ 'city' => 'Winterfell', 'age' => 23, ]); // `city` = 'Winterfell' AND `age` = 23 $connection->query('%or', [ 'city' => 'Winterfell', 'age' => [23, 25], ]); // `city` = 'Winterfell' OR `age` IN (23, 25) $connection->query('%or', [ 'city' => 'Winterfell', ['[age] IN %i[]', [23, 25]], ]); // `city` = 'Winterfell' OR `age` IN (23, 25)
If you want select multiple rows with combined condition for each row, you may use multi-column IN
expression. However, some databases do not support this feature, therefore Dbal provides universal %multiOr
modifier that will handle this for you and will use alternative expanded verbose syntax. MultiOr modifier supports optional modifier appended to the column name, set it for all entries. Let’s see an example:
$connection->query('%multiOr', [ ['tag_id%i' => 1, 'book_id' => 23], ['tag_id%i' => 4, 'book_id' => 12], ['tag_id%i' => 9, 'book_id' => 83], ]); // MySQL or PostgreSQL // (tag_id, book_id) IN ((1, 23), (4, 12), (9, 83)) // SQL Server // (tag_id = 1 AND book_id = 23) OR (tag_id = 4 AND book_id = 12) OR (tag_id = 9 AND book_id = 83)
Examples of inserting and updating:
$connection->query('INSERT INTO [users] %values', [ 'name' => 'Jon Snow' ]); // INSERT INTO `users` (`name`) VALUES ('Jon Snow') $connection->query('INSERT INTO [users] %values[]', [ ['name' => 'Jon Snow'], ['name' => 'The Imp'], ]); // INSERT INTO `users` (`name`) VALUES ('Jon Snow'), ('The Imp') $connection->query('UPDATE [users] SET %set WHERE [id] = %i', [ 'name' => 'Jon Snow' ], 1); // UPDATE `users` SET `name` = 'Jon Snow' WHERE `id` = 1
%ex
modifier expands passed array as arguments of new query()
method call.
$connection->query('%ex', ['id = %i', 1]); // equals to $connection->query('id = %i', 1);
You may add support for own modifier. To do that, create new factory class for SqlProcessor and use setCustomModifier()
method:
use Nextras\Dbal\Drivers\IDriver; use Nextras\Dbal\ISqlProcessorFactory; use Nextras\Dbal\SqlProcessor; class SqlProcessorFactory implements ISqlProcessorFactory { public function create(IDriver $driver, array $config): SqlProcessor { $processor = new SqlProcessor($driver); $processor->setCustomModifier( 'mybool', function (SqlProcessor $processor, $value) { return $processor->processModifier('s', $bool ? 'yes' : 'no'); } ); return $processor; } }
Use sqlProcessorFactory
configuration key to pass a factory instance. See configuration chapters.
SqlProcessor allows setting custom modifier resolver for any values passed for both implicit and explicit %any
modifier. This way you may introduce custom processing for your custom types. For safety reasons it is possible to override only the %any
modifier. To do so, implement ISqlProcessorModifierResolver
interface and return the modifier name for the passed value. Finally, register the custom modifier resolver into SqlProcessor. This API is especially powerful in combination with custom modifiers.
use Nextras\Dbal\Drivers\IDriver; use Nextras\Dbal\ISqlProcessorModifierResolver; use Nextras\Dbal\ISqlProcessorFactory; use Nextras\Dbal\SqlProcessor; class BrickSqlProcessorModifierResolver implements ISqlProcessorModifierResolver { public function resolve($value): ?string { if ($value instanceof \Brick\DayOfWeek) { return 'brickDayOfWeek'; } return null; } } class SqlProcessorFactory implements ISqlProcessorFactory { public function create(IDriver $driver, array $config): SqlProcessor { $processor = new SqlProcessor($driver); $processor->setCustomModifier( 'brickDayOfWeek', function (SqlProcessor $processor, $value) { assert($value instanceof \Brick\DayOfWeek); return $processor->processModifier('s', $value->getValue()); } ); $processor->addModifierResolver(new BrickSqlProcessorModifierResolver()); return $processor; } }