Search code examples
bashsqlitesql-injection

How can I escape sqlite3 query parameters in bash?

I have a script that boils down to this right now:

#!/bin/bash

SEARCH_PARAM="$1"
SQLITE3_DB="$2"

# Don't inject me please :(
sqlite3 "$SQLITE3_DB" "SELECT foo FROM Bar WHERE bundleId='$SEARCH_PARAM';"

A glaring problem is that the $SEARCH_PARAM value is very vulnerable to SQL injection. Can I fix that from the bash script or do I need to drop in another scripting language, like Python, to get access to query parameters?

How can I escape characters in SQLite via bash shell? is similar but it has fixed string arguments.

Solution

  • In SQL strings, the only character that needs escaping is the single quote, which must be doubled.

    This can be done by using pattern substitution in the parameter expansion:

    sqlite3 "..." "... bundleId = '${SEARCH_PARAM//\'/\'\'}';"

    (Non-standard SQL implementations like MySQL might have additional characters that need escaping.)