0

Possible Duplicate:
Can a number used to name a sql column

I am trying to figure out what is wrong with this code

$query = "UPDATE $table SET '$_GET[qty]'=$_GET[newprice] WHERE 'id'='1'";

this is what $query looks like - UPDATE retail_12x18 SET '25'=100 WHERE 'id'='1'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''25'=100 WHERE 'id'='1'' at line 1

I have put backticks ' every which way and cant get it to go through, always the same error message.

enter image description here

Community
  • 1
  • 1
Daniel Hunter
  • 2,546
  • 6
  • 28
  • 34
  • 2
    Please show the error message. Also, this code has massive SQL injection vulnerabilities - http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain. – El Yobo Nov 01 '11 at 22:36
  • What happens when you run it ? mysql or php issue ? – gregory Nov 01 '11 at 22:37
  • this is the query echo - UPDATE retail_12x18 SET 25 = 100 WHERE type=1 color / 1 side You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '25 = 100 WHERE type=1 color / 1 side' at line 1 – Daniel Hunter Nov 01 '11 at 22:40
  • I have included a picture of the database structure.. Also, i am using a for each function to escape the $_get variables – Daniel Hunter Nov 01 '11 at 22:44
  • This is the live link http://preferweb.com/accentps/lib/priceset.php?size=12x18&type=1+color+%2F+1+side&qty=25&newprice=100&change=true – Daniel Hunter Nov 01 '11 at 22:46
  • Your subsequent question is much better, closing this in favor of that. In the future, please just edit your existing question, you can then leave comments under the existing answers to let people know that you clarified the problem better. – Tim Post Nov 02 '11 at 03:23

2 Answers2

1

use backtick around your field name:

UPDATE table SET `25` = '{thevalue}', `100` = '{thevalue}', `200` = '{thevalue}' WHERE wherefield = '{wherevalue}'

See here (look for backtick word): http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

ariefbayu
  • 21,849
  • 12
  • 71
  • 92
  • I believe the problem lies with the lack of `` in the query. Without `` it will treat 25 (as seen in OP's link) as a number rather than a row if I am not mistaken. – MrE Nov 01 '11 at 22:50
  • Correct, only if the field name is a string can the backtick omitted. – Melsi Nov 01 '11 at 22:51
0

It's a bit hard to know for sure, without seeing the table definition, but:

[1] It might be the column types. For instance this bit:

type=" .$_GET['type'];

is trying to set the value of the "type" column without using quotes. It will fail if the "type" column is type like varchar, for example.

[2] You need to use backtics if you're going to have numeric column names

[3] It really must be said that the main thing that's wrong with your code is that you are putting un-escaped $_GET values into your SQL query. Anyone could mount an SQL injection attack by putting SQL into the URL of the page. Very bad practice.

http://en.wikipedia.org/wiki/SQL_injection

k7n4n5t3w4rt
  • 300
  • 1
  • 7