What are the best practices for handling CSV data in PHP before importing it into a MySQL table?

When handling CSV data in PHP before importing it into a MySQL table, it is important to properly parse and sanitize the data to prevent SQL injection attacks and ensure data integrity. One way to achieve this is by using the fgetcsv() function to read the CSV file line by line and then inserting the data into the MySQL table using prepared statements to bind parameters securely. 

<?php
// Open the CSV file for reading
$csvFile = fopen('data.csv', 'r');

// Loop through each line of the CSV file
while (($data = fgetcsv($csvFile)) !== FALSE) {
    // Sanitize and validate the data as needed
    $column1 = filter_var($data[0], FILTER_SANITIZE_STRING);
    $column2 = filter_var($data[1], FILTER_SANITIZE_STRING);
    
    // Prepare a SQL statement with placeholders
    $stmt = $pdo->prepare("INSERT INTO table_name (column1, column2) VALUES (?, ?)");
    
    // Bind parameters to the placeholders and execute the statement
    $stmt->execute([$column1, $column2]);
}

// Close the CSV file
fclose($csvFile);
?>

Keywords

CSV PHP MySQL data handling import

Related Questions