How to Insert Data from a Form Using PHP and MySQL
* Web 2.0 University is supported by it's audience. If you purchase through links on our site, we may earn an affiliate commision.
Inserting data from an HTML form into a MySQL database is a simple task but it’s loaded with potential security issues for the beginner developer and using things like HTML Special Chars can help but we can take it a step further.
Whether you’re creating a user registration form, a contact form, or any other data collection form, understanding how to handle form submissions securely and efficiently is essential.
This blog post will walk you through two examples:
- A basic example of inserting data from a form into a MySQL database using PHP.
- A more secure and complete example that follows best practices, ensuring your application remains safe from common vulnerabilities like SQL injection.
Outline:
Introduction
- Why inserting data from forms into MySQL is important.
- Common use cases for form submission.
Minimum Example of Inserting Data from a Form with PHP and MySQL
- Basic form HTML.
- Simple PHP code to handle form submission.
- Inserting data into MySQL using procedural MySQLi.
A More Complete and Safer Example Following Best Practices
- Why security is important (SQL injection risk).
- Using prepared statements with MySQLi.
- Validating user input before inserting into the database.
Conclusion
- Summary of key points.
- Importance of following best practices.
1. Introduction
Handling form data in web applications is a critical task, and PHP offers powerful tools to insert that data into a MySQL database. Whether you’re capturing user details, product information, or feedback, getting form data into a database is the backbone of most dynamic websites.
In this post, we will show how to insert data from a form into a MySQL database using PHP, starting with a basic example and then moving toward a more secure and practical implementation using best practices.
2. Minimum Example of Inserting Data from a Form with PHP and MySQL
In this section, we’ll cover a simple example where we collect user input through an HTML form and insert it into a MySQL database using PHP.
HTML Form Example
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Simple Form</title>
</head>
<body>
<form action="insert.php" method="post">
Name: <input type="text" name="name"><br><br>
Email: <input type="email" name="email"><br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
In this basic form:
- The
actionattribute in the form points toinsert.php, which will handle the form submission. - The
methodis set toPOST, which is the preferred method for transmitting sensitive or large data.
PHP Script to Insert Data into MySQL (insert.php)
<?php
// Database connection settings
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get form data
$name = $_POST['name'];
$email = $_POST['email'];
// SQL query to insert data
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
In this simple example:
- We establish a connection to the database using MySQLi.
- The user’s form data (
nameandemail) is collected using the$_POSTsuperglobal array. - An SQL
INSERTquery is built by directly embedding the form data in the query string. - Data is inserted into a table called
userswith columnsnameandemail.
What’s Missing?
While this method works, it’s not safe. Directly inserting user input into a query makes the code vulnerable to SQL injection attacks. This is where the next example comes into play.
3. A More Complete and Safer Example Following Best Practices
When dealing with user input, security is paramount. One of the major concerns when inserting data into a database is SQL injection, where attackers can manipulate your query and potentially gain access to your database. To prevent this, we use prepared statements. Prepared statements not only secure your queries but also make your code more maintainable and readable.
Updated HTML Form
We’ll use the same HTML form as before, but with improved PHP to handle data submission.
PHP Script Using Prepared Statements (insert.php)
<?php
// Database connection settings
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// Create connection using MySQLi
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get form data
$name = $_POST['name'];
$email = $_POST['email'];
// Validate user input (basic validation)
if (empty($name) || empty($email)) {
die("All fields are required.");
}
// Prepare an SQL statement with placeholders
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
// Bind parameters (s for string, i for integer, etc.)
$stmt->bind_param("ss", $name, $email);
// Execute the statement
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt->error;
}
// Close statement and connection
$stmt->close();
$conn->close();
?>
In this secure example:
- Prepared Statements: We use
prepare()to set up the SQL query with placeholders (?). This ensures that user input cannot alter the SQL structure, making it resistant to SQL injection. - Binding Parameters:
bind_param()binds the user input to the SQL query in a type-safe way (in this case,ssfor two string inputs). - Validation: Before running the query, we perform basic validation to ensure that the form fields are not empty.
Why Prepared Statements are Safer
Prepared statements keep your SQL logic and user input separate. Unlike in the first example, where user input is directly embedded in the SQL query, prepared statements handle user data safely, preventing SQL injection.
4. Best Practices for Form Data Insertion
- Always Validate User Input: Never assume user input is safe. Basic validation (like checking for empty fields or email format) can prevent bad data from entering your database.
- Use Prepared Statements: Prepared statements are one of the best defenses against SQL injection, ensuring that user input is treated as data, not as part of the SQL query.
- Sanitize Data: While prepared statements handle most SQL injection concerns, you should still sanitize user input (e.g., trimming whitespace or removing unwanted characters) to ensure data quality.
- Error Handling: Always handle database connection and query errors to avoid breaking the user experience. Display friendly error messages when things go wrong.
- Secure Database Credentials: Don’t hard-code sensitive credentials like database passwords in your PHP scripts. Instead, use environment variables or a configuration file stored outside the web root.
5. Conclusion
Inserting form data into a MySQL database using PHP is a common task, but it’s critical to do it correctly and securely. While the basic example shows how easy it is to insert data, it’s essential to use prepared statements and validate input for better security and performance. By following best practices, you ensure that your application remains safe from vulnerabilities like SQL injection while providing a smooth user experience.
By incorporating prepared statements and input validation, you’ll be well on your way to writing secure PHP applications that interact with MySQL databases effectively.