How to start a session and set access levels for users using mysql

Question

I already have a login system ready and 100% working, but now I would like to add access levels when logging in and I have no idea how. In my database, in the table of my logins, there is a column that I created called 'permission_level' and the default is set to 'default', and the administrators as 'master'

How can I solve this?

Unsuccessful attempt:

<?php

$mysqli = new mysqli('', '', '', '');

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT permission_level FROM authme WHERE = '".$user."'";
echo $user;

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s \n", $row["permission_level"]);
    }

    /* free result set */
    $result->close();
}


session_start();
$_SESSION['UserSession'] = $_POST['username'];

Before continuing with the requirements you illustrate above, you should know your script is ***wide open to critical SQL injection vulnerabilities***. You should never concatenate user input directly into your query text. Use [prepared statements/parameterized queries](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) instead. — esqew, May 13 '21 at 17:52
They are just ways to get the result I expected, but after several unsuccessful attempts, I asked here, sql injection I worry about the next steps later, as well as other languages that I will use as well, like jquery — Akashic, May 13 '21 at 17:59
`$user` is not assigned, so it will be empty? And after reading the question it is not clear what the problem is. (besides sql-injection) — Luuk, May 13 '21 at 18:03
$ user is assigned to a super global '$ _POST [' username '], and this page is conditioned, it is only possible to have access to it if someone has successfully logged in — Akashic, May 13 '21 at 18:06

Ken Lee · Accepted Answer · 2021-05-13T18:27:03.247

1

You need to store the db table data to the session variable after the sql query.

(1) Please move session_start(); to the start of the page.

(2) and then Change

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s \n", $row["permission_level"]);
    }

    /* free result set */
    $result->close();
}

to

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

$_SESSION['permission_level'] = $_row['permission_level'];

        printf ("%s \n", $row["permission_level"]);
    }

    /* free result set */
    $result->close();
}

then you can use $_SESSION['permission_level'] to do what you want (remember to put session_start() at the start of all the PHP scripts using the session variable)

For example, if you only want users with permisson level = "master" to access a certain page (e.g. admin.php) , then in this admin.php, you should add, at the top of the script, the following:

<?php
session_start();

if ($_SESSION['permission_level']!="master")
{
echo "You are not allowed to access this page";
exit();
}

// add other codes below this line.

?>

On the other hand, as @esquw has mentioned, please also use parameterized prepared statement to avoid SQL injection.

edited May 13 '21 at 18:27

answered May 13 '21 at 18:03

Ken Lee

2,537
2
4
21

How can I use the column value to limit that certain levels cannot access something? – Akashic May 13 '21 at 18:19
1

please see my slightly revised answer which shows you an example. – Ken Lee May 13 '21 at 18:27
I don't know what happened, but everything inside the "if ($result = $mysqli->query($query)){" is no longer working, I tested it with an echo, and the echo was not displayed Edit: My query is defined like this: "$query = "SELECT username, permission_level FROM authme WHERE username = ?";" – Akashic May 13 '21 at 18:55
please check whether your server supports Mysqli / PDO if you want to implement parameterized prepared statement in your SQL statements. (otherwise it will not work). Even if it supports, you need to amend your codes to use parameterized prepared statements and please refer to some working examples on the net. – Ken Lee May 13 '21 at 19:29
Previously, I tested queries that worked like fetch_assoc, but my query was different, instead of searching for only one user, I was searching for several, and I was successful, however, when I add in the query 'WHERE username =?' the if stops working. How can I do to search for only one user without this error appearing? – Akashic May 13 '21 at 19:55
I suggest you open a new post and put the codes you have on hand again on the new post so that the community can further reply. Thanks – Ken Lee May 14 '21 at 05:08

How to start a session and set access levels for users using mysql

1 Answers1