SQLite Forum

Why can you write triggers using functions from extensions? How does that work?

Why can you write triggers using functions from extensions? How does that work?

(1) By Isofruit2 on 2021-07-10 14:02:50 [link] [source]

Hello everybody,

This is more a conceptual question on why something works as opposed to how to get something work, because it's confusing me.

So, you can load the extensionfunctions.so extension from Liam Haley while running sqlite3. This gives you access to functions such as "reverse()" that default sqlite3 does not have.

Now I have noticed that you can of course also write triggers using these functions. What confuses me, is that I can create such a trigger, close and re-open sqlite3, insert something and the trigger still works, despite extensionfunctions.so not being loaded now. Which I would have assumed to be necessary since it's the library where that functionality originates from.

How does this work? Am I storing the binary code necessary for the function in the sqlite3 file when creating the Trigger?

(2.1) By Keith Medcalf (kmedcalf) on 2021-07-10 16:55:33 edited from 2.0 in reply to 1 [link] [source]


(3) By Ryan Smith (cuz) on 2021-07-10 21:33:36 in reply to 1 [link] [source]

That sounds like dark magic.... are you sure that worked?

Was the extension library not merely "remembered"? Does it work when you remove the library completely (delete the .dll/.so from the disk)?

So far as I understood, you had to load the library every time to use it in the way you suggest (which is rather easily automated).

To ensure I (and others) understand correct, this is the claim:

  • Load an external library containing a function that does not exist in vanilla SQLite.
  • Use said function in a function-based Index or Trigger.
  • Unload the library/Restart the app, no more library, trying to use the function in a normal SQL statement produces an error.
  • The Index/Trigger however still works when used/triggered.

If any of those statements do not hold, please indicate which and how.

(4) By Larry Brasfield (larrybr) on 2021-07-10 22:53:26 in reply to 3 [source]

I reply to Ryan, but this is really for the OP:

Ryan's "dark magic" is a colorful way to ask you to pierce a certain sort of mist that is often created by people seeking a problem solution or mystification clearing. A poster will say "I did X, Y and Z, then see A and B." followed by "How can I get C?" or "Why does B happen?" But some or all of X, Y, Z, A and B are not accurate descriptions of something that actually did or could happen in the real world. Instead, some of them are incorrect inferences of what happened, or summaries of what happened that cover a great many possible real-world happenings.

Ryan has restated your sequence of events and observations in a more concrete way, knowing that, as his verbiage is understood by most folks speaking SQLite-ese here, that sequence cannot occur. (Hence, it's dark magic.) He invites you to better restate which of his restatements does not correspond to the reality of your reported computer experience.1 I encourage you to carefully examine and answer his request for improvements of his restatement. It will help us to help you.

  1. I do not demean Ryan's method. It may work wonders for getting from the land of nebulosity to a more real-world detailed scenario.

(5) By Isofruit2 on 2021-07-11 09:01:20 in reply to 3 [link] [source]

Thank you Ryan and also Larry very much!

I agree fully with your and Larry's statement and it is definitely my bad that I did not write all the necessary details. I'll endeavour to do better.

Your description is very close to what I experienced. Your request for clarity did lead to me testing a lot more and finding the culprit somewhere else though.

I have a web application using the Django web framework and an sqlite3 database. Django, in an attempt to abstract databases away, just provides a generic "connection" object that could just as easily be for an Oracle or Postgres database. What I had not considered was, that Django might load any sqlite3 extensions on its own to reach feature parity with other databases and thus be able to do on sqlite3 the same thing as on Postgres and Co. That is mentioned in no piece of documentation of theirs that I could find that mentioned sqlite. Thus I had assumed they were merely using a normal sqlite3 binary and nothing else.

What I did was:

  1. Open the database using sqlite3 ("sqlite3 db.sqlite3")
  2. Load an extension (".load extension-functions.so" , I had moved extension-functions.so to /usr/lib/dev for this to be easy)
  3. Write an "update" Trigger that uses the "reverse" function of said extension. ("CREATE TRIGGER triggername AFTER UPDATE ON tablename BEGIN UPDATE tablename SET col1 = reverse(new.col1) WHERE id = new.id; END;")
  4. Close the connection (".quit")
  5. Start the application server locally
  6. Trigger the trigger from the web application by updating a dataset
  7. Open the database using sqlite3 again and observe the table to see whether the trigger had inverted one of the columns as intended. This had been the case and worked thusly.
  8. Try to use reverse() on the current connection with sqlite3 - That did not work, which is as expected since I had not loaded the extensionfunctions.so manually.

Since I had assumed Django to be just using vanilla sqlite3, my next assumption was that sqlite3 itself was thus somehow performing dark magic. Since we have excluded that now, my current working assumption is that Django itself is loading some sqlite3 extensions without having documented it.

With that context, my reply to your questions:

Was the extension library not merely "remembered"?

Django in this scenario has no way of knowing I connected from outside of the application using sqlite3 to the database file. My self-compiled binary and Django don't have any connection to my knowledge, other than that they both accessed the same database "db.sqlite3" file.

Does it work when you remove the library completely (delete the .dll/.so from the disk)?

It does, leading me to further believe it might be Django related.

So far as I understood, you had to load the library every time to use it in the way you suggest (which is rather easily automated).

I had so far only tried to use functions of said library when I had manually loaded it myself and had opened a database file using sqlite3. I had never even attempted such a thing while firing SQL statements with a Django connection to that file instead. When I did test it, yeah, reverse() did work surprisingly without any extensions loaded manually by me.

(6) By Ryan Smith (cuz) on 2021-07-11 10:12:22 in reply to 5 [link] [source]

Well, sounds like you found the culprit, the magic is less dark when the agent facilitating it is understood.

Given the magic is repeatable and part of the core of your system, I can add that if your app and framework will be replicated as-is on target user machines, then you should have no problem using said features freely and copiously.

If however your DB is ever intended to be shared to any other App or inspected or used with some SQLite DB management tool, that library and perhaps some accompanying documentation will need to be made available and described well.

Thus, answering your original question: There is no "byte-code" that gets saved into the DB file and its continued functioning is wholly dependent on the availability and attaching of needed byte-code used in internal DDL/SQL/UDFs, on the target system.

I've often mused at the beauty of having this - save .dll/.so files as BLOBs in a special table, and on the target system have it used automatically - perhaps by saving temporarily in temp storage, or alongside journal/.shm files etc. and loading it automatically when needed.

However, the World of vulnerabilities that would open up is too insane to contemplate. Every DB file would become a Trojan horse by design. So perhaps not. :)

Good luck!