TampaJay
asked on
BASH Script Won't Successfully Execute Query - Suggestions Needed
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.
When I execute the script, I receive the following errors:
I've tried several variations with the syntax I use with the script. I'm at a loss.
#/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
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$
I've tried several variations with the syntax I use with the script. I'm at a loss.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
arnold, when using your suggestion I got the following output:
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?
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
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?
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.
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.
Missed,
You can pump the output pre output to log through grep -v user_id
You can pump the output pre output to log through grep -v user_id
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
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.