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

mysql_real_escape_string() of course only escapes a single string literal to be quoted, not a whole statement. You need to be clear what purpose the string will be used for in the statement. According to the MySQL manual section on string literals, for inserting into a string field you only need to escape single and double quotation marks, backslashes and NULs. However, a bash string cannot contain a NUL, so the following should suffice:

#escape for MySQL single string
PASSWORD=${PASSWORD//\\/\\\\}
PASSWORD=${PASSWORD//\'/\\\'}
PASSWORD=${PASSWORD//\"/\\\"}

If you will be using the string after a LIKE, you will also probably want to escape % and _.

Prepared statements are another possibility. And make sure you don't use echo -e in your bash.

See also https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Answer 2

This will escape apostrophes

a=$(echo "$1" | sed s/"'"/"\\\'"/g)

Please note though that mysql_real_escape_string also escapes \x00, \n, \r, \, " and \x1a. Be sure to escape these for full security.

To escape \x00 for example:

a=$(echo "$1" | sed s/"\x00"/"\\\'"/g)

With a bit of effort you can probably escape these using one sed command.

Answer 3

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 4

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 5

This will work:

echo "John O'hara"  | php -R 'echo addslashes($argn);'

To pass it to a variable:

name=$(echo "John O'hara"  | php -R 'echo addslashes($argn);')