DisallowSqliteAttach feature

Permission controlled database managers let you limit the permissions of the database user used for the tenant connection in such a way that the user cannot access any other connections, limiting any potential — however unlikely — hijacking of the connection to one tenant only.

SQLite doesn’t have database users since it has no database server; it’s just a file. Therefore it cannot have a permission controlled database manager. However a SQLite connection can be exploited into accessing other SQLite files using the ATTACH statement.

Luckily, there is a way to prevent this: using an authorizer.

Some things to keep in mind

Before we go any further, let’s put things into perspective. First, this is a feature that, given some kind of a SQL injection, limits the damage to a single tenant.

However if the attacker can do this for a single tenant, they can do it for other tenants too. So then it becomes a matter of not being able to enumerate all tenants (which is why we also recommend using randomly generated tenant IDs, with a lot of context for these security considerations in our e-book).

It’s important to keep in mind that these kinds of exploits are exceedingly unlikely, since if you use an ORM like Eloquent properly, you will generally not face any SQL injection risks. It also appears that PDO simply doesn’t allow multiple statements to be executed in a single statement() call, which significantly thwarts what most SQL injection approaches can do. Therefore, an attacker would pretty much need to be able to edit the start of a statement to directly begin with an ATTACH ... for this to work¹. It is possible that there are edge cases where other types of SQL injection might let you execute a statement like that, but overall the point here is that both Laravel and PDO are already pretty hardened, so these features for extra security should not be perceived as something you need to use.

It’s mainly a matter of being able to get much more security, and limiting the blast radius of any potential attack, with very little work on your end. Permission controlled managers are generally set and forget, having a bunch of database users is usually not a problem. This feature is even more of a set and forget — it doesn’t even create any users, just sets an authorizer.

Enabling the feature

To enable this feature, simply open your config/tenancy.php file and uncomment the DisallowSqliteAttach feature:

config/tenancy.php

'features' => [
    // Stancl\Tenancy\Features\UserImpersonation::class,
    // Stancl\Tenancy\Features\TelescopeTags::class,
    // Stancl\Tenancy\Features\CrossDomainRedirect::class,
    // Stancl\Tenancy\Features\ViteBundler::class,
    Stancl\Tenancy\Features\DisallowSqliteAttach::class,
],

How it works

The authorizer feature essentially registers a function that works like this:

function authorize(int $action, ...) {
    if ($action == SQLITE_ATTACH) {
        return SQLITE_DENY;
    } else {
        return SQLITE_OK;
    }
}

(The actual code is in C and a tiny bit more complicated, but the code is basically equivalent.)

Wait, in C? Yes, in C. Before PHP 8.5, PDO SQLite didn’t support setAuthorizer() but did support $sqlite->loadExtension() so we simply bundle a tiny SQLite extension for a few common platforms in the package. It doesn’t take much space so it’s fine to distribute it like this.

 # |               name                |   size
---+-----------------------------------+----------
 0 | extensions/Makefile               |    883 B
 1 | extensions/lib                    |    224 B
 2 | extensions/lib/arm                |    160 B
 3 | extensions/lib/arm/noattach.dylib |  16.9 kB
 4 | extensions/lib/arm/noattach.so    |  69.4 kB
 5 | extensions/lib/noattach.dll       | 102.9 kB
 6 | extensions/lib/noattach.dylib     |  16.9 kB
 7 | extensions/lib/noattach.so        |  15.3 kB
 8 | extensions/noattach.c             |    699 B

This means that in PHP >= 8.5, the native Pdo\Sqlite::setAuthorizer() should be used. In PHP 8.4, Pdo\Sqlite::loadExtension() is used.

PHP builds differ

The presence of both of these methods depends on some compilation flags, so depending on how your PHP was built, you might have both, only one, or neither.

That said, the vast majority of dev builds (Homebrew, Laravel Herd, …) and production builds (package managers on Linux distributions, Docker) should have both of these enabled.

The loadExtension() approach supports, as you can see above, 64 bit: Windows (x86), Linux (x86, ARM), macOS (x86, ARM). The setAuthorizer() approach is independent of architecture, depending only on the PHP build itself.

Note that since we cannot 100% guarantee the feature will work on every possible machine, as it’s not just vanilla PHP, we simply do nothing if the feature cannot be used. There’s no exception thrown, the logic just silently fails. This is to avoid blocking the application because of a minor feature (which as explained above is mostly a security enhancement, not a critical feature) if it doesn’t happen to be available on some dev machine.

To assert that the feature did load on your machine, you can write a simple test like this:

SqliteAttachTest.php

use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;

test('sqlite ATTACH is not allowed', function () {
    config(['database.connections.noattachtest' => [
        'driver' => 'sqlite',
        'database' => ':memory:',
    ]]);

    $db = DB::connection('noattachtest');

    expect(fn () => $db->statement('attach ":memory:" as mem'))
        ->toThrow(QueryException::class, 'not authorized');
});

It will pass if DisallowSqliteAttach is enabled and fail if it’s disabled. If sqlite is your central/test connection, you can omit the steps that set up a new connection and just use DB::statement('attach ...').

Footnotes

1. The attacker would actually need to be able to hijack the beginning of one statement, and hijack another query/statement to be able to actually query the database. It’s possible there might be an edge case that would let someone run multiple statements or queries from just one SQL string, but from brief testing I do not believe this is possible with PDO as it tries hard to prevent exactly this kind of thing from happening. ↩