A tangent from another thread.

This part of src/shell.c.in handling the .shell command makes me wince. Was it written before sqlite3_str existed and never revisited?

    zCmd = sqlite3_mprintf(strchr(azArg[1],' ')==0?"%s":"\"%s\"", azArg[1]);
    for(i=2; i<nArg && zCmd!=0; i++){
      zCmd = sqlite3_mprintf(strchr(azArg[i],' ')==0?"%z %s":"%z \"%s\"",
                             zCmd, azArg[i]);
    }
    x = zCmd!=0 ? system(zCmd) : 1;
    sqlite3_free(zCmd);

Furthermore, the quoting doesn't work well with either the Bourne shell or whatever Windows program the system function invokes there. (Empty arguments vanish, for instance.)

Suggestion: deprecate .shell and add a new dot command with a different name that accepts exactly one argument and passes it unchanged to the system function.

I don’t know why people assume that spawning commands always has to go through a shell, with all the complications of shell command syntax. Much safer to use something like posix_spawn wherever possible.

When a programmer wants to run another command, it is indeed safer to bypass the shell and build the command line arguments yourself. However, it's a different matter when you want to enable an interactive user to specify external commands to be run. The user may reasonably wish to be able to use pipelines and redirection and so forth, and then the shell comes in really handy.

The problem here is that sqlite3 is a shell with a quoting syntax of its own, and it is oft-lamented fact that multilayered quoting inevitably leads to hair loss.

Perhaps one solution would be to have a version of the .shell command that does not even try to do any parsing at all, but passes the rest of the line directly to the shell. That way, the user only has to worry about shell syntax with its quoting rules. You won't be able to insert a literal newline in that command line, but how often is that needed?

For example, systemd supports variable substitutions and one or two other niceties, but no redirections or pipes.

The latter part of the statement is not true on Windows: the .system or .shell supports both redirection and pipes

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> /* Platform */
sqlite> .shell ver

Microsoft Windows [Version 10.0.22621.1555]
sqlite>
sqlite> /* Redirection */
sqlite> .shell echo .open ./db/chinook.db > xec.sql
sqlite>
sqlite> /*cummulative redirection */
sqlite> .shell echo select employeeid,city from employees limit 1 >> xec.sql
sqlite>
sqlite> /* Get the file name knowing the extension using PIPE  & switches*/
sqlite> .shell dir /b *.sql | findstr xec
xec.sql
sqlite>
sqlite> /* see the file's content */
sqlite> .shell type 'e:\temp\xec.sql'
.open ./db/chinook.db
select employeeid,city from employees limit 1
sqlite>
sqlite> /* execute the SQL in that file */
sqlite> .read e:/temp/xec.sql
EmployeeId  City
----------  --------
1           Edmonton
sqlite>
sqlite> /* output to clipboard using PIPE */
sqlite> .once | clip
sqlite> select date() || ' ' || time() as tstamp;
sqlite> /* no output in the session */
sqlite> /* session output written to clipboard */

Lawrence wrote “systemd,” not “.system” and from the doc link, that plainly is not a typo. He’s using Linux’s systemd as an example, being another widespread program that launches external programs and has good cause to restrict how it does that.

And I stated

The latter part of the statement is not true on Windows:

... just in case Windows users believe the claim to be true of their platform also.