Prepared statements
Prepared statements are used to protect SQL queries against malicious/malformed user input.
The general layout of a prepared statement is as follows:
PDO
// Define database connection parameters
$db_host = "127.0.0.1";
$db_name = "name_of_database";
$db_user = "user_name";
$db_pass = "user_password";
// Create a connection to the MySQL database using PDO
$pdo = new pdo(
"mysql:host={$db_host};dbname={$db_name}",
$db_user,
$db_pass,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE
]
);
// The SQL query using ? as a place holder for the variable we want to insert
$sql = "SELECT column_1, column_2, column_3 FROM table WHERE column_x = ?";
// Prepare the query structure
$query = $pdo->prepare($sql);
// Run the query binding $variable to the ?
// i.e. the query becomes:
// SELECT * FROM table WHERE column = "$variable"
$query->execute([$variable]);
// Fetch the first/next row into the $result variable
$result = $query->fetch(PDO::FETCH_ASSOC); // Access like: $result["column_1"];
// Other way to fetch data...
# $result = $query->fetch(PDO::FETCH_NUM); // Access like: $result[0];
# $result = $query->fetchAll(); // Access likeL $result[0]["column_1"];
# $result = $query->fetchObject(); // Access like: $result->column_1;
mysqli
// Define database connection parameters
$db_host = "127.0.0.1";
$db_name = "name_of_database";
$db_user = "user_name";
$db_pass = "user_password";
// Make connection to the database
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);
// The SQL query using ? as a place holder for the variable we want to insert
$sql = "SELECT column_1, column_2, column_3 FROM table WHERE column_x = ?";
// Prepare the query structure
$query = $mysqli->prepare($sql);
// Bind the STRING $variable to the ? placeholder in the SQL
$query->bind_param("s", $variable);
// Run the query
$query->execute();
// Store the result
$query->store_result();
// Bind the returned values
$query->bind_result($column_1, $column_2, $column_3);
// Fetch the first/next row
$query->fetch();
//Access as a standard PHP variable...
echo $column_1;
PHP redirects
Using redirects like the following won't work because you can't output data to the page (echo) and then cause a redirect.
echo "<script>alert('Successfully logged in!');</script>";
header('location: HomeForSuperAdmin.php');
Session ifs
The following checks to see if the value in $_SESSION["usertype"] is set to Admin or SuperAdmin.
if ($_SESSION['usertype'] == 'Admin'){
// Additional code...
}
elseif ($_SESSION['usertype'] == 'SuperAdmin'){
// Additional code...
}
The issue here is that as we are only now logging in there shouldn't be a value in that variable!
There would only be a value there if the user was already logged on. In your code it should actually be written as:
if ($row['usertype'] == 'Admin'){
// Additional code...
}
elseif ($row['usertype'] == 'SuperAdmin'){
// Additional code...
}
LIMIT in your SQL
You don't need to use limit in your SQL query for two reasons:
- The username field should be unique (i.e. there is only one corresponding row)
- You're not accessing the row in a loop you are only taking the first row. So even if there was multiple rows returned you'd only see one.
Flow
Bear in mind that, for the most part, we don't want to have a dozen nested if statements in our programs it makes it complicated to follow and is harder to debug, maintain, etc.
if(expression a){
if(expression b){
if(expression c){
// Some extra random code...
if(expression d){
// Some code to work with...
}
else{
// Some code...
}
}
else{
// Some code...
}
}
else{
// Some code...
}
}
else{
// Some code...
}
Instead, if we streamline the code so that it runs from top to bottom then it tends to be easier to follow:
if(expression a){
// Do something...
}
if(expression b){
// Do something else...
}
Obviously this isn't always possible - sometimes it makes more sense to nest(!) - but going too many levels with if statements can be very tricky to work with.
In your case though you're using if statements to check specific conditions have been met and if they haven't then the code should stop executing and display an error or redirect...
if(username and password have not been entered){
echo "ERROR: Username and Password are empty";
exit;
}
// If the user didn't input values then we would never get this far...
if(user doesn't exist in database){
echo "ERROR: Username doesn't exist!";
exit;
}
// If the user doesn't exist in the database then we never get this far...
if(password matches){
echo "Success! The user is logged on.";
}
Adjusted code
include "includes/config.php";
session_start();
// Check to see if the user is already logged on. If they are then
// we want to redirect them to an error page.
if(isset($_SESSION["username"])){
header("location:errorpage.php?error_code=1");
exit;
}
// Assign POST variables to PHP variables. If the POST variables
// don't exist (haven't been submitted) then set the value to NULL
// so that we can use it in the IF statement easily.
$username = $_POST['user'] ?? NULL;
$password = $_POST['password'] ?? NULL;
// Check to make sure that the user has entered something into the
// username and password fields. If they haven't then we're going
// to handle them with a redirect.
if(!$username || !$password){
header("location:errorpage.php?error_code=2");
exit;
}
$user_sql = "
SELECT password, usertype, FirstName, LastName
FROM tbl_useraccounts
WHERE employee_id = ?
";
$user_query = $pdo->prepare($user_sql);
$user_query->execute([$username]);
// Get the user from the database. If the username doesn't exist then
// redirect the user to approriate error page.
if( !($user = $user_query->fetchObject() ){
header("location:errorpage.php?error_code=3");
exit;
}
// If the user does exist then we verify the password supplied by the user
// against the one stored in the database. If it doesn't match then we'll
// redirect them to an approriate error page.
if(!password_verify($password, $user->password)){
header("location:errorpage.php?error_code=4");
exit;
}
// Set the usertype and username to the sesson SUPER GLOBAL.
// You don't need to set all of the variables (name etc.)
// if you need them get them from the database at the time!
$_SESSION["usertype"] = $user->usertype;
$_SESSION["username"] = $username;
//Redirect the user to the appropriate admin page
$usertype_redirect_locations = [
"Admin" => "adminpage.php",
"SuperAdmin" => "superadminpage.php"
];
header("location:{$usertype_redirect_locations[$user->usertype]}");
