php registration page check for email in table

Beginners - Learning PHP
#1

I have a registration page that works but will allow multiple users with same email address.
I want to prevent that. My code won’t work now that I altered it and I think it is something simple.
Thanks for any help.

[php]<?php

require ("db.php");	

$msg = "";
if(isset($_POST["submit"]))
{
// If form submitted, insert values into the database.
if (isset($_REQUEST['username']))
{
// removes backslashes
$username = stripslashes($_REQUEST['username']);
//escapes special characters in a string
$username = mysqli_real_escape_string($con,$username);

$email = stripslashes($_REQUEST['email']);
$email = mysqli_real_escape_string($con,$email);

$password = stripslashes($_REQUEST['password']);
$password = mysqli_real_escape_string($con,$password);

$trn_date = date("Y-m-d H:i:s");
		
	
	
	$sql="SELECT email FROM users WHERE email='$email'";
	$result=mysqli_query($db,$sql);
	$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
	if(mysqli_num_rows($result) == 1)
	{
		$msg = "Sorry...This email already exists in our database.";
	}
	else
	{
	$query = "INSERT INTO 'users' (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date');
		
		if($result)
		{
			
		}
	}
}

?>

Registration

<?php } ?> [/php]
#2

[php]if(isset($_POST[“submit”]))[/php]
it’s better to check the request method than to check for the submit button

[php]if (isset($_REQUEST[‘username’]))[/php]
Is the only required form field the username?

[php]$email = stripslashes($_REQUEST[‘email’]);
$email = mysqli_real_escape_string($con,$email);[/php]
All of these are unnecessary, use prepared and parameterized queries instead (which actually protect you against sql injection hacks).

[php]$sql=“SELECT email FROM users WHERE email=’$email’”;
$result=mysqli_query($db,$sql);[/php]
This is unnecessary, just set the email column to unique in the db and it will refuse to add new rows with the same value

[php]$query = “INSERT INTO ‘users’ (username, password, email, trn_date) VALUES (’$username’, '”.md5($password)."’, ‘$email’, ‘$trn_date’);[/php]
Do NOT use MD5 for storing passwords, it is not secure. Use the built in password lib
http://php.net/manual/en/function.password-hash.php

[php]$query = “INSERT INTO ‘users’ (username, password, email, trn_date) VALUES (’$username’, '”.md5($password)."’, ‘$email’, ‘$trn_date’);

if($result)[/php]
if($result) here refers to the result from the last query. You never execute the insert query.

#3

thank you! i changed the unique to email, thus preventing duplication…but it now just shows a white page when someone tries to duplicate. any ideas?

#4

check you error logs

your development environment should be configured to display all errors. a blank page is the default way to show a 500 internal server error

#5

okay i tried this and it will only input the username and password, not the first name, last name or title. thanks for any ideas.
i think i broke it.

[php]<?php
// Include config file
require_once ‘config.php’;

// Define variables and initialize with empty values
$username = $password = $confirm_password = $firstname = $lastname = $title = “”;
$username_err = $password_err = $confirm_password_err = $firstname_err = $lastname_err = $title_err = “”;

// Processing form data when form is submitted
if($_SERVER[“REQUEST_METHOD”] == “POST”){

// Validate username
if(empty(trim($_POST["username"]))){
    $username_err = "Please enter a username.";
} else{
    // Prepare a select statement
    $sql = "SELECT id FROM users WHERE username = ?";
    
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_username);
        
        // Set parameters
        $param_username = trim($_POST["username"]);
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            /* store result */
            mysqli_stmt_store_result($stmt);
            
            if(mysqli_stmt_num_rows($stmt) == 1){
                $username_err = "This username is already taken.";
            } else{
                $username = trim($_POST["username"]);
            }
        } else{
            echo "Oops! Something went wrong. Please try again later.";
        }
    }
    
    //=========================================
    
    
// Validate firstname
if(empty(trim($_POST['firstname']))){
    $firstname_err = "Please enter your first name.";     
} elseif(strlen(trim($_POST['firstname'])) < 2){
    $firstname_err = "Name must have at least 2 characters.";
} else{
    $firstname = trim($_POST['firstname']);
}
// Validate lastname
if(empty(trim($_POST['lastname']))){
    $lastname_err = "Please enter your last name.";     
} elseif(strlen(trim($_POST['lastname'])) < 2){
    $lastname_err = "Name must have at least 2 characters.";
} else{
    $lastname = trim($_POST['lastname']);
}
// Validate title
if(empty(trim($_POST['title']))){
    $title_err = "Please enter your title.";     
} elseif(strlen(trim($_POST['title'])) < 2){
    $title_err = "Title must have at least 2 letters.";
} else{
    $title = trim($_POST['title']);
}

    
    //=========================================
     
    // Close statement
    mysqli_stmt_close($stmt);
}

// Validate password
if(empty(trim($_POST['password']))){
    $password_err = "Please enter a password.";     
} elseif(strlen(trim($_POST['password'])) < 6){
    $password_err = "Password must have atleast 6 characters.";
} else{
    $password = trim($_POST['password']);
}

// Validate confirm password
if(empty(trim($_POST["confirm_password"]))){
    $confirm_password_err = 'Please confirm password.';     
} else{
    $confirm_password = trim($_POST['confirm_password']);
    if($password != $confirm_password){
        $confirm_password_err = 'Password did not match.';
    }
}

// Check input errors before inserting in database
if(empty($username_err) && empty($password_err) && empty($confirm_password_err)){
    
    // Prepare an insert statement
    $sql = "INSERT INTO users (username, password) VALUES (?, ?)";
     
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "ss", $param_username, $param_password);
        
        // Set parameters
        $param_username = $username;
        $param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            // Redirect to login page
            header("location: login.php");
        } else{
            echo "Something went wrong. Please try again later.";
        }
    }
     
    // Close statement
    mysqli_stmt_close($stmt);
}

// Close connection
mysqli_close($link);

}
?>[/php]

#6

Telling us the errors and what should happen compared to what does happen helps us help you. Without that information it makes it much more difficult to diagnose the issues.

#7

I don’t get an error. Everything looks good. But the some of the data isn’t going into the table. I don’t know how to make it tell me the problem. Sorry and thanks.

#8

Okay, what data isn’t going in that should be?

#9
Everything looks good.

Have you actually looked at all the code? If you did, particularity for the values that aren’t being inserted, you would see an obvious problem.

Next, you have a ton of unnecessary code/typing, where you are spending more time on the implementation details, and less time on what the code is actually trying to accomplish, i.e. use all the form field data in the INSERT query. You have a ‘cannot see the forest for the trees’ problem. You should always try to use the simplest logic that accomplishes any task (Keep It Simple - KISS) and avoid repeating the same code (Don’t Repeat Yourself - DRY.) Some suggestions -

  1. Don’t create discrete variables for each possible value, for both the form data and the errors. Use arrays. Arrays are for sets of data, where you will operate on each element in the set in the same/similar way. You can initialize arrays simply by assigning an empty array. No need to write out code for every possible value. By using an array for the errors, you can test at any point if there are errors or no errors by testing if the array is not empty or is empty.

  2. Trim all the form data at once, then operation on the trimmed data. You are currently repeating the trim() call three times for each form field You can do this in a single statement -

[php]$post = array_map(‘trim’,$_POST); // get a trimmed copy of all 1st level (non-array) $_POST data. if the form data contains arrays, write and use a recursive call back function instead of trim[/php]

  1. Use exceptions to handle database statement (connection/query/prepare/execute) errors. This will eliminate the existing error handling logic, add error handling for the statements that don’t have it now, and allow you to display the actual error information when learning, developing, and debugging code/queries, and log the errors when on a live/public server, by simply changing php’s display_errors and log_errors settings.

This will also allow you to easily implement something that JimL hinted at, of just trying to insert the data and detect if there was a duplicate key error from the query. There’s no need to write code to SELECT the data first and since you MUST (for a good user experience) detect and handle a duplicate value error, you only need to run extra code when there is a duplicate error.

If you do have a need to SELECT data just to test if it exists, use SELECT COUNT(*). You would then fetch the count value, which for the mysqli extension will simply things because you won’t need to use mysqli_stmt_store_result($stmt); and mysqli_stmt_close($stmt); to make the code work and not produce errors for other queries.

To enable exceptions for the mysqli extension, add the following line before the point where you make the database connection -
[php]mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);[/php]

  1. You should use OOP notation. It is shorter, less typing. less trees.

  2. You should NOT copy variables to other variables without a good reason. This is just more unnecessary clutter, adds more things to keep track of, and adds more opportunity for mistakes that you must find and fix.

  3. You should switch to use the simper php PDO extension. The msyqli extension is overly complicated and requires more statements to accomplish almost every single task. Again, cut down some of the trees so that you can see the forest you are trying to find.

  4. You don’t have to close database connections. Php automatically closes them when your script ends.

edit: 8) You have the validation of the first name, last name, and title inside the usrename logic. Validation of each form field should be separate so that you can report all validation errors at one time. This is another to many ‘trees’ problem.

Doing these things will simply all the program logic, making it easier to design, write, test, and debug problems. If I have time, I will post an example showing these things.

#10

See the following example code showing most of the suggestions. This code still uses the mysqli extension (using PDO would eliminate an extra variable and the bind statement) and it doesn’t fix the problem of only some of the data being inserted. Fixing this is trivial and is left up to you as a learning experience.

[php]<?php

// set the mysqli error mode to exceptions - then remove any existing error handling logic
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Include config file
require ‘config.php’;

$post = []; // an array to hold a working copy of the submitted form data
$errors = []; // an array to hold errors

// Processing form data when form is submitted
if($_SERVER[“REQUEST_METHOD”] == “POST”)
{
// inputs - username, firstname, lastname, title, password, confirm_password

$post = array_map('trim',$_POST); // get a trimmed copy of all 1st level (non-array) $_POST data. if the form data contains arrays, write and use a recursive call-back function instead of php's trim

// Validate username
if(empty($post["username"]))
{
	$errors['username'] = "Please enter a username.";
}
// rather than do all the SELECT query logic, just detect a duplicate key error from the INSERT query

// Validate firstname
if(empty($post['firstname']))
{
	$errors['firstname'] = "Please enter your first name.";
} elseif(strlen($post['firstname']) < 2){
		$errors['firstname'] = "First Name must have at least 2 characters.";
}

// Validate lastname
if(empty($post['lastname']))
{
	$errors['lastname'] = "Please enter your last name.";
} elseif(strlen($post['lastname']) < 2){
	$errors['lastname'] = "Last Name must have at least 2 characters.";
}

// Validate title
if(empty($post['title']))
{
	$errors['title'] = "Please enter your title.";
} elseif(strlen($post['title']) < 2){
	$errors['title'] = "Title must have at least 2 letters.";
}

// Validate password
if(empty($post['password']))
{
	$errors['password'] = "Please enter a password.";
} elseif(strlen($post['password']) < 6){
	$errors['password'] = "Password must have at least 6 characters.";
}

// Validate confirm password
if(empty($post["confirm_password"])){
	$errors['confirm_password'] = 'Please confirm password.';
}

// if no password validation errors, check if the password and confirm_password match
if(empty($errors['password']) && empty($errors['confirm_password']) && $post['password'] != $post['confirm_password']){
	$errors['confirm_password'] = 'Password did not match.';
}

// Check input errors before inserting in database
if(empty($errors))
{
	// Prepare an insert statement
	$sql = "INSERT INTO users (username, password) VALUES (?, ?)";
	$stmt = $link->prepare($sql);

	// hash the password
	$param_password = password_hash($post['password'], PASSWORD_DEFAULT); // Creates a password hash

	// Bind variables to the prepared statement as parameters
	$stmt->bind_param("ss", $post['username'], $param_password);

	try { // a local try/catch to handle a specific error type
		// Attempt to execute the prepared statement
		$stmt->execute();
	} catch (mysqli_sql_exception $e) {
		// handle any duplicate key error for this query
		// note: if there are multiple unique fields defined in the table, the first duplicate field will throw an error. you can parse the text error message here to find which field produced the error and/or run a SELECT query here to find which field(s) have duplicate data.
		if($e->getCode() == 1062){ // duplicate key error number
			$errors['username'] = "The username is already in use.";
		} else {
			throw $e; // re-throw the exception if the error number is not handled by this logic
		}
	}

	if(empty($errors))
	{
		// Redirect to login page
		header("location: login.php");
		exit;
	}
}

}

// dump the errors for demo purposes
echo ‘

’; print_r($errors);[/php]

This logic can be further reduced by creating an array that defines the validation rules for each form field. Instead of all the conditional logic to validate the inputs, you would loop over this defining array and reuse general purpose logic to test for things like required fields being empty, minimum lengths, …
#11
Register Your Full Name*: Email Address*: UserName*: Password*: http://www.trainingindelhi.com/best-php-training-in-delhi.php
#12

You drop some html with no context and a link that says “best php training”? How poor is the rest of the training there if this is the best?

Do you have a question or should I delete this as spam?

#13

There’s user on a different forum (I’m not going to name) that is can be a little rude at times, but he does have a lot of valid points.
Anyways, he says a user’s stored password should never be pulled out of the MySQL database table and that password_hash and password_verify are not secure. He gave an example and I modified it a little to fit my coding style.

Here’s my version of it:
Create:
[php] public function create($data) {
if (is_array($data)) { // If statement probably not needed:
$db = DB::getInstance();
$pdo = $db->getConnection();
/* Secure the Password by hashing the user’s password. */
$data[‘password’] = hash(‘whirlpool’,$data[‘password’]);
try {

            /* Set the query variable */
            $this->query = 'INSERT INTO users (username, password, full_name, email, confirmation_code, security_level, private, date_added) VALUES (:username, :password, :full_name, :email,  :confirmation_code, :security_level, :private, NOW())';

            /* Prepare the query */
            $this->stmt = $pdo->prepare($this->query);

            /* Execute the query with the stored prepared values */
            $this->result = $this->stmt->execute([
                ':username' => $data['username'],
                ':password' => $data['password'],
                ':full_name' => $data['full_name'],
                ':email' => $data['email'],
                ':confirmation_code' => $data['confirmation_code'],
                ':security_level' => $data['security_level'],
                ':private' => $data['private']
            ]); // End of execution:
            return \TRUE;
        } catch (PDOException $error) {
            // Check to see if name is already exists:
           // This will be only good a really really busy websites:
            $errorCode = $error->errorInfo[1];
            if ($errorCode == MYSQL_ERROR_DUPLICATE_ENTRY) {
                error_log("Duplicate Name was Enter", 1, "[email protected]");
            } else {
                throw $error;
            }
        }
    } // End of main if-statement:
}[/php]

Read:
[php] public function read($username, $password) {
$db = DB::getInstance();
$pdo = $db->getConnection();
/* Setup the Query for reading in login data from database table */
$this->query = ‘SELECT id FROM users WHERE username=:username AND password=:password’;

    $this->stmt = $pdo->prepare($this->query); // Prepare the query:
    $this->stmt->execute([
        ':username' => $username,
        ':password' => hash('whirlpool', $password)
        ]); // Execute the query with the supplied user's parameter(s):

    $this->stmt->setFetchMode(PDO::FETCH_OBJ);
    unset($password);
    if ($this->user_id = $this->stmt->fetchColumn()) {
        return $this->user_id;
    } else {
        return FALSE;
    }
}[/php]

I haven’t fully vetted this yet, I think adding a salt to the password would make it secure and I’m still redoing my own PHP registration script. Personally I think password_hash and password_verify are good enough and that is the reason I haven’t fully been sold on that part. Though I agree not pulling the password for the database table is good practice. Once I have it tested and have a fully blown login/registration script(s) I will post it here somewhere.

#14

Thank you!

#15

Please PM me this, I want to see it

Then he disagrees with afaik the entire web dev community

The answer you’re most likely to get on any PHP forum, stack overflow etc is that you should NOT make your own authentication layer. This even includes using the built in password functions in PHP. For practically any developer it’s highly recommended to use a third party authentication layer that multiple people with security background have worked on, which is tested, etc.

If you absolutely have to make your own auth layer or are only messing around, then I highly recommend using the built in functions password_hash, password_verity and password_needs_rehash. You will then be following what the PHP community recommends for hashing passwords. This can change over time but by using password_needs_rehash you can detect this and update the hash to the newer format. Ie if PHP decides to change the default from Bcrypt cost factor 10 to 14, or even change the entire hash routine from Bcrypt to ie Argon2

relevant answer on stackexchange

Bcrypt CAN BE stronger than whirlpool, it depends on your strength setting. This is one of the most important advantages about bcrypt, that you can increase the strength as computers get more powerful, plus it's extremely difficult to crack using a GPU compared to simple SHA/Whirlpool hashes.

Since PHP 5.5, we’ve had bcrypt available as easy to use functions. To create a hash you would use password_hash($pass), you don’t need to worry about salts because one is automatically generated and placed inside of the hash, because of this you need to use password_verify($pass, $hash) to check a hash, as generating a hash will use a different salt every time (and your strength settings could change).

There is almost no reason not to use this for a new application, as it’s proven to be much much more secure than standard hashing algorithms. If you’re on a version of PHP older than 5.5 there are a variety of libraries available to assist with this, most commonly ircmaxell’s password_compat.


#16

I know what forum and what user [member=57087]Strider64[/member] is talking about and his view on passwords. I called him out on it in a PM once. He skirted around my response IIRC.

Sponsor our Newsletter | Privacy Policy | Terms of Service