Add value to a field

Hi query-experts!

I know mysql queries can be built to increment, add numbers to table fields like

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

UPDATE points SET 'table_field' = +".$point_value." WHERE username = '$receiver'

and another

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.

it appears you were trying to update a string, and of course that’s not possible …

UPDATE points SET [COLOR="Red"]'table_field'[/COLOR] = ...

try updating a column instead …

UPDATE points SET [COLOR="blue"]table_field[/COLOR] = ...


hmmmmm…interesting :scratch:

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

Actually my query looks like :

UPDATE points SET [COLOR="Blue"]$table_field = $table_field+$point_value[/COLOR] WHERE username = '$receiver'

and it WORKS now!!! My mistake was

[COLOR="Red"]$table_field = $table_field+".$point_value."[/COLOR]

Thank you anyway! :slight_smile:

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.

yes, they’re called backticks

and yes, you can use them… but you shouldn’t


sql injection? i don’t see how… did he give an explanation?

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.

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?

how is anything going to get injected into a column name?

hmmmm :scratch:

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?


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.

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

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

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.

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


in my experience it depends on the purpose of the web application.

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.