JS Pinto JS Pinto - 1 month ago 6
SQL Question

php - Login redirecting to same page, static but different roles

After doing my SQL Schema (Different types of users redirected to same page (index.php) with different content), I'm starting to make my login system.
I now have this:

function login($email,$password){

$mysqli = $this ->dbConnect();
if($mysqli){

$strQuery = "SELECT USERS.ID, USERS.EMAIL, TYPES.NAME FROM `USERS` LEFT JOIN `TYPES` ON USERS.TYPEID = TYPES.ID WHERE `EMAIL` = '$email' AND `PASSWORD` = '$password'";
$recordSet = $mysqli->query($strQuery);
$row = $recordset->fetch_assoc();
if($recordset->num_rows>0){
$_SESSION['auth'] = $row['ID'];
$_SESSION['username'] = $row['EMAIL'];
$_SESSION['type'] = $row['NAME'];

return true;
}
//....
}


}

Does this look good? Is the query right? Any suggestions for improvement?

Answer

I summarized the previous comments.

1. Issue: you didn't used the same variables

function login($email,$password){ and $strQuery = " ... WHERE EMAIL = '$email' AND PASSWORD = '$password'";

2. Recomendation: use the same namming convention

On your SQL request you used two way to use fields: USERS.EMAIL and EMAIL = (with ` arround). Use the same. This will be easier for later & debugging.

i.e.: of course, you should not use table.field each time. Not mandatory for example if you have only one table OR if the fields are not shared between them. For my perosnnal usage, I always use this table.field. This will prevent any future issue :)

3. Protect your data from any injection

Example:

$post_email = isset($_POST['email']) ? htmlspecialchars($_POST['email']) : null;

Alter call

$this->login($post_email, ...)

And finally use something like this to protect your data:

$email = $mysqli->real_escape_string($email);

and you are ready for your request:

" SELECT [..] FROM users as u [...] WHERE u.email = '$email' "

4. Or use specific functions

Example (real_escape_string not needed anymore):

$stmt = $dbConnection->prepare('SELECT * FROM users WHERE email = ? AND password = ?');
$stmt->bind_param('s', $email);
$stmt->bind_param('s', $password);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

http://php.net/manual/fr/class.mysqli.php