submitting text for use in a mysql database

[windandwaves]

Hi Folk

This question has been bugging me for month. I have a website where people can enter stuff into a mysql database. Some of this
information will already be shown in some textareas and input boxes, the user may change it and then hit submit. All the values are
then passed to a mysql database, overriding the existing values (after the old ones have been backed-up).

All of this works marvellous, apart from characters like &, ' and ". To make it even worse, some of the original text already
contains &039; and other numeric character codes.

Any values from the database are displayed by retrieving them from the DB

[simplified of course]

mysql_quer(...)
$row = ...
$Vold1 = $row[x];
$Vold2 = $row[y];
etc...

<INPUT VALUE=$Vold NAME="x">
<TEXTAREA>$Vold</TEXTAREA NAME="y">


Any values that are submitted are processed as follows:

$V1 = trim(stripslashes(htmlentities(trim($_POST["x"])))))
$V1 = trim(stripslashes(htmlentities(trim($_POST["y"])))))

then $V is inserted into the table using a mysql query.

Am I doing it right or am I making a mess of it?

- Nicolaas

 

[ZeldorBlat]

As a start, check that magic_quotes is enabled in php.ini. This will
automatically escape " and ' characters for you. If that isn't an
option, check out mysql_real_escape_string:

http://www.php.net/manual/en/function.mysql-real-escape-string.php

You shouldn't need to use htmlentities() when you insert/update data
in the database. Instead you should use it when you pull it out and
display it on your webpage.

 

[Toby Inkster]

<INPUT VALUE=$Vold NAME="x">
<TEXTAREA>$Vold</TEXTAREA NAME="y">

<INPUT VALUE=$Vold NAME="x">
<TEXTAREA>htmlentities($Vold)</TEXTAREA NAME="y">

?

 

[windandwaves]

As a start, check that magic_quotes is enabled in php.ini. This will
automatically escape " and ' characters for you. If that isn't an
option, check out mysql_real_escape_string:

Thank you for your reply

I checked it out and magic_quotes_gpc is on, but magic_quotes_runtime is off
and so is magic_quotes_sybase

however, it seems to be working.

http://www.php.net/manual/en/function.mysql-real-escape-string.php

You shouldn't need to use htmlentities() when you insert/update data
in the database. Instead you should use it when you pull it out and
display it on your webpage.

Does it matter if I do this process when it is inserted rather than
displayed? All the data is for display only anyway. In that way, I only
have to convert once and I can display the data in 100 ways without ever
having to worry about converting it using the htmlentities thing.

Let me know if I am doing it right.

Thank you.

 

[Greg Schmidt]

Thank you for your reply

I checked it out and magic_quotes_gpc is on, but magic_quotes_runtime is off
and so is magic_quotes_sybase

however, it seems to be working.

This may only be because you haven't had anyone put anything malicious
in yet. What if the "text" to be displayed in your textarea looks like
this:

</TEXTAREA><OBJECT "some malicious object pulled from another
server"></OBJECT><TEXTAREA>

Now, visitors to your page could be infected with a trojan, shown
pornographic images, have their browser or OS crashed by some exploit,
or any number of other nasty things.

You need to think of similar things when putting the data into MySQL.
What happens if the input looks like this:

"; drop database "xyz

Exact details of how to destroy your system will vary, of course, but
remember that someone can sit there and keep plugging stuff into your
form (via an automated script, even) all day long.

I've just gone through all of these issues for a site I'm working on, so
they're fresh in my mind.

 

[windandwaves]

This may only be because you haven't had anyone put anything malicious
in yet. What if the "text" to be displayed in your textarea looks
like this:

</TEXTAREA><OBJECT "some malicious object pulled from another
server"></OBJECT><TEXTAREA>

I agree, but that is why all the new data that people "Post" is converted
for htmlentities, what else should I do?

Now, visitors to your page could be infected with a trojan, shown
pornographic images, have their browser or OS crashed by some exploit,
or any number of other nasty things.

You need to think of similar things when putting the data into MySQL.
What happens if the input looks like this:

"; drop database "xyz

Exact details of how to destroy your system will vary, of course, but
remember that someone can sit there and keep plugging stuff into your
form (via an automated script, even) all day long.

In my mind there is no doubt that they can if they really wanted to... I
protect myself by reducing the number of forms that are accessible in the
non-password protected area and paying extra attention to them. If people
log-on to my site, then obviously there are less likely to be malicious.

For the most vulnerable locations, I also check for special words (e.g. drop
and database).

What else should i be doing?

 

[Toby Inkster]

What else should i be doing?

Escaping single-quote marks.

 

[windandwaves]

Escaping single-quote marks.

I have done that now, using ENT_QUOTES in the htmlentities function.

Thank you very much

- Nicolaas

 

[Zachary Kessin]

Escaping single-quote marks.

Use "mysql_real_escape_string" which will do much of the escaping for
you. It will not only get quotation marks but a lot of other things.


--Zach