Update a value only if it is bigger than the actual

Question

I have a field that stores a numeric value that will go from 0 to 7. It is a counter for some steps to be completed in the application. Each time a step is completed, the counter is updated with the new value. User can go back on the steps and then forward. If he has completed step 3, he can go back to step 1 and then forward till step 3 again. What I want to do is to avoid that when the user returns to step 3 the counter gets updated with 1 and 2 values but remains 3. I want to investigate a way to do it within the update query.

The query is the following:

try{
    $pdo->query("UPDATE ruolo SET wiz_step='$step' WHERE id_user='$utente'");
}
catch(PDOException $e){
    $status='500';
    $data['step']=$step;
    $message='Si è verificato un errore. Abbiamo aperto una segnalazione al team tecnico.';
}
$message="Step aggiornato correttamente";
}

Is it possible to tell mysql to update wiz_step only if $step is > than the value of wiz_step before the update?

Table structure is just made of three int fields: id primary and autoincrement, id_user and wiz_step. Note: I assume I am not open to mysql injections since none of the values in the query are coming from a user input. They are all set by the php logic.

didn't think about IS operator! Great suggestion, thanks. It is clean and simple as expected. Thanks! — Lelio Faieta, Jul 19 '21 at 15:58
this code is **vulnerable** to **sql injection** so use only **prepared statemnts with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Jul 19 '21 at 16:03
@nbk why sql injections if the values passed are in no cases coming from the user? Just to understand better — Lelio Faieta, Jul 19 '21 at 16:51
every website is permantly subjeted to attacks, and even when you check the input it still can be dangerous, so use **always** for queries with data, so that you and all others don't copy or use, string concatination — nbk, Jul 19 '21 at 17:01
@nbk this query is not coming from any user input. The input is hardcoded in my application and this query is triggered by when specific other part of my php code are executed. Neither this values are coming from the db or previously user inserted values. And you still assume there can be a sql injection? How? Just to understand. — Lelio Faieta, Jul 20 '21 at 09:49
that is not the pint insecure code, is insecure, even when in your case teh calues ar computer generated. Make it right the first time — nbk, Jul 20 '21 at 10:39

score 1 · Answer 1 · answered Jul 19 '21 at 16:12

1

As these are all values controlled by code it is quite simple to do, also change to using prepared queries to protect your code from SQL Injection Attack

try{
    
    $data = [':step' => $step, ':step1' => $step, ':uid' => $utente];

    $stmt = $pdo->prepare("UPDATE ruolo 
                            SET wiz_step=:step 
                            WHERE id_user=:uid
                            AND :step1 > wiz_step");
    $stmt->execute($data);
}

answered Jul 19 '21 at 16:12

RiggsFolly

93,638
21
103
149

a sql injection will raise on input coming from user input, not from values coming from the application, do you agree? – Lelio Faieta Jul 19 '21 at 17:00
1

No, even if you get data from the database, you have to know for sure that it was sanitised correctly before using it RAW, so always use a prepared statement – RiggsFolly Jul 19 '21 at 17:10

score 1 · Answer 2 · answered Jul 19 '21 at 17:54

Here's a slight variation on the answer from @RiggsFolly:

try{
    
    $data = ['step' => $step, 'uid' => $utente];

    $stmt = $pdo->prepare("UPDATE ruolo 
                            SET wiz_step=GREATEST(:step, wiz_step) 
                            WHERE id_user=:uid");
    $stmt->execute($data);
}

See the GREATEST() function in the MySQL manual. It returns the greater value of its arguments. So if the parameter is greater, it will be used to update the column. If the existing value is greater, then no change will be made, because wiz_step = wiz_step is a no-op.

P.S.: It's not necessary to use the : character in the array keys when you pass parameters to a prepared query. It was needed in an early version of PDO long ago, but not anymore.

Update a value only if it is bigger than the actual

2 Answers2