Bash – How to escape characters in SQLite via bash shell

bashcommand lineescapingshellsqlite

I am trying to send a query to SQLite from the command line using bash. I need to escape both single quotes and double quotes, and escape them so that bash does not misinterpret them. Here is a typical query:

select * from contacts where source = "Nancy's notes";

How can I send this query from the command line? The basic syntax is something like this:

sqlite3.bin contacts.db 'select * from contacts where source = "Nancy's notes"'

But in this case, the shell misinterprets either the single or double quotes. I've tried escaping using both double and triple slashes but this doesn't work. I'm befuddled. Any suggestions?

Best Answer

The trouble with MarkusQ's solution is knowing which characters are special inside double quotes - there are quite a lot of them, including back-ticks, dollar-open parenthesis, dollar-variable, etc.

I would suggest it is better to enclose the string inside single quotes; then, each single quote inside the string needs to be replaced by the sequence quote, backslash, quote, quote:

sqlite3.bin contacts.db 'select * from contacts
      where source = "Nancy'\''s notes"'

The first quote in the replacement terminates the current single-quoted string; the backslash-quote represents a literal single quote, and the final quote starts a new single-quoted string. Further, this works with Bourne, Korn, Bash and POSIX shells in general. (C Shell and derivatives have more complex rules needing backslashes to escape newlines, and so on.)

Related Solutions

Bash – How to check if a directory exists in a Bash shell script

To check if a directory exists in a shell script, you can use the following:

if [ -d "$DIRECTORY" ]; then
  # Control will enter here if $DIRECTORY exists.
fi

Or to check if a directory doesn't exist:

if [ ! -d "$DIRECTORY" ]; then
  # Control will enter here if $DIRECTORY doesn't exist.
fi

However, as Jon Ericson points out, subsequent commands may not work as intended if you do not take into account that a symbolic link to a directory will also pass this check. E.g. running this:

ln -s "$ACTUAL_DIR" "$SYMLINK"
if [ -d "$SYMLINK" ]; then 
  rmdir "$SYMLINK" 
fi

Will produce the error message:

rmdir: failed to remove `symlink': Not a directory

So symbolic links may have to be treated differently, if subsequent commands expect directories:

if [ -d "$LINK_OR_DIR" ]; then 
  if [ -L "$LINK_OR_DIR" ]; then
    # It is a symlink!
    # Symbolic link specific commands go here.
    rm "$LINK_OR_DIR"
  else
    # It's a directory!
    # Directory command goes here.
    rmdir "$LINK_OR_DIR"
  fi
fi

Take particular note of the double-quotes used to wrap the variables. The reason for this is explained by 8jean in another answer.

If the variables contain spaces or other unusual characters it will probably cause the script to fail.

Bash – How to get the source directory of a Bash script from within the script itself

#!/usr/bin/env bash

SCRIPT_DIR="$( cd -- "$( dirname -- "${BASH_SOURCE[0]}" )" &> /dev/null && pwd )"

is a useful one-liner which will give you the full directory name of the script no matter where it is being called from.

It will work as long as the last component of the path used to find the script is not a symlink (directory links are OK). If you also want to resolve any links to the script itself, you need a multi-line solution:

#!/usr/bin/env bash

SOURCE="${BASH_SOURCE[0]}"
while [ -h "$SOURCE" ]; do # resolve $SOURCE until the file is no longer a symlink
  DIR="$( cd -P "$( dirname "$SOURCE" )" >/dev/null 2>&1 && pwd )"
  SOURCE="$(readlink "$SOURCE")"
  [[ $SOURCE != /* ]] && SOURCE="$DIR/$SOURCE" # if $SOURCE was a relative symlink, we need to resolve it relative to the path where the symlink file was located
done
DIR="$( cd -P "$( dirname "$SOURCE" )" >/dev/null 2>&1 && pwd )"

This last one will work with any combination of aliases, source, bash -c, symlinks, etc.

Beware: if you cd to a different directory before running this snippet, the result may be incorrect!

Also, watch out for $CDPATH gotchas, and stderr output side effects if the user has smartly overridden cd to redirect output to stderr instead (including escape sequences, such as when calling update_terminal_cwd >&2 on Mac). Adding >/dev/null 2>&1 at the end of your cd command will take care of both possibilities.

To understand how it works, try running this more verbose form:

#!/usr/bin/env bash

SOURCE="${BASH_SOURCE[0]}"
while [ -h "$SOURCE" ]; do # resolve $SOURCE until the file is no longer a symlink
  TARGET="$(readlink "$SOURCE")"
  if [[ $TARGET == /* ]]; then
    echo "SOURCE '$SOURCE' is an absolute symlink to '$TARGET'"
    SOURCE="$TARGET"
  else
    DIR="$( dirname "$SOURCE" )"
    echo "SOURCE '$SOURCE' is a relative symlink to '$TARGET' (relative to '$DIR')"
    SOURCE="$DIR/$TARGET" # if $SOURCE was a relative symlink, we need to resolve it relative to the path where the symlink file was located
  fi
done
echo "SOURCE is '$SOURCE'"
RDIR="$( dirname "$SOURCE" )"
DIR="$( cd -P "$( dirname "$SOURCE" )" >/dev/null 2>&1 && pwd )"
if [ "$DIR" != "$RDIR" ]; then
  echo "DIR '$RDIR' resolves to '$DIR'"
fi
echo "DIR is '$DIR'"

And it will print something like:

SOURCE './scriptdir.sh' is a relative symlink to 'sym2/scriptdir.sh' (relative to '.')
SOURCE is './sym2/scriptdir.sh'
DIR './sym2' resolves to '/home/ubuntu/dotfiles/fo fo/real/real1/real2'
DIR is '/home/ubuntu/dotfiles/fo fo/real/real1/real2'

Best Answer

Related Solutions

Bash – How to check if a directory exists in a Bash shell script

Bash – How to get the source directory of a Bash script from within the script itself

Related Topic