如何将此代码修复为唯一的用户名/电子邮件注册

Question

this codes adds username and email to MYSQL database successfully but it accepts the SAME USER NAME and the SAME EMAIL if they are submitted in Upper case / lower case / or comobo of both ? what do I need to do to avoid adding the same USERNAME OR EMAIL all together ?

Note if I use for example USERNAME: moenagy18 I can also sign up with MoeNAGY18

this is the code in my register.php file.

//if logged in redirect to members page
if( $user->is_logged_in() ){ header('Location: memberpage.php'); exit(); }

//if form has been submitted process it
if(isset($_POST['submit'])){

if (!isset($_POST['username'])) $error[] = "Please fill out all fields";
if (!isset($_POST['email'])) $error[] = "Please fill out all fields";
if (!isset($_POST['password'])) $error[] = "Please fill out all fields";

$username = $_POST['username'];

//very basic validation
if(!$user->isValidUsername($username)) {
$error[] = 'Usernames must be at least 3 Alphanumeric characters';
} else {
$stmt = $db->prepare('SELECT username FROM members WHERE username = :username');
$stmt->execute(array(':username' => $username));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if(!empty($row['username'])){
$error[] = 'Username provided is already in use.';
}

}

if(strlen($_POST['password']) < 3){
$error[] = 'Password is too short.';
}

if(strlen($_POST['passwordConfirm']) < 3){
$error[] = 'Confirm password is too short.';
}

if($_POST['password'] != $_POST['passwordConfirm']){
$error[] = 'Passwords do not match.';
}

//email validation
$email = htmlspecialchars_decode($_POST['email'], ENT_QUOTES);
if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
$error[] = 'Please enter a valid email address';
} else {
$stmt = $db->prepare('SELECT email FROM members WHERE email = :email');
$stmt->execute(array(':email' => $email));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if(!empty($row['email'])){
$error[] = 'Email provided is already in use.';
}

}


//if no errors have been created carry on
if(!isset($error)){

//hash the password
$hashedpassword = $user->password_hash($_POST['password'], PASSWORD_BCRYPT);

//create the activasion code
$activasion = md5(uniqid(rand(),true));

try {

//insert into database with a prepared statement
$stmt = $db->prepare('INSERT INTO members (username,password,email,active) VALUES (:username, :password, :email, :active)');
$stmt->execute(array(
':username' => $username,
':password' => $hashedpassword,
':email' => $email,
':active' => 'Yes' 
));
$id = $db->lastInsertId('memberID');

//send email
$to = $_POST['email'];
$subject = "Registration Confirmation";
$body = "<p>Thank you for registering at demo site.</p>
<p>To activate your account, please click on this link: <a href='".DIR."activate.php?x=$id&y=$activasion'>".DIR."activate.php?x=$id&y=$activasion</a></p>
<p>Regards Site Admin</p>";

$mail = new Mail();
$mail->setFrom(SITEEMAIL);
$mail->addAddress($to);
$mail->subject($subject);
$mail->body($body);
$mail->send();

//redirect to index page
header('Location: index.php?action=joined');
exit;

//else catch the exception and show the error.
} catch(PDOException $e) {
$error[] = $e->getMessage();
}

}

}

Answer 1

Use BINARY in the query and it will do a byte by byte comparison this will check for an exact match(it will check if it is case sensitive).

 $stmt = $db->prepare('SELECT username FROM members WHERE BINARY username = :username');

Another method is using php strcmp(). Example: This compares 2 strings. If the same will = 0.

if(strcmp($dataA, $dataB) !== 0){$response = "Username or Password are Invalid!";
echo $response;
exit();}