sqlite_set_authorizer

mIRC SQLite

sqlite_set_authorizer
Registers an authorizer for a connection.
Syntax
$sqlite_set_authorizer ( conn, authorizer_alias [, prop ] )
/sqlite_set_authorizer conn authorizer_alias [ prop ]
Parameters
conn
The connection identifier.
authorizer_alias
The mIRC alias to register as an authorizer.
prop
A custom property you want to use with alias name. Optional.
Return Value
1 if the authorizer was registered successfully, or $null if there was an error.
Remarks
An authorizer lets you to examine what kind of query SQLite is trying to run, and change the behavior of the result by either denying the operation, or ignoring it. The foremost purpose of an authorizer is to allow scripts to safely execute user-entered SQL queries, without compromising security of the database.

Only one authorizer can be registered at a time for a connection. Setting a new authorizer for a connection will override the old authorizer, if one is registered. You can unregister the authorizer by passing $null for authorizer_alias.

The authorizer should return $SQLITE_OK if the current action should be allowed, $SQLITE_DENY if it should be denied (generates an error) or $SQLITE_IGNORE if it should be ignored (treated as a no-op). You can use an authorizer to make certain columns or functions return NULL by returning $SQLITE_IGNORE for the corresponding action (see below). Returning any other value than the ones previously mentioned will auto-assume $SQLITE_OK.

The authorizer alias will receive a few arguments. The first argument is the type of action SQLite is performing. The second and third argument provide additional information depending on what type of an action is in question. The fourth argument is the name of the database (eg. "main" or "temp") where applicable. The fifth argument is the name of the inner-most trigger or view that triggered the authorizer, or $null if the authorizer was triggered directly from code.

You can see all the possible types of action that authorizer can be triggered for below, and the associated arguments for it (the 2nd and 3rd arguments of the authorizer alias).
Type                          Value     2nd Arg         3rd Arg
$SQLITE_CREATE_INDEX              1     Index Name      Table Name
$SQLITE_CREATE_TABLE 2 Table Name $null
$SQLITE_CREATE_TEMP_INDEX 3 Index Name Table Name
$SQLITE_CREATE_TEMP_TABLE 4 Table Name $null
$SQLITE_CREATE_TEMP_TRIGGER 5 Trigger Name Table name
$SQLITE_CREATE_TEMP_VIEW 6 View Name $null
$SQLITE_CREATE_TRIGGER 7 Trigger name Table Name
$SQLITE_CREATE_VIEW 8 View Name $null
$SQLITE_DELETE 9 Table Name $null
$SQLITE_DROP_INDEX 10 Index Name Table Name
$SQLITE_DROP_TABLE 11 Table Name $null
$SQLITE_DROP_TEMP_INDEX 12 Index Name Table Name
$SQLITE_DROP_TEMP_TABLE 13 Table Name $null
$SQLITE_DROP_TEMP_TRIGGER 14 Trigger Name Table Name
$SQLITE_DROP_TEMP_VIEW 15 View Name $null
$SQLITE_DROP_TRIGGER 16 Trigger Name Table Name
$SQLITE_DROP_VIEW 17 View Name $null
$SQLITE_INSERT 18 Table Name $null
$SQLITE_PRAGMA 19 Pragma Name 1st Arg or $null
$SQLITE_READ 20 Table Name Column Name
$SQLITE_SELECT 21 $null $null
$SQLITE_TRANSACTION 22 $null $null
$SQLITE_UPDATE 23 Table Name Column Name
$SQLITE_ATTACH 24 Filename $null
$SQLITE_DETACH 25 Database Name $null
$SQLITE_ALTER_TABLE 26 Database Name Table Name
$SQLITE_REINDEX 27 Index Name $null
$SQLITE_ANALYZE 28 Table Name $null
$SQLITE_CREATE_VTABLE 29 Table Name Module Name
$SQLITE_DROP_VTABLE 30 Table Name Module Name
$SQLITE_FUNCTION 31 Function Name $null
Example
; The following example demonstrates how to create an authorizer that:
; 1) Disallows queries of other type than SELECT.
; 2) Disallows access to certain columns by making SQLite return NULL for them.
alias my_authorizer {
  ; To make the function look cleaner, let's assign the tokens to variables.
  var %type = $1
  ; First deny queries other than SELECT
  if (%type != $SQLITE_SELECT && %type != $SQLITE_READ && %type != $SQLITE_FUNCTION) {
    ; Not authorized!
    return $SQLITE_DENY
  }
  ; Next make sure that we aren't trying to access a "secret" column, eg. a password in an users table.
  if (%type == $SQLITE_READ) {
    var %table = $2, %column = $3
    if (%table == users && %column == password) {
      ; By returning $SQLITE_IGNORE SQLite will return NULL for this column. It's a good idea to do this instead
      ; of denying the whole query, because in that case query such as "SELECT * FROM users" would fail as well.
      return $SQLITE_IGNORE
    }
  }
  ; Nothing special in this action, allow it.
  return $SQLITE_OK
}

; To register the authorizer do (assumes that %db exists):
sqlite_set_authorizer %db my_authorizer