LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 11-18-2013, 12:58 PM   #1
djlerman
LQ Newbie
 
Registered: Sep 2007
Posts: 7

Rep: Reputation: 0
Running mysql from ssh & query statement has a Text Header in the SELECT statement?


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
 
Old 11-18-2013, 02:24 PM   #2
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,912

Rep: Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513
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.

Last edited by jpollard; 11-18-2013 at 02:26 PM.
 
Old 11-18-2013, 04:30 PM   #3
djlerman
LQ Newbie
 
Registered: Sep 2007
Posts: 7

Original Poster
Rep: Reputation: 0
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"
 
Old 11-19-2013, 03:15 AM   #4
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,912

Rep: Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513
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...).

Last edited by jpollard; 11-19-2013 at 03:19 AM.
 
Old 11-19-2013, 10:37 AM   #5
djlerman
LQ Newbie
 
Registered: Sep 2007
Posts: 7

Original Poster
Rep: Reputation: 0
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.
 
Old 11-19-2013, 03:09 PM   #6
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,912

Rep: Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513Reputation: 1513
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.

Last edited by jpollard; 11-19-2013 at 03:11 PM.
 
Old 11-19-2013, 06:33 PM   #7
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
why not
Code:
 mysql -uroot -p -h<dbhost> -e  "SELECT 'Column 1 text' as 'Column 1 Heading', table1.* FROM table1;' dataBase1"
?
 
  


Reply

Tags
command line, linux, mysql, ssh


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Help with Ubuntu MySQL remote select statement kcleveland Linux - Software 3 05-20-2013 07:40 AM
[SOLVED] Using while loop & select statement - Loop issues Kustom42 Programming 4 05-17-2013 08:43 AM
[SOLVED] MySQL SELECT INTO statement: Beware of 0, 2 or more result rows! rm_-rf_windows Linux - General 3 03-22-2012 05:52 PM
Bash script, if else statement to ping & ssh connection to correct i.p. VipX1 Linux - Newbie 2 12-31-2009 11:05 PM
Bash select statement and spaces meshcurrent Linux - General 2 04-09-2003 09:35 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 09:22 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration