1

How can I execute multiple SQL queries in the bash script?

I read these two posts from previous years:

A better way to execute multiple MySQL commands using shell script

How to execute a MySQL command from a shell script?

They brought some clarification, but there is still something I do not understand.

I have multiple queries for deleting information about subject with defined subject_id. Unfortunately I need to run all of them since the table is not in the "cascade" mode.

Is there a way, to create a bash script in which I can use the "user given" variable (by that I mean for example [ read -p 'Subject ID' SUBJECT_ID ]) that will be used inside as the subject_id in each of the queries? Do I still have to adjust everything to this:

mysql -h "server-name" -u root "password" "database-name" < "filename.sql"

or is there a way to just run this script with connection to db from .cnf file inside it?

Michał Rózga
  • 83
  • 6

1 Answers1

2

There are two questions above. One is how to get a bash variable into your SQL script. I would do this:

read -p 'Subject ID' SUBJECT_ID

mysql -e "SET @subject = '${SUBJECT_ID}'; source filename.sql;"

Bash will expand ${SUBJECT_ID} into the string before it uses it as an argument to the mysql -e command. So the MySQL variable is assigned the string value of SUBJECT_ID.

This will be tricky if SUBJECT_ID may contain literal single-quote characters! So I suggest using Bash syntax for string replacement to make each single-quote in that into two single-quotes:

mysql -e "SET @subject = '${SUBJECT_ID//'/''}'; source filename.sql;"

Note you must put a semicolon at the end after the filename.

The second question is about specifying the host, user, and password. I would recommend putting these into an options file:

[client]
host=server-name
user=root
password=xyzzy

Then when you invoke the mysql client:

mysql --defaults-extra-file myoptions.cnf -e '...'

This is a good idea to avoid putting your plaintext password on the command-line.

Read https://dev.mysql.com/doc/refman/8.0/en/option-files.html for more details on option files.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you very much for the answer. Is it wiser to use the Bash syntax even if I am sure that the ID is an integer in all of the cases, just to be safe? – Michał Rózga Aug 15 '22 at 19:37
  • How can you be sure the ID is an integer if it is taken from user input? – Bill Karwin Aug 15 '22 at 19:43
  • I see your point, but it will be done by someone who knows they are always integers. But just in case of a mistake, I will do it properly. – Michał Rózga Aug 16 '22 at 08:18
  • Yes, because someday that person will change jobs and a new person needs to take over. Perhaps that person won't be so good at remembering the required format for the input. – Bill Karwin Aug 16 '22 at 14:53