logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> Other Software View previous topic :: View next topic
Reply to topic   Topic: PHP :: MySQL update issue
Author
Mox



Joined: 14 Sep 2021
Posts: 6

PostPosted: Wed 22 Sep '21 14:42    Post subject: PHP :: MySQL update issue Reply with quote

I have an issue updating record with PHP.
I am using PHP 7.2 and MySQL 8.0.

I have two pages: register.php and activate.php

In register.php there is a code that inserts user in a mysql table Users like this:

Code:
$db = new PDO("mysql:host=" . $mysqlHost . ";dbname=" . $mysqlDB, $mysqlUser, $mysqlPassword);

$sql = "INSERT INTO Users (name, email, active, activationCode) VALUES ('$name', '$email', $active, '$activationCode')";
                 
$result = $db->exec($sql);


Field 'active' in Users table is a boolean field with 0 or 1 value.

This code executes properly and user is inserted properly in table User. Field 'active' has a value 0.

Next, user gets email with an activation link to click in order to activate user account, like this http://activate.php?activationCode=123456

activate.php validates activation code and updates field 'active' with value 1, like this:
Code:

$sql = "UPDATE Users SET active = 1 WHERE activationCode = '" . $activationCode . "'";
$result = $db->exec($sql);


Problem that I am facing is, if the user opens register.php and activate.php on the same device, for example computer, then UPDATE Users SET active = 1 is working ok, meaning field 'active' updates value with 1.
But, if the user opens register.php on computer, and activate.php on mobile phone, then field 'active' gets updated with 1 and then after a second, it reverts to 0.

What is the problem?
Is mysql connection related to PHP session? Maybe locking issue?
How to solve this?
Back to top
mraddi



Joined: 27 Jun 2016
Posts: 149
Location: Schömberg, Baden-Württemberg, Germany

PostPosted: Wed 22 Sep '21 15:37    Post subject: Reply with quote

Hello Mox,

might be a transaction but no COMMIT for it. And as your PHP-script closes the connection the modification is reverted. But this is just a guess...
From my point of view it is not a locking issue because in this case your script would either wait until the lock is not locked anymore or will fail.

Regarding your SQL-statements: please sanitize your input to avoid SQL-injection-attacks - https://www.php.net/manual/de/pdo.prepare.php
See what might happen if you don't: https://xkcd.com/327/ Very Happy
Back to top
Mox



Joined: 14 Sep 2021
Posts: 6

PostPosted: Wed 22 Sep '21 18:23    Post subject: Reply with quote

I tried also manual COMMIT, but still the same issue.
It does update for a second, and then reverts, so COMMIT does occur, but why it reverts, is a puzzle to me.

mraddi wrote:
Regarding your SQL-statements: please sanitize your input to avoid SQL-injection-attacks

I did, I ommited here for clarity sake.
Back to top


Reply to topic   Topic: PHP :: MySQL update issue View previous topic :: View next topic
Post new topic   Forum Index -> Other Software