使用mysql排序规则的安全问题/ sql注入？

Question

Perhaps I don't have enough of an understanding of this yet, so I'm looking for a little direction.

All of our tables show a collation of latin1_swedish_ci. Here's what I see in the mysql variables:

collation connection utf8_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

Now, we see utf8 (or, at least, foreign language content) stored in the db pretty frequently, and it renders correctly. Does the collation not matter for this?

Using something like php addslashes() on user input - is this sufficient? Or, does this leave an injection opportunity?

EDIT: So, looking at the full set of collation / charset settings, at least in phpmyadmin, I see:

character set client    utf8
(Global value)  latin1
character set connection    utf8
(Global value)  latin1
character set database  latin1
character set filesystem    binary
character set results   utf8
(Global value)  latin1
character set server    latin1
character set system    utf8
character sets dir  /usr/share/mysql/charsets/
collation connection    utf8_general_ci
(Global value)  latin1_swedish_ci
collation database  latin1_swedish_ci
collation server    latin1_swedish_ci

Answer 1

The collation does only describe rules for comparing characters of a certain character set. One rule could be that a is equal A, b is equal B, etc. or that ß is equal to ss, ä is equal to ae, etc.

And for an explicit escaping of strings for MySQL, use mysql_real_escape_string. This function does in opposite to addslashes and mysql_escape_string take the actual character encoding of the connection into account.

But you need to set the character encoding of the connection with mysql_set_charset. Because otherwise a change will not be recognized (see C API Functions Description – mysql_real_escape_string()):

If you need to change the character set of the connection, you should use the mysql_set_character_set() function rather than executing a SET NAMES (or SET CHARACTER SET) statement. mysql_set_character_set() works like SET NAMES but also affects the character set used by mysql_real_escape_string(), which SET NAMES does not.