Unknown Column " " in field list - Update Query

Question

This query had previously worked, now when it is run again we get Unknown Column in field list error.

The query works well if we do not use variables and set raw data. The columns match those in the database.

$update_order_id = "UPDATE order_tbl SET o_process=$process, o_payment=$payment, o_paymentType=$paymenttype WHERE o_id=$orderid AND o_active='1'";

This looks suspiciously like you are using unsafe insertion methods. Please read [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and begin learning how to use prepared statements in an API that supports them (PDO or MySQLi) which avoids these problems entirely. — Michael Berkowski, Mar 16 '14 at 20:40
And to understand how the SQL query is improperly built, read [When to use single quotes, double quotes, and backticks](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Michael Berkowski, Mar 16 '14 at 20:41
@MichaelBerkowski for simplicity I had omitted those from the question, since I thought that was a given. The necessary steps have been taken to prevent sql injections. We had found out that the reason why we were having trouble was because we had to use mysqli_affected_rows rather than num_rows. That seemed to have worked. But thank you for pointing out that statements need to be escaped to prevent SQL injections. (No sarcasm intended). We had tried a number of ways before coming over, but since it has now worked, thank you to everyone who helped us out :) — StrangeLondoner, Mar 19 '14 at 10:05
We'll always point it out, because a seemingly big majority of PHP questions have clear and direct vulnerabilities (like `$_GET` straight in the SQL). So many askers have never heard of SQL injection and are also unaware of the `mysql_*()` extension's deprecation. Advice about prepared statements over vars in the SQL string stands though - it circumvents quoting problems on variables regarless of whether these are string or int types. — Michael Berkowski, Mar 19 '14 at 11:35

score 1 · Accepted Answer · answered Mar 16 '14 at 20:38

1

You need wrap single quotes for the values in the query as

o_process='$process'

etc

So the query will be as below. For string values its necessary.

$update_order_id = "UPDATE order_tbl 
SET o_process='$process',
o_payment='$payment', 
o_paymentType='$paymenttype'
WHERE o_id= '$orderid' AND o_active='1'";

answered Mar 16 '14 at 20:38

Abhik Chakraborty

44,654
6
52
63

Yes corrected that no harm adding quotes for int also. – Abhik Chakraborty Mar 16 '14 at 20:41

score 1 · Answer 2 · answered Mar 16 '14 at 20:38

You might need to surround your variables with quotes, only integer columns doesn't need quotes.

$update_order_id = "UPDATE order_tbl SET o_process='$process', o_payment='$payment', o_paymentType='$paymenttype' WHERE o_id='$orderid' AND o_active='1'";

Unknown Column " " in field list - Update Query

2 Answers2