What best practices should be followed when importing data from a CSV file into a MySQL database using PHP to ensure data integrity and security?

When importing data from a CSV file into a MySQL database using PHP, it is important to sanitize the data to prevent SQL injection attacks and ensure data integrity. One way to do this is by using prepared statements to bind parameters and validate the data before inserting it into the database. 

<?php

// Connect to MySQL database
$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);
}

// Read CSV file
$csvFile = fopen('data.csv', 'r');

// Prepare statement
$stmt = $conn->prepare("INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");

// Bind parameters
$stmt->bind_param('sss', $column1, $column2, $column3);

// Insert data into database
while (($data = fgetcsv($csvFile)) !== FALSE) {
    $column1 = $data[0];
    $column2 = $data[1];
    $column3 = $data[2];
    
    $stmt->execute();
}

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

?>

Keywords

CSV MySQL data integrity security PHP functions

Related Questions