DingoDile DingoDile - 2 months ago 25
PHP Question

Check if email already exist

how do i check if the email already exist in the database and deny the registration.

mysql was the one taught to me and im currently on a wall using mysqli

here is the code that im currently working on using myqsli

<?php
$cookie_name = "loggedin";

$servername = "localhost";
$username = "root";
$password = "";
$database = "scholarcaps";
$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
die("Database connection failed: ".mysqli_connect_error());
}

if (isset($_POST['login']))
{
$user = $_POST['username'];
$pass = $_POST['password'];

$phash = sha1(sha1($pass."salt")."salt");

$sql = "SELECT * FROM users WHERE username='$user' AND password='$phash';";

$result = mysqli_query($conn, $sql);
$count = mysqli_num_rows($result);

if ($count == 1)
{
$cookie_value = $user;
setcookie($cookie_name, $cookie_value, time() + (180), "/");
header("Location: personal.php");
}
else
{
echo "Username or password is incorrect!";
}
}
else if (isset($_POST['register']))
{
$user = $_POST['username'];
$email = $_POST['email'];
$pass = $_POST['password'];

$phash = sha1(sha1($pass."salt")."salt");

$sql = "INSERT INTO users (id, email, username, password) VALUES ('','$email', '$user', '$phash');";

$result = mysqli_query($conn, $sql);
}
?>

Answer

Despite using mysqli your code is still vulnerable to sql injection as you directly embed variables in the sql statements - use prepared statements to avoid nasty surprises. The following is not tested but should show how you can use prepared statements. There are better ways of hashing the password - such as password_hash and also password_verify though these are not available in PHP versions prior to 5.5

$response=array();
$cookie_name='loggedin';


$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   ''; 
$dbname =   'scholarcaps';
$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );




if( isset( $_POST['login'] ) ) {

    $user  = $_POST['username'];
    $pass  = $_POST['password'];
    $phash = sha1( sha1( $pass . "salt" ) . "salt" );

    $sql='select `username`, `password` from `users` where `username`=? and `password`=?';
    $stmt=$db->prepare( $sql );

    if( $stmt ){

        $stmt->bind_param( 'ss', $username, $phash );
        $result=$stmt->execute();

        if( !$result ) $response[]='Query failed';


        $stmt->store_result();
        $stmt->bind_result( $name, $pwd );
        $stmt->fetch();

        if( $stmt->num_rows()==0 ) $response[]='No such user';
        else {

            $stmt->free_result();
            $stmt->close();
            $db->close();

            setcookie( $cookie_name, $user, time() + 180, "/" );
            exit( header( "Location: personal.php" ) );
        }


        $stmt->free_result();
        $stmt->close();
        $db->close();

        /* show errors */
        if( !empty( $response ) ){
            echo '<ul><li>',implode('</li><li>',$response),'</li></ul>';
        }
    }

} elseif( $_POST['register'] ){

    $user = $_POST['username'];
    $email = $_POST['email'];
    $pass = $_POST['password'];
    $phash = sha1( sha1( $pass . "salt" ) . "salt" );



    /* Does the email address already exist? */

    $emailfound=false;

    $sql='select `email` from `users` where `email`=?';
    $stmt=$db->prepare( $sql );
    if( $stmt ){

        $stmt->bind_param('s',$email);
        $result=$stmt->execute();
        if( $result ){
            $stmt->store_result();
            $stmt->bind_result( $emailfound );
            $stmt->fetch();
            $stmt->free_result();
        }
    }

    if( $emailfound ){
        echo 'Sorry, that email address already exists in our database. Please try again with a different address.';
        $stmt->close();
        $db->close();

    } else {

        /* the `id` should be automatically generated I assume - hence being omitted here */
        $sql='insert into `users` (`email`, `username`, `password`) values (?,?,?);';
        $stmt=$db->prepare( $sql );

        if( $stmt ){

            $stmt->bind_param( 'sss', $email, $username, $phash );
            $result=$stmt->execute();

            $stmt->free_result();
            $stmt->close();
            $db->close();

            if( $result ) header('Location: login.php');
            else{
                /* failed to register the user */
            }
        }
    }
}