How to insert HTML entities in MySQL using CodeIgniter?

Question

I am creating an application using CodeIgniter/MySQL. The problem is that when I store a text with Quotes or Apostrophe. It stores an Blank value to MySQL database.

function add_article($title, $desc){
        $data = array(
            'title' => htmlentities($title, ENT_QUOTES, "UTF-8"),
            'desc' => htmlentities($desc, ENT_QUOTES, "UTF-8")
        );
        if($this->db->insert('articles', $data)){
            return TRUE;
        } else {
            return FALSE;
        }
}

The character set of my table "articles" is "utf8" and COLLATE is utf8_unicode_ci. And HTML character set is also UTF-8. what's wrong? Please help me, and thanks in advance. If i don't use htmlentities() function i face two problems which i mention in my comments below:

Why are you using `htmlentities()` for something that's being stored in your database? That function is for escaping HTML, you use it on your *output*. — Wesley Murch, Jan 15 '12 at 15:43
@Madmartigan Because if don't use this, I face these problems http://stackoverflow.com/questions/8864127/how-i-can-convert-this-character-to-html-entities-using-php and http://stackoverflow.com/questions/8860407/how-can-i-strip-these-as-type-of-characters-with-php — Rizwan Khan, Jan 15 '12 at 15:46
I don't have an answer, which is why I didn't post one, but I wanted to comment about the side issue. There's no actual reason to encode to HTML entities in this context. — Wesley Murch, Jan 15 '12 at 15:53

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

You could have problems either on input or on output.

On Input

First of all, make sure that the string you receive actually is utf-8.

If you received the data from a form, add accept-encoding="utf-8" to the form element.
Verify that your input string could be a valid utf-8 stream with TRUE===mb_check_encoding($string, 'UTF-8')
Check that the byte sequence is as expected by counting characters: mb_strlen($string, 'UTF-8') should return the same number of characters as what you see, and a number less than strlen($string) (which counts bytes).

In your application/config/database.php, ensure you have these two settings for your database connection:

$db[$dbgroupname]['char_set'] = "utf8";
$db[$dbgroupname]['dbcollat'] = "utf8_unicode_ci";

Replace $dbgroupname with the group name of your connection (e.g., 'default').

Don't use htmlspecialchars or htmlentities on data before you store it. Use those on output, in your views.

On Output

Ensure that whatever you are using to view the data that comes out of your database expects a utf-8 encoding.

For html, make sure your Content-Type header includes charset=utf8 and your html document's head looks like this:

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

Check your results in multiple browsers. Some browsers do charset sniffing and might choose a different charset than what you declare. If so, this means that something on your page is not valid UTF-8--find that thing and eliminate.

If you are using some kind of database viewer (PHPMyAdmin, Navicat, etc), make sure the connection is configured to expect utf-8.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 15 '12 at 16:42

Francis Avila

31,233
6
58
96

I counts the characters. There is a difference b/w mb_strlen($string, 'UTF-8')=29 and strlen($string)=31. String is "HTML5′s placeholder Attribute" – Rizwan Khan Jan 15 '12 at 17:16
If i don't use the htmlentities(). My string becomes HTML5â?²s placeholder Attribute – Rizwan Khan Jan 15 '12 at 17:19
Yes, but *where* does it become that? The very thing you are using to *view* the string may be corrupting it! – Francis Avila Jan 15 '12 at 17:21
But it save in database with the same view like "HTML5â?²s placeholder Attribute"? What should i do now? i think problem is (′). – Rizwan Khan Jan 15 '12 at 17:25
Please follow the steps on the "On Output" section of my answer. What you are using to view the string in the database may itself not understand "UTF-8". In other words, it may be *stored* in the database correctly, only *displaying* incorrectly. – Francis Avila Jan 15 '12 at 17:26
You can see live example of my application http://www.seekphp.com/look/html5s-placeholder-attribute/1754 – Rizwan Khan Jan 15 '12 at 17:31
The string you are outputting (seen in view-source in the browser) is `HTML5â?²s placeholder Attribute`. This means it has already been improperly re-encoded by `htmlentities`. What you need to do is *not* use `htmlentities`! – Francis Avila Jan 15 '12 at 17:35
But it's not encoded by me. It is encode automatically by CodeIgniter. – Rizwan Khan Jan 15 '12 at 17:37
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6737/discussion-between-francis-avila-and-rizwan-khan) – Francis Avila Jan 15 '12 at 17:38

score 0 · Answer 2 · answered Jan 15 '12 at 17:39

0

Try this:
Add this mysql query in the system/database/DB.php file at the end of DB function. (before return $DB; ) .

$DB->simple_query('SET NAMES \'utf8\'');

answered Jan 15 '12 at 17:39

Valeh Hajiyev

3,216
4
19
28

I try the query but getting same problem. – Rizwan Khan Jan 15 '12 at 17:45
problem is mentioned in my question. and in two questions http://stackoverflow.com/questions/8864127/how-i-can-convert-this-character-to-html-entities-using-php and http://stackoverflow.com/questions/8860407/how-can-i-strip-these-as-type-of-characters-with-php – Rizwan Khan Jan 15 '12 at 17:56
This is unnecessary. Codeigniter does this for you in `mysql_driver.php`, `db_set_charset()` method, which is run as part of connection initialization. – Francis Avila Jan 15 '12 at 18:19

How to insert HTML entities in MySQL using CodeIgniter?

2 Answers2

On Input

On Output