PHP doesn't like this code...

Discussion in 'Web Design and Development' started by jlewis2k1, Dec 16, 2008.

  1. jlewis2k1 macrumors 6502a

    jlewis2k1

    Joined:
    Jan 14, 2005
    Location:
    in your closet
    #1
    I have gone to number of websites and I see the same example and for whatever reason its a bad code...
    PHP:
        mysql_query("INSERT INTO Information_Center (t_id, title, date, body, author, to) VALUES ('300', '$title', '$date', '$body', '$uid', '$to')") or die("There seems to be a problem with sending your message. Here is the error that was received:<br><BR>\n\n ".mysql_error()."");
    this is the error:
     
  2. Nugget macrumors 65816

    Nugget

    Joined:
    Nov 24, 2002
    Location:
    Houston Texas USA
    #2
    If any of those fields like t_id or uid are integer values then you shouldn't be escaping them with ticks (') in the SQL statement. You'd have to do something like:

    INSERT INTO Information_Center (t_id, title) VALUES (300, 'test');

    Also, make damn sure that you've cleaned up those variables with some sort of data hygiene function like mysql_real_escape_string to avoid any risk of sql injection attacks. Depending on where you're getting those values for $title, $body and the rest you might have problems. Imagine someone supplying this book title to your web form:

    BOOK'; DROP TABLE Information_Center; --

    It doesn't look like php's mysql functions support bound queries (I'm a postgresql guy, myself) which is how I'd expect to do this with a good database. :) Failing that, you want to clean up those variables to properly escape any special characters like ' or ; so that they aren't interpreted as part of the SQL command.
     
  3. jlewis2k1 thread starter macrumors 6502a

    jlewis2k1

    Joined:
    Jan 14, 2005
    Location:
    in your closet
    #3
  4. Nugget macrumors 65816

    Nugget

    Joined:
    Nov 24, 2002
    Location:
    Houston Texas USA
    #4
    You took out ALL the quotes, or just the ones for any integer or numeric fields?

    That URL is bad. "myadvtge.com" doesn't exist.
     
  5. jlewis2k1 thread starter macrumors 6502a

    jlewis2k1

    Joined:
    Jan 14, 2005
    Location:
    in your closet
  6. Knox Administrator

    Knox

    Staff Member

    Joined:
    Jul 1, 2002
    Location:
    UK
    #6
    'to' is a reserved word and so you can't use it in column names without using backticks:

    Code:
    INSERT INTO Information_Center (`t_id`, `title`, `date`, `body`, `author`, `to`) VALUES (300, '$title', $date, '$body', $uid, '$to')
    
    (Technically you would only need them on the 'to' column but I would do them all for consistency)
     
  7. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #7
    Do you happen to know off hand what the 'to' keyword is for? I'm drawing a blank and that's very hard to Google for. I can only think of 'into.' Good catch by the way.
     
  8. Knox Administrator

    Knox

    Staff Member

    Joined:
    Jul 1, 2002
    Location:
    UK
    #8
    I did wonder that myself - I was just guessing that it would be reserved based on what point it complained about the query :eek:

    Looking through the manual there are a few ALTER statements that can/do use TO, but not sure if there are any others.
     
  9. savar macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #9
    You can put the numbers in quotes. Mysql is smart enough to figure that out.
     
  10. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #10
    Since contradicting advice was offered in this topic I created a table in my phpMyAdmin. The following is true based on real queries and testing via MySQL 5.1

    If you use any reserved keyword as a table name, it must be wrapped in backticks within any query - but creating any table with reserved keywords is not a good practice (hence topics like this, plus general confusion)

    The "To" keyword is certainly reserved, as documented here.

    You can insert numbers as strings (wrapped in a quote) into any integer field, just as savar noted.

    Nugget hit it dead on about SQL injection protection, fantastic advice. Also, it's strongly recommended that register auto globals be disabled in the PHP ini file. As of later versions of php, register globals is turned off by default, a sensible setting for improved security.

    -jim
     
  11. jlewis2k1 thread starter macrumors 6502a

    jlewis2k1

    Joined:
    Jan 14, 2005
    Location:
    in your closet
    #12
    LOL I liked that cartoon. Thanks for all of your help everyone. I am now moving on to new parts of the site that I am developing. My deadline is Jan 4th but I have like MAYBE 40% of it done.
     
  12. InfoTech11 macrumors member

    Joined:
    Nov 18, 2008
  13. primes macrumors newbie

    Joined:
    Jun 4, 2008
    #14
    Refer to "MySQL In a Nutshell/O'Reilly" under "TO" in index, it is used in the following commands:

    ALTER TABLE
    BACKUP TABLE
    PURGE MASTER LOGS
    RENAME TABLE
     
  14. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #15
    That is incorrect. If the field is an integer you do not need to use single or double quotes, not even back ticks for the value. You can and should as part of SQL injection best practices, but it is not required. I tested this in MySQL to be sure.

    -jim
     

Share This Page