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

Question

我在数据库中的电子邮件列上放置了唯一索引，当我输入已注册的电子邮件时，数据库不会更新。这样效果很好。我现在需要告诉用户输入已经存在的电子邮件（在注册页面上），它已经注册并将他们重定向到主页。请检查我的 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

根据评论 - 如果您select在尝试执行之前做一个简单的操作，insert则可以分叉程序逻辑并让用户知道。

<?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";

    }

  }

}