After calling gpkgAddGeometryTriggers() on a table, any attempt to update the table's geometry field fails with an error saying "Unsafe use of ST_SRID." I'm not sure why this issue hasn't shown up sooner or for more people. It appears to be the result of a sqlite security patch from 2+ years ago that won't let triggers call user/extension defined functions added with any of the sqlite3_create_function* functions unless specific conditions are met -- the pragma trusted_schema has to be on or the function has to be created with the SQLITE3_INNOCUOUS flag.

Similarly calling gpkgAddSpatialIndex throws the error "Unsafe use of virtual table". That can be fixed by calling sqlite3_vtab_config in the xCreate method of the virtual table implementation and passing in SQLITE_VTAB_INNOCUOUS or by turning on the trusted_schema pragma.

According to the Sqlite documentation the trusted_scema pragma is turned on for every connection by default unless a compile-time flag is set to turn it off. I'm not sure why it's off in my implementation -- I'm running sqlite in a condo environment installed from condo-forge. But in any case, It seems like these spatialite functions should work even when trusted_schema is turned off (which sqlite recommends be done for each connection even if the pragma isn't off by default), assuming the underlying functions/vtab implementations are safe.

Adding that this occurs when running these functions and inserting geometry fields from a sql script run using the sqlite3 command line utility.

Running on Mac OS in a conda/mini-conda environment on an M1 chip

yes, you are absolutey right.

after declaring PRAGMA trusted_schema=0 SpatiaLite is almost completely forbidden to correctly work, because for security reasons SQLite will then block any attempt to call any Spatial function from within Triggers or Views.

unhappily SpatiaLite critically depends on many Triggers calling Spatial functions, and an absolute disaster will quickly follow.

conclusion: all current versions of SpatiaLite (5.1.0 or any provious one) can work only if PRAGMA trusted_schema=1 is set but fortunately this is the default setting and this explains why no one has ever reported this problem before now.

solution adopted: the next version of libspatialite will take care of declaring all extended SQL functions and all VirtualTables as SQLITE_INNOCUOUS, so to allow all them to work as expected also when PRAGMA trusted_schema=0 is set.

the corrected code has already been committed into the Fossil repository.

Thank you very much for noticing and reporting this potentially very critical issue