How to write an update query to subtract a certain value?

Question

Is this right way to perform the subtraction operation in case of prepared statement in the sql query ?

$sql = "UPDATE users set credits = (credits-$price) WHERE username = ?";

Code to subtracted the user credits based on the value of $price

$price = $row0['price'];
    
    $sql = "UPDATE users set credits = (credits-$price) WHERE username = ?;";
        $stmt1 = mysqli_stmt_init($conn);
        if(!mysqli_stmt_prepare($stmt1, $sql)) {
                $db_err = array("error" => "Database");
                echo json_encode($db_err);
            } else {
                mysqli_stmt_bind_param($stmt1, "s", $_SESSION['username']);
                mysqli_stmt_execute($stmt1);

No, this is not the right way in the case of prepared statements, because you are concatenating a value into the query. — GrumpyCrouton, Jul 10 '20 at 11:36

score 1 · Accepted Answer · edited Feb 05 '23 at 08:53

1

You need to use a placeholder for your $price variable to properly use prepared statements. Concatenating the value is never safe unless you are able to compare the value with a list of possible values.

$sql = "UPDATE users set credits = (credits - ?) WHERE username = ?;";

mysqli_stmt_prepare($stmt1, $sql);
mysqli_stmt_bind_param($stmt1, "ss", $price, $_SESSION['username']);
mysqli_stmt_execute($stmt1);

Note that it's better to use object syntax for many reasons. Here is how you do that:

$stmt1 = $mysqli->prepare("UPDATE users set credits = (credits - ?) WHERE username = ?");
$stmt1->bind_param("ss", $price, $_SESSION['username']);
$stmt1->execute();

edited Feb 05 '23 at 08:53

Your Common Sense

156,878
40
214
345

answered Jul 10 '20 at 11:39

GrumpyCrouton

8,486
7
32
71

Please note that procedural interface is inferior to object for [many reasons](https://phpdelusions.net/mysqli/mysqli_connect#procedural) and it's better to use the latter providing an example. – Your Common Sense Jul 10 '20 at 11:46
@YourCommonSense Can you double check my answer addition please. I'm not very familiar with mysqli, I almost exclusively use PDO. – GrumpyCrouton Jul 10 '20 at 11:53
is there any difference between i and s cant it be ss – Jskccc Jul 10 '20 at 11:54
actually the value of price is in decimal – Jskccc Jul 10 '20 at 11:57
@Jskccc you can safely use s all the time – Your Common Sense Jul 10 '20 at 12:05
1

Using d for for the exact-type values will lead to obvious errors. – Your Common Sense Jul 10 '20 at 12:10
@Your Common Sense But price is not string The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string. I am confused here. The argument may be one of four types: i - integer d - double s - string b - BLOB – Jskccc Jul 10 '20 at 13:31
@Jskccc Almost everything can be a string. It's best to use string whenever you can – Dharman Jul 10 '20 at 14:05
@Jskccc if you are interested in the theory, read up on the fixed type values in mysql. If you are interested in the answer, you should use s for the decimal field. – Your Common Sense Jul 10 '20 at 14:33

How to write an update query to subtract a certain value?

1 Answers1