What are common pitfalls when using PHP to insert data from a form into a MySQL database?

Common pitfalls when using PHP to insert data from a form into a MySQL database include not properly sanitizing user input, leaving the database vulnerable to SQL injection attacks, and not handling errors effectively, which can result in data loss or corruption. To solve these issues, always use prepared statements or parameterized queries to prevent SQL injection, validate and sanitize user input before inserting it into the database, and implement error handling to catch and handle any potential issues that may arise during the insertion process. 

<?php
// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Sanitize user input
$name = mysqli_real_escape_string($conn, $_POST['name']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$message = mysqli_real_escape_string($conn, $_POST['message']);

// Prepare and bind the SQL statement
$stmt = $conn->prepare("INSERT INTO messages (name, email, message) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $message);

// Execute the statement and check for errors
if ($stmt->execute()) {
    echo "New record inserted successfully";
} else {
    echo "Error: " . $stmt->error;
}

// Close the statement and connection
$stmt->close();
$conn->close();
?>

Keywords

PHP MySQL form submission SQL injection data validation

Related Questions