我如何告诉用户他们的电子邮件已经注册

Question

我在数据库中的电子邮件列上放置了一个UNIQUE索引，当我输入一封已经注册的电子邮件时，数据库不会更新。所以效果很好。我现在需要告诉(在注册页面上(输入现有电子邮件的用户它已经注册，并将他们重定向到主页。

请检查我的SQL注入代码，如果有任何错误，请更正。

<?php
$fullname = $_POST['fullname'];
$email = $_POST['email'];
$mobilenumber = $_POST['mobilenumber'];
//prevent sql injection
$fullname = stripslashes($fullname);
$email = stripcslashes($email);
$mobilenumber = stripslashes($mobilenumber);
$fullname = mysql_real_escape_string($fullname);
$email = mysql_real_escape_string($email);
$mobilenumber = mysql_real_escape_string($mobilenumber);

//Database Connection
$conn = new mysqli("#","#","#","#");
if($conn->connect_error){
die('connection Failed : '.$conn->connect_error);
}else{
$stmt = $conn->prepare("insert into signup(fullname,email,mobilenumber)values(?,?,?)");
$stmt->bind_param("ssi",$fullname,$email,$mobilenumber);
$stmt->execute();
header("Location:thankyou.html");
$stmt->close();
$conn->close();
}
?>

Answer 1

根据注释-如果在尝试执行insert之前执行简单的select，则可以派生程序逻辑并让用户知道。

<?php
if( $_SERVER['REQUEST_METHOD']=='POST' && isset( 
$_POST['fullname'], 
$_POST['email'], 
$_POST['mobilenumber'] 
)){

$fullname = $_POST['fullname'];
$email = $_POST['email'];
$mobilenumber = $_POST['mobilenumber'];



$dbport =   3306;
$dbhost =   'localhost';
$dbuser =   'dbo-user-xxx';
$dbpwd  =   'dbo-pwd-xxx';
$dbname =   'db-xxx';



error_reporting( E_ALL );
mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

try{

#check email before insert
$sql='select `email` from `signup` where `email`=?';
$stmt=$conn->prepare( $sql );
$stmt->bind_param('s',$email);
$stmt->execute();
$stmt->store_result();

if( $stmt->num_rows==0 ){
/* email does not exist - perform insert */
$sql='insert into `signup` ( `fullname`, `email`, `mobilenumber` ) values ( ?, ?, ? )';
$stmt=$conn->prepare( $sql );
$stmt->bind_param('sss', $fullname, $email, $mobilenumber );
$stmt->execute();
$stmt->close();
$conn->close();

exit( header('Location: thankyou.html') );

}else{
/* email does exist - tell user */
$stmt->free_result();
$stmt->close();

exit( header('Location: ?error=true&email=true' ) );
}

}catch( mysqli_sql_exception $e ){
exit( $e->getMessage() );
}
}
?>

或者，您可以像以前一样try/catch，但使用返回错误代码来派生逻辑

<?php
/*

mysql> describe signup;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| fullname     | varchar(50)      | NO   |     | NULL    |                |
| email        | varchar(64)      | NO   | UNI | NULL    |                |
| mobilenumber | varchar(16)      | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

mysql> select * from signup;
+----+----------+-----------------------------+--------------+
| id | fullname | email                       | mobilenumber |
+----+----------+-----------------------------+--------------+
|  1 | fred     | [e-mail] | 123          |
+----+----------+-----------------------------+--------------+
*/


/* Attempt to insert duplicate - but use error code 1062 to fork the logic */
$dbport =   3306;
$dbhost =   'localhost';
$dbuser =   'dbo-user-xxx';
$dbpwd  =   'dbo-pwd-xxx';
$dbname =   'db-xxx';


/* same email and phone number but different fullname */
$email='[e-mail]';
$fullname='freddy boy';
$mobilenumber=123;



error_reporting( E_ALL );
mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

try{

$sql='insert into `signup` ( `fullname`, `email`, `mobilenumber` ) values ( ?, ?, ? )';
$stmt=$conn->prepare( $sql );
$stmt->bind_param('sss', $fullname, $email, $mobilenumber );
$stmt->execute();

}catch( mysqli_sql_exception $e ){
if( $e->getCode()==1062 ){
/* redirect the user and let them know the email already exists */
exit( header( sprintf('Location: ?error=%s',$e->getMessage() ) ) );
}
}

?>

Answer 2

// first check the database to make sure 
// a email does not already exist with the same  email
$fullname = $_POST['fullname'];
$email = $_POST['email'];
$mobilenumber = $_POST['mobilenumber'];
$user_check_query = "SELECT * FROM signup WHERE email='$email'LIMIT 1";
$result = mysqli_query($cons, $user_check_query);
$emailcheck= mysqli_fetch_assoc($result);

if ($emailcheck) { // if email exists
if ($emailcheck['email'] === $email) {
array_push($errors, "email already exists");
header('location: index.php');
}
}
// Finally, register user if there are no errors in the form
if (count($errors) == 0) {
$sql = "insert into 
signup(fullname,email,mobilenumber)values($fullname,$email,$mobilenumber)";
$runsql = mysqli_query($cons, $sql);
if($runsql) {
header("Location:thankyou.html");
} else {
echo"Some thing is wrong";
}
}
}