SQL Update query using variables

Question

I am writing php to update a user's balance, but my UPDATE query seems to be throwing an error when it is executed.

    $student = $database->quote($_POST ["studentID"]);
    $amount = $database->quote($_POST ["update_balance"]);

    //sets query to update user balance
    $query = "UPDATE `User` SET `balance`= (`.$amount.`) WHERE `userID`= (`.$student.`)";
    //excecutes the query
    $database->exec($query);

The 'studentID' and 'update_balance' are names of input fields being captured in the HTML.

Possible duplicate of [Reference - What does this symbol mean in PHP?](http://stackoverflow.com/questions/3737139/reference-what-does-this-symbol-mean-in-php) — Chay22, Jul 10 '16 at 10:07

score 1 · Answer 1 · edited Jul 10 '16 at 10:14

1

remove (`. things . and run sql query

$query = "UPDATE `User` SET `balance`= '$amount' WHERE `userID`= '$student'";

edited Jul 10 '16 at 10:14

Jalali

596
4
19

answered Jul 10 '16 at 10:04

Aniruddha Chakraborty

1,849
1
20
32

This fixes the issue of it the error being thrown, however it is updating the balance from 'NULL' to 0. The type is an int and the input tag has a type of "number". Could it possibly be passing the $amount through as a string and converting that to 0? – Georgie Lyme Jul 10 '16 at 10:13
Apologies, i was referencing the wrong field in $amount. Should really check better before i ask silly questions. – Georgie Lyme Jul 10 '16 at 10:15
your problem solved or no? if no echo your query and paste it here – Jalali Jul 10 '16 at 10:16
@GeorgieLyme would you pleae mark this as a correct answer? :) – Aniruddha Chakraborty Jul 10 '16 at 11:31

score 0 · Answer 2 · answered Jul 10 '16 at 10:20

You should use prepared statements as it's considered much safer than any string escaping mechanism:

$statement = $somePdoInstance->prepare("UPDATE user SET balance = :balance WHERE userId = :user_id");
$statement->execute(array(
   "balance" => $amount, // the values from POST
   "user_id" => $student
));

Now your update query should work fine and it's much safer.

SQL Update query using variables

2 Answers2