Running mysql from ssh & query statement has a Text Header in the SELECT statement?

djlerman · 11-18-2013, 12:58 PM

Thank you in advance for helping out!!!

I have an mySQL statement that works locally:

Code:

    mysql -u root -ppassword  -e 'SELECT "Column 1 text" as "Column 1 Heading", table1.* FROM table1;' dataBase1

If I try and run it with SSH, it errors out. I don't know how to get the quote in the SQL statement through SSH.

Code:

    ssh server1 "mysql -u userName -ppassword  -e 'SELECT 'Column 1 text' as 'Column 1 Heading', table1.* FROM table1;' dataBase1"

Any suggestion would be greatly appreciated..

Thanks,
~Donavon

jpollard · 11-18-2013, 02:24 PM

Your quoting is messed up.

If you look, you use apostrophe quoting in the first sample, with double quotes used for the contents.

In the second, you use apostrophe quoting for the contents (difference between "Column 1 text", and 'Column 1 text'). You can't use apostrophe quoting with embedded apostrophe quoting - without getting really confused on how many apostrophe quotes you need.

You can TRY escaping the embedded apostrophes... but I don't expect it to work very easily.

The problem is that you are quoting locally (which removes one level of quotes) and then passing the string to a remote system, which then gets confused because the quoting is now confused.

djlerman · 11-18-2013, 04:30 PM

I've tried all versions of quotes with no success. There must be some way to do this.

Code:

ssh server1 "mysql -u userName -ppassword  -e 'SELECT 'Column 1 text' as 'Column 1 Heading', table1.* FROM table1;' dataBase1"

Code:

ssh server1 "mysql -u userName -ppassword  -e 'SELECT \'Column 1 text\' as \'Column 1 Heading\', table1.* FROM table1;' dataBase1"

Code:

ssh server1 'mysql -u userName -ppassword  -e "SELECT 'Column 1 text' as 'Column 1 Heading', table1.* FROM table1;" dataBase1'

Code:

ssh server1 'mysql -u userName -ppassword  -e "SELECT \"Column 1 text\" as \"Column 1 Heading\", table1.* FROM table1;" dataBase1'

Code:

ssh server1 "mysql -u userName -ppassword  -e 'SELECT \"Column 1 text\" as \"Column 1 Heading\", table1.* FROM table1;' dataBase1"

Code:

ssh server1 "mysql -u userName -ppassword  -e 'SELECT "Column 1 text" as "Column 1 Heading", table1.* FROM table1;' dataBase1"

jpollard · 11-19-2013, 03:15 AM

It would be easier to put the command in a shell script and send the shell script over to run it. That way you avoid the multiple layers of shell interpretation.

You have to remember that EACH shell involved will remove a layer of escapes... So even to pass the first level you have to escape all the special characters (even the * ).

Another way to develop it is to build the script up in parts... when you get part of the script working, add the next part. But it is hard, and not secure. In the case of database operations you are also fighting the quoting done by two different shells; with three instance of shell interpretation:

1. local shell interpreting the command line for ssh.
2. remote shell interpreting the command to run the mysql command
3. the mysql command interpretation of its own command.

One way to avoid the first one is to try it this way:

Code:

ssh server1 <file

where the file contains the mysql command "mysql -u root -ppassword ..." from your working local version. This avoids the problem of the local shell interpretation, and SHOULD allow the remote shell interpretation to work as the command would be local, but running on the host server1.

The security aspect gets in there because it is possible for the remote
shell (or local one for that matter) slipping in a substitution you don't intend - even including data for a mysql command (http://xkcd.com/327/ for an example). This problem is unavoidable...

I have even run across one where an authors name caused a database failure (the author was of Chinese decent, and his name included an apostrophe...).

djlerman · 11-19-2013, 10:37 AM

Hmm...

One of the ways that I will need to use this is to run it from a perl system command.

Code:

system($cmd);

The SQL part is being built up on the fly.

How would I do "ssh server1 < file" with out writing the SELECT statement to an file.

jpollard · 11-19-2013, 03:09 PM

Most insecure.

You would do better using the perl DBI module for a remote connection. Not that hard, more flexible, and MUCH safer. (http://dev.mysql.com/doc/refman/5.1/...nnections.html and http://dbi.perl.org/)

It is next to impossible to get queries that are built up on the fly secure. You never know what a metacharacter is (between the shell, and mysql, and depending on where it starts...), the environment of the shell...

Building up a prepared query and passing parameters is much safer, and avoids the problem of having to handle metacharacters.

Habitual · 11-19-2013, 06:33 PM

why not

Code:

 mysql -uroot -p -h<dbhost> -e  "SELECT 'Column 1 text' as 'Column 1 Heading', table1.* FROM table1;' dataBase1"

?