Escaping MYSQL command lines via Bash Scripting

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.

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

function quoteSQL() {
    printf "FROM_BASE64('%s')" "$(echo -n "$1" | base64 -w0 )"
}

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

# I would prefer piping
printf 'INSERT INTO active_records (password) VALUES (%s);\n' $(quoteSQL "$PASSWORD") | doSQL

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

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.

Sure, why not just use the real thing?

A script, anywhere, such as
~/scripts/mysqli_real_escape.php

#!/bin/php
<?php

$std_input_data = '';
$mysqli             = new mysqli('localhost', 'username', 'pass', 'database_name');

if( ftell(STDIN) !== false  )       $std_input_data = stream_get_contents(STDIN);
if( empty($std_input_data)  )       exit('No input piped in');
if( mysqli_connect_errno( ) )       exit('Could not connect to database');

fwrite  (   STDOUT, 
            $mysqli->real_escape_string($std_input_data) 
        );

exit(0);

?>

Next, run from bash terminal:

chmod +x ~/script/mysqli_real_escape.php`
ln -s ~/script/mysqli_real_escape.php /usr/bin/mysqli_real_escape

All set! Now you can use mysqli_real_escape in your bash scripts!

#!/bin/bash
MyString="stringW@#)*special characters"
MyString="$(printf "$MyString" | mysqli_real_escape )"

Note: From what I understand, command substitution using "$(cmd ..."$var")" is preferred over using backticks. However, as no further nesting would be needed either should be fine.

Further Note: When inside command substitution, "$(...)", a new quote context is created. This is why the quotes around variables do not screw up the string.

This is how I did it, where my-file.txt contains spaces, new lines and quotes:

IFS='' content=$(cat my-file.txt)
mysql <flags> -e "update table set column = $(echo ${content@Q} | cut -c 2-) where something = 123"

Here are a couple Bash functions I wrote, grouped into a library.

It provides methods for proper quoting/escaping strings and identifiers:

##### db library functions #####

# Executes SQL Queries on localhost's MySQL server
#
# @Env
# $adminDBUser: The database user
# $adminDBPassword: The database user's password
#
# @Params
# $@: Optional MySQL arguments
#
# @Output
# >&1: The MySQL output stream
db::execute() {
  # Uncomment below to debug
  #tee --append debug.sql |
    mysql \
      --batch \
      --silent \
      --user="${adminDBUser:?}" \
      --password="${adminDBPassword:?}" \
      --host=localhost \
      "$@"
}

# Produces a quoted string suitable for inclusion in SQL statements.
#
# @Params
# $1: The string to bo quoted
#
# @Output
# >&1: The quoted identifier suitable for inclusion in SQL statements
db::quoteString() {
  local -- string="${1:?}"
  local -- bas64String && bas64String=$(printf %s "${string}" | base64)
  db::execute <<< "SELECT QUOTE(FROM_BASE64('${bas64String}'));"
}

# Produces a quoted identifier suitable for inclusion in SQL statements.
#
# @Params
# $1: The identifier to bo quoted
#
# @Output
# >&1: The quoted identifier suitable for inclusion in SQL statements
db::quoteIdentifier() {
  local -- identifier="${1:?}"
  local -- bas64Identifier && bas64Identifier=$(printf %s "${identifier}" | base64)
  db::execute <<< "SELECT sys.quote_identifier(FROM_BASE64('${bas64Identifier}'))"
}

Short answer: use \ (escape character).

If your value has dollar character, for example: $p123$hello then in bash you may escape this by \. Then string will be \$p123\$hello

In my case for example i have been change password like this:

mysql> update users set password='\$p123\$hello' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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);')