submitting text for use in a mysql database

Discussion in 'HTML' started by windandwaves, Apr 1, 2005.

  1. windandwaves

    windandwaves Guest

    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
     
    windandwaves, Apr 1, 2005
    #1
    1. Advertising

  2. windandwaves

    ZeldorBlat Guest

    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.
     
    ZeldorBlat, Apr 1, 2005
    #2
    1. Advertising

  3. windandwaves

    Toby Inkster Guest

    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
     
    Toby Inkster, Apr 1, 2005
    #3
  4. windandwaves

    windandwaves Guest

    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/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.
     
    windandwaves, Apr 1, 2005
    #4
  5. windandwaves

    Greg Schmidt Guest

    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
    Trawna Publications http://www.trawna.com/
     
    Greg Schmidt, Apr 2, 2005
    #5
  6. windandwaves

    windandwaves Guest

    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?
     
    windandwaves, Apr 2, 2005
    #6
  7. windandwaves

    Toby Inkster Guest

    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
     
    Toby Inkster, Apr 3, 2005
    #7
  8. windandwaves

    windandwaves Guest

    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
     
    windandwaves, Apr 3, 2005
    #8
  9. Toby Inkster <> 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
     
    Zachary Kessin, Apr 3, 2005
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chipmunk
    Replies:
    2
    Views:
    322
    Eric Lawrence [MSFT]
    Feb 23, 2004
  2. Alex
    Replies:
    5
    Views:
    1,640
    Jonathan N. Little
    Dec 28, 2006
  3. Ben

    Submitting Form to Database

    Ben, Nov 26, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    266
    Peter Bromberg [C# MVP]
    Nov 27, 2007
  4. Chipmunk
    Replies:
    2
    Views:
    115
    Eric Lawrence [MSFT]
    Feb 23, 2004
  5. julian
    Replies:
    8
    Views:
    489
    Avatar
    Apr 6, 2006
Loading...

Share This Page