Preventing SQL injection

Question

In Drupal 6, queries are issued with db_query(). Using %-modifiers, query arguments are substituted into the query after being filtered. Strings %s (and only strings) are ultimately run through mysqli_real_escape_string() (using db_escape_string()).

Looking at the php docs for mysqli_real_escape_string(), I see the following security warning:

The character set must be set either at the server level, or with the API function mysqli_set_charset() for it to affect mysqli_real_escape_string().

The answer to another question about mysql_real_escape_string / SQL Injection explains that:

Another way to get yourself into hot water using mysql_real_escape_string is when you set the database connection encoding using the wrong method. You should do this:

mysql_set_charset('utf8', $link);

You can also do this though:

mysql_query("SET NAMES 'utf8'", $link);

The problem is that the latter bypasses the mysql_ API, which still thinks you're talking to the database using latin1 (or something else). [...] This can be used for injection attacks in certain multibyte string situations.

Drupal does not call mysqli_set_charset(), and instead issues the query 'SET NAMES utf8'. This can be seen in db_connect().

In order to properly protect Drupal 6 against SQL Injection, does the charset need to be set at the server level?

Answer 1

This answer to a stackoverflow question does a great job at explaining the attack that can get around mysqli_real_escape_string() using multi-byte character sets.

I've concluded that when running Drupal 6, we do not have to set the charset at the server level in order to be safe from the attack.

Drupal always sets the client to UTF8 (and not the GBK or BIG-5 charsets that are required for the exploit). Newer versions MySQL (> 5.1) are also not vulnerable.

HOWEVER, although Drupal is safe from this exploit, we should still follow the installation documentation which states, "The database should be created with UTF-8 (Unicode) encoding, for example utf8_general_ci.".

One helpful comment on the installation docs outlines how to create your database with UTF8 encoding, and how to ensure that UTF8 is used when performing a backup/restore:

To achieve this by the mysql command line interface use the following commands:
$ mysql -u root -p # Login
mysql> CREATE DATABASE databasename CHARACTER SET utf8; # Create with utf-8

When using mysqldump/mysql for backup/restore also force both server and client to utf8 by inserting into /etc/mysql/my.cnf:
default-character-set = utf8 # Server
skip-character-set-client-handshake # Force client