Using SQLITE_DIRECTONLY instead of SPATIALITE_SECURITY=relaxed

31 views
Skip to first unread message

Even Rouault

unread,
Apr 5, 2021, 3:46:15 PMApr 5
to spatiali...@googlegroups.com
Hi,

I just read through
https://www.sqlite.org/c3ref/c_deterministic.html#sqlitedirectonly and
it seems that it may be possible to declare "dangerous" function with
SQLITE_DIRECTONLY in sqlite3_create_function_v2 instead of relying on
SPATIALITE_SECURITY=relaxed

"""

SQLITE_DIRECTONLY
    The SQLITE_DIRECTONLY flag means that the function may only be
invoked from top-level SQL, and cannot be used in VIEWs or TRIGGERs nor
in schema structures such as CHECK constraints, DEFAULT clauses,
expression indexes, partial indexes, or generated columns. The
SQLITE_DIRECTONLY flags is a security feature which is recommended for
all application-defined SQL functions, and especially for functions that
have side-effects or that could potentially leak sensitive information.

"""

At least *when* #ifdef SQLITE_DIRECTONLY is available at build time. It
is relatively "recent". I see that sqlite 3.11 does not have it, but
3.31 has it.

Even

--
http://www.spatialys.com
My software is free, but my time generally not.

Alessandro Furieri

unread,
Apr 19, 2021, 11:01:27 AMApr 19
to SpatiaLite Users
Hi Even,

thanks a lot for signaling this interesting new feature of SQLite;
I've completely missed to notice it.

just for precision: it was introduced by version 3.31.0 released
on 2020-01-22

few considerations: by declaring the SQLITE_DIRECTONLY flag all
security-sensible SQL functions will be forbidden to be called 
in the context of TRIGGERs and VIEWs, or in CHECK and DEFAULT clauses.

this seems to be a robust security check ensuring against many
possible malicious attacks, but it seems to me that it's not enough
to completely prevent an insidious threat.

what will happen if a third party SQL script will exploit such 
dangerous SQL functions in order to steal sensitive data ?
simply declaring SQLITE_DIRECTONLY will not offer any protection
in such a scenario.

I'm inclined to thiks that declaring SQLITE_DIRECTONLY whenever
is possible surely is a good thing, but I'm not really sure that 
it could be a reasonable full replacemente for 
SPATIALITE_SECURITY_RELAXED that has a deeper and broader approach.

bye Sandro
Reply all
Reply to author
Forward
0 new messages