Bash - Escaping single quotes

Question

I got a database script that searches for duplicate IDs, when given a certain ID, last name, and first-name. Here are the meat and potatoes of it:

PSQL="psql -p $PGPORT -h $DBHOST -d $DB -tAc "

DUP_ID=$($PSQL "SELECT id FROM inmate WHERE id NOT SIMILAR TO '(0*)${1}' AND lastname ILIKE '${_LNAME}' AND firstname ILIKE '${_FNAME}' LIMIT 1")

Works great, except when either the last or first name has an apostrophe in it, such as "O'Neil". I've attempted to escape any instance of ' with \', and have not yet met with success. I've spent all day searching the forums, and tried different variations on my own, but it still will not add a \ in front of every '.

Here's what I got so far:

local _LNAME=`echo "${2}" | sed "s/'/\\\'/g"`
local _FNAME=`echo "${3}" | sed "s/'/\\\'/g"`
echo -e $_LNAME
echo -e $_FNAME

# Output

O'Neil
Robert

As always, thanks in advance!

What's with the `echo | sed` thing? Bash has built-in parameter expansion operators for performing string manipulation; for instance, you can run: `in="'"; out="\\'"; escaped_lname=${lname//$in/$out}`, and this will be vastly faster and more efficient than trying to play games with sed. — Charles Duffy, Apr 29 '14 at 23:57
...also, note that all-caps is conventional for environment variables and builtins. Variables local to your script should contain at least one lowercase character to avoid namespace collisions with these classes. — Charles Duffy, Apr 29 '14 at 23:59
...also, `echo -e` is (like `echo -n`) non-POSIX and generally deprecated; `printf` is preferred for all but very simple uses of `echo`. If you want to display a variable's contents in a human-readable way, `printf '%q\n' "$_lname"` would be a better habit to be in -- that even shows things like newlines, carriage returns, and other unprintable characters in a format in which they can be entered back into the shell. — Charles Duffy, Apr 30 '14 at 02:18
Related: *[How to escape single quotes within single quoted strings](https://stackoverflow.com/questions/1250079/)* — Peter Mortensen, Aug 16 '23 at 19:20

score 2 · Answer 1 · answered Apr 30 '14 at 00:18

2

QUERY=(
  SELECT id FROM inmate WHERE
  id NOT SIMILAR TO "'(0*)$1'" AND
  lastname ILIKE "'$_LNAME'" AND
  firstname ILIKE "'$_FNAME'" LIMIT 1
)
psql -p $PGPORT -h $DBHOST -d $DB -tAc "${QUERY[*]}"

answered Apr 30 '14 at 00:18

Zombo

1
62
391
407

1

Neat, and one of the few times it is correct to use `"${array[*]}"` rather than `"${array[@]}"`. – Jonathan Leffler Apr 30 '14 at 00:21
@JonathanLeffler well I always use `*` when I can, it is "simpler" but most people dont understand the difference I think – Zombo Apr 30 '14 at 00:22
You can protect against [Little Bobby Tables](http://xkcd.com/327/) by using `${_LNAME//\'/''}` to expand any single quotes in the name into doubled up single quotes. I'll work out, one day, why you need the backslash in the search part of that and not in the replacement part. – Jonathan Leffler Apr 30 '14 at 00:35
1

@StevenPenny, "simpler"? I'm not so sure that putting the first character of $IFS everywhere is exactly a model of simplicity -- especially if getting back to multiple words involves string-splitting and glob-expansion steps, as opposed to just keeping the original word boundaries in the first place. But yes, it's a perfectly fine thing in this specific use case, when the goal is a single word. – Charles Duffy Apr 30 '14 at 02:14

Charles Duffy · Accepted Answer · 2014-04-29T23:58:37.410

1

This is the wrong way to pass a complex command:

PSQL="psql -p $PGPORT -h $DBHOST -d $DB -tAc "

Instead, use arrays:

single_quote="'"
escaped_single_quote="\\'"
quoted_fname=${1//$single_quote/$escaped_single_quote}
quoted_lname=${2//$single_quote/$escaped_single_quote}
psql=( psql -p "$PGPORT" -h "$DBHOST" -d "$DB" -tAc )
dup_id=( "${psql[@]}" "SELECT id FROM ... WHERE ... '${quoted_lname}'" )

...and then using "${dup_id[@]}" to run your command will protect you from shell injection bugs. It isn't guaranteed to protect you from SQL injection attacks (there are too many ways to perform those, and databases have too many idiosyncrasies around character set conversion, to trust the simple character-replacement-based escaping used here against hostile data), but, well, that's why folks who are concerned about correctness or security use languages that support bind parameters -- a set of which bash is not a member -- for generating SQL queries.

See also BashFAQ #50, and the BashWeaknesses page from the freenode.org #bash channel's wiki -- the latter of which explicitly calls out SQL generation as a task for which bash is unfit.

edited Apr 29 '14 at 23:58

answered Apr 29 '14 at 23:53

Charles Duffy

280,126
43
390
441

Great answer except you need to escape single-quotes by doubling them. – Jeff Oct 06 '16 at 14:01
@Jeff, eh? `''` in bash is exactly the same as having no quotes at all. Try it: `printf '%s\n' ''*''` prints all filenames in the local directory, just like `printf '%s\n' *` does, rather than printing a `*` surrounded by literal single-quotes. – Charles Duffy Oct 06 '16 at 16:07
@Jeff, ...if you're referring to correct **sql** quoting as opposed to bash quoting, you might well be right that `escaped_single_quote="''"` would be more correct -- but that would mean that the question itself is asking for the wrong thing, as the OP is explicitly asking how to add leading backslashes, not how to double quotes or (more generally) generate correct SQL quoting for strings. – Charles Duffy Oct 06 '16 at 16:13

Bash - Escaping single quotes

2 Answers2