Use SQL to check if user exists in database

Question

I'm trying to check a table called members to see if a user exists with it's email and password. I'm able to connect to the database, but for some reason, it jumps all these if statements and echoes 'You have been logged in!' even when I put the wrong email or password? Here is the html and php:

<form action="/login-user" method="POST">
    Email: <input type="text" name="login_email"><br>
    Password: <input type="password" name="login_password"><br>
    <button type="submit">Login</button>
</form>

PHP:

<?php
    session_start();
    /*error_reporting(0);*/

    require 'users/functions/user-functions.php';

    require 'users/connect-database.php';

    if (empty($_POST) === false) {
        $email = mysqli_real_escape_string($connection, $_POST['login_email']);
        $password = stripslashes(mysqli_real_escape_string($connection, $_POST['login_password']));
        $encrypted_password = md5($password);
        if (empty($email)) {
            echo 'You need to enter an email<br>';
        } else if (empty($password)) {
            echo 'You need to enter a password<br>';
        } else if(user_exists($connection, $email, $encrypted_password) === false) {
            echo 'You don\'t seem to be registered?';
        } else if (user_active($connection, $email, $encrypted_password) === false) {
            echo 'You haven\'t activated your account!';
        } else {
            $login = login($connection, $email, $encrypted_password);
            if ($login === false) {
                echo 'That email/password combination is incorrect';
            } else {
                $_SESSION['user_id'] = $login;
                $_SESSION['logged_in'] = true;
                echo 'You have been logged in!';
            }
        }
    /*print_r($errors);*/
} else {
    echo 'inputs were empty<br>';
}
require 'users/disconnect-database.php';
?>

Content of the file 'user-functions.php':

<?php
    function sanitize($connection, $data) {
        return mysqli_real_escape_string($connection, $data);
    }
    function logged_in() {
        return $_SESSION['logged_in'];
    }
    function user_exists($connection, $email, $password) {
        $query = mysqli_num_rows(mysqli_query($connection, "SELECT * FROM members WHERE email = '$email' AND password = '$password'"));
        return ($query > 0) ? true : false;
    }
    function user_active($connection, $email, $password) {
        $query = mysqli_query($connection, "SELECT user_id FROM members WHERE email = '$email' AND password = '$password' AND `active` = 1");
        return ($query !== false) ? true : false;
    }
    function return_user_id($connection, $email, $password) {
        return mysqli_query($connection, "SELECT user_id FROM members WHERE email = '$email' AND password = '$password'");
}
    function login($connection, $email, $password) {
        /*$user_id = mysql_result(mysqli_query($connection, "SELECT user_id FROM members WHERE email = '$email' AND password = '$password'"), 0, 'user_id');*/
        /*$password = md5($password);*/
        $query = mysqli_query($connection, "SELECT user_id FROM members WHERE email = '$email' AND password = '$password'");
        /*return (mysqli_query($connection, $query) or die (false));*/
        if ($query === false) {
            return false;
        } else {
            return $query;
        }
    /*return ($query !== false) ? true : false;*/
    }
    function log_out() {
        unset($_SESSION['logged_in']);
        session_unset();         
        session_destroy();
    }
?>

If the answer is using mysql_result or mysqli_result, please explain in full detail because even after reading on the manual and W3Schools and everywhere else, I still don't understand how those functions work.

Thanks for any answers, and by the way, I have read all the other posts about this stuff but I didn't find any answers. Thanks.

SuperDJ · Accepted Answer · 2014-05-23 18:13:51Z

1

First of all I would really advise to use a sha for encrypting passwords because md5 is decrypted in no time at all.

for your login function try the following:

<?php
function login($connection, $email, $password) {
    $query = mysqli_query($connection, "SELECT `email`, `password` FROM `members` WHERE `email` = '$email' AND `password` = '$password'");
    $count = mysqli_num_rows($query); //counting the number of returns

    //if the $count = 1 or more return true else return false
    if($count >= 1) {
        return true;
    } else {
        return false;
    }
}
?>

after the script has returned true you could set a session or do what you need to do with it.

EDIT You need session_start in every file so the best thing to do is include this. I hope this works I typed it realy fast so there might be some errors in it but please let me know:

<?php
function generate($password) {
    $password = hash('sha1', $password);
    return $password;
}

function login($connection, $email, $password) {
    $password = generate($password);

    $query = mysqli_query($connection, "SELECT `email`, `password` FROM `members` WHERE `email` = '$email' AND `password` = '$password'");
    $count = mysqli_num_rows($query); //counting the number of returns

    //if the $count = 1 or more return true else return false
    if($count >= 1) {
        return true;
    } else {
        return false;
    }
}

function exists($connection, $detail, $table, $row, $value) {
    $query = mysqli_query($connection, "SELECT `$detail` FROM `$table` WHERE `$row` = '$value'");
    $count = mysqli_num_rows($query);

    if($count >= 1) {
        return true;
    } else {
        return false;
    }
}

function detail($connection, $detail, $table, $row, $value) {
    $query = mysqli_query($connection, "SELECT `$detail` FROM `$table` WHERE `$row` = '$value'");
    $associate = mysqli_fetch_assoc($query);

    return $associate[$detail];
}

function errors($error) {
    echo '<ul class="error">';
    foreach($error as $fault) {
        echo '<li>'.$fault.'<li>';
    }
    echo '</ul>';
}

function isLoggedIn() {
    if(!empty($_SESSION['logged_in']) && exists($connection, 'id', 'members', 'id', $_SESSION['logged_in']) == true) {
        return true;
    } else {
        return false;
    }
}

function logout() {
    unset($_SESSION['logged_in']);
}

if($_POST) {
    $email = mysqli_real_escape_string($connect, strip_tags($_POST['email']));
    $password = mysqli_real_escape_string($connect, strip_tags($_POST['password']));

    if(!empty($email) && !empty($password)) {
        if(!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $error[] = 'Your email: '.$email.' is not valid';
        }

        if(exists($connection, 'email', 'members', 'email', $email) == false) {
            $error[] = 'You are not registered';
        }

        if(detail($connection, 'active', 'members', 'email', $email) != 1) {
            $error[] = 'Your account is not activated';
        }

        if(empty($error)) {
            $query = login($connection, $email, $password);

            if($query == true) {
                $_SESSION['logged_in'] == detail($connection, 'id', 'members', 'email', $email);
            }
        }
    } else {
        $error[] = 'There are empty fields';
    }

    if(!empty($error)) {
        echo errors($error);
    }
}
?>

<form action="" method="POST">
    Email: <input type="text" name="email"><br>
    Password: <input type="password" name="password"><br>
    <input type="submit" value="Login">
</form>

edited May 23, 2014 at 18:13

answered May 23, 2014 at 16:42

SuperDJ

7,69911 gold badges44 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

BestAnswer Over a year ago

do I need to pass $connection to mysqli_num_rows?

SuperDJ Over a year ago

@codeshackel no you don't but I'm improving my answer in a few minutes

BestAnswer Over a year ago

password is stored in database in sha1 form, so shouldn't I be passing $encrypted_password to querys?

BestAnswer Over a year ago

little error in function detail() in connection variable pass to query

SuperDJ Over a year ago

Well if the isLoggedIn() function works then there is no problem in my opionion but if it doesn't then you should give that as a param to the function. I used to do my sites like this but since a few months I use OOP and these things are a lot easier in OOP. If you also want to learn more php and even OOP check this: youtube.com/…

|

Collectives™ on Stack Overflow

Use SQL to check if user exists in database

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related