123

I often see something similar to this below in PHP scripts using MySQL

query("SET NAMES utf8");

I have never had to do this for any project yet so I have a couple basic questions about it.

  1. Is this something that is done with PDO only?
  2. If it is not a PDO specific thing, then what is the purpose of doing it? I realize it is setting the encoding for mysql but I mean, I have never had to use it so why would I want to use it?
Alan Moore
  • 73,866
  • 12
  • 100
  • 156
JasonDavis
  • 48,204
  • 100
  • 318
  • 537
  • 4
    "SET NAMES utf8" should be avoided because of SQL injection. See php.net/manual/en/mysqlinfo.concepts.charset.php for details. – masakielastic Jun 08 '13 at 11:51
  • 3
    @masakielastic I do not see where setting 'set names utf8' is a threat to sql injection? Using proper MySQL API where is the thread? – broadband Aug 29 '13 at 07:20
  • 3
    Sorry for my unkindness. See ircmaxell's answer: http://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. – masakielastic Aug 29 '13 at 08:02

8 Answers8

78

It is needed whenever you want to send data to the server having characters that cannot be represented in pure ASCII, like 'ñ' or 'ö'.

That if the MySQL instance is not configured to expect UTF-8 encoding by default from client connections (many are, depending on your location and platform.)

Read http://www.joelonsoftware.com/articles/Unicode.html in case you aren't aware how Unicode works.

Read Whether to use "SET NAMES" to see SET NAMES alternatives and what exactly is it about.

Community
  • 1
  • 1
Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • 3
    'ö' and 'ñ' are extended ASCII. Would you still need to `SET NAMES UTF8` for them? – Tim Jun 26 '11 at 07: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? – NexusRex Aug 30 '11 at 19:15
  • 1
    @ 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 Čerpnjak Jun 23 '15 at 13:40
  • Note that it also specifies the character set that the server should use for sending results back to the client, thus is also needed when receiving this data, using for example a `SELECT` statement. – Leopoldo Sanczyk Nov 20 '15 at 20:54
  • 1
    @Tim. There's not really any such thing as "extended ASCII". There are a whole bunch of different encodings that can all be called extended ASCII (any single-byte character set where the first half is the same as ASCII, and there are loads of those). – TRiG Feb 11 '20 at 14:33
49

From the manual:

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.)

karim79
  • 339,989
  • 67
  • 413
  • 406
38

Getting encoding right is really tricky - there are too many layers:

  • Browser
  • Page
  • PHP
  • MySQL

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';

Few more notes:

  • 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. I recommend MySQL Workbench.
Community
  • 1
  • 1
Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277
20

This query should be written before the query which create or update data in the database, this query looks like :

mysql_query("set names 'utf8'");

Note that you should write the encode which you are using in the header for example if you are using utf-8 you add it like this in the header or it will couse a problem with Internet Explorer

so your page looks like this

<html>
    <head>
        <title>page title</title>
        <meta charset="UTF-8" />   
    </head>
    <body>
    <?php
            mysql_query("set names 'utf8'");   
            $sql = "INSERT * FROM ..... ";  
            mysql_query($sql);
    ?>    

    </body>
</html>
usama sulaiman
  • 2,013
  • 4
  • 24
  • 37
12

The solution is 

 $conn->set_charset("utf8");
nurp
  • 1,239
  • 2
  • 14
  • 23
6

Instead of doing this via an SQL query use the php function: mysqli::set_charset mysqli_set_charset

Note:

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.

See the MySQL character set concepts section for more information.

from http://www.php.net/manual/en/mysqli.set-charset.php

user1783273
  • 61
  • 1
  • 3
1

Thanks @all!

don't use: query("SET NAMES utf8"); this is setup stuff and not a query. put it right afte a connection start with setCharset() (or similar method)

some little thing in parctice:

status:

  • mysql server by default talks latin1
  • your hole app is in utf8
  • connection is made without any extra (so: latin1) (no SET NAMES utf8 ..., no set_charset() method/function)

Store and read data is no problem as long mysql can handle the characters. if you look in the db you will already see there is crap in it (e.g.using phpmyadmin).

until now this is not a problem! (wrong but works often (in europe)) ..

..unless another client/programm or a changed library, which works correct, will read/save data. then you are in big trouble!

user3162905
  • 11
  • 1
0

Not only PDO. If sql answer like '????' symbols, preset of you charset (hope UTF-8) really recommended:

if (!$mysqli->set_charset("utf8")) 
 { printf("Can't set utf8: %s\n", $mysqli->error); }

or via procedure style mysqli_set_charset($db,"utf8")

dmitry_podyachev
  • 509
  • 5
  • 5