Is there any option to pass a parameter from the command line to MySQL script?
Something like @start_date in this example:
mysql –uuser_id -ppassword –h mysql-host -A -e
"set @start_date=${start_date}; source ${sql_script};" >${data_file};
4 Answers
Found an answer on the net here.
Basically, suppose that we want to run this query:
Select c_id, c_first_name,c_last_name, c_address,last_modified_date
from customer
where last_modified_date >=@start_date and last_modified_date <= @end_date;
We can pass 'start_date' and 'end_date' like this:
/usr/bin/mysql –uuser_id -ppassword –h mysql-host -A \
-e "set @start_date=${start_date}; set @end_date=${end_date};\
source ${sql_script};" > ${data_file}
4 Comments
sastorsl
It is worth noting that it's the shell which expands this variable so this answer implies a *nix shell.
mmv-ru
But this SQL parameters usage don't protect from SQL injection. So there no advantage over direct include shell var in sql query.
DreamConspiracy
As pointed out by the other two comments, this is not a parameterized statement and is an absolutely terrible solution to this problem.
DreamConspiracy
I have opened a related question asking about the security of this.
The Short answer:
bash :
mysql -u"$mysql_user" -p"$mysql_user_pw" \
-e "set @mysql_db='$mysql_db';source \
00.create-mysql-starter-db.mysql ;" > "$tmp_log_file" 2>&1
mysql:
SET @query = CONCAT('CREATE DATABASE /*!32312 IF NOT EXISTS*/ `'
, @mysql_db , '` /*!40100 DEFAULT CHARACTER SET utf8 */') ;
-- SELECT 'RUNNING THE FOLLOWING query : ' , @query ;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The Long answer : The whole concept as a runnable bash , mysql app :
https://github.com/YordanGeorgiev/mysql-starter
or
git clone [email protected]:YordanGeorgiev/mysql-starter.git
1 Comment
Areza
what is that 00. ? before the sql file name
The "-e" option is sensible to the used quotes, try the double quote:
mysql –uuser_id -ppassword –h mysql-host -A -e
"set @start_date=\"$start_date\"; source \"$sql_script\";" >\"$data_file\";"
Comments
You may be able to avoid bash code in some cases by composing scripts together to run SQL code with different predefined arguments.
For example:
do-something-production.sql
USE mydb-prod;
SET @arg=5;
SOURCE do-something.sql;
do-something-test.sql:
USE mydb-test;
SET @arg=10;
SOURCE do-something.sql;
do-something.sql:
UPDATE table SET col = (SELECT @arg);
-voption.