1

When I execute a MySQL query from the shell script using value as variable, it shows

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 '@gmail.com' at line 1

my code

#!/bin/bash

email='new_user@gmail.com'

# Checking - is the email already exists,
email_exists=$(mysql -u username -ppassword -e "SELECT id FROM db.user where email=$email;")

no problem when using inline (without using a variable).

FYI: I am using email as a variable because I need to reuse somewhere in code.

Jifri Valanchery
  • 189
  • 5
  • 15
  • Eh? It is indeed a problem if you write that code inline. What you're generating is `SELECT id FROM db.user WHERE email=new_user@gmail.com` – Charles Duffy Jul 29 '20 at 11:46

2 Answers2

1

Add single quotes around the string (inside your existing double quotes) to make it valid SQL:

email_exists=$(mysql -u username -ppassword -e "SELECT id FROM db.user where email='$email';")

That said, note that this is only safe at all when you control the string and are certain it doesn't contain any literal quotes inside its content. (new_user@gmail.com is safe, but new_user@gmail.com'; DROP TABLE db; -- ' would not be). Bill Kelwin's answer on "Injection proof SQL statements from command line" provides one way to avoid this pitfall.

Charles Duffy
  • 280,126
  • 43
  • 390
  • 441
1

Single-quotes and double-quotes are used in bash to escape certain characters, they are not used to signal a string. The single-quotes in your case will not be used in your mysql statement, therefore you get a syntax error (you don't have a string your mysql statement).

#!/bin/bash

email=new_user@gmail.com

# Checking - is the email already exists,
email_exists=$(mysql -u username -ppassword -e "SELECT id FROM db.user where email='$email';")
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • (BTW -- flagged my own instance of this answer -- preceding this by 9 seconds -- as community wiki on account of answering a known duplicate, which is contrary to guidance in the "Answer Well-Asked Questions" section of [How to Answer](https://stackoverflow.com/help/how-to-answer) and thus not behavior it's good practice to profit by). – Charles Duffy Jul 29 '20 at 11:51
  • Thanks @fancyPants, Working fine with and without using quotes in the variable declaration (email=new_user@gmail.com). – Jifri Valanchery Jul 29 '20 at 13:21