Executing SQL queries using a file in special format

Question

I am trying to write a script that reads lines from a file containing:

a filename of an SQL script, and
some SQL queries on the same line

and launches a query.

There is a list.txt file in the following format:

query.sql; set @var:='prod';

where query.sql is a file with SQL queries, e.g.:

select *
  from db
 where system=@var

I am trying to write a Bash script that executes the queries like this:

mysql -uroot -proot -e 
    "set @var:='prod'; 
     select *
       from db
      where system=@var;"

Here is what I have tried so far:

while read line;
    do app=$(echo $line  | awk '{for (i=2; i <= NF; i++) printf FS$i; print NL }';
    cat `echo $line | awk -F\; '{print $1}'`; echo ";");
    mysql -uroot -proot -e "`echo $app`"
done < list.txt

But I am having an SQL error, because the shell does not escape well the * character in the query.sql.

Debugging my code I obtain:

$echo $app
$set @var:='prod'; select a list.txt mysql query1.sql query.sql from db where system=@var ;

How can I adjust the code in order to have

$echo $app
$set @var:='prod'; select * from db where system=@var ;

?

Further details

There is a loop in the awk command, because the file list.txt may contain multiple queries, e.g.:

query.sql; set @var:='prod'; @a=asd; @b=zxc, @...=...;

My mistake, just edited the question (it was the real query I have on server) — Alessandro Blasetti
– Alessandro Blasetti, Commented Nov 22, 2016 at 14:32
@AlessandroBlasetti, you want to execute the second SQL statement (after the first ;), then the statements from the file specified before the first ;, right? — Ruslan Osmanov
– Ruslan Osmanov, Commented Nov 22, 2016 at 14:33
@RuslanOsmanov I'd like to execute my last row: set @var:='prod'; select * from db where system=@var ; — Alessandro Blasetti
– Alessandro Blasetti, Commented Nov 22, 2016 at 14:37

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

I don't think you need to read the SQL files line by line. Assuming that there are no semicolons in the filenames, you can construct the query with the following AWK command:

awk -F\; '{
 for (i = 2; i <= NF; i++) {
   if ($i != "") printf("%s;", $i);
 }
 printf("source %s;\n", $1)
}' list.txt | mysql -uroot -proot

The command uses ; as a field separator. The for loop prints all fields starting from the second. The last printf function builds a query sourcing the SQL file.

The Cause of the Error

Your code fails to pass the correct SQL to the MySQL client because of this line:

mysql -uroot -proot -e "`echo $app`"

The shell interprets the special characters within the $app variable. In particular, the * character is expanded to "every file in the current directory". To prevent interpreting, use weak quoting, i.e. enclose the variable in double quotes: "$app":

When referencing a variable, it is generally advisable to enclose its name in double quotes. This prevents reinterpretation of all special characters within the quoted string -- except $, (backquote), and \ (escape)...

Keeping $ as a special character within double quotes permits referencing a quoted variable ("$variable"), that is, replacing the variable with its value

Also, there is no need for subshell expression (the backticks and the echo), if you want to get the value of the variable:

mysql -uroot -proot -e "$app"

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 22, 2016 at 14:38

Ruslan Osmanov

21.8k8 gold badges52 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alessandro Blasetti Over a year ago

awk -F\; '{for (i = 2; i <= NF; i++) {if ($i != "") printf("%s;", $i);} printf("source %s;\n", $2)}' list.txt gives me set @var:='prod';source set @var:='prod'; and not the set @var... ; query

Alessandro Blasetti Over a year ago

Yes you are right, I was modifying on my own and it works! Great

Alessandro Blasetti Over a year ago

Just to understand better bash scripting (I am a newbie), do you know which was the problem in my code?

Ruslan Osmanov Over a year ago

@AlessandroBlasetti, I've added explanation of the issue in your code

Ruslan Osmanov Over a year ago

@AlessandroBlasetti, of course. For example, you can simply modify the AWK code accordingly (how? It depends on what you mean by "other commands".)

|

Collectives™ on Stack Overflow

Executing SQL queries using a file in special format

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related