1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

PHP MySQL issues

Discussion in 'PHP & Perl' started by thelock, Jun 2, 2010.

  1. thelock

    thelock Newbie

    Joined:
    May 14, 2010
    Messages:
    9
    Likes Received:
    1
    Location:
    Durham, UK
    Not sure what is going on with my code but for some reason I can't get the database to update (using code that has worked on MySQL 4 now running on MySQL 5).

    PHP:
    mysql_query("update products set desc='$desc' where id=$ref;")
    I'm sure I am just missing something simple but any pointers would be great. I have tried using the code that is on tizag (going back to basics) but still get the error:

    Cheers
     
  2. kaidoristm

    kaidoristm Power Member

    Joined:
    Feb 13, 2009
    Messages:
    561
    Likes Received:
    726
    Occupation:
    Freelancer
    Location:
    Estonia
    Home Page:
    Try this

    PHP:
    mysql_query("UPDATE products SET desc='$desc' WHERE id='$ref'");
     
    • Thanks Thanks x 1
  3. Killswitch

    Killswitch BANNED BANNED

    Joined:
    Aug 9, 2009
    Messages:
    14
    Likes Received:
    5
    PHP:
    mysql_query("UPDATE products SET desc='".$desc."' WHERE id=".$ref);
    Never just put your variable in the query, or anything for that matter, always have ".$var." or if you don't use quotes and just use apostrophes, always put '.$var.'
     
    • Thanks Thanks x 1
  4. thelock

    thelock Newbie

    Joined:
    May 14, 2010
    Messages:
    9
    Likes Received:
    1
    Location:
    Durham, UK
    Thanks guys; I feel like such a dunce.

    After throwing my computer out the window I have realised that the reason that it wasn't updating the mysql table was because I had a field called 'desc' which is obviously a MySQL command and can't be a field name. DOH!!

    Cheers to kaidoristm and killswitch
     
  5. Killswitch

    Killswitch BANNED BANNED

    Joined:
    Aug 9, 2009
    Messages:
    14
    Likes Received:
    5
    Yeah, I always make my field names part of the table, say I have articles table, then the description field would be article_desc.
     
  6. voyevoda

    voyevoda Regular Member Premium Member

    Joined:
    Mar 21, 2010
    Messages:
    217
    Likes Received:
    97
    Location:
    Eastern Front
    This is the exact same query he's trying to run, but you forgot a semicolon, so it wouldn't work anyways. Case doesn't matter in SQL keywords and function names (e.g., MIN(), MAX(), GROUP BY, etc.).

    There is nothing wrong with interpolation. It's generally more readable. Why are you recommending against it? Your concatenation approach results in the exact same (REALLY BAD) SQL query. Either way allows arbitrary SQL code to be injected (like "-- DROP DATABASE `whatever`;").

    What you really want to be using is something like this:

    PHP:
    function mysql_safe_query($query$params=false) { 
        if (
    $params) { 
            foreach (
    $params as &$v) { $v mysql_real_escape_string($v); }
            
    $sql_query vsprintfstr_replace("?","'%s'",$query), $params );    
            
    $sql_query mysql_query($sql_query);
        } else { 
            
    $sql_query mysql_query($query);
        } 

        return (
    $sql_query); 
    }
    And you would write SQL queries like this:

    PHP:
    mysql_safe_query("UPDATE products SET desc='?' WHERE id=?;"$description$product_id);
    This method properly sanitizes all input that is being used to dynamically generate the SQL query.

    Christ, PHP makes me sad.

    This suggestion brought to you by the Department of Redundancy Department. How about something that's actually descriptive, like... 'description'? I'm revoking your advice giving privileges until further notice.
     
    • Thanks Thanks x 2
    Last edited: Jun 3, 2010
  7. sipser

    sipser Newbie

    Joined:
    Feb 4, 2008
    Messages:
    43
    Likes Received:
    18
    If you run into this in the future and don't want to rename your column, enclose it into ``'s. Example:

    PHP:
     mysql_query("UPDATE products SET `desc`='value' WHERE `id`='value");  
     
  8. Killswitch

    Killswitch BANNED BANNED

    Joined:
    Aug 9, 2009
    Messages:
    14
    Likes Received:
    5
    I was just assuming he had already sanitized the variables, I was here to help him fix his query, not secure his code for him :p

    Because I just find it easier doing it that way, neither way are wrong.