mysql - PHP 上传 CSV 并注入(inject)现有的 mySQL 表日期格式

Question

因此，我更改了脚本以使用 LOAD DATA LOCAL INFILE，因为它对我来说应该更快。问题是我无法正确填充我的日期。

我现在有了这个。 (我尝试了 50 种不同的方法)

if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
    echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
    echo $target_file;

    // Get file for processing
    $sql = "
            LOAD DATA LOCAL INFILE \"C:/GIT/Production/csv/$target_file\"
            INTO TABLE exp_subs
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '\n'
            (id,site_id,hash,member_id,card_id,coupon_id,plan_id,voucher_id,entry_id,recurring_interval,recurring_interval_count,amount,total_amount,total_cycles,status,renew,trial_starts_at,trial_ends_at,retry_count,retry_date,shipping_name,shipping_address1,shipping_address2,shipping_address3,shipping_city,shipping_state,shipping_zip,shipping_country,notes,test_mode,created_by,updated_by,canceled_by,next_billing_at,last_billed_at,@var1,@var2,@var3,@var4,@var5,@var6)
            SET ended_at = STR_TO_DATE(@var1, '%Y-%m-%d %H:%i:%S'),
            canceled_at = STR_TO_DATE(@var2, '%Y-%m-%d %H:%i:%S'),
            expires_at = STR_TO_DATE(@var3, '%Y-%m-%d %H:%i:%S'),
            created_at = STR_TO_DATE(@var4, '%Y-%m-%d %H:%i:%S'),
            updated_at = STR_TO_DATE(@var5, '%Y-%m-%d %H:%i:%S'),
            deleted_at = STR_TO_DATE(@var6, '%Y-%m-%d %H:%i:%S')
        ";

        mysql_query($sql) or die(mysql_error());
} else {
    echo "Sorry, there was an error uploading your file.";
}

这会提取除日期之外的所有内容。日期仍以“0000-00-00 00:00:00”的形式输入。如果我使用像这样的更简单的版本，也会发生同样的事情:

if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
    echo "The file ". basename( $_FILES["fileToUpload"]["name"]). " has been uploaded.";
    echo $target_file;

    // Get file for processing
    $sql = "
        LOAD DATA LOCAL INFILE \"C:/GIT/Production/csv/$target_file\"
        INTO TABLE exp_subs
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
    ";

    mysql_query($sql) or die(mysql_error());
} else {
    echo "Sorry, there was an error uploading your file.";
}

CSV 文件以以下格式保存日期:“4/21/2016 7:00”

如何将它们从上传的文件转换为我需要的方式？

Answer 1

我希望这对其他人有帮助......

这就是我最终得到的对我有用的结果。可能有一种更简单的方法，但我无法让另一种方法正常工作。

本质上，我必须创建一个行变量来跳过 CSV 文件的第一行(即标题)，然后，由于我发送了多个日期，所以我必须分解 CSV 中的每一列或字段，以便我可以获取作为日期传入的字段并从中创建一个日期字段，将它们转换为所需的格式，然后在将其添加到 SQL INSERT 语句之前覆盖该变量。

最后，我总是以插入数千行结束。 CSV 文件出现大量空白 (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,) 行并添加它们，所以我最终检查了空白，如果空白则结束 while 循环。

<?php
    include "connection.php"; //Connect to Database

    //Upload File
    if (isset($_POST['submit'])) {
        if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
            echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
        }

        $csvRow = 1;

        //Import uploaded file to Database
        $csvHandle = fopen($_FILES['filename']['tmp_name'], "r");

        $csvKey = 0; // Set the parent array key to 0

        while (($csvData = fgetcsv($csvHandle)) !== FALSE) {

            if($csvRow == 1){
                $csvRow++;
                continue;
            }

            $c = count($csvData); // Count the total keys in each row

            // 33-40 are dates that need to be converted
            $dateArray = array(33, 34, 35, 36, 37, 38, 39, 40);

            // ## Flag variable ##########
            $empty = true;

            for ($x = 0; $x < $c; $x++) { // Loop through the "columns"

                // ## Test each value ##########
                $empty = $empty && (empty($csvData[$x]));

                if(in_array($x, $dateArray)){
                    $date = date_create($csvData[$x]);
                    $FormattedDate = date_format($date,"Y-m-d H:i:s");
                    $csvData[$x] = $FormattedDate;
                }
            }

            $sql = "" . "INSERT INTO exp_subs(id,site_id,hash,member_id,card_id,coupon_id,plan_id,voucher_id,entry_id,recurring_interval,recurring_interval_count,amount,total_amount,total_cycles,status,renew,trial_starts_at,trial_ends_at,retry_count,retry_date,shipping_name,shipping_address1,shipping_address2,shipping_address3,shipping_city,shipping_state,shipping_zip,shipping_country,notes,test_mode,created_by,updated_by,canceled_by,next_billing_at,last_billed_at,ended_at,canceled_at,expires_at,created_at,updated_at,deleted_at) VALUES('$csvData[0]','$csvData[1]','$csvData[2]','$csvData[3]','$csvData[4]','$csvData[5]','$csvData[6]','$csvData[7]','$csvData[8]','$csvData[9]','$csvData[10]','$csvData[11]','$csvData[12]','$csvData[13]','$csvData[14]','$csvData[15]','$csvData[16]','$csvData[17]','$csvData[18]','$csvData[19]','$csvData[20]','$csvData[21]','$csvData[22]','$csvData[23]','$csvData[24]','$csvData[25]','$csvData[26]','$csvData[27]','$csvData[28]','$csvData[29]','$csvData[30]','$csvData[31]','$csvData[32]','$csvData[33]','$csvData[34]','$csvData[35]','$csvData[36]','$csvData[37]','$csvData[38]','$csvData[39]','$csvData[40]')";
            $result = mysql_query($sql) or die(mysql_error());

            // ## Stop loop if all empty ##########
            if ($empty) {
                break;
            }
            $csvKey++;
        }

        fclose($csvHandle);

        print "<br />Import done";
    } else {
        print "Upload new csv by browsing to file and clicking on Upload<br />\n";
        print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
        print "File name to import:<br />\n";
        print "<input size='50' type='file' name='filename'><br />\n";
        print "<input type='submit' name='submit' value='Upload'></form>";
    }
?>