PHP & MySQL Input String Escape Problem

Question

Hello,

I've a weird string escape problem with my PHP script. I'm trying to get data from iSnare and put them into MySQL table.

I'm reading POST data and escaping strings with mysql_real_espace_string() function, also I can insert same data to .txt file without a problem but when I try to insert data into table, it cuts the string from apostrophes (') (or another char sometimes)

If that helps, my tables and fields are UTF8 and utf8_general_ci..

include("database.php");

 function security_sql($x){
    return mysql_real_escape_string(trim(stripslashes($x)));
 }

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


// Writes fine to text file
$fp = fopen('test.txt', 'a');
fwrite($fp, $title."\n");
fwrite($fp, $article."\n\n\n\n");
fclose($fp);



// BUT DOESNT WORK FINE WITH MYSQL
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());

Answer 1

I'd reccomend using htmlentities with the ENT_QUOTES flag as a part of your own sanitizing function.

function clean($string) {
    $ret = str_replace('=','=',$string);
    $ret = htmlentities($ret,ENT_QUOTES);
    return $ret;
    }

Above is the very simple sanitizing function I use for content output to a web browser stored in a relational database. It's probably not perfect, but it works well for me. (Note, = must be replaced to prevent injected queries involving integers)