Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts

    Question SQL query problem

    This is driving me crazy and its probably something stupid...I have the following lines of code:

    PHP Code:
        $query 'UPDATE '$_SESSION['username']. ' SET price='$_POST['price']. ', description='$_POST['description']. ' WHERE ref_no='$index_no2;
        
    $result mysql_query($query);
        if (
    mysql_errno())
        {
            echo 
    $query;
            die(
    "<br>" mysql_errno() . ": " mysql_error() . "<br>");
        } 
    A MySQL error occurs, so I get the following text/error message:

    UPDATE username_table SET price=191.53, description=LCD 15\'\' Monitor WHERE ref_no=2
    1064: You have an error in your SQL syntax near '15\'\' Monitor WHERE ref_no=2' at line 1
    what is the error in the query? My guess is something is going on with the quotes inside the query, like they are not being escaped....but the slashes indicate they are escaped, right?
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I find writing it like this is more readable(using double quotes and remember to take the single quotes out of the array keys):
    Code:
    $query = "UPDATE $_SESSION[username] SET price='$_POST[price]', description='$_POST[description]' WHERE ref_no='$index_no2'";
    the error is you need single quotes around the string. the string may have been escaped but you didn't encapsulate the entire string with single quotes.
    this is probably how you want it to look:
    Code:
     UPDATE username_table SET price='191.53', description='LCD 15\'\'' Monitor WHERE ref_no='2'
    I tend to put single quotes around everything especially if accepting user data, what if the value is an empty string? then you'd get a query error (unless you cast it then it'd be zero, but whatever).
    Last edited by fci; 02-16-2006 at 01:36 AM.

  • #3
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    thanks, i knew it was something simple...
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #4
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You should never, ever drop $_POST vars directly into a db query. Always sanitize and validate your data.

  • #5
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Kid Charming
    You should never, ever drop $_POST vars directly into a db query. Always sanitize and validate your data.
    I figured that's what he was doing, lazy way for anyone(not recursive):
    Code:
    $_POST = array_map('mysql_real_escape_string', $_POST);
    although you should have a library to abstract all of this so you never have to call the ugly mysql_real_escape_string function.

  • #6
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    These were hidden variables in the POST...not user entered...no need to validate them...

    Hence,
    You should never, ever drop $_POST vars directly into a db query.
    That is not always true.
    Last edited by chump2877; 02-16-2006 at 04:02 AM.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #7
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    It's easy to fake any input, including hidden variables.

    For example, a user with evil intent could simply save your form site to his hard drive, change the action attribute to an absolute address to your site, and replace all inputs of type "hidden" to "text". Then he can send you modified data easily.

    dumpfi

  • #8
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dumpfi
    It's easy to fake any input, including hidden variables.

    For example, a user with evil intent could simply save your form site to his hard drive, change the action attribute to an absolute address to your site, and replace all inputs of type "hidden" to "text". Then he can send you modified data easily.

    dumpfi
    I like the Web Developer extension.
    Right-click on page-> Web Developer-> Forms -> Display Form Details
    then it's possible to edit the hidden inputs (they're converted to text). I'm sure a simple bookmarklet could achieve the same affect.

  • #9
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    For example, a user with evil intent could simply save your form site to his hard drive, change the action attribute to an absolute address to your site, and replace all inputs of type "hidden" to "text". Then he can send you modified data easily.
    that is very true....in the case of my script, if someone were to do that -- modify the hidden variables -- the application would simply fail for that person....There would be no security risk involved... the db table receiving the POST information is temporary, unique to each user, and does not contain any valuable data....The only thing someone might accomplish is to send me a whole lot of form generated spam, maybe fill up my database, and use up bandwidth....but you run that risk with any HTML form (and there are ways to prevent this)...
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #10
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it's about best practices. i don't agree with your rationalization and a temp table for a user.. that sounds like bad design (although I don't know enough about your situation there has to be a better way).

  • #11
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    it's about best practices. i don't agree with your rationalization and a temp table for a user.. that sounds like bad design (although I don't know enough about your situation there has to be a better way).
    Then it's a bad design that works flawlessly for me....

    Please also understand that I don;t design every app using the same methods...this app called for a different approach...normally I wouldn;t use hidden variables if the data was delicate...In fact, normally I wouldn;t use hidden variables at all because they are impractical and mostly inefficient...

    Anyway, shall we agree to disagree? I wouldn;t have asked my original question if I had thought it would spark such controversy....
    Last edited by chump2877; 02-16-2006 at 06:46 PM.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #12
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by fci
    although you should have a library to abstract all of this so you never have to call the ugly mysql_real_escape_string function.
    Hehe I've been using the quote_smart()
    PHP Code:
    // Quote variable to make safe
    function quote_smart($value)
    {
       
    // Stripslashes
       
    if (get_magic_quotes_gpc()) {
           
    $value stripslashes($value);
       }
       
    // Quote if not integer
       
    if (!is_numeric($value)) {
           
    $value "'" mysql_real_escape_string($value) . "'";
       }
       return 
    $value;

    How do you feel about that?

    And I tried not to comment but I have to say that I think its always best not to be complacent and go ahead and protect against rogue user actions. Regardless of how minute the consequences may seem. Am I wrong in saying that a person could append another SQL statement adding themself as a user of your DB? That little problem gets a lot bigger once tables are dropped and DB servers shutdown. I guarantee the evil intent is much more than what you underestimate. Okay thats my soapbox for today. I won't go overboard anymore.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #13
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    Am I wrong in saying that a person could append another SQL statement adding themself as a user of your DB? That little problem gets a lot bigger once tables are dropped and DB servers shutdown.
    I'd like to know how that is possible if the code is server side (if at all)....DB usernames, passwords, db names, table names, etc., are unaccessable, unless you think i'm passing this kind of vital data as hidden variables in my form...which i'm not
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #14
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Yes sir I did believe you were passing the variables via your form.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #15
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by chump2877
    I'd like to know how that is possible if the code is server side (if at all)....DB usernames, passwords, db names, table names, etc., are unaccessable, unless you think i'm passing this kind of vital data as hidden variables in my form...which i'm not
    I'm pretty sure the mysql functions in php won't allow it, although there are other db functions that do allow it (basically just placing a semicolon within the call to run multiple queries at once). if you google for mysql injection, you can see various examples of it.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •