MySQL cuts off string at special chars

Question

I'm trying to insert remote POST data (articles sent by iSnare) into MySQL with PHP. Data comes successfully from remote POST sender and I can write it to plain-text file without a problem.

Unfortunately, when it comes to insert it into MySQL, MySQL cuts off string (article) at special char. I tried many things but still I'm unsuccessful!

I tried:

• Escaping chars with mysql_real_escape_string()
• Using htmlentities() and htmlspecialchars() (with every parameter..)
• Sending SET NAMES utf8 query to MySQL before doing everything else
• All tables and columns are UTF-8 encoded and utf8_general_ci (also tried utf8_unicode_ci and utf8_bin as collation)
• Saving all PHP files as UTF-8

Still I couldn't find the solution. I will appreciate it very very very much if someone can help me solve this problem.

---

Here is my table definition and PHP codes:

PHP

function guvenlik_sql($x){

    // Cleans inputs agains sql injection
    return mysql_real_escape_string(htmlentities(stripslashes($x)), ENT_QUOTES);
}    

// Check if data really comes from an Isnare.com server (Address hidden)
if ($_SERVER['REMOTE_ADDR'] == $isnareIP || $_SERVER['REMOTE_ADDR'] == "xxx.xxx.xxx.xxx") {

    $title = guvenlik_sql($_POST["article_title"]);
    $first_name = guvenlik_sql($_POST["article_author"]);
    $description = guvenlik_sql($_POST["article_summary"]);
    $category = guvenlik_sql($_POST["article_category"]);
    $article = guvenlik_sql($_REQUEST["article_body_text"]);
    $article_html = guvenlik_sql($_POST["article_body_html"]);
    $resource_box = guvenlik_sql($_POST["article_bio_text"]);
    $resource_box_html = guvenlik_sql($_POST["article_bio_html"]);
    $keywords = guvenlik_sql($_POST["article_keywords"]);
    $email = guvenlik_sql($_POST["article_email"]);

    $fp = fopen('test.txt', 'a');
    fwrite($fp, $title."\n");
    fwrite($fp, $article."\n\n\n\n");
    fclose($fp);

mysql_query("INSERT INTO articles " . 
            "(" . 
                "first_name, " . 
                "email, " . 
                "title, " . 
                "description, " . 
                "article, " . 
                "article_html, " . 
                "category, " . 
                "resource_box, " . 
                "resource_box_html, " . 
                "keywords, " . 
                "distributor, " . 
                "distributor_host" . 
            ") VALUES (" . 
                "'$first_name', " . 
                "'$email', " . 
                "'$title', " . 
                "'$description', " . 
                "'$article', " . 
                "'$article_html', " . 
                "'$category', " . 
                "'$resource_box', " . 
                "'$resource_box_html', " . 
                "'$keywords', " . 
                "'$isnare', " . 
                "'$_SERVER['REMOTE_ADDR']', " . 
            ")") or die(mysql_error());

} //end if security

Table definiton

CREATE TABLE `articles` (
   `article_ID` int(11) NOT NULL auto_increment,
   `first_name` varchar(100) NOT NULL,
   `last_name` varchar(100) NOT NULL,
   `email` varchar(100) NOT NULL,
   `password` varchar(100) NOT NULL,
   `author_url` varchar(255) NOT NULL,
   `company_name` varchar(100) NOT NULL,
   `address1` varchar(100) NOT NULL,
   `address2` varchar(100) NOT NULL,
   `state_2digit` varchar(100) NOT NULL,
   `state` varchar(100) NOT NULL,
   `zip_code` varchar(100) NOT NULL,
   `country` varchar(100) NOT NULL,
   `phone` varchar(100) NOT NULL,
   `newsletter` varchar(100) NOT NULL,
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `article` longtext NOT NULL,
   `article_html` longtext NOT NULL,
   `category` varchar(100) NOT NULL,
   `cat` varchar(100) NOT NULL,
   `resource_box` text NOT NULL,
   `resource_box_html` longtext NOT NULL,
   `keywords` varchar(255) NOT NULL,
   `publish_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
   `distributor` varchar(255) NOT NULL default '',
   `distributor_host` varchar(255) NOT NULL,
   PRIMARY KEY  (`article_ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Answer 1

I've just dealt with the same situation, entries were cut off where the special character (ä, ö, è, etc) supposed to be. All my files are UTF8 encoded, the connection is UTF8 encoded, table collations are UTF8, still the entries were cut off.

My solution was: even more UTF-encoding! :) Use utf8_encode() on the entries which can contain special characters.

mysql_query("INSERT INTO articles (first_name, email, title, description, article, article_html, category, resource_box, resource_box_html, keywords, distributor, distributor_host) values (
                                  '" . utf8_encode($first_name) . "',
                                  '" . $email . "',
                                  '" . utf8_encode($title) . "',
                                  '" . utf8_encode($description) . "',
                                  // etc

Answer 2

Very late answer, but I came across this issue in one of our older projects. It turns out, that MySql has its own "implementation" of UTF8. It cannot save chars, which consists of more than 3 bytes (like emoticons etc.). Sometimes it throws error (like in this question "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?) and sometimes it just cuts off the string.

The solution is switching from utf8 to utf8mb4 as described in linked question above, or make sure you cut off all long chars before saving.

I know this is an old question, but as we are using more and more emoticons, this can occur in some older apps. Hope it helps someone.