Correct way of processing user input strings in SQL and PHP ?

Question

I have designed a comment system. This is what I am basically doing.

$story=$_POST['story'];
$story=mysql_real_escape_string($story);
$query = "INSERT INTO `comment` VALUES('$story')";

Now the problem is when i store the comment all the " are replaced by \" and all the ' are replaced by \'. So when I display the comments back these \ also show up in the comment.

Another problem is that & disappears. eg: if user comments I & you only I is stored into the database.

In fact in few cases comments don't even enter the database.

What is the correct way of processing & storing user comments so that you can display them back as written originally?

PS: I am not worried about sql injection. I just want comments to show up the way they were entered.

Answer 1

It looks like you have magic qoutes turned on. You should simply disable them from php.ini.

If you are worried about sql injection, consider using prepared statements.

Answer 2

Magic quotes may be turned on in your PHP install.

See Disabling Magic Quotes for more information

Answer 3

The \s aren't stored in your database. If you display the escaped $story variable you'll see the backslashes, but when you retrieve the data later on with a select-query, it'll just be the original data.

Make sure you have magic quotes disabled, otherwise the already escaped string will be escaped again automatically, causing e.g. "\\" which means that a backslash will be inserted.

Answer 4

Are you sure & disappears from the database? I'm guessing it doesn't appear on the page because & denotes the start of a HTML entity.

Use stripslashes to first remove the backslashes in front of your quotes, then use htmlspecialchars to escape HTML entities.

Answer 5

The easiest way to get them into the database is to use prepared statements and let someone else down the line worry about escaping.

Then when you get them out again, you still need to make sure ampersands etc are escaped to fit into html (i.e. use htmlspecialchars() or htmlentities()). When you get them they're in UTF-8 or ASCII or something. When you output them they're inside HTML. That means "showing up the way they were entered" doesn't mean giving back what you got directly.

Answer 6

use this:

function safe_mysql( $value ) {
    $magic_quotes_active = get_magic_quotes_gpc();
    $new_enough_php = function_exists( "mysql_real_escape_string" );
    if( $new_enough_php ) {

        if( $magic_quotes_active ) { $value = stripslashes( $value ); }
        $value = mysql_real_escape_string( $value );
    } else {

        if( !$magic_quotes_active ) { $value = addslashes( $value ); }

    }
    return $value;
}

Answer 7

Personally i use the following to santize data before inserting into MySQL.

$output = filter_var($input, FILTER_SANITIZE_STRING, FILTER_SANITIZE_SPECIAL_CHARS);

Unfortunatley this is for PHP 5 >= 5.2.0 so may not work on many shared servers.