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.

Answer 3

"Set names utf8" only sets the character set for table and column names, you have to use "set character set utf8" too, for the content character set.

Answer 4

Are you sure that the string is not cut off because of was longer than your column definition allows?

The problem could also be that the data is stored successfully in the database, but the application which you use to show the data from the database cuts off the displayed string. This mostly happens with strings containing null characters in windows-based applications since windows-controls use null-terminated strings internally.

Answer 5

1) put that sql string you're generating into a variable and write it to a file before you send it to MySQL (so you can look at exactly what is being sent)

2) mysqld has a setting "max_allowed_packet" which I think cuts off long queries.

3) you're passing ENT_QUOTES to mysql_real_escape_string(). I think you mean to pass it to htmlentities()

Answer 6

Just solved a similar issue. My problem was I was trying to insert Latin-1 encoded data into a UTF-8 encoded table. Double check the encoding of your data -- you are likely getting non-UTF-8 data from your input.

Answer 7

Using mysqli_set_charset is what saved the day for me:

$conn = new mysqli($host, $username, $password, $database);
mysqli_set_charset($conn, 'utf8');

Answer 8

For those who might have the same problem and the answers before do not help, I got another suggestion: Check the CONTENT TYPE ! It should be like this:

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

I had the very same probleme as Donald had - this did the MAGIC :) (I did not imagin that the colegue who worked on the file in the first place hadn't set it right...)