Create a Login System Using PHP and Microsoft Azure Database
I never wrote PHP system for a while, and I asked to build a simple login system for the current system.
Most of my experience is using MySQL database to build login system, and it was a long time ago.
Compared with MySql, Azure SQL database has some different things, especially queries.
Almost all tutorials are about MySQL, so I need to use MySQL knowledge and convert it to Azure SQL database version.
First we need to create the database structure , We need to thing what data we need to store and what we need to trace.
Database is very important if on beginning we don't make it better will cause many issue on the end.
So Today I will share my login system create by PHP and Azure SQL Database .
Azure SQL Database structure
You can easy using Azure Data Studio to create the database structure or management data, recommended get a try .
database.sql
--- Azure SQL Database login system structure by childofcode.com ---
CREATE TABLE dbo.Login
(UniqueID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
LoginUsername text NULL,
LoginPassword text NULL,
IPAddress text NULL,
Permission varchar(100) NULL,
LastLoginTime datetime2 NULL
)
GO
Microsoft Azure Database connection
conn.php
This is the Azure Database connection configuration file you can setup you own Database here.
<?php
// Microsoft Azure Database connection PHP script by childofcode.com
$serverName = "tcp:servername.database.windows.net,1433";
$connectionOptions = array(
"Database" => "YourDatabaseName",
"UID" => "YourDatabaseUserID",
"PWD" => "YourDatabasePassword"
);
$conn = sqlsrv_connect($serverName, $connectionOptions);
?>
User Login Page
login.php
Create login page with bootstrap stylesheet.
PHP script Features include record user ip address, login time and some basic user validation function and regular expression remove all unnecessary special character on username input.
Remark: Don't return too many login errors information. Hackers can use this information to damage your system. you can check on my code all error will be show with the same error message "Login Error !".
<?php
/* Microsoft Azure Database PHP login page by childofcode.com */
session_start();
// SESSION check user login status
if(isset($_SESSION["loggedin"]) && $_SESSION["loggedin"] === true){
header("location: content.php");
exit;
}
// require Database connection files
require_once "conn.php";
// login error reminder
function FormatErrors( $errors )
{
echo "Error information: ";
foreach ( $errors as $error )
{
echo "SQLSTATE: ".$error['SQLSTATE']."";
echo "Code: ".$error['code']."";
echo "Message: ".$error['message']."";
}
}
// variables Declaring
$username = $password = "";
$username_err = $password_err = "";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Get login user current datetime
$LoginTime = date('d-M-Y h:i:s');
// Get login user IP address
$UserIp = '127.0.0.1';
if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ipInfo = $_SERVER['HTTP_CLIENT_IP'];
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ipInfo = $_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
$ipInfo = $_SERVER['REMOTE_ADDR'];
}
$ipInfo = parse_url($ipInfo);
$UserIp = $ipInfo['host'];
// Check username input empty
if(empty(trim($_POST["username"]))){
$username_err = "Please enter username.";
} else{
$username = trim($_POST["username"]);
}
// Check password input empty
if(empty(trim($_POST["password"]))){
$password_err = "Please enter your password.";
} else{
$password = trim($_POST["password"]);
}
// Regular expression remove all unnecessary special character.
$usernameInput = preg_replace('/[^A-Za-z0-9\-]/', '', (string)$username);
// Continue processing only when without error
if(empty($username_err) && empty($password_err))
{
// SQL query for check is username valid and password correct
$tsql= "SELECT * FROM Login WHERE LoginUsername LIKE '" . $usernameInput . "' ORDER BY UniqueID";
$getResults= sqlsrv_query($conn, $tsql);
$checkUsername = "";
$hashedPassword = "";
$validateUserInfo = true;
// SQL query error and warning information handle
if ($getResults == FALSE)
{
die(FormatErrors(sqlsrv_errors()));
}
# Returns available row of data as an associative array
$row = sqlsrv_fetch_array($getResults);
$checkUsername = $row['LoginUsername'];
$hashedPassword = $row['LoginPassword'];
# Check if the username is registered on database
if($checkUsername == "")
{
$validateUserInfo = false;
$login_err = "Login Error !";
}
# Check if the password is registered on database
if($hashedPassword == "")
{
$validateUserInfo = false;
$login_err = "Login Error !";
}
// Continue processing only is register user
if($validateUserInfo)
{
// Verifies that the given hash matches the user input password.
if(password_verify($password, $hashedPassword))
{
// SQL query for updated login user latest info
$tsqlUpdate= "UPDATE Login SET IPAddress = ?, LastLoginTime = ? WHERE LoginUsername LIKE ? ";
$paramsUpdate = array($UserIp, $LoginTime, $checkUsername );
$updatedResults= sqlsrv_query($conn, $tsqlUpdate, $paramsUpdate);
$rowsAffected = sqlsrv_rows_affected($updatedResults);
if ($updatedResults == FALSE or $rowsAffected == FALSE)
{
// SQL query error and warning information handle
echo "error update user info" ;
die(FormatErrors(sqlsrv_errors()));
}
else
{
//create a session after sucuess login
session_start();
$_SESSION["loggedin"] = true;
$_SESSION["username"] = $row['LoginUsername'];
$_SESSION["userpermission"] = $row['Permission'];
//Go to secure content page and and terminate the current script
header("location: content.php");
exit;
}
}
else
{
$login_err = "Login Error !";
}
}
// Frees all resources after SQL query
sqlsrv_free_stmt($getResults);
}
// Closes the connection and releases resourses.
sqlsrv_close( $conn );
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta charset="utf-8">
<meta name=description content="PHP and Microsoft Azure Database Login by childofcode.com">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PHP and Microsoft Azure Database Login by childofcode.com</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
body{
color: #666;
font: 14px sans-serif;
background-color: #fafafa;
}
.login-block{
padding: 20px;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
text-align: center;
min-height: 100vh;
}
.wrapper{
width:300px;
height:100%;
padding: 20px;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
text-align: center;
background-color: white;
border: 1px solid #eaeaea;
}
.login-button{
color: #f9f9f9;
background-color: #00bfff;
border-color: #eaeaea;
}
.login-label{
color: #666;
}
input[type="text"]::placeholder {
text-align: center;
}
input[type="password"]::placeholder {
text-align: center;
}
</style>
</head>
<body>
<div class="login-block">
<div class="wrapper">
<h2>User login page</h2>
</br>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]."?page=".$_GET['page']."&data=".$_GET['data']) ; ?>" method="post">
<div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>">
<label class="login-label" >Username</label>
<input type="text" name="username" class="form-control" value="<?php echo $username; ?>" placeholder="Enter username">
</div>
<div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>">
<label class="login-label" >Password</label>
<input type="password" name="password" class="form-control" placeholder="Enter password">
</div>
<div class="form-group <?php echo (!empty($username_err) || !empty($password_err) || !empty($login_err)) ? 'has-error' : ''; ?>">
<span class="help-block"><?php echo $username_err; ?></span>
<span class="help-block"><?php echo $password_err; ?></span>
<span class="help-block"><?php echo $login_err; ?></span>
</div>
<div class="form-group">
<input type="submit" class="btn btn-primary login-button" value="Login">
</div>
</form>
<a href="https://childofcode.com/">by childofcode.com</a>
</div>
</div>
</body>
</html>
Protect Content Page
This is a secure content page that we need to protect.
You can simply reuse your own content by simply copying the php part at the top before you html or php code.
Copy the following PHP script to the page you need to protect
Copy the following PHP script to the page you need to protect
<?php
/* Microsoft Azure Database PHP login protected page by childofcode.com */
// Initialize the session, Check if the user is logged in, if not then redirect him to login page
session_start();
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
header("location: login.php");
exit;
}
?>
content.php
Simple content protect page
<?php
/* Microsoft Azure Database PHP login protected page by childofcode.com */
// Initialize the session
session_start();
// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
header("location: login.php");
exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta charset="utf-8">
<meta name=description content="PHP and Microsoft Azure Database Login by childofcode.comn">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Microsoft Azure Database PHP login page secure content page</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body{
color: #666;
font: 14px sans-serif;
background-color: #fafafa;
}
.login-block{
padding: 20px;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
text-align: center;
min-height: 100vh;
}
.wrapper{
width:100%;
height:100%;
padding: 20px;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
text-align: center;
background-color: white;
border: 1px solid #eaeaea;
}
.logout-button{
color: #f9f9f9;
background-color: #00bfff;
border-color: #eaeaea;
}
</style>
<script>
window.onload = function()
{
// logout function
$(document).off("click", "#logout-button");
$(document).on("click", "#logout-button", function()
{
window.location.href = "logout.php";
});
}
</script>
</head>
<body>
<div class="login-block">
<div class="wrapper">
<h2>secure content page</h2>
</br>
<h1>Hi, <b><?php echo htmlspecialchars($_SESSION["username"]); ?></b>. Welcome to secure content page.</h1>
<input type="submit" class="btn btn-primary logout-button" value="Logout">
<a href="https://childofcode.com/">by childofcode.com</a>
</div>
</div>
</body>
</html>
Logout PHP script
User logout php script, frees and destroys all session and redirect user to login.php .
logout.php
<?php
/* Microsoft Azure Database PHP logout script by childofcode.com */
session_start();
$_SESSION = array(); // Frees all session variables
session_destroy(); // destroys all session data
header("location: login.php"); // redirect back to login page
exit;
?>
Above is all the codes for login system.
However, if you want use for production mode, there are still some TODO parts that need to be completed. For example, the registered page, profile page (update profile or change password) and the forgotten password page.
For my purposes, we don’t allow people to register themselves, so in my case, I simply use a php script to generate password hashes and create user data through Azure Data Studio.
Below is the PHP script and SQL query I used to create a new user, First, I will use a PHP script to generate user password hash after that using Azure Data Studio to create user data on a Microsoft Azure database.
A simple PHP script to Creates a password hash.
hash.php
<?php
/* PHP password hash generate script by childofcode.com */
// Add plain text password on 'pw' URL parameters to Creates a password hash, Example: hash.php?pw=passs1234
$password = $_GET['pw'];
$param_password = password_hash($password, PASSWORD_DEFAULT); // Creates a password hash
echo $param_password ;
?>
SQL query to create new user on database
insert.sql
INSERT INTO Login (LoginUsername,LoginPassword,Ip,LastLoginTime)
VALUES ('username', 'The Password hash generate by hash.php', '127.0.0.1', '31-Aug-2020 18:39:50');
You can simply use the php and query above to complete other TODO parts. It should not be difficult for you now.
Completed source can be found on GitHub repository
GitHub
Let me know if anyone really needs other parts, I will update them when I have time. If you find any security issues, errors, bugs, contact me anytime. thanks.