SQLite3::setAuthorizer
Configures a callback to be used as an authorizer to limit what a statement can do
Description
public bool SQLite3::setAuthorizer(callablenull $callback
)
The authorizer callback may be called multiple times for each statement prepared by
SQLite. A SELECT
or UPDATE
query will call the
authorizer for every column that would be read or updated.
The authorizer is called with up to five parameters. The first parameter is always
given, and is an int (action code) matching a constant from
SQLite3
. The other parameters are only passed for some actions. The
following table describes the second and third parameters according to the action:
List of action codes and parameters
Action |
Second parameter |
Third parameter |
SQLite3::CREATE_INDEX | Index Name | Table Name |
SQLite3::CREATE_TABLE | Table Name | null |
SQLite3::CREATE_TEMP_INDEX | Index Name | Table Name |
SQLite3::CREATE_TEMP_TABLE | Table Name | null |
SQLite3::CREATE_TEMP_TRIGGER | Trigger Name | Table Name |
SQLite3::CREATE_TEMP_VIEW | View Name | null |
SQLite3::CREATE_TRIGGER | Trigger Name | Table Name |
SQLite3::CREATE_VIEW | View Name | null |
SQLite3::DELETE | Table Name | null |
SQLite3::DROP_INDEX | Index Name | Table Name |
SQLite3::DROP_TABLE | Table Name | null |
SQLite3::DROP_TEMP_INDEX | Index Name | Table Name |
SQLite3::DROP_TEMP_TABLE | Table Name | null |
SQLite3::DROP_TEMP_TRIGGER | Trigger Name | Table Name |
SQLite3::DROP_TEMP_VIEW | View Name | null |
SQLite3::DROP_TRIGGER | Trigger Name | Table Name |
SQLite3::DROP_VIEW | View Name | null |
SQLite3::INSERT | Table Name | null |
SQLite3::PRAGMA | Pragma Name | First argument passed to the pragma, or null |
SQLite3::READ | Table Name | Column Name |
SQLite3::SELECT | null | null |
SQLite3::TRANSACTION | Operation | null |
SQLite3::UPDATE | Table Name | Column Name |
SQLite3::ATTACH | Filename | null |
SQLite3::DETACH | Database Name | null |
SQLite3::ALTER_TABLE | Database Name | Table Name |
SQLite3::REINDEX | Index Name | null |
SQLite3::ANALYZE | Table Name | null |
SQLite3::CREATE_VTABLE | Table Name | Module Name |
SQLite3::DROP_VTABLE | Table Name | Module Name |
SQLite3::FUNCTION | null | Function Name |
SQLite3::SAVEPOINT | Operation | Savepoint Name |
SQLite3::RECURSIVE | null | null |
The 4th parameter will be the name of the database ("main"
,
"temp"
, etc.) if applicable.
The 5th parameter to the authorizer callback is the name of the inner-most trigger or
view that is responsible for the access attempt or null
if this access attempt is
directly from top-level SQL code.
When the callback returns SQLite3::OK
, that means the operation
requested is accepted. When the callback returns SQLite3::DENY
,
the call that triggered the authorizer will fail with an error message explaining that
access is denied.
If the action code is SQLite3::READ
and the callback returns
SQLite3::IGNORE
then the prepared statement is
constructed to substitute a null
value in place of the table column that would have
been read if SQLite3::OK
had been returned. The
SQLite3::IGNORE
return can be used to deny an untrusted user
access to individual columns of a table.
When a table is referenced by a SELECT
but no column values are
extracted from that table (for example in a query like "SELECT count(*) FROM
table"
) then the SQLite3::READ
authorizer callback is
invoked once for that table with a column name that is an empty string.
If the action code is SQLite3::DELETE
and the callback returns
SQLite3::IGNORE
then the DELETE operation proceeds but the
truncate optimization is disabled and all rows are deleted individually.
Only a single authorizer can be in place on a database connection at a time. Each call
to SQLite3::setAuthorizer overrides the previous call. Disable the
authorizer by installing a null
callback. The authorizer is disabled by default.
The authorizer callback must not do anything that will modify the database connection
that invoked the authorizer callback.
Note that the authorizer is only called when a statement is prepared, not when it's
executed.
More details can be found in the
» SQLite3 documentation.
Parameters
-
callback
-
The callable to be called.
If null
is passed instead, this will disable the current authorizer callback.
Return Values
Returns true
on success or false
on failure.
Errors/Exceptions
This method doesn't throw any error, but if an authorizer is enabled and returns an
invalid value, the statement preparation will throw an error (or exception, depending
on the use of the SQLite3::enableExceptions method).
Examples
Example #1 SQLite3::setAuthorizer example
This only allows access to reading, and only some columns of the
users
table will be returned. Other columns will be replaced with
null
.
<?php
$db = new SQLite3('data.sqlite');
$db->exec('CREATE TABLE users (id, name, password);');
$db->exec('INSERT INTO users VALUES (1, \'Pauline\', \'Snails4eva\');');
$allowed_columns = ['id', 'name'];
$db->setAuthorizer(function (int $action, ...$args) use ($allowed_columns) {
if ($action === SQLite3::READ) {
list($table, $column) = $args;
if ($table === 'users' && in_array($column, $allowed_columns) {
return SQLite3::OK;
}
return SQLite3::IGNORE;
}
return SQLite3::DENY;
});
print_r($db->querySingle('SELECT * FROM users WHERE id = 1;'));
The above example will output:
Array
(
[id] => 1
[name] => Pauline
[password] =>
)