Home ›

More Fun With Bash Quoting

Nov 19, 2009  By Mitch Frazier
 in

I've written about bash quoting before, and yes, it's about as exciting as watching paint dry or listening to the corn grow. It can also be extremely frustrating when it doesn't do what you want, case in point: trying to script the updating of a field in a mysql table when the field to be changed contains quote characters.

Let's imagine we have a simple table with the following data and we would like to change the name field:

+----+----------------------+---------------------+
| id | name                 | create_date         |
+----+----------------------+---------------------+
|  1 | name 'with' "quotes" | 2009-11-19 08:48:59 |
+----+----------------------+---------------------+

Your first script might look something like this:

#!/bin/bash

USERNAME=${USERNAME:-user}
PASSWORD=${PASSWORD:-pwd}

mysql_cmd="mysql -u $USERNAME -p$PASSWORD test"

# Remove mysql header line.
function remove_header()
{
    shift
    echo $*
}

id=1
name=$(remove_header $($mysql_cmd -e "SELECT name FROM atable WHERE id='$id'"))

new_name="$name and more"
echo mysql -e "UPDATE atable SET name='$new_name' WHERE id='$id'"
$mysql_cmd -e "UPDATE atable SET name='$new_name' WHERE id='$id'"

# vim: tabstop=4: shiftwidth=4: noexpandtab:
# kate: tab-width 4; indent-width 4; replace-tabs false;

And when you run it, it will puke something like this:

$ bash badfix.sh
mysql -e UPDATE atable SET name='name 'with' quotes and more' WHERE id='1'
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 'with' quotes and more' WHERE id='1''
    at line 1

Note, the function at the top (remove_header) removes the header line from the mysql output so that we don't get the name of the field included in the data.

We all know the solution here: we need to escape the quotes in the value so that both bash and mysql are happy. However, this turns out to be easier said than done, and perhaps I missed the obvious, but after numerous attempts (on more than one occasion) the following finally did the trick:

#!/bin/bash

USERNAME=${USERNAME:-user}
PASSWORD=${PASSWORD:-pwd}

mysql_cmd="mysql -u $USERNAME -p$PASSWORD test"

# Remove mysql header line.
function remove_header()
{
    shift
    echo $*
}

# Quote any quotes in a mysql value.
function fix_quotes()
{
    local  val="$*"
    if [[ "$val" =~ .*\'.* ]]; then #'
        echo "String contains single quotes: $val" >&2
        #val="${val//'/\\\\'}"
        val=$(sed -e "s/'/' \"'\" '/g" <<<"$val")
        echo New Value: "$val" >&2
    fi
    echo "$val"
}



id=1
name=$(remove_header $($mysql_cmd -e "SELECT name FROM atable WHERE id='$id'"))

fixed_name="$(fix_quotes "$name") and more"
echo mysql -e "UPDATE atable SET name='$fixed_name' WHERE id='$id'"
$mysql_cmd -e "UPDATE atable SET name='$fixed_name' WHERE id='$id'"

# vim: tabstop=4: shiftwidth=4: noexpandtab:
# kate: tab-width 4; indent-width 4; replace-tabs false;

The fix_quotes function only checks for single quotes since our mysql value is contained in single quotes:

$mysql_cmd -e "UPDATE atable SET name='$fixed_name' WHERE id='$id'"
#                                     ^           ^

As you would expect, we don't need to escape double quotes inside single quotes for mysql. However, if we wanted to use a literal value in our SQL command we would need to escape double quotes since our SQL command is contained inside double quotes:

$mysql_cmd -e "UPDATE atable SET name='quoted \"value\"' WHERE id='$id'"
#             ^                                                        ^

We need to escape them in this case for bash's benefit and not for mysql: bash will "remove" the backslashes before passing the command to mysql.

One of my initial attempts (which you can see commented out in the code) was to try to change the value directly using a bash assignment statement. I tried to change each single quote to an escaped single quote:

val="${val//'/\\\\'}"

Interestingly, this does not modify the string at all, a result that I don't quite understand. I tried a similar thing using sed and that also did not work. The solution that finally worked is based on the fact that mysql, like C++, concatenates adjacent strings into a single string. So, I change (using sed) all single quotes inside the string into the sequence: single-quote, space, double-quote, single-quote, double-quote, space, single-quote. You may notice that the double quotes are escaped, but that's for escaping within the sed command, those don't make it into the value that's passed to mysql.

val=$(sed -e "s/'/' \"'\" '/g" <<<"$val")

Running this final version does the trick:

$ bash fix.sh
String contains single quotes: name 'with' \"quotes\"
New Value: name ' "'" 'with' "'" ' \"quotes\"
mysql -e UPDATE atable SET name='name ' "'" 'with' "'" ' \"quotes\" and more' WHERE id='1'

and you can see the result in the table.

+----+-------------------------------+---------------------+
| id | name                          | create_date         |
+----+-------------------------------+---------------------+
|  1 | name 'with' "quotes" and more | 2009-11-19 08:48:59 |
+----+-------------------------------+---------------------+

Have we had enough quoting yet????

______________________

Mitch Frazier is an Associate Editor for Linux Journal.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

other ways? - fun with bash

Anonymous's picture
Submitted by Anonymous (not verified) on Sat, 11/28/2009 - 20:28.

val=ab\'cd
> echo $val
ab'cd

> echo ${val//\'/\\\\\'}
ab\\'cd

...but
> echo "${val//\'/\\\\\'}"
ab\\\'cd

...better:
> echo "${val//\'/\\\'}"
ab\\'cd

Use eval

cfajohnson's picture
Submitted by cfajohnson (not verified) on Sat, 11/28/2009 - 08:10.

Much quoting complexity can be eliminated by using eval:

eval "$command"

remove_header

FlatCap's picture
Submitted by FlatCap (not verified) on Wed, 11/25/2009 - 10:10.

To keep your examples simple, add "--skip-column-names" to your mysql command, then you can get rid of "remove_header".

Thanks

Mitch Frazier's picture
Submitted by Mitch Frazier on Wed, 11/25/2009 - 11:42.

Can't believe I never noticed that option before, thanks.

Mitch Frazier is an Associate Editor for Linux Journal.

pat is subject to (some) expansions in ${var/pat/rep}

Anonymous's picture
Submitted by Anonymous (not verified) on Wed, 11/25/2009 - 06:55.

As the man page says, “In each of the cases below, word is subject to tilde expansion, parameter expansion, command substitution, and arithmetic expansion.”


$ val="ab'cd"
$ echo ${val//"'"/\\\\"'"}
ab\\'cd

Bob.

Thanks

Mitch Frazier's picture
Submitted by Mitch Frazier on Wed, 11/25/2009 - 11:44.

That explains why that attempt didn't work.

Mitch Frazier is an Associate Editor for Linux Journal.

even python has this!

Anonymous's picture
Submitted by Anonymous (not verified) on Sat, 11/21/2009 - 10:24.


a = '''This is a "string", but you 'already' know that.'''
b = """This is 'just' the same. You "still" with me?"""

q and qq

Anonymous's picture
Submitted by Anonymous (not verified) on Fri, 11/20/2009 - 01:31.

Perl had it right with 'q' and 'qq'... instead of quotation marks you can use 'q' and either a single char or an opening bracket of and the string is quoted until you have that char again.

qq is the same thing but acts like double-quotes instead of single.
I like to use rare or high-ascii chars or something that I know won't be in the string:

$v = qq^This is a "string". Don't you know, $name?^;
$s = q^This is a "string". Don't you know?^;

I wish other languages implemented this functionality.

Webcast
More Resources
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.

Learn More

Sponsored by AMD

White Paper
More Resources
Red Hat White Paper: Using an Open Source Framework to Catch the Bad Guy

Built-in forensics, incident response, and security with Red Hat Enterprise Linux 6

Every security policy provides guidance and requirements for ensuring adequate protection of information and data, as well as high-level technical and administrative security requirements for a system in a given environment. Traditionally, providing security for a system focuses on the confidentiality of the information on it. However, protecting the data integrity and system and data availability is just as important. For example, when processing United States intelligence information, there are three attributes that require protection: confidentiality, integrity, and availability.

Learn more about catching the bad guy in this free white paper.

Learn More

Sponsored by DLT Solutions