member activation by email: clicking on the activation link

[Himself12794]

Hello, I'm having trouble setting up membership on my website to activation by email.

The email is sent successfully, but there is a problem in the verification process. The account is activated when the activated column=1.

Here's the code:

 

<?php
//Start session
session_start();

//Include database connection details
require_once('config.php');

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}

//Info sent from email
$activationKey = $_GET['ak'];

//Activate account
$qry = "INSERT INTO members(activate) VALUES('1') WHERE activation_key='$activationKey'";
$result = mysql_query($qry);

//Check if activation was successful
if($result) {
	header("location: activated.php");
	exit();
}else {
	die("Query failed");
}

?>

 

Whenever I try to activate, it returns the Query failed error. I can't find the problem. Can anyone spot it?

[Pikachu2000]

For the time being, echo the query string and the error generated by MySQL instead of the generic error message.

 

} else {
     die( "<br>Query: $qry<br>Failed with error: " . mysql_error() );
}

 

Then post the output it generates.

[Himself12794]

It returns this:

Query: INSERT INTO members(activate) VALUES('1') WHERE activation_key='319770278153397993011709668425323553761391406339'

Failed with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE activation_key='319770278153397993011709668425323553761391406339'' at line 1

[Pikachu2000]

INSERT queries don't use a WHERE clause. I'm assuming you need to update the record that was created upon initial user registration, correct? You would need to use UPDATE syntax for that.

 

UPDATE table SET field1 = value1, field2 = value2 WHERE some_field = some_value

[Himself12794]

It works perfectly. Thanks alot!

[Pikachu2000]

OK, but you aren't really through yet. You need to eliminate SQL injection vulnerabilities that are present in that script, and change the error message back to something generic . . .

[Himself12794]

would adding the line:

          $activationKey = mysql_real_escape_string($activationKey);

be sufficient?

[Pikachu2000]

Is the value always all numeric characters, and the same length? If so, validate it with ctype_digit, and strlen, and leave the quotes off of it in the query string.

[Himself12794]

It is and I'll try that. Thanks.