Passing variables to SQL script in bash

Question

I am currently writing a script in bash that uses an SQL file to run some queries to setup a database and table structure.

I need to pass some user inputted data to the script, but have hit a brick wall. This is the line I am using to run the SQL script:

mysql --user="root" --password="$mysql_password" --execute "SET @domain=${domain}" < mailserver-databases.sql

An example of one of the commands in the 'mailserver-database.sql' file that needs a variable passed to it is:

INSERT INTO `servermail`.`virtual_domains`(`id` ,`name`)VALUES('1', '@domain');

Where '@domain' would be the user inputted value - 'domain' is set from the following command:

echo -n 'Please enter the domain you wish to use > '
read domain

When I enter 'test.com' for the domain, it comes back with the following error:

ERROR 1109 (42S02) at line 1: Unknown table 'test' in field list

So it looks like the variable is being passed to the script, but I'm assuming there is a syntax error somewhere in my script

Here is the SQL file output:

DROP DATABASE IF EXISTS servermail;
CREATE DATABASE servermail;
GRANT SELECT ON servermail.* TO 'usermail'@'127.0.0.1' IDENTIFIED BY 'mailpassword';
FLUSH PRIVILEGES;
USE servermail;
CREATE TABLE `virtual_domains` (`id`  INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_users` (`id` INT NOT NULL AUTO_INCREMENT,`domain_id` INT NOT NULL,`password` VARCHAR(106) NOT NULL,`email` VARCHAR(120) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `email` (`email`),FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_aliases` (`id` INT NOT NULL AUTO_INCREMENT,`domain_id` INT NOT NULL,`source` varchar(100) NOT NULL,`destination` varchar(100) NOT NULL,PRIMARY KEY (`id`),FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `servermail`.`virtual_domains`(`id` ,`name`)VALUES('1', '@domain');

It rather looks like an issue with the SQL file not with what you're doing in bash. — fejese
– fejese, Commented Nov 13, 2014 at 11:35
@fejese I have updated my answer to include the full SQL script — Jake Neal
– Jake Neal, Commented Nov 13, 2014 at 11:41
ahh that's mus be because of missing quotation. see my answer's update — fejese
– fejese, Commented Nov 13, 2014 at 11:45

fejese · Accepted Answer · 2014-11-13 11:45:41Z

3

I don't think that in case you execute commands with -e|--execute it also executes commands sent to the standard input.

You can couple them together like this:

echo -n 'Please enter the domain you wish to use > '
read domain

{
  echo "SET @domain='${domain}';";
  cat mailserver-databases.sql;
} | mysql --user="root" --password="$mysql_password"

Also if you want to use the variable's value do not wrap the variable in quotes:

INSERT INTO `servermail`.`virtual_domains`(`id` ,`name`) VALUES ('1', @domain);

edited Nov 13, 2014 at 11:45

answered Nov 13, 2014 at 11:40

fejese

4,6284 gold badges32 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Passing variables to SQL script in bash

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related