database encoding problem? Double and single quotes being displayed w/ question marks

Question

Old server db = MySQL v4.0.21 New server db = MySQL v5.0.45

I am moving an app to a new server, and I copied over the database.

The app sends an agenda for the day and whenever there are double & single quotes " ', they show up as question marks ?

Comes up like this on the server it was moved to ?The Horror of Race: HBO?s True Blood?

This is what it looks like on the original server app was built on: “The Horror of Race: HBO’s True Blood”

Screenshot of database w/i phpmyadmin http://grab.by/2EsU (original server mysql v4.0.21) and http://grab.by/2EtN (new server mysql 5.0.45)

Screenshot of table the data is stored within: http://grab.by/2Et2 (it only happens w/i the body column)

Screenshot of data within new server table: http://grab.by/2Etb (you'll notice the question marks ?)

Screenshot of data within original server table: http://grab.by/2Etl

The app is built w/ PHP, and it prints the body like nl2br($body);

The string is stored w/i the $body variable before being inserted into the db table, like this: $body=addslashes($_POST['body']);

Any help as to why it is displaying the ? marks in the place of double and single quotes, would be helpful - much appreciated.

Answer 1

Thhis is most likely due to a difference in character encoding settings. This may be in effect in a couple of places. I would advise you to log into both servers and do:

mysql> show variables like '%character%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | latin1                                        |
| character_set_connection | latin1                                        |
| character_set_database   | latin1                                        |
| character_set_filesystem | binary                                        |
| character_set_results    | latin1                                        |
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |
| character_sets_dir       | D:\Servers\MySQL\MySQL_5_1_36\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

See if you see any difference there. For example, if the default connection characterset is different for the new server, you could get these results.

You should also ensure the character encoding settings for the columns: do a SHOW CREATE TABLE <table-name> and check if the character sets are still the same at the column level mysql>

EDIT Alternatively, as Martin pointed out in the comments, you could be dealing with a SQL dump that is encoded in an encoding you didn't anticipate. Here's some more information on that: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_default-character-set. In this case you could try to re-encode the dump file using a tool like iconv (http://www.gnu.org/software/libiconv/documentation/libiconv/iconv.1.html)

Answer 2

To be honest i think there is another piece of the puzzle that is missing for this description of the problem because everything looks valid. There are two other functions that you could use to prevent SQL Injection that may solve your strange problem.

$var=addslashes(htmlspeicalchars($var,ENT_QUOTES));

or a better approach:

$var=mysql_real_escape_string($var);