How to add an external variable with BULK INSERT DATA in php?

Question

I have a large csv file (over 100,000 lines) encoded in UTF-8-BOM that looks like this:

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

And a table in my SQL Server database with these columns :

storenumber | stylecode | colour | size | units | timestamp

I use Bulk Insert Data to load the file at once, but I would like to add my $timestamp variable to each line inserted in my table but it doesn't work... How do I do it?

 <?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

You need to consider the following:

• Always try to use parameters in your statement (of course if possible) or carefully sanitize the input data. In this specific case you may try to check if the input file exists and after that inject filename in your statement.
• The input data doesn't match the table definition, so you may try to import the data in a temporary table.
• Pass datetime values as text using an unambiguous format (e.g. 2020-07-25T12:00:00).

The following example is a possible solution to your problem:

Table:

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

PHP script:

<?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-d\TH: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

Like I mention in the comment, you can't parametrise a BULK INSERT statement. Therefore you have to use Dynamic SQL. I don't know/write PHP, however, the above I can clearly see is wide open to injection, as you're simply injecting the file's name into the SQL statement. You need to fix that, and parametrise your statement How to: Perform Parameterized Queries.

As for the SQL, this will look something like this:

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;