validating user registration against value stored in database

[blackholesun]

Hi,

 

I am *slowly* developing a website for our gaming guild as a project to help me with my learning of php and MySQL. So far, any stumbling blocks i have encountered i have been able to overcome through using the mighty google and reinterpretation of code snippets posted to many various websites including stackoverflow and devshed. However, i can't seem to find any solutions that can help me with this problem i have encountered.

 

I want to be able to verify a new user account from an 'application form' they fill in. The theory is that a new prospect applies to join the guild by filling out a simple form. This form is then viewed by an admin and, if approved, that application is issued a unique application ID which the new member then uses as part of their main signup form for the guild site. This process uses two tables ('applications' and 'users'). All new applications are, naturally stored on the 'applications' table and registered users on the 'users' table.

 

In order to prevent fraudulent registrations or bot registrations, the ID issued to the new member is checked against the one stored on the 'applications' table and, if it passes, the new member is registered with the site on the 'users' table and their application is automatically erased from the 'applications' table as it's no longer needed.

 

The registration form uses $_POST methods to check that all data has been inputted and assigns them to variables for injection to the table. What i want to be able to do is check that the character string inputted in the 'application ID' text field fo the form matches the string stored in the 'applications' table. If it does, execute the injection to the 'users' tble and clean up the no longer needed data from the 'applications' table. If they don't match, throw an error and terminate the script. If you need to see what code I have then i'll happily post it, I haven't in this post as (a) it's already long enough and (b ) it's all fairly standard stuff, i'm just missing the bit that does the cross-checking....

 

Is there a simple solution to this? Or will it require a lot of coding? Any suggestions/examples would be most welcome

Edited May 14, 2013 by blackholesun

[Strider64]

I have it where I check the username (name) against the names in the database during the registration process and in my opinion there isn't to much code:

$query = "
            SELECT
                1
            FROM users
            WHERE
                username = :username1
        ";
        
        
        $query_params = array(
            ':username1' => htmlspecialchars($_POST['username1'])
        );

        
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);
                
       
        $row = $stmt->fetch();
        
        // If a row was returned, then we know a matching username was found in
        // the database already and we should not allow the user to continue.
        if($row)
        {
          error_log("This username is already registered", 3, "../logs/my-errors.log");
                         
          $announce->errorHandler("user_taken");
          $user_input = $announce->error_return();
          $error_msg = true;
       
        }

I stop the registration process before it is even entered into the table, thus no need in cleaning it up. To prevent bots I employe a Captcha scheme, don't like doing it...however, it's a necessary evil . Though over time I have a utility to purge the really really old accounts that are inactive. 

Edited May 14, 2013 by Strider64

[blackholesun]

I have it where I check the username (name) against the names in the database during the registration process and in my opinion there isn't to much code:

$query = "
            SELECT
                1
            FROM users
            WHERE
                username = :username1
        ";
        
        
        $query_params = array(
            ':username1' => htmlspecialchars($_POST['username1'])
        );

        
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);
                
       
        $row = $stmt->fetch();
        
        // If a row was returned, then we know a matching username was found in
        // the database already and we should not allow the user to continue.
        if($row)
        {
          error_log("This username is already registered", 3, "../logs/my-errors.log");
                         
          $announce->errorHandler("user_taken");
          $user_input = $announce->error_return();
          $error_msg = true;
       
        }

I stop the registration process before it is even entered into the table, thus no need in cleaning it up. To prevent bots I employe a Captcha scheme, don't like doing it...however, it's a necessary evil . Though over time I have a utility to purge the really really old accounts that are inactive. 

Thanks for the reply

 

I have something similar for users that already exist. What i am trying to do here though is slightly different for new users yet to register. The process goes something like:

 

1. apply to join [data written to 'applications' table] ->

2. admin verifies and assigns reg code [data written to 'applications' table in "verification" field] ->

3. user signs up to site proper and provides reg code [which should match string in 'applications',"verification"] ->

*if codes match (true) ->

*register additional user details in 'users' table

*pull existing info from 'applications' table and add to equivalent fields in 'users'

*erase unnecessary user data from 'applications' table (this table only needs to store such information as is 'pending' for full signup)

**if codes do not match (false)->

**throw error and abort registration process.

 

I have 1 & 2 working well, i'm just stuck on 3.. I know it seems a convoluted way of going about it but i am at that experimenting stage of learning atm and i have (fairly) sound reasons for doing it this way

 

BTW, the 'apply' form is really simple (much simpler than a forum signup, four fields), the 'register' form is about as involved as a forum signup form (just to clarify)

Edited May 14, 2013 by blackholesun

[blackholesun]

ah well, just by plugging at it, i solved the problem... my solution is probably ugly as all hell, needs optimising and tweaking before public deployment but at least it got the bugger to work!

 

...and that's half the battle

 

here's what i came up with:

if(empty($errors)){//if everything is ok
	//Register the application in the database...
	
	//cross-check input against the application data 'verfifcation' field
	//if input doesn't match the number on record, throw an error and terminate registration.
	$q = "SELECT verification FROM recruitment WHERE (`verification` LIKE '$ve')";
	$ru = @mysqli_query($dbc, $q);//run query
	while (list($verification) = mysqli_fetch_array($ru, MYSQLI_NUM)){
	if($verification == $ve){//if all is ok
		//print success and write verification ID to file
			echo'<div id="apply">
			<p>Your verification code matched that which was stored.</p><br />
			</div>';
			//extract email from 'recruitment' table
			$q1 = "SELECT email FROM recruitment WHERE (`verification` LIKE '$ve')";
			$rn = @mysqli_query($dbc, $q1);//run query
				while (list($email) = mysqli_fetch_array($rn, MYSQLI_NUM)){
					$q4 = "INSERT INTO users (username, charID, apiKey, password, first_name, last_name, birthday, country, town, verification, email) 
					VALUES ('$un', '$id', '$api', SHA1('$pw'), '$fn', '$ln', '$bd', '$ct', '$tw', '$ve', '$email')";
					$r2 = @mysqli_query($dbc, $q4);//run query
				}
			}else{//if it did not run Ok (maybe an else if here as error message is not displaying)
			//Public message:
			echo '<div id="apply">
			<h1>System Error</h1>
			<p>Your application could not be processed at this time due to a system error. We apologise for the inconvenience.</p>
			</div>';
					
			//Debugging message:
			echo '<p>' . mysqli_error($dbc) . '<br /><br /> Query: ' . $q . '</p>';
			}
		}
			
	mysqli_close($dbc);//close the db connection.
		
	//Include the footer and quit the script:
	include 'includes/overall/overall_footer.php';
	exit();
		
	}else{//report the errors
		
	echo'<style type="text/css">h1{font-size:2.0em;color:#fff;}p{font-size:1.3em;color:#fff;}</style><h1>Error!</h1>
	<p>The following error(s) occurred:<br />';
	foreach ($errors as $msg){//print each error.
		echo " - $msg<br />\n";
	}
		echo '</p><p>Please try again.</p><p><br /></p>';	
} //end of if (empty($errors)))

mysqli_close($dbc);//close the db connection

As can be seen from the comments, still a way to go but at least for now it does what i want to to when the form and data match and when they don't (but just need to get the errors to display now when registration fails)...

Edited May 15, 2013 by blackholesun