SQLite Forum

Feature request: Dynamically execute SQL from shell
Login

Feature request: Dynamically execute SQL from shell

(1) By Tony Papadimitriou (tonyp) on 2020-05-03 18:42:06 [link]

Imagine something this:

```
create table sql(stmt,notes);
insert into sql values('update t set name = trim(name)','Trim all names in t');
...

exec(select stmt from sql);
```

This would execute each SQL statement retrieved by `SELECT` from the sql table.

Many applications, such as automatically conditioning data by running multiple SQL statements in sequence.

(2) By Simon Slavin (slavin) on 2020-05-03 19:10:04 in reply to 1 [link]

This has been previously considered but was rejected.  One of the reasons was that it would be very difficult to protect against exploits which involve editing the database to include harmful commands.  Such exploits might not only delete/change the database but also influence what the program accessing it did.

You might want to check out

<https://sqlite.org/cli.html#database_content_self_tests>

which can do something like what you want.  This works only in the command line shell for SQLite (which can be scripted from your OS shell).  It will not influence any program which just calls the SQLite API.

(4.1) By Tony Papadimitriou (tonyp) on 2020-05-03 20:05:26 edited from 4.0 in reply to 2 [link]

I fail to see the dangers as this is meant to run from the shell only.
If you have access to the database from the shell you can run the same SQL statements anyway.  So, no difference.

Your link is nice but has two issues:

  1. All statements will run, not just the ones selected.
  2. A result must match the expected one.

(3) By Keith Medcalf (kmedcalf) on 2020-05-03 19:43:57 in reply to 1 [link]

There is an extension for that.  

See ext/misc/eval.c <https://www.sqlite.org/src/artifact/04bc9aada78c8883>

(5) By Tony Papadimitriou (tonyp) on 2020-05-04 08:03:01 in reply to 3 [link]

I tried it, and it appears to work well with the little testing I did.  Thanks.

Suggestion for improvement (unless it can already do this somehow I didn't see).

To make it more dynamic, allow more arguments with the first being the SQL statement and the remaining positional substitutes for the SQL statements.

Example:

`eval('update t set a=?, b=?','one','two')`

to do this:

`update t set a ='one', b='two'`

(8) By curmudgeon on 2020-05-04 13:32:49 in reply to 3 [link]

Hi Keith, is there an easy way to make extensions available to the shell? Ages ago you showed me how to make them available to my c code by adding a core_init function to bottom of the sqlite3.c amalgamation file and adding the wanted extensions.

#include "eval.c"
#include "carray.c"
#include "btreeinfo.c"

int core_init(const char* dummy)
{
	int nErr = 0;
	nErr += sqlite3_auto_extension((void(*)())sqlite3_eval_init);
#ifndef SQLITE_OMIT_VIRTUALTABLE
	nErr += sqlite3_auto_extension((void(*)())sqlite3_carray_init);
	nErr += sqlite3_auto_extension((void(*)())sqlite3_btreeinfo_init);
#endif
	return nErr ? SQLITE_ERROR : SQLITE_OK;
}

(10) By Keith Medcalf (kmedcalf) on 2020-05-04 16:45:47 in reply to 8 [link]

You can do the same thing when building the shell.  That is, the shell is merely an application which uses SQLite3, so you can extend builtin functionality the same way.

There is an additional caveat with the shell though.  There are (unfortunately) some extensions which are built into the shell which are not built into SQLite3 (that is, rather than adding them in the general case, they have been added only in the shell).  This causes all sorts of problem since you will end up with duplicate symbols and incompatibilities -- the best way I have found to fix these are to remove the extensions from the shell (where they do not belong) and add them to the SQLite3 core.

If you build your own amalgamation files (sqlite3.c/shell.c) this done rather easily by modifying the TCL scripts which "build" those files.  The primary caveat is that the hash returned by the -version will no longer match the "official versions" because it is the hash of the source, and you have changed the source.  It is, however, far easier to maintain than to keep track of changes to the amalgamation files -- it is merely a new fossil branch into which you merge changes from the trunk.

(12) By curmudgeon on 2020-05-04 17:23:22 in reply to 10 [link]

Thanks Keith. Thinking back I'm now realising I tried this and gave up after meeting the problems you describe. I'll give it another go.

(6) By Gisbert (gisbert) on 2020-05-04 12:39:52 in reply to 1 [link]

I may have misunderstood the question, but it seems all this (and more) should be possible using the scripting language of your choice to script the shell (as Simon has already suggested), even without any SQLite extensions. Or am I missing the point?

(7) By Tony Papadimitriou (tonyp) on 2020-05-04 12:57:58 in reply to 6 [link]

With that reasoning a lot of functionality could be removed from SQLite3 as it can also be done with scripting.  Actually, the whole shell could be removed. :)

(9) By Keith Medcalf (kmedcalf) on 2020-05-04 16:30:13 in reply to 7 [link]

You make the mistake of assuming that the SQLite3 CLI/shell *IS* SQLite3.

It is not.  It is merely an application which uses SQLite3 that happens to be brought to you by the same folks that write and maintain SQLite3.

(11.1) By Tony Papadimitriou (tonyp) on 2020-05-04 16:50:06 edited from 11.0 in reply to 9

I don't think I'm making any mistake.
I clearly stated in the title that I'm talking about the shell.

SQLite3 may be the name of the library (the SQL engine) but it also happens to be the official name of the compiled shell.

Don't you get `sqlite3[.exe]` when you compile?

It's also the name used for the shell inside the official pre-compiled binaries:

For example, [https://www.sqlite.org/2020/sqlite-tools-win32-x86-3310100.zip](https://www.sqlite.org/2020/sqlite-tools-win32-x86-3310100.zip)

At any rate, how does that invalidate my previous statement (about removing functionality from the shell to the point of not needing it anymore as the same functionality can be had from scripting)?