StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h1>TLDR</h1> <pre><code>// The key is the "charset=utf8" part. $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8'; $dbh = new PDO($dsn, 'user', 'pass'); </code></pre> <p>This answer has an emphasis on php's pdo library because it's so ubiquitous.</p> <p>A brief reminder - mysql is a client-server architecture. This is significant because there's not only the mysql server where the actual database is, but there's also the seperate mysql client driver, which is the thing that talks to the mysql server(they're separate entities). You could kinda sorta say the mysql client and pdo are mixed together.</p> <p>When you use <code>set names utf8</code>, you issue a standard sql query to mysql. While the sql query does pass through pdo, and then through the mysql client library, and then finally it reaches the mysql server, ONLY the mysql server parses and interprets that sql query. This is significant because the mysql server doesn't send any message back to pdo or the mysql client letting it know the character set and encoding has changed, and so the mysql client and pdo are both totally ignorant to fact that it happened.</p> <p>It's important not to do this because the client library cannot properly handle strings if it isn't aware of the current character set. Most common operations will work correctly without the client knowing the correct character set, but one that won't is string escaping, such as <a href="http://php.net/manual/en/pdo.quote.php" rel="nofollow noreferrer">PDO::quote</a>. You may think you don't need to worry about such manual primitive string escaping because you use prepared statements, but the truth is the vast majority of pdo:mysql users unknowingly use <a href="http://php.net/manual/en/pdo.constants.php#pdo.constants.attr-emulate-prepares" rel="nofollow noreferrer">emulated prepared statements</a> because it's been the default setting for the pdo:mysql driver for a very long time now. An emulated prepared statement doesn't use real native mysql prepared statements as provided by the mysql api; instead, php does the equivalent of calling <code>PDO::quote()</code> on all your values, and str_replacing'ing all your placeholders with the quoted values for you.</p> <p>Since you can't properly escape a string unless you know the character set you're using, these emulated prepared statements are vulnerable to sql injection if you've changed to certain character sets via <code>set names</code>. Regardless of the possibility of sql injection, you can still break your strings if you use an escaping scheme intended for a different character set.</p> <p>For the pdo mysql driver, you can specify the character set when you connect, by <a href="http://php.net/manual/en/ref.pdo-mysql.connection.php" rel="nofollow noreferrer">specifying it in the DSN</a>. The client library and the server will both be aware of the character set if you do this, and so things will work like they should.</p> <pre><code>// The key is the "charset=utf8" part. $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8'; $dbh = new PDO($dsn, 'user', 'pass'); </code></pre> <p>But improper string escaping isn't the only problem. For example, you can also have problems with using <a href="http://php.net/manual/en/pdostatement.bindcolumn.php" rel="nofollow noreferrer">PDO::bindColumn</a> because column names are specified as strings, and so again the encoding matters. An example could be a column name named <code>ütube</code>(note the umlaut), and you switch from <code>latin</code> to <code>utf8</code> via set names, and then you try to <code>$stmt->bindColumn('ütube', $var);</code> with <code>ütube</code> being a utf8 encoded string because your php file is utf8 encoded. It won't work, you would need to encode the string as a latin1 variant... and now you have all kinds of crazy going on.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload