Security is a measured challenge while creating an application in PHP. The raw SQL query in PHP can be injected through a malicious function. This may affect your entire functionality. Even you can lose your database or particular table and even any record. Due to the SQL injection, the vulnerable code or script can be injected into your SQL query. It is the most important thing to prevent SQL injection in your PHP application. I already shared a brief post on SQL Injection and Prevention Techniques in PHP. The PHP frameworks like Laravel, CodeIgniter, etc. are secured from the SQL injection. We can overcome the possibility of SQL injection using a prepared statement. The PHP prepared statement query will generate a placeholder. It will prepare a SQL statement and later will bind the values to the placeholder. In this post, I will create a CRUD app using a prepared statement.
We are going to build something like this.
Prerequisites
For creating this CRUD application in php using a prepared statement, you must have the below configuration.
- Apache/Nginx Server
- PHP >=7
- MySQL >=5
- VS Code (Optional)
Create a Project Folder Structure
Firstly, go to htdocs directory if you are a Windows user and already have installed xampp/wampp. If you are a Linux user then go to the var/www/html and create a new folder there. In my case, the folder name is crud. Inside this folder, I have created these two subfolders named assets and master.
Let me explain the file
Make the folder structure as shown below.
crud
--> master
--> header.php
--> footer.php
create.php
DataController.php
DBController.php
edit.php
index.php
show.phpThe assets folder has a script.js file that will contain the script code for form validation.
Inside the master folder there are two files –
- header.php and
- footer.php
I have used Bootstrap CDN so I kept it inside the header.php file. We will include the header and footer files in our other files.
- create.php– This will contain a basic form for creating a new post.
- show.php– On this page, there will be the inputs to display the post detail.
- edit.php– It is for updating the post.
- index.php– All the posts will be shown in the form of a table.
- DataController.php– All the server-side logic and operations will be written here.
- DBController.php– It will contain the database configuration.
Here, I have opened the project folder in the VS Code editor. It should look like this.
After creating the files let’s add the code one by one.
Recommended: How to Create a Virtual Host in XAMPP For PHP in Windows
Create a Database and Table
At very first, you have to create a database connection. We will be using the object-oriented approach for connecting the database. Even the entire PHP functionality will be written in the Object Oriented form.
Hence, go to the phpMyAdmin or MySQL terminal. Then create a database there.
CREATE DATABASE php_crud;After creating the database, just create a table. You can dump the below schema there. It will generate the posts table with the specified fields.
CREATE TABLE posts (
id bigint PRIMARY KEY AUTO_INCREMENT NOT NULL,
title varchar(250) DEFAULT NULL,
description text,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;After creating the database and table, you have to create a connection for this project.
Database Connection For PHP Prepared Statement
We already have the DBController.php file. Hence, you need to open it and paste the below snippet.
<?php
class DBController {
public string $hostname;
public string $dbname;
public string $username;
public string $password;
/**
* Constructor
* @param No
*/
function __construct()
{
$this->hostname = "localhost";
$this->dbname = "php_crud";
$this->username = "root";
$this->password = "root";
}
/**
* Create Connection
* @param No
* @return $conn
*/
function connect() {
$conn = new mysqli($this->hostname, $this->username, $this->password, $this->dbname);
if($conn->connect_errno > 0) {
die("database connection failed <br/>" . $conn->connect_error);
}
return $conn;
}
/**
* Close connection
* @param $conn
* @return null
*/
function close($conn) {
$conn->close();
}
}After that, we will be moving to the layouts part of this project.
Recommended: How to Create a CRUD Application in Laravel 10 For Beginners
Create Master Layouts in PHP
Firstly, start with the header.php file. I have added Bootstrap 4 CDN to use the predefined classes for the style.
<!doctype html>
<html lang="en">
<head>
<title>PHP CRUD Application Using Prepared Statements</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous"/>
<style>
label.error {
color: #dc3545;
}
</style>
</head>
<body>
<div class="container-fluid py-3">
<h3 class="text-center font-weight-bold">PHP CRUD App Using Prepared Statement </h3>Next, inside the footer.php put the below snippet. Here, I used jQuery CDN and jQuery Validator. Lastly, I have created the validation rules provided by the jQuery Validator.
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"
integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous">
</script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous">
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.2/jquery.validate.min.js"></script>
<!-- custom script -->
<script>
$(document).ready(function() {
$("#postForm").validate({
rules: {
title: {
required: true,
minlength: 10,
},
description:{
required: true,
minlength: 20,
}
},
messages: {
title: {
required: "Title is required",
minlength: "Title cannot be less than 10 characters"
},
description: {
required: "Description is required",
minlength: "Description cannot be less than 20 characters"
},
}
});
});
</script>
</body>
</html>After creating the master layouts, let’s create the script file and put the code there.
Recommended: How to Create a CRUD Application in CodeIgniter 4 For Beginners
Create an Index File For Displaying the Data
In the index.php file, we will have a table for displaying the posts. There will be a button to navigate to a new page called create.php file. Add the below snippet there.
<?php
ini_set('display_errors', 1);
include_once ('./master/header.php');
require_once('./DataController.php');
$dataCtrl = new DataController;
$posts = $dataCtrl->posts();
?>
<div class="row py-4">
<div class="col-xl-5 col-xl-5 col-md-5 col-sm-12 col-12 offset-xl-3 offset-lg-3">
<?php echo isset($_SESSION['response'])?
($_SESSION['response']['status'] == "success" ? '<div class="alert alert-success alert-dismissible">
<button type="button" class="close" data-dismiss="alert">× </button>
'.$_SESSION['response']['message'].'</div>' :
'<div class="alert alert-danger alert-dismissible">
<button type="button" class="close" data-dismiss="alert">× </button>
'.$_SESSION['response']['message'].'</div>'):''?>
<?php session_unset(); ?>
</div>
<div class="col-xl-4 col-lg-4 col-md-4 text-right">
<a href="create.php" class="btn btn-primary "> Create Post </a>
</div>
</div>
<div class="table-responsive py-4">
<table class="table table-striped">
<thead class="bg-secondary text-white">
<tr>
<th>Id</th>
<th>Title</th>
<th>Description</th>
<th>Date</th>
<th style="width:16%">Action</th>
</tr>
</thead>
<tbody>
<?php
if(count($posts) > 0):
foreach($posts as $post):?>
<tr>
<td> <?= $post['id']; ?> </td>
<td> <?= stripslashes($post['title']); ?> </td>
<td> <?= stripslashes($post['description']); ?> </td>
<td> <?= $post['created_at']; ?> </td>
<td> <a href="show.php?post=<?= $post['id']; ?>" name="show" class="btn btn-info btn-sm">View</a>
<a href="edit.php?post=<?= $post['id']; ?>" class="btn btn-success btn-sm">Edit</a>
<a href="DataController.php?delete=<?= $post['id'];?>" class="btn btn-danger btn-sm" onclick="return confirm('Do you want delete this record?');">Delete</a>
</td>
</tr>
<?php
endforeach;
else: ?>
<tr>
<td colspan="5" rowspan="2">
<h5 class="text-danger text-center"> Sorry! No post found. </h5>
</td>
<?php endif; ?>
</tbody>
</table>
</div>
<?php
include_once ('./master/footer.php');
?>In the above snippet, at the top, I have included the header and DataController file. We will see the DataController.php file snippet in the next step. Created a table and iterated the array which will come through the DataController file. Also, there are action buttons to View, Edit, and Delete the post.
When you run the index.php file, it will show you the result that looks like the below screenshot.
Yet this time, we haven’t created any posts. Hence, there is no post. Therefore, put the CRUD operations functionality in the DataController.php file.
Recommended: How to Create a CRUD Application Using Ajax in Laravel 10
CRUD in PHP Using Prepared Statement
For the CRUD operations, I will be using a PHP prepared statement. In my last post, I already explained the benefits of using a prepared statement. So, we have already created the DataController.php file for handling all the requests and functional parts. Hence, add the below code. I will explain each and every function.
<?php
ini_set('display_errors', '1');
session_start();
require_once ('./DBController.php');
$dataController = new DataController;
$redirctUrl = "http://localhost/php/crud/index.php";
// Create => if request has post method and create action
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (isset($_POST['create'])) {
$response = $dataController->store($_POST);
$_SESSION['response'] = $response;
header('Location:'.$redirctUrl);
}
}
// Update => if request has post method and update action
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (isset($_POST['update'])) {
$response = $dataController->update($_POST);
$_SESSION['response'] = $response;
header('Location:'.$redirctUrl);
}
}
// Delete => if request has get method and delete action
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
if (isset($_GET['delete'])) {
$response = $dataController->delete($_GET);
$_SESSION['response'] = $response;
header('Location:'.$redirctUrl);
}
}
class DataController {
public $conn;
public $dbController;
public $request = [];
public $response = [];
function __construct()
{
$this->dbController = new DBController();
$this->conn = $this->dbController->connect();
$this->request = $_POST;
}
/**
* Create Post
* @param $request
* @return response array
*/
function store($request):array {
$inputs = $this->sanitizeInputs($request);
$stmt = $this->conn->prepare("INSERT INTO posts (title, description) VALUES (?, ?)");
$stmt->bind_param("ss", $inputs['title'], $inputs['description']);
if ($stmt->execute()) {
$this->response['status'] = "success";
$this->response['message'] = "Success! Post created.";
}
else {
$this->response['status'] = "failed";
$this->response['message'] = "Failed! Post not created.";
}
$stmt->close();
$this->dbController->close($this->conn);
return $this->response;
}
/**
* Sanitize inputs
* @param $request
* @return $inputs[]
*/
function sanitizeInputs($request):array {
$inputs = [];
$inputs['title'] = trim(filter_var($this->conn->real_escape_string($request['title']), FILTER_SANITIZE_STRING));
$inputs['description'] = trim(filter_var($this->conn->real_escape_string($request['description']), FILTER_SANITIZE_STRING));
return $inputs;
}
/**
* @param NO
* @return $response:array
*/
function posts():array {
$stmt = $this->conn->prepare("SELECT id, title, description, created_at, updated_at FROM posts ORDER BY id DESC");
if ($stmt->execute()) {
$result = $stmt->get_result();
if($result->num_rows > 0) {
$this->response = $result->fetch_all(MYSQLI_ASSOC);
}
}
$stmt->close();
$this->dbController->close($this->conn);
return $this->response;
}
/**
* @param $id
* @return $response:array
*/
function post($id):array {
try {
// sanitize param
$this->checkParam($id);
// prepared statement
$stmt = $this->conn->prepare("SELECT id, title, description, created_at, updated_at FROM posts WHERE id = ?");
$stmt->bind_param("i", $id);
if($stmt->execute()) {
$result = $stmt->get_result();
if($result->num_rows > 0) {
$this->response = $result->fetch_assoc();
}
}
return $this->response;
}
catch (Exception $e) {
echo $e->getMessage();
}
}
/**
* Sanitize param
* @param $param
* @return int $param
*/
function checkParam($param):int {
return (int)filter_var($this->conn->real_escape_string($param), FILTER_SANITIZE_STRING);
}
/**
* Update Post
* @param $request
* @return response array
*/
function update($request):array {
$inputs = $this->sanitizeInputs($request);
$id = $this->checkParam($request['id']);
$stmt = $this->conn->prepare("UPDATE posts SET title = ?, description = ? WHERE id = ?");
$stmt->bind_param("ssi", $inputs['title'], $inputs['description'], $id);
if ($stmt->execute()) {
$this->response['status'] = "success";
$this->response['message'] = "Success! Post updated.";
}
else {
$this->response['status'] = "failed";
$this->response['message'] = "Failed! Post not updated.";
}
$stmt->close();
$this->dbController->close($this->conn);
return $this->response;
}
/**
* Delete Post
* @param $request
* @return response array
*/
function delete($request):array {
$id = $this->checkParam($request['delete']);
// prepared statement
$stmt = $this->conn->prepare("DELETE FROM posts WHERE id = ?");
$stmt->bind_param("i", $id);
if($stmt->execute()) {
$result = $stmt->get_result();
$this->response['status'] = "success";
$this->response['message'] = "Success! Post deleted.";
}
else {
$this->response['status'] = "failed";
$this->response['message'] = "Failed! Post not deleted.";
}
$stmt->close();
$this->dbController->close($this->conn);
return $this->response;
}
}Create Post
To create a post, click on the Create Post button at the top right in the index file. It will open a new page (create.php). But, before that, you have to add the below snippet in the create.php file.
<?php
include_once ('./master/header.php');
?>
<div class="row">
<div class="col-xl-12 text-right">
<a href="./index.php" class="btn btn-primary">Back to Post </a>
</div>
</div>
<div class="row py-5">
<div class="col-xl-6 col-lg-6 col-md- 6 col-sm-12 col-12 m-auto">
<form action="DataController.php" method="POST" id="postForm">
<div class="card">
<div class="card-header">
<h5 class="card-title"> Create Post </h5>
</div>
<div class="card-body">
<div class="form-group">
<label> Title </label>
<input type="text" class="form-control" name="title" id="title" placeholder="Post title" />
</div>
<div class="form-group">
<label> Description </label>
<textarea class="form-control" id="description" name="description" placeholder="Post description"></textarea>
</div>
</div>
<div class="card-footer">
<button type="submit" class="btn btn-success" name="create"> Save </button>
</div>
</div>
</form>
</div>
</div>
<?php
include_once ('./master/footer.php');
?>In the above snippet, there is a form with two inputs. The form has an action to submit the data with the validation. For the form validation, I have used here the client-side validation using jQuery. You can use advanced validation as per the inputs like email, password, etc.
Here, when you try to submit the form without filling the title and description. It will show the validation error message. This is basic validation, you can make it more advanced as per the inputs.
Now, try creating a post by filling up the required details.
After creating the post, it will redirect you to the index.php file with the success response.
Show Post
For the post detail, you have to click on the view button inside the Action column. So, to display the post you have to add the below snippet in the show.php file.
<?php
include_once ('./master/header.php');
require_once ('./DataController.php');
// get query string of post id
if ($_SERVER['REQUEST_METHOD'] == "GET" && $_GET['post']) {
$dataController = new DataController;
$post = $dataController->post($_GET['post']);
}
?>
<div class="row">
<div class="col-xl-12 text-right">
<a href="./index.php" class="btn btn-primary">Back to Post </a>
</div>
</div>
<div class="row py-5">
<div class="col-xl-6 col-lg-6 col-md- 6 col-sm-12 col-12 m-auto">
<div class="card">
<div class="card-header">
<h5 class="card-title"> Post Detail </h5>
</div>
<div class="card-body">
<div class="form-group">
<label> Title </label>
<input type="text" readonly class="form-control" placeholder="Post title" value="<?= stripslashes($post['title']) ?? "" ?>" />
</div>
<div class="form-group">
<label> Description </label>
<textarea class="form-control" readonly placeholder="Post description"><?= stripslashes($post['description']) ?? "" ?></textarea>
</div>
</div>
</div>
</div>
</div>
<?php
include_once ('./master/footer.php');
?>Here is the result of the post detail with readonly inputs and there is no button inside the form.
Update Post
For updating the post, firstly, click on the Edit option available in the index file. It will load the post to the edit.php file. Here is the snippet for the edit.php file.
<?php
include_once ('./master/header.php');
require_once ('./DBController.php');
require_once ('./DataController.php');
if ($_SERVER['REQUEST_METHOD'] == "GET" && $_GET['post']) {
$dataCtrl = new DataController;
$post = $dataCtrl->post($_GET['post']);
}
?>
<div class="row">
<div class="col-xl-12 text-right">
<a href="./index.php" class="btn btn-primary">Back to Post </a>
</div>
</div>
<div class="row py-5">
<div class="col-xl-6 col-lg-6 col-md- 6 col-sm-12 col-12 m-auto">
<form action="DataController.php" method="POST" id="postForm">
<input type="hidden" name="id" value="<?= $post['id'];?>" />
<div class="card">
<div class="card-header">
<h5 class="card-title"> Update Post </h5>
</div>
<div class="card-body">
<div class="form-group">
<label> Title </label>
<input type="text" class="form-control" name="title" placeholder="Post title" value="<?= stripslashes($post['title']) ?? "" ?>" />
</div>
<div class="form-group">
<label> Description </label>
<textarea class="form-control" name="description" placeholder="Post description"><?= stripslashes($post['description']) ?? "" ?></textarea>
</div>
</div>
<div class="card-footer">
<button type="submit" class="btn btn-success" name="update"> Update </button>
</div>
</div>
</form>
</div>
</div>
<?php
include_once ('./master/footer.php');
?>This file will read the query string and then will send it to the DataController class. We have already written the function to sanitize the query string properly in the controller file.
Check the result and try to update the post.
Here, I have updated the description and after that, I will click on the update button.
After updating the post, let me show you the database table. Here, inside the posts table, there are three posts. The noticeable point is the description has a single quote while updating the post. In the database, it is converted into the escaped string.
Delete Post
For deleting the post, we not need any view (HTML) file. I have added a javascript confirm dialog to ask for deleting the post. The prepared statement for post deleting is already inside the DataController.php class.
When you will confirm, it will delete the post and show you the success message.
Conclusion
Finally, we created the PHP crud using a prepared statement. For the input escaping characters, we used the real_escape_string() function along with filter_var(). There are other functions available in PHP to sanitize and validate the inputs. I used the Object-Oriented approach for the CRUD operations. This is a basic demo for enhancing the security level of your PHP code. You can make it more clear and advanced as per your requirement.
Leave a Reply