Make Safe For MYSQL Insert

Sops21

I'm inserting user input into mysql. I'm going to be later displaying the information. I'm using nl2br to preserve the "enters". I want to remove all other HTML from the input as well make the information safe to insert into the database.

This is what I'm using now. Is this enough?

$message =nl2br(htmlspecialchars($_POST['message'], ENT_QUOTES));

dagon

mysql_real_escape_string

sneakyimp

"safe to insert into the database" is largely handled by what dagon suggested. That function is critical to make sure you are safe from mysql injection attacks.

What you do with the data when you pull it out of the database is also a point of concern. Are you concerned about hackers inserting javascript into your site? Embedding porn spam in your pages? If so, you might consider [man]strip_tags[/man] too. Either that or some fancy preg_replace to remove stuff you consider bad.

laserlight

mysql_real_escape_string should be used for strings, as the name implies. For numbers, cast them to the appropriate type, or use sprintf. You could use quotes and treat them as if they were strings, and it would work with mysql_real_escape_string, but semantically that is wrong.

htmlspecialchars, which you are already using, should be sufficient as a non-destructive alternative to strip_tags. You should not use "some fancy preg_replace to remove stuff you consider bad". Rather, if you do want to remove stuff, then you should remove everything that you don't consider good.

anakadote

And I would personally suggest saving the formatting stuff for when you're ready to display it. So, going into the database save the raw, escaped data:

$message = mysql_real_escape_string($_POST['message']);
// Save to database

And then once you've queried the database for the record apply the formatting:

// Select record from database
echo nl2br(htmlentities($row['message'], ENT_QUOTES));

Sops21

Thanks for all the feedback. I'm going to use the mysql_real_escape_string. I'm thinking it about using strip_tags instead of htmlspecialchars. There is no need for the users to enter html so I'd rather strip out everything.

The only decision that I have left is whether or not to use strip_tags before inserting the data into the database or when when displaying it. Is there any reason to do it before or after, or is it just personal preference?

sneakyimp

Usually a database inserts data once and then displays it many times. In that case, it's probably better to strip the tags before you insert the data because you'll only need to strip them once on the insert rather than every single time you have to display it. Also, the database will be smaller if you strip the tags before inserting. Searches faster, etc.

laserlight

Sops21 wrote:
The only decision that I have left is whether or not to use strip_tags before inserting the data into the database or when when displaying it. Is there any reason to do it before or after, or is it just personal preference?

I strongly suggest that you do not use strip_tags, unless you are very sure that you can accept destruction of the input. Read the warning:

PHP manual wrote:
Because strip_tags() does not actually validate the HTML, partial, or broken tags can result in the removal of more text/data than expected.

That said, if you do decide to use strip_tags, or if you decide to use htmlspecialchars or htmlentities instead, anakadote's suggestion of storing the data as-is and only using these functions when displaying data is a good approach because it makes sure that you retain flexibility. Unfortunately, sneakyimp's point is also a good one: it is more efficient to be inflexible by committing to the "safe for display" version. You have to decide on what you want. You could get the best of both worlds by caching the "safe for display" version, but that comes at the cost of more data storage.