xis xis - 2 months ago 5
MySQL Question

Different results from MySQL when run directly/run via bash

I run the code

user@host:~$ mysql -h mysql-server -uuser -ppassword scans -N -B -e
'SELECT scan_id, count(*) FROM scan_info NATURAL JOIN found_results
WHERE fp_id = "1669" AND timestamp >= "2016-08-31"
ORDER BY scan_id LIMIT 1'


And the result is

52169471 9


However when I run it via
bash -c
I got

user@host:~$ bash -c "mysql -h mysql-server -uuser -ppassword scans -N -B -e
'SELECT scan_id, count(*) FROM scan_info NATURAL JOIN found_results
WHERE fp_id = "1669" AND timestamp >= "2016-08-31"
ORDER BY scan_id LIMIT 1'"


I got a different result:

51278887 23


Any ideas why?

Answer

There are three levels of quoting if the above command. This will cause a problem with the inner double quotes. Here's the command that bash will actual execute:

mysql -h mysql-server -uuser -ppassword scans -N -B -e 'SELECT scan_id, count(*)  FROM scan_info  NATURAL JOIN found_results  WHERE fp_id = 1669 AND timestamp >= 2016-08-31  ORDER BY scan_id  LIMIT 1'

As you can see, the inner double quotes are gone. You can solve that, by escaping the inner double quotes:

bash -c "mysql -h mysql-server -uuser -ppassword scans -N -B -e
'SELECT scan_id, count(*)  FROM scan_info  NATURAL JOIN found_results  
WHERE fp_id = \"1669\" AND timestamp >= \"2016-08-31\"  
ORDER BY scan_id  LIMIT 1'"
Comments