"set names" vs mysqli_set_charset — besides affecting mysqli_escape_string, are they identical?

Question

It seems to be common knowledge to use mysql_set_charset / mysqli::set_charset instead of the direct MySQL query set names.

The reason often cited is that set names is insecure because the encoding used for mysql_real_escape_string / mysqli::real_escape_string will only be set by a call to mysql_set_charset / mysqli::set_charset. (Another reason cited is that the PHP docs says it's "not recommended" §.)

However, is it safe to use the direct MySQL query set names if we use prepared statements andor other means of escaping besides mysql_real_escape_string / mysqli::real_escape_string / mysqli_escape_string?

Besides affecting the encoding of mysql_real_escape_string / mysqli::real_escape_string / mysqli_escape_string, Is there any difference between set names vs mysql_set_charset/mysqli::set_charset?

Answer 1

Calling SET NAMES on the connection is equivalent to calling set_charset, provided you call neither get_charset nor mysql_real_escape_string (and friends).

---

When you call set_charset, PHP does two things. First, it calls SET NAMES on the connection. Second, it remembers what charset you set. That state information is later used only in the get_charset and mysql_real_escape_string (and friends) functions. Therefore, if you don't use these functions, then you may consider the two equivalent.

Let's walk the source:

1. Userland functions mysql_set_charset and mysqli_set_charset call...
2. Engine function mysql_set_character_set calls...

3. Engine macro mysqlnd_set_character_set, which is defined as:

   #define mysqlnd_set_character_set(conn, cs) \ ((conn)->data)->m->set_charset((conn)->data, (cs)))

   and expands to...

4. MYSQLND_METHOD(mysqlnd_conn_data, set_charset) which contains the following code (numbered for discussion, these are not actual source line numbers):

---

 1   if (PASS == conn->m->local_tx_start(conn, this_func)) {
 2      char * query;
 3      size_t query_len = mnd_sprintf(&query, 0, "SET NAMES %s", csname);
 4 
 5      if (FAIL == (ret = conn->m->query(conn, query, query_len))) {
 6          php_error_docref(NULL, E_WARNING, "Error executing query");
 7      } else if (conn->error_info->error_no) {
 8          ret = FAIL;
 9      } else {
10           conn->charset = charset;
11      }
12      mnd_sprintf_free(query);
13 
14      conn->m->local_tx_end(conn, this_func, ret);
15   }

---

As you can see, PHP calls SET NAMES on the connection itself (line 3). PHP also tracks the charset just set (line 10). The comments further discuss what happens with conn->charset, but suffice to say it winds up only being in get_charset and mysql_real_escape_string (and friends).

So, if you don't care about this state, and you agree to use neither get_charset nor mysql_real_escape_string, then you may call SET NAMES on the connection itself with no ill effect.

As an aside, and I've never done this, but it looks like compiling PHP with -DPHP_DEBUG=1 will enable substantial debugging through various DBG macros. That may be useful in seeing how your code is passing through this block.

Answer 2

Two things must be done (in this area):

• Escape quotation marks (and other characters) before putting them inside quotes. Otherwise the quotes would give you syntax errors.
• Establish the encoding of the bytes in the client. This is so that INSERTs/SELECTs will know how to change the bytes during the write/read.

The first needs to escape apostrophe and double-quote, since both of those are acceptable quote marks for strings in MySQL syntax. Then, the escape character, itself, needs escaping. Those 3 characters are sufficient for must applications. However if you are trying to escape a BLOB (such as a .jpg), various control characters may cause trouble. You are probably better off converting to hex, then using UNHEX(), to avoid issues. Note: Nothing is mentioned here about character sets. If you aren't dealing with BLOBs, you can get away with PHP's addslashes().

The second item's purpose is to say "this stream of bytes is encoded this way (utf8/latin1/etc)". It's only use is for converting between the CHARACTER SET of the column being stored/fetched and the desired encoding in your client (PHP, etc). It is handled in a variety of ways by various languages. For PHP:

• mysql_* -- Do not use this interface; it is deprecated and will soon be removed.
• mysqli_* -- mysqli::set_charset(...)
• PDO -- new PDO('...;charset=UTF8', ...)

Does set_charset() do something with real_escape_string? I do not know. But it should not matter. SET NAMES obviously cannot since it is a MySQL command, and knows nothing about PHP.

htmlentities() is another PHP function in this area. It turns 8-bit codes into & entities. This should not be used going into MySQL. It would only mask other problems. Use it only in certain situations involving HTML, not PHP or MySQL.

The only reasonable CHARACTER SETsto use today are ascii, latin1, utf8, and utf8mb4. Those have no "characters" in the "control" area. Sjis and a few other character sets do. This confusion over control characters may be a reason for real_escape_string existing.

Conclusion:

As I see it, you need two mechanisms: One for escaping, and one for establishing the encoding in the client. They are separate.

If they are tied together, the PHP manual has failed to provide any compelling reason for picking one method over another.