SQLite Forum

How to ignore "no such function" in metadata creation
Login

How to ignore "no such function" in metadata creation

(1) By anonymous on 2020-12-08 15:58:39 [link] [source]

I have a simple application that defines several user functions. I want to create some views in the database that use these functions. The application is simple enough to not try to create all the metadata by itself and would rather rely on a metadata script and sqlite3 to do that.

However sqlite3 produces the "no such function" error and doesn't create the views that use the user-defined functions. Is there a way to make it ignore this and create the view anyway?

I guess that should be possible, because I can do it in the SQLiteSpy Windows tool - the view gets created, marked by a big red exclamation mark and, of course, cannot be used outside my application, but works OK within the application.

(2) By Larry Brasfield (LarryBrasfield) on 2020-12-08 16:19:22 in reply to 1 [link] [source]

You could put your "user functions" into an extension DLL and load it before creating views which use the functions. If you only wish to use sqlite3 for DDL, the functions would not even need to have real implementations; they could return NULL.

(3) By Richard Hipp (drh) on 2020-12-08 17:11:20 in reply to 1 [link] [source]

Security Sidebar

I have a simple application that defines several user functions. I want to create some views in the database that use these functions.

Do your functions have side-effects? If so, then an attacker who can control the content of the database file might be able to trick your application into invoking those functions and causing the side-effects without your knowledge.

For example, suppose one of your functions is "send_money(AMT,ACCOUNT)". And suppose that your application runs "SELECT * FROM config;" at startup, in order to read configuration data. If an attacker can write to the database file while you are not looking, then he might do something like this:

    ALTER TABLE init RENAME TO real_init;
    CREATE VIEW init AS SELECT * FROM real_init
      WHERE send_money(100000.0,'my-swiss-bank-account')<>'blahblah'
         OR 1+2==3;

Then, the next time you run your application, when it does its initial "SELECT * FROM init;" it will also send $100K to the attackers bank account without you even knowing!

Among the many defenses against this kind of attack are:

  1. Don't create application-defined SQL functions that have side-effects.

  2. If you really need application-defined SQL functions that have side effects, then at least mark them as SQLITE_DIRECTONLY. This prevents them from being run inside of triggers and views and CHECK constraints and anyplace else that a clever hacker might leave them laying around.

See the Defense Against Dark Arts document, and especially item 8 under paragraph 1.2 for additional defensive tactics.

(4.1) By Keith Medcalf (kmedcalf) on 2020-12-08 20:50:14 edited from 4.0 in reply to 1 [link] [source]

Compile the library/shell/whatever you want to ignore unknown functions with ENABLE_UNKNOWN_SQL_FUNCTION defined.

This will defer the no such function error from parse time to execution time.

SQLite version 3.35.0 2020-12-08 08:34:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view x as select dingus(1);
sqlite> select * from x;
Error: no such function: dingus
sqlite>

Technically, the parser will substitute a call to any function that it does not know about as a call to the UnknownFuction function. This will allow the parse stage to pass but the UnknownFunction function will return an error if it is ever actually executed.

(5) By anonymous on 2020-12-08 22:01:44 in reply to 4.1 [source]

Thanks, I think this is exactly what I was looking for. If I understand right, this is a build time switch, so I will need a custom built sqlite3, but I hope that will not be so difficult.

Does anyone agree that it would be nice if it could be surfaced as an sqlite3 option or pragma or something similarly 'runtime'?