How to prevent SQL injection in MySQL's command-line shell interface?

Question

I use shell script to communicate to a MySQL database. MySQL supports specifying query as a shell argument, like this:

mysql my_db -B -N -e "select id from Table"

However, if I have a parameter, which I'd like to use in a query, how can I get protection against injection attacks?

A naive way is to just paste variable value to the request, but it's not very secure:

mysql my_db -B -N -e "select id from Table where name='$PARAM'"

Are there any tricks or documented interfaces to make an injection-safe queries from command line?

Answer 1

You can base64 encode the value, and then base64 decode it once it's in MySQL. There are UDFs in MySQL for converting Base64 data to common data. Additionally, most systems either have uuencode, or the 'base64' command for base64 encoding data.

Answer 2

The answer by Sargun Dhillon pointed me to the right direction. Unfortunately is FROM_BASE64 not available before MySQL 5.6 so I went with UNHEX.

The script below is an example to query the details of a Redmine user from a shell. I still wouldn't sleep well if untrusted users had access to this script but it is safe enough in my case. (It is also limited to string values and you shouldn't have a question mark in your query but those limitations are fine with me.)

#!/bin/bash

MYSQL_OPTS='--defaults-file=/etc/redmine/mysql.cnf'

mysql_query() {
  local db=$1
  local sql=$2
  shift 2 || return 1
  declare -a args=("$@")

  sql=${sql//[%]/%%}
  sql=${sql//[?]/UNHEX('%s')}
  for ((i=0; i<${#args[@]}; i++)); do
    args[$i]=$(echo -n "${args[$i]}" | hexdump -v -e '/1 "%02X"')
  done
  sql=$(printf "$sql" "${args[@]}")
  mysql $MYSQL_OPTS "$db" -e "$sql" || return $?
}

for u in "$@"; do
  mysql_query redmine 'select * from users where login=?\G' "$u" 
done

If you discover any SQL or Shell injection I missed, please comment.

Answer 3

Your application is succeptible to an SQL Injection attack any time you are construct SQL by concatenating parameters (as in your example). There is a writeup about this on wikipedia at this link: http://en.wikipedia.org/wiki/SQL_injection

I suspect that you will want to write a unix filter to construct your SQL query using the mysql_real_escape_string function mentioned in the article.

Consider passing the SQL as the first parameter and the variable(s) as the subsequent parameters then have it return the constructed SQL. If you name your filter "blobbo" the command like for your example might look like this:

blobbo "select id from Table where name=%s" $PARAM

Answer 4

You not only have to protect against SQL injection, but against shell injection as well. You may want to write the query (after sanitizing any dynamic parts) out to a file and then redirect that file into mysql, rather than hoping the query won't break the shell. Consider:

PARAM="name'\"; rm -rf / ; echo 'pwn3d U"

becoming

mysql my_db -B -N -e "select id from Table where name='name'"; rm -rf / ; echo 'pwn3d U'

or:

command 1: mysql my_db -B -N -e "select id from Table where name='name'"
command 2: rm -rf /
command 3: echo 'pwn3d U'

Instead, do something like:

 cat <<EOT > query.sql
 select .... blah blaah blah .... sanitized query here
 EOT
 mysql my_db -B -N < query.sql

This would prevent any user-specified data from ever appearing within the shell command itself, prevent at least one level of injection vulnerability. But then, you still have to handle the SQL injection problem.