In PHP, We should always use a prepared statement which protects from SQL Injection. As well, its high efficiency, makes the query faster and more secure.
Prepared statements basically work on:
- Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labelled “?”). Example:
INSERT INTO MyGuests VALUES (?, ?, ?) - Compile: The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it.
- Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values
Compared to executing SQL statements directly, prepared statements have three main advantages:
- Prepared statements reduce parsing time as the preparation of the query is done only once (although the statement is executed multiple times).
- Bound parameters minimise bandwidth to the server as you need to send only the parameters each time, and not the whole query.
- Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
MySQL Query
We will use this query to create a database phploginregister and create users table inside it.
CREATE DATABASE `phploginregister`; USE `phploginregister`; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Database Connection (conn.php)
To connect with the Database we will call mysqli class with the server name, username, password and database name. It creates a successful connection or returns an error if unsuccessful.
<?php
$servername = "SERVERNAME"; // Your Server Name (Generally 'localhost')
$username = "USERNAME"; // Your Database Username
$password = "PASSWORD"; // Your Database Password
$database = "DATABASENAME"; // Your Database Name
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>Register Page (register.php)
We will create a simple registration form. Once the user submits the form, the form will be handled by jQuery and using AJAX it sends the data asynchronously to the action.php.
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //Database Error Report
require "conn.php";
//Checking for valid session
if (isset($_SESSION['email'])) {
header("Location: dashboard.php");
}
?>
<html>
<body>
<form method="POST" id="register">
Name : <input type="text" name="name" placeholder="Your Name" required><br>
Email : <input type="email" name="email" placeholder="Youe Email" required><br>
Password : <input type="password" name="email" placeholder="Your Password" required><br>
<input type="submit" value="Submit" name="submit">
</form>
<div id="msg"></div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
$('form#register').submit(function(e) {
e.preventDefault();
var name = $("input[name=name]").val();
var email = $("input[name=email]").val();
var password = $("input[name=password]").val();
var dataString = "name="+name+"email="+email+"password="+password+"do_register=do_register";
$.ajax({
type:'post',
url:'action.php',
data:dataString,
success:function(response){
if(response == "registration_success"){
$('#msg').html('<p style="color:green"><strong>Success!<strong> Registration Successfull.');
}
if(response == "account_exist"){
$('#msg').html('<p style="color:red"><strong>Error!<strong> Account already exists.');
}
},
processData: false,
contentType: false
});
});
</script>
</body>
</html>Login Page (login.php)
The User can use the same credential which he used at the time of registration, For user login, we use the session_start() function which creates a session. We will use the same jQuery and AJAX to handle the request where it calls action.php and validate the email and password. If validation will be successful, it creates a user session and automatically redirected to the dashboard.php else it returns an error.
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //Database Error Report
session_start();
require "conn.php";
//Checking for valid session
if (isset($_SESSION['email'])) {
header("Location: dashboard.php");
}
?>
<html>
<body>
<form method="POST" id="login">
Email : <input type="email" name="email" placeholder="Youe Email" required><br>
Password : <input type="password" name="email" placeholder="Your Password" required><br>
<input type="submit" value="Submit" name="submit">
</form>
<div id="msg"></div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
$('form#login').submit(function(e) {
e.preventDefault();
var email = $("input[name=email]").val();
var password = $("input[name=password]").val();
var dataString = "email="+email+"&password="+password+"&do_login=do_login";
$.ajax({
type:'post',
url:'action.php',
data:dataString,
success:function(response){
if(response == "login_success"){
window.location.href = 'dashboard.php';
}
else if(response == "wrong_password"){
$('#msg').html('<p style="color:red"><strong>Error!<strong> Wrong Password');
}
},
processData: false,
contentType: false
});
});
</script>
</body>
</html>Action Page (action.php)
This page contains all the functions which include registering the user, saving user details into the database, creating sessions, and checking valid usernames and passwords.
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //Database Error Report
session_start();
include 'conn.php'; // Database Connection Page
if(isset($_POST['do_register'])){
$name=$_POST['name'];
$email=$_POST['email'];
$password=$_POST['password'];
$hashPassword = password_hash($password, PASSWORD_DEFAULT); // Creating Hash Password
$stmt = $conn->prepare("SELECT * FROM TABLENAME WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->store_result();
// Checking the account exist with email or not
if ($stmt->num_rows == 0) {
$stmt_1 = $conn->prepare("INSERT INTO TABLENAME (name, email, password) VALUES (?, ?, ?)");
$stmt_1->bind_param("sss", $name, $email, $password);
$stmt_1->execute();
echo 'registration_success';
$stmt_1->close();
}
else{
echo "account_exist";
}
$stmt->free_result();
$stmt->close();
}
if(isset($_POST['do_login'])){
$email=$_POST['email'];
$password=$_POST['password'];
$stmt = $conn->prepare("SELECT id, password FROM TABLENAME WHERE email=?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->bind_result($id, $pass);
$stmt->fetch();
if (password_verify($password, $pass)) { // Verifying Password
session_regenerate_id();
$_SESSION['loggedin'] = TRUE;
$_SESSION['email'] = $email;
$_SESSION['id'] = $id;
echo 'login_success';
}
else {
echo 'wrong_password';
}
}
else{
echo 'wrong_password';
}
$stmt->close();
}
?>Dashboard Page (dashboard.php)
The user automatically redirected to this page once he logged in. Also, this page validated the user session. We can’t access this page without login, if we do so the user will be redirected to the login.php page.
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //Database Error Report
session_start();
require "conn.php";
//Checking for valid session
if (!isset($_SESSION['email'])) {
header("Location: login.php");
}
else{
// Login Successfull
echo "<h4>Login Succussfull,<h4>";
echo "<p>This is your Dashboad<p>";
echo "<a href='logout.php'>Logout</a>";
}
?>Logout Page (logout.php)
This page contains the session_destroy() function, which destroys all of the data associated with the current session.
<?php
session_start();
$_SESSION = array();
session_destroy();
header("location: login.php");
exit;
?>
