Accessing sqlite3 with xparse (under XeLaTeX)

Question

I was very happy when I read egreg's 2019 Addendum about how to launch a shell script from LaTeX and grab the output.

But when I tried to use it to connect with a SQLite database, I got a LaTeX3 error. Here is the (minimal) code:

\documentclass{article}
\usepackage{xparse}
\ExplSyntaxOn
\NewDocumentCommand{\captureshell}{som}
 {
  \sdaau_captureshell:Ne \l__sdaau_captureshell_out_tl { #3 }
  \IfBooleanT { #1 }
   {% we may need to stringify the result
    \tl_set:Nx \l__sdaau_captureshell_out_tl
     { \tl_to_str:N \l__sdaau_captureshell_out_tl }
   }
  \IfNoValueTF { #2 }
   {
    \tl_use:N \l__sdaau_captureshell_out_tl
   }
   {
    \tl_set_eq:NN #2 \l__sdaau_captureshell_out_tl
   }
 }
\tl_new:N \l__sdaau_captureshell_out_tl
\cs_new_protected:Nn \sdaau_captureshell:Nn
 {
  \sys_get_shell:nnN { #2 } { } #1
  \tl_trim_spaces:N #1 % remove leading and trailing spaces
 }
\cs_generate_variant:Nn \sdaau_captureshell:Nn { Ne }
\ExplSyntaxOff
\begin{document}
\captureshell{sqlite3 /hom/yannis/texmf/yannis.db 'select title from title where id="grafematik2020/hara/hara.tex"'}
\end{document}

The error says I'm not allowed to use double-quotes in shell commands:

! LaTeX3 Error: Quotes in shell command 'sqlite3 /hom/yannis/texmf/yannis.db
(LaTeX3)        'select title from title where
(LaTeX3)        id="grafematik2020/hara/hara.tex"''.
For immediate help type H <return>.
 ...
l.34 ... where id="grafematik2020/hara/hara.tex"'}
? H
Shell commands cannot contain quotes (").

How can I nest a string inside an SQL command inside a shell command line, without using double-quotes?

Writing the value of the id argument without quotes is against SQL syntax: it considers it as a column name…

[PS. No, I do not wish to use LuaTeX or Context, even though I know that they are better suited for talking with sqlite3.]

That will be hard (if not impossible) because the web2c implementation of \input drops quotes when scanning a file name (even if you use the syntax with braces available since last year), so when you do the command \input{|ls "/some/path/with spaces"} it will execute the shell command ls /some/path/with spaces. expl3 just warns you that your command won't work as intended due to the engine limitation. I'm afraid you'll have to find your way around without the quotes — Phelype Oleinik, Jan 18 '21 at 14:00
Last year Karl and I tried to find a way to preserve quotes with the (plain) syntax \input{...}, but that would be inconsistent with other primitives like \pdffilesize, that take a braced file name and also drops quotes. Basically we're stuck with no quotes in \input :( — Phelype Oleinik, Jan 18 '21 at 14:03
Are double quotes " the only character I cannot use? At worst I can go through a filter that will systematically replace some other character (for example ＂, the full-width version of the double quote) into double quotes. — yannis, Jan 18 '21 at 14:06
if sqlite accepts single quotes you could use \' to get a single quote in to a '-delimted string at the shell level,but a generic way to avoid all quoting issues is to use \immediate\write to write the entire command line to tmp.sh then use write 18 to execute bash ./tmp.sh — David Carlisle, Jan 18 '21 at 14:10
@yannis As far as I recall, yes, only " (ASCII 34). Knuth's original implementation stopped scanning a file name at a space character, so the quote syntax was introduced to allow \input "/some/file/with spaces", and the implementation recklessly kills all " in the string. — Phelype Oleinik, Jan 18 '21 at 14:11

score 1 · Answer 1 · answered Jan 19 '21 at 13:37

You can't have (directly) have double quotes " (ASCII 34) in shell commands for the same reason you can't have them in file names in web2c TeX implementations (TeX Live, MiKTeX, etc.).

Shell-escaping is implemented as a pseudo-file, so you use I/O primitives (\openin, \write, \input) to interact with the shell, thus all restrictions for file names apply for shell commands.

Knuth's original TeX implementation did not allow spaces in file names (for the greater good :) because it used a blank space as the terminator of a file name. You can still see that implementation if you use the primitive \input (in LaTeX it is \@@input) syntax:

\input my_file.tex text after

the line above will input my_file.tex, do whatever is in there, then typeset text after.

To allow spaces in file names, in 2004 Olaf W implemented quoted file names as a filesystem-dependent change in TeX (thus affecting Knuth TeX), so you can type:

\input "file with spaces.tex" text after

but the implementation also allows you to type

\input file" "with" "spaces.tex text after

and it will remove all quotes in the process, while preserving spaces found after an odd number of quotes.

The issue with that implementation is that quotes are removed from a file name, no matter what (even with braced filename syntax), so it is currently not possible to use quotes anywhere in a file name (thus not anywhere in a shell command: expl3 only stops you from using that command because it will likely not work as you expect without the quotes).

That said, that limitation is for file names handled from within TeX. David's suggestion should work fine because quotes don't go through TeX's file name parsing:

\newwrite\tmp
\openout\tmp{tmp.sh}
\immediate\write\tmp{ls "/some/path/with spaces/"}
\closeout\tmp
\input{|bash tmp.sh} % \input{"|bash tmp.sh"} if in LaTeX, or your own \captureshell command

Accessing sqlite3 with xparse (under XeLaTeX)

1 Answers1