Escaping MYSQL command lines via Bash Scripting

Question

PHP has mysql_real_escape_string() to correctly escape any characters that might cause problems. What is the best way to mimic this functionality for BASH?

Is there anyway to do prepared mysql statements using bash? This seems to be the best way.

Most of my variables won't (shouldn't) have special characters, however I give the user complete freedom for their password. It may include characters like ' and ".

I may be doing multiple SQL statements so I'll want to make a script that takes in parameters and then runs the statement. This is what I have so far:

doSQL.sh:

#!/bin/sh

SQLUSER="root"
SQLPASS="passwor339c"
SQLHOST="localhost"

SQL="$1"
SQLDB="$2"


if [ -z "$SQL" ]; then echo "ERROR: SQL not defined"; exit 1; fi
if [ -z "$SQLDB" ]; then SQLDB="records"; fi

echo "$SQL" | mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST $SQLDB

and an example using said command:

example.sh:

PASSWORD=$1
doSQL "INSERT INTO active_records (password) VALUES ('$PASSWORD')"

Obviously this would fail if the password password contained a single quote in it.

Answer 1

In Bash, printf can do the escaping for you:

$ a=''\''"\;:#[]{}()|&^$@!?, .<>abc123'
$ printf -v var "%q" "$a"
$ echo "$var"
\'\"\\\;:#\[\]\{\}\(\)\|\&\^\$@\!\?\,\ .\<\>abc123

I'll leave it to you to decide if that's aggressive enough.

Answer 2

This seems like a classic case of using the wrong tool for the job.

You've got a lot of work ahead of you to implement the escaping done by mysql_real_escape_string() in bash. Note that mysql_real_escape_string() actually delegates the escaping to the MySQL library which takes into account the connection and database character sets. It's called "real" because its predecessor mysql_escape_string() did not take the character set into consideration, and could be tricked into injecting SQL.

I'd suggest using a scripting language that has a MySQL library, such as Ruby, Python, or PHP.

If you insist on bash, then use the MySQL Prepared Statements syntax.

Answer 3

There is no escape from the following construct, no matter what quotes you use:

PASSWORD=$1
doSQL "INSERT INTO active_records (password) VALUES (FROM_BASE64('$(echo -n $PASSWORD|base64)'))"