tl;dr: What are our options in php to make SQLite not write files when given malicious SQL queries as a hardening measure against SQL injection?
One of the most famous web application security vulnerabilities is the SQL injection.
This is where you have code like:
doQuery( "SELECT foo1, foo2 from bar where baz = '" . $_GET['fred'] . "';" );
The attacker goes to a url like ?fred='%20UNION%20ALL%20SELECT%20user%20'foo1',%20password%20'foo2'%20from%20users;--
The end result is: doQuery( "SELECT foo1, foo2 from bar where baz ='' UNION ALL SELECT user 'foo1', password 'foo2' from users ;-- ';" );
and the attacker has all your user's passwords. Portswigger has a really good detailed explanation on how such attacks work.
In addition to dumping all your private info, the usual next step is to try and get code execution. In a PHP environment, often this means getting your DB to write a a php file in the web directory.
In MariaDB/MySQL this looks like:
SELECT '<?php system($_GET["c"]);?>' INTO OUTFILE "/var/www/html/w/foo.php";
Of course, in a properly setup system, permissions are such that mysqld/mariadbd does not have permission to write in the web directory and the DB user does not have FILE privileges, so cannot use INTO OUTFILE.
In SQLite, the equivalent is to use the ATTACH command to create a new database (or VACUUM). Thus the SQLite equivalent is:
ATTACH DATABASE '/var/www/html/w/foo.php' AS foo; CREATE TABLE foo.bar (stuff text); INSERT INTO foo.bar VALUES( '<?php system($_GET["c"]);?>' );
This is harder than the MySQL case, since it involves multiple commands and you can't just add it as a suffix but have to inject as a prefix. It is very rare you would get this much control in an SQL injection.
Nonetheless it seems like the sort of thing we would want to disable in a web application, as a hardening best practice. After all, dynamically attaching multiple databases is rarely needed in this type of application.
Luckily, SQLite implements a feature called run time limits. There are a number of limits you can set. SQLite docs contain a list of suggestions for paranoid people at https://www.sqlite.org/security.html. In particular, there is a LIMIT_ATTACH which you can set to 0 to disable attaching databases. There is also a more fine grained authorizer API which allows setting a permission callback to check things on a per-statement level.
Unfortunately PHP PDO-SQLITE supports neither of these things. It does set an authorizer if you have open_basedir on to prevent reading/writing outside the basedir, but it exposes no way that I can see for you to set them yourself. This seems really unfortunate. Paranoid people would want to set runtime limits. People who have special use-cases may even want to raise them. I really wish PDO-SQLITE supported setting these, perhaps as a driver specific connection option in the constructor.
On the bright side, if instead of using the PDO-SQLITE php extension, you are using the alternative sqlite3 extension there is a solution. You still cannot set runtime limits but you can set a custom authorizer:
$db = new SQLite3($dbFileName);
$db->setAuthorizer(function ( $action, $filename ) {
return $action === SQLite3::ATTACH ? Sqlite3::DENY : Sqlite3::OK;
});
After this if you try and do an ATTACH you get:
Warning: SQLite3::query(): Unable to prepare statement: 23, not authorized in /var/www/html/w/test.php on line 17
Thus success! No evil SQL can possibly write files.
