0

I have the following command which does not work:

sqlite3 my_db.sqlite "SELECT name FROM sqlite_master WHERE type = 'table';" | for i in $(cat) ; do sqlite3 my_db.sqlite 'SELECT * FROM "${i}"'; done

To explain it quickly: the first part below is supposed to retrieve the table names from a sqlite file that I have:

sqlite3 my_db.sqlite "SELECT name FROM sqlite_master WHERE type = 'table';"

And this part is supposed to display the entire content of each table recursivelyin the stdout:

for i in $(cat) ; do sqlite3 my_db.sqlite 'SELECT * FROM "${i}"'; done

The problem is that I have no idea how I am supposed to pass i to the sqlite command. I tried with "${i}" but obviously it is interpreted as a classic string to find a matching table name, and just return Error: no such table: ${i}

How should I pass i ?

Thank you in advance for your help.

Yoann Pageaud
  • 412
  • 5
  • 22
  • Piping to `for i in $(cat)` is an odd way to read from standard input into a variable. You'll much more often see something like `while read -r i; do ...` – Benjamin W. Mar 14 '18 at 17:33
  • 1
    This is a [SQL injection attack](https://en.wikipedia.org/wiki/SQL_injection) waiting to happen, even if you use the correct quotes. – chepner Mar 14 '18 at 17:40
  • @BenjaminW. using while instead of for is really improving the code or it is just a more traditional way to proceed. I tried it with the while loop you wrote, it works also, but I don't really see any difference in the time of processing the speed is similar when I use the for loop. If you can explain me I am always open to learn more things! – Yoann Pageaud Mar 14 '18 at 17:57
  • @chepner don't worry this code is not meant to be available online. – Yoann Pageaud Mar 14 '18 at 17:59
  • For starters, `$(cat)` creates a child process in every iteration, whereas `read` is a built-in. You're also subject to word splitting and glob expansion - if you have a table called `*`, it'll expand to all files in the current directory. Have a look at [BashFAQ/001](https://mywiki.wooledge.org/BashFAQ/001), which applies here. – Benjamin W. Mar 14 '18 at 18:01
  • @BenjaminW. ok good to know. Thanks! – Yoann Pageaud Mar 15 '18 at 14:33

1 Answers1

0

You must use double quotes around the query to allow bash to recognize the variable. This implies that you have to correctly escape the double quotes that are part of the SQL statement:

... | for i ... ; do sqlite3 my_db.sqlite "SELECT * FROM \"${i}\""; done
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Yes ! it works. I was close, I read that somewhere but unfortunately it was for MySQL so I thought it wouldn't work. Thank you for the help! I accept the answer as soon as I can. – Yoann Pageaud Mar 14 '18 at 17:36