Pass parameter to MySQL script command line

Question

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

@Kaii he is most likely looking for something like "scripting variables" that [SQLCMD of SQL Server supports with the `-v` option](http://msdn.microsoft.com/en-us/library/ms162773.aspx). — Christian.K, Apr 23 '12 at 16:28

score 42 · Accepted Answer · edited Jan 17 '14 at 23:21

42

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}

edited Jan 17 '14 at 23:21

Henk Langeveld

8,088
1
43
57

answered Apr 22 '12 at 05:49

danieln

4,795
10
42
64

2

It is worth noting that it's the shell which expands this variable so this answer implies a *nix shell. – sastorsl Jan 05 '16 at 10:12
5

But this SQL parameters usage don't protect from SQL injection. So there no advantage over direct include shell var in sql query. – mmv-ru Mar 21 '19 at 14:29
4

As pointed out by the other two comments, this is not a parameterized statement and is an absolutely terrible solution to this problem. – DreamConspiracy Mar 27 '19 at 03:57
1

I have opened a [related question](https://stackoverflow.com/questions/55374938/injection-proof-sql-statements-from-command-line) asking about the security of this. – DreamConspiracy Mar 27 '19 at 10:28

score 3 · Answer 2 · answered Sep 24 '16 at 10:56

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 git@github.com:YordanGeorgiev/mysql-starter.git

what is that 00. ? before the sql file name – Areza Nov 09 '20 at 13:09 — Areza, Nov 09 '20 at 13:09

score 0 · Answer 3 · answered Oct 06 '15 at 15:31

0

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\";"

answered Oct 06 '15 at 15:31

crmeye

34
3

score -1 · Answer 4 · answered Apr 06 '21 at 18:39

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

Pass parameter to MySQL script command line

4 Answers4

Linked

Related