What are some best practices for passing values from a CSV file to a PHP script for database insertion?

When passing values from a CSV file to a PHP script for database insertion, it is important to properly sanitize and validate the data to prevent SQL injection attacks or other security vulnerabilities. One common approach is to read the CSV file line by line, parse the values, and then insert them into the database using prepared statements to securely handle the data. 

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

// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Prepare the SQL statement
$stmt = $pdo->prepare("INSERT INTO mytable (column1, column2, column3) VALUES (?, ?, ?)");

// Loop through each line in the CSV file
while (($data = fgetcsv($file)) !== false) {
    // Sanitize and validate the data
    $value1 = filter_var($data[0], FILTER_SANITIZE_STRING);
    $value2 = filter_var($data[1], FILTER_VALIDATE_INT);
    $value3 = filter_var($data[2], FILTER_SANITIZE_STRING);

    // Bind the values to the prepared statement and execute
    $stmt->execute([$value1, $value2, $value3]);
}

// Close the file and database connection
fclose($file);
$pdo = null;
?>

Keywords

CSV file PHP script database insertion data validation error handling

Related Questions