simple trouble on account verification script

[Russia]

Hey guys, I am having a bit of trouble on my new registered account verification script.

It gets the code from the email sent after you register to activate your account to a 'level1' user. It uses a randomly generated code to do it and a GET function.

Using the url with the variable: verify.php?id=codingforums

Here is my code:

<?php
$queryString = $_GET['id'];
$query  = "SELECT * FROM users LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    if ($queryString == $row["activationkey"]) {
        echo "Congratulations! You have activated your account. You may login your account.";
        $sql = "UPDATE users SET activationkey = '', level='1' WHERE (user_id = $row[user_id])";
        if (!mysql_query($sql)) {
            die('Error: ' . mysql_error());
        }
    } else {
        echo "The account containing the verification code you requested has already been activated, or the validation code is invalid";
    }
}
?>

But I keep using the code with an account thats not verified. and it keeps returning 'The account containing the verification code you requested has already been activated, or the validation code is invalid'

here is how my DB looks


Anyone notice the problem at all?

[oaass]

What's the output of var_dump on $queryString and $row["activationkey"]? 

 

if ($queryString == $row["activationkey"]) {

 

As a side note to this, I would suggest you stop using the mysql extension since it is extremely out dated, and look into mysqli and/or PDO

[PaulRyan]

It's a simple issue.

 

Firstly, you have to escape the ID before you use it.

 

$queryString = isset($_GET['id']) ? mysql_real_escape_string($_GET['id']) : FALSE ;

 

Then you actually have to use the ID in the MySQL query, which you haven't:

 

$query  = "SELECT `user_id`, `activationkey` FROM `users` WHERE `user_id` = '{$queryString}' LIMIT 1"

[Russia]

 

It's a simple issue.

 

Firstly, you have to escape the ID before you use it.

 

$queryString = isset($_GET['id']) ? mysql_real_escape_string($_GET['id']) : FALSE ;

 

Then you actually have to use the ID in the MySQL query, which you haven't:

 

$query  = "SELECT `user_id`, `activationkey` FROM `users` WHERE `user_id` = '{$queryString}' LIMIT 1"

The thing is, querystring isnt the user id, its the activationcode. So why would i search for the activation code 'codingforums' in the column 'user_id' shouldent i look for it from the row column 'activationkey'?

[PaulRyan]

Yeah you are right, I mis-read the post.

 

$query  = "SELECT `user_id`, `activationkey` FROM `users` WHERE `activationkey` = '{$queryString}' LIMIT 1"

[Russia]

Thanks, it works now by removing the activation code and settting the user level to 1 but now when I reload the page with the same ID it doesnt show the message The account containing the verification code you requested has already been activated, or the validation code is invalid it just loads a blank page. It should say that code if the code is not found in any of the rows.
 
Why is it doing that? The else statement should work.
 
Updated Code:

<?php
$queryString = $_GET['id'];
$query = "SELECT `user_id`, `activationkey` FROM `users` WHERE `activationkey` = '{$queryString}' LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
if ($queryString == $row["activationkey"]) {
echo "Congratulations! You have activated your account. You may login your GoverScape account.";
$sql = "UPDATE users SET activationkey = '', level='1' WHERE (user_id = $row[user_id])";
if (!mysql_query($sql)) {
die('Error: ' . mysql_error());
}
} else {
echo "The account containing the verification code you requested has already been activated, or the validation code is invalid";
}
}
?>

[PaulRyan]

Try this:

 

<?PHP
 
  $queryString = isset($_GET['id']) ? mysql_real_escape_string($_GET['id']) : FALSE;
  
  $query  = "SELECT `user_id`, `activationkey` FROM `users` WHERE `activationkey` = '{$queryString}' LIMIT 1";
  $result = mysql_query($query) or die(mysql_error());
  
  $row = mysql_fetch_assoc($result);
  
  if($row['activationkey'] != $_GET['id']) {
    echo 'The account containing the verification code you requested has already been activated, or the validation code is invalid.';
  } else {
    $updateRowQuery = "UPDATE `users` SET `activationkey` = '', `level` = 1 WHERE (`user_id` = {$row['user_id']})"; 
    $updateRow      = mysql_query($updateRowQuery);
    
    if(!mysql_affected_rows()) {
      echo 'An error occurred: ' . mysql_error();
    } else {
      echo 'Congratulations! You have activated your account. You may login your GoverScape account.';  
    }
  }
 
?>

 

Edit* - You should have a better error reporting mechanism in place, to log errors and save them for viewing etc.

[Russia]

That seemed to work perfectly! Much appreciated my friend!