SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Add value to a field

    Hi query-experts!

    I know mysql queries can be built to increment, add numbers to table fields like
    Code:
    UPDATE 'numbers' SET 'number' = +1 WHERE 'pageid' = '$pageid'
    BUT how should I build the query if I want a dynamic value to be added to a field????
    I tried
    Code:
    UPDATE points SET 'table_field' = +".$point_value." WHERE username = '$receiver'
    and another
    Code:
    UPDATE points SET 'table_field' = '+$point_value' WHERE username = '$receiver'
    nothing worked so far.... Is there an option for this at all?
    Thank you advanced.
    Regards

    Full time ADMIN - art community
    Part time coder - dsign

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it appears you were trying to update a string, and of course that's not possible ...
    Code:
    UPDATE points SET 'table_field' = ...
    try updating a column instead ...
    Code:
    UPDATE points SET table_field = ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it appears you were trying to update a string, and of course that's not possible ...
    Code:
    UPDATE points SET 'table_field' = ...
    try updating a column instead ...
    Code:
    UPDATE points SET table_field = ...
    Actually my query looks like :
    Code:
    UPDATE points SET $table_field = $table_field+$point_value WHERE username = '$receiver'
    and it WORKS now!!!! My mistake was
    Code:
    $table_field = $table_field+".$point_value."
    Thank you anyway!

    Full time ADMIN - art community
    Part time coder - dsign

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use back-ticks (I think that is what they're called) ,`, instead of single quotes to wrap table column names in.

    The ` is on the same key as ~ on my keyboard.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    you can use back-ticks (I think that is what they're called) ,`, instead of single quotes to wrap table column names in.
    yes, they're called backticks

    and yes, you can use them... but you shouldn't


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you can use them... but you shouldn't

    hmmmmm...interesting

    and yet someone else on another forum once told me we should in order to help prevent sql injection attacks.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sql injection? i don't see how... did he give an explanation?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it would have been about 12 months ago I guess and I can't remember even which website it was on.

    he/she probably did, but since I have my own measures to prevent sql injection I personally don't use backticks and so any explanation that might have been given is now lost from my memory.

    it is just something I recall reading in a thread somewhere some time ago.

  9. #9
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so if we shouldn't use them, does that mean that sql code is no more vulnerable to sql injection if there are no backticks in cases where there are no other measures to resist sql injection?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how is anything going to get injected into a column name?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmmm

    answering a question with another question suggests to me you don't know the answer to

    so if we shouldn't use them, does that mean that sql code is no more vulnerable to sql injection if there are no backticks in cases where there are no other measures to resist sql injection?
    either.

    I'm not a db expert, but if the column names are generated dynamically in php code, could the non use of backticks be a security issue?

    I don't use backticks for the reason I posted earlier so it isn't an issue for me, but perhaps you could explain to those that do use backticks why they shouldn't.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    ,,,if the column names are generated dynamically in php code, could the non use of backticks be a security issue?
    only if you are somehow feeding user input (entered in a form field or off a query string parameter) directly into a php variable which will substitute into a column name

    why would you want the user to input a column name?

    oh wait...

    rather than asking a rhetorical question this time, so that you don't make any conclusions about what i might know, i should have said it in terms of a statement ...

    you wouldn't want to let the user input a column name

    if your column is a variable, this means you sometimes want this column, sometimes that column, you don't know which column, it could be one of multiple columns... which is a huge red flag that you haven't designed the tables properly

    i'm thinking of examples like SELECT Total2009Amt, Total2010Amt, ...

    as i said, using backticks should be avoided

    if you are using a reserved word as a column name, or have a special character in your column name, like for some reason you wanted to call the column `acct#` or `acct no` instead of acctno, then you should simply rename the column and avoid the need for backticks altogether

    it has nothing to do with whether there can be sql injection into a column name -- there can, with or without backticks, if you programmed for it, so the backticks aren't the issue, but it would be dumb programming to make a column a variable

    backticks should be avoided because the sql is cleaner without them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    besides, i can attest from hanging around these forums for many years that we regularly get people posting problems like "oh noes, my query doesn't work, i wrote UPDATE 'mytable' ... and it says syntax error, halp!!!"

    so the more people posting sql with backticks (and especially phpmyadmin users, where the backticks seem to be the default setting, aaarg), the more likely newbies are to make this mistake
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    .......but it would be dumb programming to make a column a variable
    not necessarily. you could legitimately have a php function or class method with a single query in it and you pass to the function parameters that include the column names you want the query to use.

    the dumb part would be not validating and sanitising the column names and any other user inputs before passing them to the query.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    the dumb part would be not validating and sanitising the column names and any other user inputs before passing them to the query.
    quite true, except that in my experience any variability in function parameters should be accomplished by redesigning the database tables so that it is rows that you're filtering (with WHERE conditions) rather than columns (with variable column names)

    and the backticks still don't make a different to sql injection, and for clarity, should simply be avoided

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    quite true, except that in my experience any variability in function parameters should be accomplished by redesigning the database tables so that it is rows that you're filtering (with WHERE conditions) rather than columns (with variable column names)
    in my experience it depends on the purpose of the web application.

  17. #17
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Backticks have nothing to do with preventing sql injections. What you (and the poster in the thread you were reading about) was in escaping user inputted values. in PHP for instanced you'd use mysql_real_escape_string to do so.


Bookmarks

Posting Permissions

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