CSV Import in WordPress: Switch from mysql_query to PDO

by

Summary

A WordPress plugin developer attempted to import CSV files into MySQL using the deprecated mysql_query() function combined with LOAD DATA LOCAL INFILE, but failed because the uploaded file was never actually saved to the filesystem or properly referenced. The code attempted to use a literal string 'file.csv' instead of the temporary file path from $_FILES. This represents a classic multi-layered failure involving deprecated APIs, missing file handling, and fundamental misunderstandings of how LOAD DATA LOCAL INFILE operates.

Root Cause

The failure stems from three critical issues:

  • Deprecated MySQL extension: The code uses mysql_query(), which was deprecated in PHP 5.5 and removed entirely in PHP 7.0. The user claims to use PHP 8+, making this code completely non-functional.
  • No file upload handling: The code never saves the uploaded CSV file. $_FILES['uploadcsv'] contains a temporary file path, but the code ignores this entirely.
  • Literal string as filename:Using 'file.csv' as a literal string tells MySQL to look for a file literally named “file.csv” on the server’s filesystem, not the uploaded file.

The actual uploaded file exists temporarily in the system’s temp directory (e.g., /tmp/phpXXXX), accessible via $_FILES['uploadcsv']['tmp_name'], but this path must be explicitly moved or referenced.

Why This Happens in Real Systems

This pattern repeats frequently because:

  • Legacy tutorial contamination: Many outdated PHP tutorials still reference mysql_* functions, leading junior developers to copy code without checking PHP version compatibility.
  • LOAD DATA LOCAL INFILE confusion: Developers often assume this command automatically handles form uploads, not understanding it requires a valid server-side file path.
  • WordPress context misleading: The mention of “globally available database credentials” in WordPress creates false confidence that authentication is handled, but the core issue remains: no valid file path exists.
  • Insufficient error handling: Without proper error reporting, developers fail to see the cascade of failures (function not exists → file not found → query fails silently).

Real-World Impact

The impact of this bug includes:

  • Complete query failure: The script fails at the first line because mysql_query() does not exist in PHP 8.
  • No data imported: Zero rows are inserted into the database.
  • Security vulnerabilities: If the developer attempts “fixes” by disabling security flags (like local_infile), they may expose the database to risks.
  • Production downtime: A plugin with this code in production would silently fail, leaving users confused why their imports never work.

Example or Code (if necessary and relevant)

The broken code attempts:

This fails because mysql_query() does not exist, 'file.csv' is not a valid path, and no file handling occurs.

How Senior Engineers Fix It

Senior engineers address this comprehensively:

  • Use PDO or MySQLi: Replace deprecated functions with modern APIs that support prepared statements and proper error handling.
  • Handle the file upload properly: Use move_uploaded_file() to persist the uploaded file from the temporary location.
  • Use LOAD DATA LOCAL INFILE correctly: Reference the actual saved file path in the query.
  • Implement proper error handling: Catch and log exceptions rather than letting failures silently pass.

A correct approach uses PDO with proper file handling:

prefix}table_name 
                FIELDS TERMINATED BY ',' 
                ENCLOSED BY '\"' 
                LINES TERMINATED BY '\\n' 
                IGNORE 1 ROWS";

        $result = $wpdb->query($sql);

        if ($result !== false) {
            echo "Imported successfully.";
        } else {
            echo "Import failed: " . $wpdb->last_error;
        }
    }
}
?>

Alternatively, use fgetcsv() for safer, more portable parsing without requiring LOCAL INFILE privileges.

Why Juniors Miss It

Junior developers commonly miss this bug because:

  • Copy-paste from unreliable sources: Stack Overflow answers from 2010 still appear in search results and contain deprecated code.
  • No version awareness: Developers assume PHP code from any era still works.
  • Misunderstanding “LOCAL”: The word “LOCAL” in LOAD DATA LOCAL INFILE tricks juniors into thinking it handles local (client-side) uploads automatically.
  • Insufficient testing: Without creating a test CSV and running the code, the failure goes unnoticed until production.
  • Missing debug skills: Not checking $_FILES, not enabling error reporting, and not verifying function existence before calling them.

Leave a Comment