Sanitize user input for database insertion

This always confuses me

[madk]

Hello all,

I'm working on adding some sort of comment system to my blog and I always have a hard time figuring out how to properly protect my database.

My server has Magic Quotes turned on by default but I am reading conflicting things as to whether this is a good thing.

INSERT INTO ds_comments (post_id, user_id, datetime, comment) VALUES ('$post_id', '$user_id', NOW(), '$comment')

The only data that is coming from the user is the comment variable. Here is how I have been preparing the input:

// Prepare Comment
$comment = strip_tags($_POST['comment'], '<b><i><img>');
$comment = mysql_real_escape_string($comment);

Is this enough protection? If not please provide me with some appropriate steps. Please educate me a bit.

Thank you in advance,
M. Kris

[PHP_Chimp]

The strip tags stops people putting html on the page, so you wont get to many links across to porn. It also helps with stopping people loading the excellent javascript window.location... come to my page thanks.

Escaping the input will help to stop people sending bad stuff to your server.

magic_quotes only escapes ',",\ and (i think) null's. So if you have it active it will (maybe) assist, but you have to remember to remove the added extra quotes, so it is actually a bit of a pain. With the other steps you are taking you may just want to turn it off.

You may want to stop users putting certain characters into there comments, like <, >, ;, :, #, % and a few others, as (apart from the %) how many of those are actually used in a normal conversation. Just make a list of characters that have special meaning in html and disallow then in all comments. Or use htmlspecialchars [uk2.php.net] or htmlentities [uk2.php.net] to try and stop people adding inappropriate code.

<edit>
Dont allow image tags...as IE has a huge amount of XSS holes for image tags.
If you want to allow people to use images then you need to consider how you will check them to stop XSS. I suppose that you could get them to upload the image, then in its binary state check it for the usual XSS added extras...but honestly that isnt something I have bothered to think about.

And as with everything security wise it all depends on what you are protecting. Image tags and XSS may not be something you are that worried about, or it may be a huge problem...up to you.

[edited by: PHP_Chimp at 4:23 pm (utc) on Nov. 1, 2007]

[jdMorgan]

Parallel discussion in the Databases forum: [webmasterworld.com...]

Jim

[joelgreen]

mysql_real_escape_string does not escape %. So you'll have escape this one if user input is used with LIKE.

[FourDegreez]

Another thing to look into, if your host supports it, is PDO and bind variables. This is a more programatically "mature" technique to use, safer and better performing. I've started to use it and won't go back.