Bash - Single quotes

Question

I got a script database that looks for duplicate identifiers when it is assigned a specific identifier, last name and first name. Here are the meat and potatoes:

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")

It works fine, unless the last or first name has an apostrophe in it, for example, "O'Neil". I tried to escape from any instance of "c" and have not yet succeeded. I searched the forums all day and tried different options myself, but he still won’t add \ before each.

Here is 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!

Answer 1

This is the wrong way to pass a complex command:

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

Use arrays instead:

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}'" )

... "${dup_id[@]}" , . SQL- ( , ), ), , , , , , - bash - SQL-.

. BashFAQ # 50, BashWeaknesses wiki freenode.org # bash - SQL , bash .

Answer 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[*]}"