"SET NAMES utf8" should be avoided because of SQL injection. See php.net/manual/en/mysqlinfo.concepts.charset.php for details.
–
masakielasticJun 8 '13 at 11:51

1

@masakielastic I do not see where setting 'set names utf8' is a threat to sql injection? Using proper MySQL API where is the thread?
–
broadbandAug 29 '13 at 7:20

3

Sorry for my unkindness. See ircmaxell's answer: stackoverflow.com/a/12118602/531320 Althogh "SET NAMES" has no problem as long as using UTF-8, the possibility you will use GBK or Big5 (Chinese) or Shift_JIS (Japanese) in the future is undeniable.
–
masakielasticAug 29 '13 at 8:02

'ö' and 'ñ' are extended ASCII. Would you still need to SET NAMES UTF8 for them?
–
TimJun 26 '11 at 7:12

2

I have found that I often have to add utf8_decode($my_text); in PHP to get special UTF-8 characters to show on websites properly when the data was queried from MySQL. My tables and columns are set to UTF-8 in MySQL—so should this be necessary?
–
NexusRexAug 30 '11 at 19:15

@ Vinko Vrsalovic: Not necessarily... I had all my files in utf8 but my previous hoster has had the mysql charset set to latin1 and because i havent told mysql that I am sending chars in utf8 (hence set names utf8) it stored them in latin charset and all my special chars (Slovenian čšž) looked like they were overrun by a car - one more thing: when you make a search in phpmyadmin you wont find results, because a č is like Å and so on
–
Erik ČerpnjakJun 23 at 13:40

SET NAMES indicates what character set
the client will use to send SQL
statements to the server.

More elaborately, (and once again, gratuitously lifted from the manual):

SET NAMES indicates what character set
the client will use to send SQL
statements to the server. Thus, SET
NAMES 'cp1251' tells the server,
“future incoming messages from this
client are in character set cp1251.”
It also specifies the character set
that the server should use for sending
results back to the client. (For
example, it indicates what character
set to use for column values if you
use a SELECT statement.)

The SQL command "SET CHARSET utf8" from PHP will ensure that the client side (PHP) will get the data in utf8, no matter how they are stored in the database. Of course, they need to be stored correctly first.

DDL definition vs. real data

Encoding defined for a table/column doesn't really mean that the data are in that encoding. If you happened to have a table defined as utf8 but stored as differtent encoding, then MySQL will treat them as utf8 and you're in trouble. Which means you have to fix this first.

What to check

You need to check in what encoding the data flow at each layer.

Check HTTP headers, headers.

Check what's really sent in body of the request.

Don't forget that MySQL has encoding almost everywhere:

Database

Tables

Columns

Server as a whole

Client
Make sure that there's the right one everywhere.

Conversion

If you receive data in e.g. windows-1250, and want to store in utf-8, then use this SQL before storing:

SET NAMES 'cp1250';

If you have data in DB as windows-1250 and want to retreive utf8, use:

SET CHARSET 'utf8';

Last note:

Don't rely on too "smart" tools to show the data. E.g. phpMyAdmin does (was doing when I was using it) encoding really bad. And it goes through all the layers so it's hard to find out. Also, Internet Explorer had really stupid behavior of "guessing" the encoding based on weird rules. Use simple editors where you can switch encoding. Also, I recommend MySQL Workbench.