Bulletproof Your Database: How to Submit HTML Form Data to MySQL Using PHP While Safeguarding Against SQL Injection Attacks
Protect your database from malicious attacks by learning how to securely submit HTML form data to MySQL using PHP and safeguard against SQL injection.
Introduction
Are you tired of hackers infiltrating your database like a thief in the night? Have you ever wished you could protect your sensitive information from malicious attacks and sleep soundly knowing your data is secure? Well, you're in luck because this tutorial is about making your dreams come true!
In this exciting guide, we'll show you how to securely submit HTML form data to MySQL using PHP while foiling SQL injection attacks. We'll take you on a wild ride through the world of web security, where you'll learn to protect your database like a fearless knight on a noble quest.
So put on your armor, grab your trusty sword, and join us on this epic adventure to bulletproof your database and safeguard your sensitive information from those sneaky hackers. By the end of this guide, you'll have all the tools you need to keep your database safe and sound. Are you ready to take on the challenge? Let's go!
In the previous blog, we learned how to execute queries in PHP. In this blog, we learn how to send the data from HTML form to the MySQL database. For that, we need to create a new HTML file. Let's name it "form.html".
Creating an HTML Form
The first step is to create an HTML form with input fields for the data you want to send to the database. In this example, we'll create a form with four fields: name, phone, email, and message.
<!DOCTYPE html>
<html>
<head>
<title>Form Submission</title>
</head>
<body>
<h1>Form Submission</h1>
<form method="post" action="submit.php">
<label for="name">Name</label>
<input type="text" name="name" id="name" required>
<label for="phone">Phone</label>
<input type="text" name="phone" id="phone" required>
<label for="email">Email</label>
<input type="email" name="email" id="email" required>
<label for="message">Message</label>
<textarea name="message" id="message" required></textarea>
<input type="submit" name='submit' value="Submit">
</form>
</body>
</html>
This form uses the POST method to submit the data to a PHP script called submit.php. Each input field has a name attribute that corresponds to a column in the MySQL table, and the required attribute ensures that the field must be filled out before the form can be submitted.
Creating a PHP Script
The next step is to create a PHP script that will handle the form submission and insert the data into the MySQL database.
Create a new file called submit.php and add the following code:
include 'database.php';
// Check if the form is submitted
if(isset($_POST['submit'])) {
// Retrieve form data
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$message = $_POST['message'];
// Insert data into MySQL database
$sql = "INSERT INTO data (name, phone, email, message) VALUES ('$name', '$phone', '$email', '$message')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
}
If the data are submitted, the message "New record created successfully" will be displayed."
That's it! You've successfully sent data from an HTML form to a MySQL database with PHP. Note that this is a simple example and there are many ways to improve the security and reliability of this code, such as using prepared statements to prevent SQL injection attacks.
Preventing SQL attack and displaying the error message in same page.
Here, we will learn, how to display the success and error messages on the HTML page. For this, let's create a new page, form.php
<?php
include 'database.php';
// Initialize message variable
$message = "";
// Check if the form is submitted
if(isset($_POST['submit'])) {
// Retrieve form data
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$message_text = $_POST['message'];
// Sanitize form data
$name = mysqli_real_escape_string($conn, $name);
$phone = mysqli_real_escape_string($conn, $phone);
$email = mysqli_real_escape_string($conn, $email);
$message_text = mysqli_real_escape_string($conn, $message_text);
// Validate form data
if (empty($name) || empty($email) || empty($message_text)) {
$message = "Please fill in all required fields";
} else if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$message = "Invalid email format";
} else {
// Insert data into MySQL database
$sql = "INSERT INTO data (name, phone, email, message) VALUES ('$name', '$phone', '$email', '$message_text')";
if (mysqli_query($conn, $sql)) {
$message = "New record created successfully";
} else {
$message = "Error: " . $sql . "<br>" . mysqli_error($conn);
}
}
}
?>
In the above code, we've initialized a $message variable to store the success or error message. If the form is submitted and there is an error, the error message is stored in the $message variable. If the form is submitted successfully, the success message is stored in the $message variable.
On the HTML page, we've added a check to display the message only if it's not empty. If the message is not empty, it's displayed using the <p> tag.
The mysqli_real_escape_string() function is used to escape special characters in a string that is going to be used in an SQL statement. The function takes two parameters: the database connection and the string to be escaped. It returns the escaped string.
This function is used to prevent SQL injection attacks. An SQL injection attack occurs when a hacker inserts SQL code into a form field or URL query string to try to modify or retrieve data from a database. The mysqli_real_escape_string() function ensures that any special characters in the form data are escaped so that they are not interpreted as SQL code.
In the example code, the mysqli_real_escape_string() function is used to escape the special characters in the form data before inserting it into the MySQL database. This is done for each of the form fields.