Why are question marks replacing certain characters in mysql database with collation utf-8?

Question

I'm using Jsoup to scrape a webpage. It takes the text and enters it directly into the database.

The text on the target webpage looks perfectly fine, but after entering it into the database i get question marks replacing certain characters.

For example the single right quotation marks (U+2019) in the following sentence:

I can’t imagine uh, a domain of human endeavor that isn’t impacted by the imagination.

Will show up like this in the database and on the webpage i'm outputting it on:

I can?t imagine uh, a domain of human endeavor that isn?t impacted by the imagination.

Initially i thought this was just a problem with the charset/collation of the database but after trying out different types, the problem persists...

The sql database i'm currently working in is in utf-8:

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
+--------------------------+--------+

And the meta is set:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I've tried specifically setting it in java like so:

  url = "jdbc:mysql://localhost:3306/somedb?useUnicode=true&characterEncoding=utf-8";

I've tried sql queries like:

SET NAMES 'utf8'
SET CHARACTER SET utf8

I've tried creating a new database and nothing seems to work..

Any ideas why this might be happening?

yes, just tried it now, still getting the same problem unfortunately. But nice suggestion — Jonathan Laliberte, Dec 20 '17 at 20:26

score 1 · Accepted Answer · answered Dec 21 '17 at 13:25

Jsoup automatically detects the charset for the webpage being crawled. However, many websites do not set character set encoding along with the content-type header by not defining charset.

If you crawl such webpage, where the charset attribute is missing in HTTP response Content-Type header, Jsoup parses the page using platform’s default character set. That also means that you might not get expected results as the platform’s default character set might be different from the webpage you are crawling. It might result in loss of characters or them being parsed/printed incorrectly.

To avoid such behavior you need to read the URL as InputStream and manually specify your desired character set in parse method of Jsoup as given below:

String page = "http://www.somepage.com";

//get input stream from the URL
InputStream in = new URL(page).openStream();

//parse document using input stream and specify the charset
Document doc = Jsoup.parse(in, "ISO-8859-1", page);

//..do your processing

Thanks for your answer. Unfortunately this was what i tried after @Luke Bajada mentioned jsoup encoding in the comment above. It didn't work either :'( And also the encoding is set on the page I'm scraping — Jonathan Laliberte, Dec 21 '17 at 18:20

score 0 · Answer 2 · answered Dec 27 '17 at 01:37

0

There are several steps to make a page work correctly.

See "question mark" in Trouble with UTF-8 characters; what I see is not what I stored

answered Dec 27 '17 at 01:37

Rick James

135,179
13
127
222

Why are question marks replacing certain characters in mysql database with collation utf-8?

2 Answers2