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(stripslash es(htmlentities (trim($_POST["x"])))))
$V1 = trim(stripslash es(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]

Re: submitting text for use in a mysql database

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_esca pe_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]

Re: submitting text for use in a mysql database

windandwaves wrote:
[color=blue]
> <INPUT VALUE=$Vold NAME="x">
> <TEXTAREA>$Vold </TEXTAREA NAME="y">[/color]

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

?

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

[windandwaves]

Re: submitting text for use in a mysql database

ZeldorBlat wrote:[color=blue]
> 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_esca pe_string:[/color]

Thank you for your reply

I checked it out and magic_quotes_gp c is on, but magic_quotes_ru ntime is off
and so is magic_quotes_sy base

however, it seems to be working.

[color=blue]
>
> http://www.php.net/manual/en/functio...ape-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.[/color]

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]

Re: submitting text for use in a mysql database

On Sat, 2 Apr 2005 08:38:55 +1200, windandwaves wrote:
[color=blue]
> ZeldorBlat wrote:[color=green]
>> 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_esca pe_string:[/color]
>
> Thank you for your reply
>
> I checked it out and magic_quotes_gp c is on, but magic_quotes_ru ntime is off
> and so is magic_quotes_sy base
>
> however, it seems to be working.[/color]

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><OBJEC T "some malicious object pulled from another
server"></OBJECT><TEXTARE A>

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.

--
Greg Schmidt gregs@trawna.co m
Trawna Publications http://www.trawna.com/

[windandwaves]

Re: submitting text for use in a mysql database

Greg Schmidt wrote:
[color=blue]
> 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><OBJEC T "some malicious object pulled from another
> server"></OBJECT><TEXTARE A>[/color]

I agree, but that is why all the new data that people "Post" is converted
for htmlentities, what else should I do?
[color=blue]
>
> 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.[/color]


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?

[Ian.H]

Re: submitting text for use in a mysql database

On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
<winandwaves@co ldmail.com> wrote:
[color=blue][color=green]
>> 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.[/color]
>
>
>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).[/color]



Interesting theory... spammers sign up and pay "over the odds" money
just to then go ahead and abuse the host they've just paid a lot of
money to be on.

Remember, crackers aren't just script kiddies.. there are some really
talented ones about (no, I don't in any way condone cracking). They
wouldn't think twice about signing up with fake info probably through a
proxy if they really wanted to.

A current client of mine has had people sign up to his forum just to be
able to spam... admins also worry about local exploits, not just remote
ones. If we didn't have to worry about users, we wouldn't care about
local exploits.. unfortunately, you have to treat _everyone_ as a 'bad
guy(tm)' on the net.

There's also no room IMO, for "most vulnerable".. every form on your
site is as vulnerable as the next if not written securely.. just because
some may not have access to some of them.. doesn't mean the ones that do
don't want to have a poke about.. even out of curiosity if not
maliciously.. although sometimes this is a hard line to define.

Just my personal thoughts on this matter.



Regards,

Ian


--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[windandwaves]

Re: submitting text for use in a mysql database

Ian.H wrote:[color=blue]
> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
> <winandwaves@co ldmail.com> wrote:
>[color=green][color=darkred]
>>> 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.[/color]
>>
>>
>> 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).[/color]
>
>
>
> Interesting theory... spammers sign up and pay "over the odds" money
> just to then go ahead and abuse the host they've just paid a lot of
> money to be on.
>
> Remember, crackers aren't just script kiddies.. there are some really
> talented ones about (no, I don't in any way condone cracking). They
> wouldn't think twice about signing up with fake info probably through
> a proxy if they really wanted to.[/color]

There is absolutely no way you can sign up for these reasons as only
nenowned B&Bs in New Zealand can sign up and only after they have been
personally inspected.
[color=blue]
> A current client of mine has had people sign up to his forum just to
> be able to spam... admins also worry about local exploits, not just
> remote ones. If we didn't have to worry about users, we wouldn't care
> about local exploits.. unfortunately, you have to treat _everyone_ as
> a 'bad guy(tm)' on the net.[/color]

Not in this case.
[color=blue]
>
> There's also no room IMO, for "most vulnerable".. every form on your
> site is as vulnerable as the next if not written securely.. just
> because some may not have access to some of them.. doesn't mean the
> ones that do don't want to have a poke about.. even out of curiosity
> if not maliciously.. although sometimes this is a hard line to define.[/color]

There is no doubt in my mind that with my limited brainpower I can ever
write fully secure code, so I have to be practical about it. I think that
once we get some problems, we will start to be more serious it. So far, we
have had none.
[color=blue]
> Just my personal thoughts on this matter.
>
>
>
> Regards,
>
> Ian[/color]

[Ian.H]

Re: submitting text for use in a mysql database

On Sun, 3 Apr 2005 10:03:03 +1200, "windandwav es"
<winandwaves@co ldmail.com> wrote:
[color=blue]
>Ian.H wrote:[color=green]
>> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
>> <winandwaves@co ldmail.com> wrote:[/color][/color]


[ snip ]

[color=blue]
>I think that once we get some problems, we will start to be more serious it. So far, we
>have had none.
>[color=green]
>> Just my personal thoughts on this matter.[/color][/color]


I had planned on writing a full reply.. but with this sort of attitude,
I feel I'd be wasting my time.

Knowledge or otherwise, hoping something bad doesn't happen is _not_
security and just damn right scary!

If I decide to take up motor racing and had no idea about mechanics..
should I just hope that the brakes will stop me for the corner / other
cars? =)

Depending on the system also depends who gets hurt in the cross-fire..
while this may solely destroy your server if something bad did happen,
having a general attitude like the above when it comes to more "open
access", you'll probably end up writing code like the recent PHP-Nuke
webmail module which hurt millions of users all over the world.



Regards,

Ian

--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[Toby Inkster]

Re: submitting text for use in a mysql database

windandwaves wrote:
[color=blue]
> What else should i be doing?[/color]

Escaping single-quote marks.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

[windandwaves]

Re: submitting text for use in a mysql database

Ian.H wrote:[color=blue]
> On Sun, 3 Apr 2005 10:03:03 +1200, "windandwav es"
> <winandwaves@co ldmail.com> wrote:
>[color=green]
>> Ian.H wrote:[color=darkred]
>>> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
>>> <winandwaves@co ldmail.com> wrote:[/color][/color]
>
>
> [ snip ]
>
>[color=green]
>> I think that once we get some problems, we will start to be more
>> serious it. So far, we have had none.
>>[color=darkred]
>>> Just my personal thoughts on this matter.[/color][/color]
>
>
> I had planned on writing a full reply.. but with this sort of
> attitude, I feel I'd be wasting my time.
>
> Knowledge or otherwise, hoping something bad doesn't happen is _not_
> security and just damn right scary!
>
> If I decide to take up motor racing and had no idea about mechanics..
> should I just hope that the brakes will stop me for the corner / other
> cars? =)
>
> Depending on the system also depends who gets hurt in the cross-fire..
> while this may solely destroy your server if something bad did happen,
> having a general attitude like the above when it comes to more "open
> access", you'll probably end up writing code like the recent PHP-Nuke
> webmail module which hurt millions of users all over the world.
>
>
>
> Regards,
>
> Ian[/color]

Dear Ian

I think you fully misunderstand me. Just because top-security is not my
number one priority does not mean I am a bad person or stupid or what have
you. I do not usually lock my car and my house either and I have never had
a problem with that. This does not hurt anyone either.

Look at it like this. Microsoft invests billions of dollars in security,
yet they also have holes. All I do is that I admit that I have them and I
am also a realist that I will not be able to fix each one of them. All I
want to do is fix the biggest ones and slowly make it better over time. The
mere fact that I am asking for help on a newsgroup should give you some
indication that I take it serious. I know of 1000s of sites that take less
of an interest than I do.

You can't run before you can walk, so I want to learn to walk first, that is
all.

Besides, time, money and energy are limited resources, so you need to decide
where you use them. What I was saying is that I am not going to put all my
eggs in the security basket. I make good backups and if there is a problem I
nuke the whole site and load it from scratch.

Thank you for your help.

- Nicolaas

[windandwaves]

Re: submitting text for use in a mysql database

Toby Inkster wrote:[color=blue]
> windandwaves wrote:
>[color=green]
>> What else should i be doing?[/color]
>
> Escaping single-quote marks.[/color]

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

Thank you very much

- Nicolaas

[Zachary Kessin]

Re: submitting text for use in a mysql database

Toby Inkster <usenet200504@t obyinkster.co.u k> writes:
[color=blue]
> windandwaves wrote:
>[color=green]
> > What else should i be doing?[/color]
>
> Escaping single-quote marks.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> Contact Me ~ http://tobyinkster.co.uk/contact[/color]

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


--Zach

[Ian.H]

Re: submitting text for use in a mysql database

On Sun, 3 Apr 2005 16:44:31 +1200, "windandwav es"
<winandwaves@co ldmail.com> wrote:
[color=blue]
>Ian.H wrote:[color=green]
>> On Sun, 3 Apr 2005 10:03:03 +1200, "windandwav es"
>> <winandwaves@co ldmail.com> wrote:
>>[color=darkred]
>>> Ian.H wrote:
>>>> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
>>>> <winandwaves@co ldmail.com> wrote:[/color]
>>
>>
>> [ snip ]
>>
>>[color=darkred]
>>> I think that once we get some problems, we will start to be more
>>> serious it. So far, we have had none.
>>>
>>>> Just my personal thoughts on this matter.[/color]
>>
>>
>> I had planned on writing a full reply.. but with this sort of
>> attitude, I feel I'd be wasting my time.
>>
>> Knowledge or otherwise, hoping something bad doesn't happen is _not_
>> security and just damn right scary!
>>
>> If I decide to take up motor racing and had no idea about mechanics..
>> should I just hope that the brakes will stop me for the corner / other
>> cars? =)
>>
>> Depending on the system also depends who gets hurt in the cross-fire..
>> while this may solely destroy your server if something bad did happen,
>> having a general attitude like the above when it comes to more "open
>> access", you'll probably end up writing code like the recent PHP-Nuke
>> webmail module which hurt millions of users all over the world.
>>
>>
>>
>> Regards,
>>
>> Ian[/color]
>
>Dear Ian
>
>I think you fully misunderstand me. Just because top-security is not my
>number one priority does not mean I am a bad person or stupid or what have
>you. I do not usually lock my car and my house either and I have never had
>a problem with that. This does not hurt anyone either.[/color]


Well, that's already your biggest failure.. you don't care (security not
a top-priority!? so what _do_ you class as "top priority"?)

[color=blue]
>Look at it like this. Microsoft invests billions of dollars in security,
>yet they also have holes. All I do is that I admit that I have them and I
>am also a realist that I will not be able to fix each one of them.[/color]


Then take your code offline!

windoze should _NEVER_ be directly connected to the net either, for the
same reasons.. however, I'm wasting my time replying.

--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[windandwaves]

Re: submitting text for use in a mysql database

Ian.H wrote:[color=blue]
> On Sun, 3 Apr 2005 16:44:31 +1200, "windandwav es"
> <winandwaves@co ldmail.com> wrote:
>[color=green]
>> Ian.H wrote:[color=darkred]
>>> On Sun, 3 Apr 2005 10:03:03 +1200, "windandwav es"
>>> <winandwaves@co ldmail.com> wrote:
>>>
>>>> Ian.H wrote:
>>>>> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwav es"
>>>>> <winandwaves@co ldmail.com> wrote:
>>>
>>>
>>> [ snip ]
>>>
>>>
>>>> I think that once we get some problems, we will start to be more
>>>> serious it. So far, we have had none.
>>>>
>>>>> Just my personal thoughts on this matter.
>>>
>>>
>>> I had planned on writing a full reply.. but with this sort of
>>> attitude, I feel I'd be wasting my time.
>>>
>>> Knowledge or otherwise, hoping something bad doesn't happen is _not_
>>> security and just damn right scary!
>>>
>>> If I decide to take up motor racing and had no idea about
>>> mechanics.. should I just hope that the brakes will stop me for the
>>> corner / other cars? =)
>>>
>>> Depending on the system also depends who gets hurt in the
>>> cross-fire.. while this may solely destroy your server if something
>>> bad did happen, having a general attitude like the above when it
>>> comes to more "open access", you'll probably end up writing code
>>> like the recent PHP-Nuke webmail module which hurt millions of
>>> users all over the world.
>>>
>>>
>>>
>>> Regards,
>>>
>>> Ian[/color]
>>
>> Dear Ian
>>
>> I think you fully misunderstand me. Just because top-security is not
>> my number one priority does not mean I am a bad person or stupid or
>> what have you. I do not usually lock my car and my house either and
>> I have never had a problem with that. This does not hurt anyone
>> either.[/color]
>
>
> Well, that's already your biggest failure.. you don't care (security
> not a top-priority!? so what _do_ you class as "top priority"?)
>
>[color=green]
>> Look at it like this. Microsoft invests billions of dollars in
>> security, yet they also have holes. All I do is that I admit that I
>> have them and I am also a realist that I will not be able to fix
>> each one of them.[/color]
>
>
> Then take your code offline!
>
> windoze should _NEVER_ be directly connected to the net either, for
> the same reasons.. however, I'm wasting my time replying.[/color]

Thank you for your help.