如何在php中添加带有BULK INSERT DATA的外部变量

Question

我有一个用UTF-8-BOM编码的大csv文件(超过100000行(，看起来像这样：

27336;00203-AND1;90-BLACK;9.5;2
27336;00203-ET1;90-BLACK;10;1
27336;00203-ET1;90-BLACK;12;1
...

以及我的SQL Server数据库中的一个表，其中包含以下列：

storenumber | stylecode | colour | size | units | timestamp

我使用Bulk Insert Data一次加载文件，但我想将$timestamp变量添加到表中插入的每一行，但它不起作用。。。我该怎么做？

<?php
include("connexion.php");
ini_set('max_execution_time', 32400);
$timestamp= date("y-m-d H:i");

$csv= "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.txt";
$query = "BULK INSERT dbo.Y2_Inventory 
FROM '$csv' 
WITH (
FIELDTERMINATOR = ';', 
ROWTERMINATOR = 'n',
ERRORFILE = 'myfileerror.log'
)";
$stmt = $conn->query( $query );     
if (!$stmt) { echo $conn->error;} 

$query2 = "UPDATE dbo.Y2_Inventory SET timestamp = ? WHERE timestamp IS NULL";
$stmt = $conn->query( $query2 );     


echo "good";        
?>

Answer 1

您需要考虑以下因素：

• 始终尝试在语句中使用参数(当然，如果可能的话(或仔细清理输入数据。在这种特定情况下，您可以尝试检查输入文件是否存在，然后在语句中插入文件名
• 输入数据与表定义不匹配，因此可以尝试在临时表中导入数据
• 使用明确的格式(例如2020-07-25T12:00:00(将datetime值作为文本传递

以下示例是您的问题的可能解决方案：

表：

CREATE TABLE Y2_Inventory (
storenumber int,
stylecode nvarchar(50),
colour nvarchar(50),
size numeric(10, 1), 
units int,
[timestamp] datetime
)

PHP脚本：

<?php
//
include("connexion.php");
ini_set('max_execution_time', 32400);
// CSV file
$csv = "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.txt";  
$err = "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.err";
if (!file_exists($csv)) {
die("CSV file not exists.");    
}

// Time stamp
$timestamp = date("Y-m-dTH:i:s");
// INSERT Data
try {
$query = "
SET NOCOUNT ON;

SELECT storenumber, stylecode, colour, size, units
INTO #t
FROM Y2_Inventory
WHERE 1 = 0;

BULK INSERT #t 
FROM '$csv' 
WITH (
ERRORFILE = '$err',
FIELDTERMINATOR = ';', 
ROWTERMINATOR = 'n'
);

INSERT INTO Y2_Inventory (storenumber, stylecode, colour, size, units, [timestamp])
SELECT storenumber, stylecode, colour, size, units, ?
FROM #t;

DROP TABLE #t;
";
$stmt = $conn->prepare($query);     
$stmt->bindParam(1, $timestamp, PDO::PARAM_STR);
$stmt->execute();
echo "OK";        
} catch (PDOException $e) {
die ("Error executing query. ".$e->getMessage());
}
?>

Answer 2

正如我在评论中提到的，您不能参数化BULK INSERT语句。因此，您必须使用动态SQL。我不知道/写PHP，但是，我可以清楚地看到，上面是wide对注入的开放，因为您只需将文件名注入到SQL语句中。您需要来修复此问题，并参数化您的语句如何：执行参数化查询。

至于SQL，它看起来像这样：

DECLARE @FilePath nvarchar(256); --This would be your parameter, so might not bneed a declaration
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'BULK INSERT dbo.Y2_Inventory 
FROM N''' + REPLACE(@FilePath,'''','''''') + N'''
WITH(FIELDTERMINATOR = '';'',
FIELDTERMINATOR = ''n'',
ERRORFILE = ''myfileerror.log'');';
--PRINT @SQL; --Your Best Friend
EXEC sys.sp_executesql @SQL;