471,336 Members | 1,293 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,336 software developers and data experts.

submitting text for use in a mysql database

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

Jul 17 '05 #1
14 1836
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/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.

Jul 17 '05 #2
windandwaves wrote:
<INPUT VALUE=$Vold NAME="x">
<TEXTAREA>$Vold</TEXTAREA NAME="y">


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

?

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

Jul 17 '05 #3
ZeldorBlat wrote:
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/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.


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.
Jul 17 '05 #4
On Sat, 2 Apr 2005 08:38:55 +1200, windandwaves wrote:
ZeldorBlat wrote:
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.


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.

--
Greg Schmidt gr***@trawna.com
Trawna Publications http://www.trawna.com/
Jul 17 '05 #5
Greg Schmidt wrote:
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?

Jul 17 '05 #6
On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
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).


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/
Jul 17 '05 #7
Ian.H wrote:
On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
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).


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.


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.
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.
Not in this case.

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.
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.
Just my personal thoughts on this matter.

Regards,

Ian

Jul 17 '05 #8
On Sun, 3 Apr 2005 10:03:03 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
Ian.H wrote:
On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
<wi*********@coldmail.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

--
Ian.H
digiServ Network
London, UK
http://digiserv.net/
Jul 17 '05 #9
windandwaves wrote:
What else should i be doing?


Escaping single-quote marks.

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

Jul 17 '05 #10
Ian.H wrote:
On Sun, 3 Apr 2005 10:03:03 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
Ian.H wrote:
On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
<wi*********@coldmail.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


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

Jul 17 '05 #11
Toby Inkster wrote:
windandwaves wrote:
What else should i be doing?


Escaping single-quote marks.


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

Thank you very much

- Nicolaas

Jul 17 '05 #12
Toby Inkster <us**********@tobyinkster.co.uk> writes:
windandwaves wrote:
What else should i be doing?


Escaping single-quote marks.

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


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
Jul 17 '05 #13
On Sun, 3 Apr 2005 16:44:31 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
Ian.H wrote:
On Sun, 3 Apr 2005 10:03:03 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
Ian.H wrote:
On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
<wi*********@coldmail.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


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.

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

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.

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/
Jul 17 '05 #14
Ian.H wrote:
On Sun, 3 Apr 2005 16:44:31 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:
Ian.H wrote:
On Sun, 3 Apr 2005 10:03:03 +1200, "windandwaves"
<wi*********@coldmail.com> wrote:

Ian.H wrote:
> On Sun, 3 Apr 2005 09:10:29 +1200, "windandwaves"
> <wi*********@coldmail.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


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.

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

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.

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.


Thank you for your help.
Jul 17 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Kevin Chambers | last post: by
2 posts views Thread by mike2098 | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.