[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MySQL turns apostrophes into symbols?

Posted on 2007-11-29
7
Medium Priority
?
4,547 Views
Last Modified: 2013-12-12
I am using, as per my understanding, MySQL version 5.0.45-community.

Using a PHP script, I am retrieving text from a database after inputting it with phpMyAdmin. This text contains apostrophes (the keyboard character, not the ASCII code version).

When that text is outputted to a web page, it appears as a symbol (in Firefox, as a question mark inside a diamond) instead of an apostrophe.

What is causing this, and how can I fix it?
0
Comment
Question by:TheAnarchist
7 Comments
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 20380678
Are you using the proper charset to connect to DB and in webpag? What you see when you view source?
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 20380681
I am talking about HTML source in output webpage.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 1200 total points
ID: 20380690
is it a unicode string field? either that or it's a charset issue. in your html header, specify the correct charset
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 21

Expert Comment

by:nizsmo
ID: 20380714
Try when inserting the data with the apostrophe to use either:

$escaped = addslashes($myvalue);

or

$escaped = mysql_escape_string($myvalue);

and see if it works when retrieving.

Hope this helps.
0
 
LVL 13

Assisted Solution

by:Robin Hickmott
Robin Hickmott earned 300 total points
ID: 20380729
Its because firefox is recognising it as a reserved character.

Depending on the scripting your using ( HTML,XHTML ( Transitional, Strict, Etc )).

IE has a "Quirks" mode which will display quotes etc BUT because there used in the HTML language they are normally need to be converted to explicitly tell the browser to display the character rather than interpret it as part of a tag. firefox tends to convert most things £ signs, Aphersands etc into the diamond question mark if you dont print it correctly.

For this reason you need to convert "'s to &quots; &'s into & and so on.

If you view your source you will find that the right character is there.... Its just firefox wont display it.

To get around this problem you can use the htmlentities function ( http://uk.php.net/htmlentities ) to convert special characters into displayable ones. Its worth noting you should do this on all data from a database you wish to display and all incoming data to make sure that the user hasent put "> into a textbox etc otherwise when you display the results it will become part of the html which breaks things significantly. If your putting stuff into the database from a user you should make sure that no ' and " exist as it can lead to SQL injection attacks. Once you have the input you can use the reverse function and addslashes etc for inserting into your database.
0
 
LVL 6

Author Comment

by:TheAnarchist
ID: 20417090
My apologies for not getting back to this question yet. A number of technical issues and other unrelated problems have been coming up.

I will resolve this question, hopefully before the end of next week.
0
 
LVL 6

Author Comment

by:TheAnarchist
ID: 20424989
Thanks to all for your help. I have split the points.

The issue at hand turned out to be with the character set. The HTML page (which was built by another developer) used UTF-8 encoding. Changing the character set to an ISO character set solved the issue of the question marks.

imitchie gets 400 points for solving the issue at hand.

rhickmott, gets 100 points for the detail provided in their answer (and because it has solved a seperate issue that I will need to consider further down the line). As it turned out, the question mark symbol was visible in the source as much as it was on the page - changing the character set resolved this issue, however.

Thanks again.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question