SQL insert and outputting advice

Teach

Hello

I hope you can give me some advice please.

Until recently, we stored all MySQL input with mysql_real_escape_string(htmlentities(foo)), until someone said to me that was the wrong way, as I should be formatting it when extracting it from the database, not before.

I have therefore been through all my PHP (thousands of lines!, taken hours) and removed them so now they just have mysql_real_escape_string().

I then went through our SQL dump and replaced the 5 things that htmlentities() replaces.

Anyway, where I'm at now is wondering when to use htmlentities()? So far, I've just done it when we allow a textarea input that we echo our with nl2br.

However, before adding htmlentities() to everywhere we echo out data, is there some custom SQL function I can use to let PHP do it itself? We have literally thousands of things we echo out, and adding htmlentities() to each (which comes from user input) will be a nightmare.

Any advice/code/help would be greatly appreciated. :o

laserlight

Teach wrote:
Anyway, where I'm at now is wondering when to use htmlentities()? So far, I've just done it when we allow a textarea input that we echo our with nl2br.

The PHP manual has something to say about this in its PHP and HTML entry. Besides forms, you should use it whenever you print output to the user that is not intended to be interpreted as client side source code, but as ordinary text.

Teach wrote:
However, before adding htmlentities() to everywhere we echo out data, is there some custom SQL function I can use to let PHP do it itself? We have literally thousands of things we echo out, and adding htmlentities() to each (which comes from user input) will be a nightmare.

I am not aware of such a function, but it should be possible to write one. You could write a PHP function instead that abbreviates htmlentities(), but that may not help all that much, and potentially obfuscates the code.

Teach

Ah, okay thanks.

I'm right in assuming though that pretty much anything the user has stored should be outputted with htmlentities() or suchlike?

I'm just thinking let's say I have an input for "NAME", and someone puts some HTML Javascript there... that's potentially dangerous.

I'm thinking otherwise whether to use something similar to below to strip all HTML tags other than the ones we use. We have absolutely no use for any other code.

Does this look okay below?

function prepare($value)
{
    $stripped = strip_tags($value, '<strong></strong><em></em><u></u><code></code><sup></sup><a></a><span></span><img>');
    return $stripped;
}

$_POST = array_map('prepare', $_POST);
$_GET = array_map('prepare', $_GET);
$_COOKIE = array_map('prepare', $_COOKIE);
$_REQUEST = array_map('prepare', $_REQUEST);

a) Would the above work in theory?
b) Are there any reasons for not using the above opposed to strip_tags/htmlentities on every output (when we 100% only need those tags above)

Thanks!

Teach

Just edited above code to include working PHP.

Just really wanting to know the answer to "b)", whether it's a silly idea that might cause problems, or should in theory work to my needs.

I'm hoping this will prevent the need from putting htmlentities() anywhere, as the submission itself (along with my validation elsewhere) would prevent stay html, i.e. a <script>

Teach

Well, it's working perfectly for the function we need.

I've got a new problem though, and I really hope it's fixable 🙁 I can't find anything on google with what I'm searching...

When we use things like $_POST['to'][0], and [1] etc, those are no longer working.

When I remove that new code, they do again.

Is it fixable? Thank you.

Edit: Got this fixed with "if (is_array($value))"...back to that "b)" question then 😃

bradgrafelman

Teach;10901454 wrote:
b) Are there any reasons for not using the above opposed to strip_tags/htmlentities on every output (when we 100% only need those tags above)

Yes, because you're immediately destroying data integrity before you get a chance to properly process it.

What if you did this for a message board, or shoutbox, and we were having a mathematical discussion. If I stated the equality "if a<b or c>d do this", your code above would immediately alter the data to read "if ad do this" without giving you the chance to properly convert '<' to "<" and so on.