MySQL cuts off string at special chars

Question

Hello,

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 UTF8 and utf8_general_ci (also tried utf8_unicode_ci and utf8_bin as collation)
• saving all PHP files as UTF8

Still I couldn't find the solution. I'll be very very very appreciated if someone can help to solve this problem....

---

Here is my table definition and PHP codes...

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...
    if ($_SERVER['REMOTE_ADDR'] == $isnareIP || $_SERVER['REMOTE_ADDR'] == "85.105.50.46") {
        $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

--- HERE IS THE 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

"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 2

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 3

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()