How to embed BASH variable in MySQL query in shell script file

Question

#!/bin/bash
fut=$(date -d "+14 days" +'%Y-%m-%d')
echo $fut
mysql -u user -ppassword base1 << "EOF"
INSERT INTO tableB SELECT * FROM tableA WHERE startdate < $fut;
....many lines of MySQL....
EOF

Mysql engine doesn't understand $fut as a variable. What syntax should be applied to make this work?

Almost any dialect of SQL should have a way of generating a timestamp internally without needing to inject a value via the shell. In this case, ADDDATE(CURDATE(), 14) looks to be the necessary SQL code. — chepner
– chepner, Commented Feb 18, 2017 at 20:30

Fred · Accepted Answer · 2017-02-18 18:58:19Z

2

I have no problem with using a here-document, but I would like to suggest another possibility which is slightly more verbose, but has advantages when you may have to do more than just substituting variables : piping the output of a function.

#!/bin/bash

sql_statement()
{
  local fut=$(date -d "+14 days" +'%Y-%m-%d')
  echo "INSERT INTO tableB SELECT * FROM tableA WHERE startdate < $fut;"
  echo "....many lines of MySQL...."
}

mysql -u user -ppassword base1 < <(sql_statement)

This is not required in this specific case, but allows the function to have additional logic (e.g. if blocks, loops...) to generate a more complex or variable SQL statement. As a general solution, it has nice advantages.

answered Feb 18, 2017 at 18:58

Fred

7,09512 silver badges22 bronze badges

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

1 Comment

Fred Over a year ago

The comment from Mark Reed about security applies here too, and is important to understand. At some point, though, a system has to interact with data from outside, so at some point in the stack there has to be a competent programmer that controls or sanitizes the data being used, and a competent administrator that protects the system from being tampered with (because, in the end, if you don't trust your shell, your system utilities, your database engine not being infected or buggy, you can't really accomplish much).

Stephen Rauch · Accepted Answer · 2017-02-18 18:33:40Z

1

You need to remove the " from the EOF:

#!/bin/bash
fut=$(date -d "+14 days" +'%Y-%m-%d')
echo $fut
mysql -u user -ppassword base1 << EOF
INSERT INTO tableB SELECT * FROM tableA WHERE startdate < $fut;
....many lines of MySQL....
EOF

From bash docs:

No parameter substitution when the "limit string" is quoted or escaped.

answered Feb 18, 2017 at 18:33

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

2 Comments

Mark Reed Over a year ago

Any time you are doing simple string substitution into an SQL query, you open up the possibility of injection attacks. In this case, the value of $fut is being set by the code, not an outside source, so the risk is negligible. But in general, you should consider using a language binding that lets you do prepared statements with parameter substitution handled at the MySQL tier. That includes Perl, Python, Ruby, any JVM language. In my opinion, interacting with an RDBMS is a good "time to move up from bash to something else" signpost.

Mark Reed Over a year ago

Note that this differs from e.g. Perl, which respects the type of quotes: substitution within <<"EOF", but not within <<'EOF'. The shell, on the other hand, turns off substitution with any quoting; even <<\EOF has that effect.

Collectives™ on Stack Overflow

How to embed BASH variable in MySQL query in shell script file

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related