CODEWITHSUNDEEP
bash
Jake Neal
Jake Neal

Reputation: 187

Passing variables to SQL script in bash

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');

Upvotes: 2

Views: 7594

Answers (1)

fejese
fejese

Reputation: 4628

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);

Upvotes: 3

Related Questions