Solved

BASH Script Won't Successfully Execute Query - Suggestions Needed

Posted on 2015-02-07
5
98 Views
Last Modified: 2015-02-08
My script is designed to cycle through environments, hit a database and check to see if any first/last name combination has an associated user ID. I am not sure why I'm getting the error listed below.

    #/bin/bash

    #specify user to search for  *******DO NOT PUT SPACE, ie. jaylefler
    echo "Please enter user first name: "
    read first_name

    echo "Please enter user last name: "
    read last_name

    echo "Please enter LDAP User ID: "
    read ldapuser

    echo "Please enter LDAP password: "

    stty -echo

    read ldappw

    stty echo
    #logs to write output to
    log="ui_${username}_access.log"

    #create file if not exist, else null it
    [[ -f ${log} ]] && cat /dev/null > ${log} || touch ${log}

    #log it all
    {

    echo "environment"
    sshpass -p $ldappw ssh $ldapuser@54.123.777.567 'mysql -h host -u user -ppassword database -e "select user_id from users where first like "%'${first_name}'%" and last like "%'${last_name}'%";"'

    } > $log

Open in new window


When I execute the script, I receive the following errors:

    Please enter user first name: 
    jay
    Please enter user last name: 
    lefler
    Please enter LDAP User ID: 
    jlefler
    Please enter LDAP password: 
    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check   the manual that corresponds to your MySQL server version for the right syntax to use near '%jay% and last like %lefler%' at line 1
    jlefler@ubuntu:~/Desktop$ 

Open in new window


I've tried several variations with the syntax I use with the script. I'm at a loss.
0
Comment
Question by:TampaJay
  • 3
5 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 40596321
Your pattern % is outside the single quote.

The query needs to look like
Select user_id from table where firstname like '%firstname%'
In your case, you are placing the single quote after the % which makes the query look as
Select user_id from table where firstname like %'firstname'%. Which is why you are getting the error.
Replace your select with the one below
select user_id from users where first like '%${first_name}%' and last like '%${last_name}%';

Open in new window


You should keep track of your use of quotes, a sequential closes the prior
You should only gave one set to open and close if you must have quotes within you have to escape them using the \" method.

In the select the quote (") is out of place.
0
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 40596333
the quotes are not getting passed to mysql.  try something like
ssh $ldapuser@54.123.777.567 'mysql -h host -u user -ppassword database -e "select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"'
0
 

Author Comment

by:TampaJay
ID: 40596411
arnold, when using your suggestion I got the following output:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%jay% and last like %lefler%' at line 1

Open in new window


ozo, when using your suggestion I got the following output I received the correct results. Thank you so much! However, in my log I'm getting the following:

environment
user_id
jlefler

Anyway I can prevent 'user_id' from showing up? Or will I need to do post-processing?
0
 
LVL 77

Expert Comment

by:arnold
ID: 40596423
The quotes and single quotes as ozo pointed out are being stripped.

An option could be to change the
(Ssh user@host "echo \\\"select user_id from table where firstname like '%${firstname}%' and lastname like '%${lastname}%';\\\" \| mysql -uusername -ppassword database")  >> $log

the output you are getting is a table column name, resulting data.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40596425
Missed,




You can pump the output pre output to log through grep -v user_id
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question