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.

If you want comments to show up the way they were entered, you are worried about SQL injection. — Incognito
– Incognito, Commented Oct 12, 2010 at 20:20

Sarfraz · Accepted Answer · 2010-10-12 20:16:06Z

4

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.

edited Oct 12, 2010 at 20:16

answered Oct 12, 2010 at 20:14

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshua Rodgers · Accepted Answer · 2010-10-12 20:15:18Z

1

Magic quotes may be turned on in your PHP install.

See Disabling Magic Quotes for more information

answered Oct 12, 2010 at 20:15

Joshua Rodgers

5,4342 gold badges33 silver badges30 bronze badges

Comments

TheNone · Accepted Answer · 2010-10-12 20:19:57Z

0

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;
}

answered Oct 12, 2010 at 20:19

TheNone

5,80215 gold badges60 silver badges98 bronze badges

2 Comments

Gaurav Over a year ago

thanks. that seems to work for the ` problem but &` still does not enter the database. I would understand if it didn't show up correctly but it totally disappears & everything after that goes as well.

TheNone Over a year ago

I have tested with '&' and there is no any problem.

Alec · Accepted Answer · 2010-10-12 20:16:52Z

0

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.

answered Oct 12, 2010 at 20:16

Alec

9,1089 gold badges41 silver badges43 bronze badges

Comments

casablanca · Accepted Answer · 2010-10-12 20:17:49Z

0

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.

answered Oct 12, 2010 at 20:17

casablanca

70.9k8 gold badges139 silver badges155 bronze badges

Comments

Michael Clerx · Accepted Answer · 2010-10-12 20:18:47Z

0

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.

answered Oct 12, 2010 at 20:18

Michael Clerx

3,0762 gold badges37 silver badges53 bronze badges

3 Comments

Gaurav Over a year ago

& does not even make it to the database. Why should that happen?

bobince Over a year ago

Good question. & is not a special character for SQL, so if it doesn't make it to the database (are you sure that's the case, have you checked with the mysql client?) there must be some misguided component processing the input other than just magic_quotes. Are you using some kind of framework or server mod that might be blocking it?

Michael Clerx Over a year ago

Have you tried some different ways of inserting data (using your current PHP methods, using phpmyadmin, using prepared statements, using an app like TOAD). Stuff like this may help you pinpoint where the error lies.

David Cooke · Accepted Answer · 2010-10-13 16:05:15Z

0

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.

answered Oct 13, 2010 at 16:05

David Cooke

1411 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

Comments

Comments

2 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

2 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related