#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0

    SQL Insert Into On duplicate - with variables for values


    What is wrong with this

    PHP Code:

    $sql_connect
    =mysqli_query($link,"INSERT INTO testtable5 (ITEMNO,".$value.") VALUES ($ITEMNO,'parallax')ON DUPLICATE KEY UPDATE 

    ITEMNO = values(ITEMNO),
     "
    .$value." = values(".$value.")"
    This much works fine

    PHP Code:
    $sql_connect=mysqli_query($link,"INSERT INTO testtable5 (ITEMNO,".$value.") VALUES ($ITEMNO,'paralax')"
    Add the rest on, change that random 'Parallax' value to some other word and it doesnt update anymore. I though my variable insertion was right in the on duplicate, and the variables function and have data, so...
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,094
    Rep Power
    1990
    Your SQL in the UPDATE part is pretty wrong. It should be something like this:

    Code:
    $sql_connect = mysqli_query ($link, "INSERT INTO testtable5 (ITEMNO, ".$value.") VALUES (".$ITEMNO.",'parallax')ON DUPLICATE KEY UPDATE ".$value." = 'parallax' WHERE ITEMNO = ".$ITEMNO."");
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Nope, WHERE is not allowed in ON DUPLICATE KEY. You already have the "where" bit, it's the row causing the key conflict.

    Your SQL query should just be simply:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'parallax') 
    ON DUPLICATE KEY UPDATE 
    {$value} = 'parallax'" 
    This assumes, of course, that you have a PRIMARY or UNIQUE key on the field ITEMNO.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0
    Originally Posted by ManiacDan
    Nope, WHERE is not allowed in ON DUPLICATE KEY. You already have the "where" bit, it's the row causing the key conflict.

    Your SQL query should just be simply:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'parallax') 
    ON DUPLICATE KEY UPDATE 
    {$value} = 'parallax'" 
    This assumes, of course, that you have a PRIMARY or UNIQUE key on the field ITEMNO.

    So If I wanted to use a variable instead of 'parallax' it would just be

    PHP Code:

    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},{&variable})  
    ON DUPLICATE KEY UPDATE 
    {$value} = {$variable}

    Also, and forgive the basic nature of this, but why are you using "{$value}" Instead of .$value. ? This code, for example, works with the "."

    PHP Code:
    $sql_connect=mysqli_query($link,"INSERT INTO testtable5 (ITEMNO,".$value.") VALUES ($ITEMNO,'paralax')"
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    If you want to use a variable in place of parallax it would be:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'{$theValue}')  
    ON DUPLICATE KEY UPDATE 
    {$value} = '{$theValue}'" 
    You had an ampersand instead of a dollar sign. Also, this is wide open for security holes and assumes all these values are strings, but presumably you know that and will handle it outside of this line.

    As for the string interpolation question, I'm using complex string interpolation syntax, the recommended way of building strings. It stops all that concatenation nonsense you're doing and makes the string easier to read and understand. It also reduces the chance of you forgetting to add quotes, since you're no longer ending/beginning quotes multiple times throughout your strings.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0
    Originally Posted by ManiacDan
    If you want to use a variable in place of parallax it would be:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'{$theValue}')  
    ON DUPLICATE KEY UPDATE 
    {$value} = '{$theValue}'" 
    You had an ampersand instead of a dollar sign. Also, this is wide open for security holes and assumes all these values are strings, but presumably you know that and will handle it outside of this line.

    As for the string interpolation question, I'm using complex string interpolation syntax, the recommended way of building strings. It stops all that concatenation nonsense you're doing and makes the string easier to read and understand. It also reduces the chance of you forgetting to add quotes, since you're no longer ending/beginning quotes multiple times throughout your strings.
    Thank you very much for your comment. I did in fact already read that link you posted, but I am struggling as a new learner. What security issues doe that create and how can I stop that?
  12. #7
  13. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    You're vulnerable to SQL injection with this method. You're expecting the user to put a valid value into this field, but they could put SQL code into this value. Your code doesn't do anything to protect against that.

    The two ways to protect against this are:
    1) mysqli_real_escape_string. Run this on strings before they're included in a query to protect against basic injection. Not 100% effective, and makes the code a bit sloppy.

    2) Using PDO and bound parameters. A bit awkward for your particular type of query since values are repeated, and also requires a rewrite of your querying entirely. However, this is the right choice and you should read the PDO tutorial in the stickies.

    Your query could be rewritten a bit more to:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'{$theValue}')   
    ON DUPLICATE KEY UPDATE 
    {$value} = VALUES( {$value} )" 
    This removes some of the problem with bound params. Note also that you can't bind the column names, they have to remain variables.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0
    Originally Posted by ManiacDan
    If you want to use a variable in place of parallax it would be:
    PHP Code:
    "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'{$theValue}')  
    ON DUPLICATE KEY UPDATE 
    {$value} = '{$theValue}'" 
    You had an ampersand instead of a dollar sign. Also, this is wide open for security holes and assumes all these values are strings, but presumably you know that and will handle it outside of this line.

    As for the string interpolation question, I'm using complex string interpolation syntax, the recommended way of building strings. It stops all that concatenation nonsense you're doing and makes the string easier to read and understand. It also reduces the chance of you forgetting to add quotes, since you're no longer ending/beginning quotes multiple times throughout your strings.

    I noticed this breaks without the single quotes around the '{$thevalue}'...not sure why
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Originally Posted by misterguns
    I noticed this breaks without the single quotes around the '{$thevalue}'...not sure why
    Because strings need to be quoted, that's why I said:
    ...and assumes all these values are strings, but presumably you know that and will handle it outside of this line.
    I put quotes around everything because (a) you had quotes in your original string and (b) I don't know what your data model is so I can't make assumptions. Unless your value is purely a number (no commas or anything, just digits and a decimal place or negative sign), it needs quotes around it.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0
    Thanks a ton, man, you have been much help.

IMN logo majestic logo threadwatch logo seochat tools logo