MySQL Prepared PDO statement is removing backslashes

Question

Using Symfony 3, XAMPP and php 5.6

I have a form where a user can upload a .csv file, and the rows should be inserted into a table

        if ($form->isSubmitted() && $form->isValid()) {

            $file = $form->get('memberList')->getData();

            $db = $this->getDoctrine()->getEntityManager()->getConnection();
            $sql = "LOAD DATA INFILE '$file' INTO TABLE `MemberList` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;";
            $stmt = $db->prepare($sql);
            $stmt->execute();
        }

I then get the following error

An exception occurred while executing 'LOAD DATA INFILE 'C:\xampp\tmp\php198.tmp' INTO TABLE MemberList CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES;':

SQLSTATE[HY000]: General error: 29 File 'C:\xampp\mysql\data\xampp mpphp198.tmp' not found (Errcode: 22 "Invalid argument")

I stepped through the code in a debugger, and the file is successfully being uploaded to C:\xampp\tmp\php198.tmp. I've just no idea why it's looking for a file at C:\xampp\mysql\data\xampp mpphp198.tmp, which seems to be causing the issue as nothing exists there

Update: tried it with LOAD DATA LOCAL INFILE and now get

SQLSTATE[HY000]: General error: 7890 Can't find file 'C:xampp mpphp2F24.tmp'.

The temporary file being created is actually C:\xampp\tmp\php2F24.tmp

Update 2: I'm an idiot, it's clearly escaping the backslashes - but why? I thought preparing with PDO would leave these intact

Always use forward slashes ('/') in file paths. Yes, windows is quite happy about it and it saves so much hassle with all the standard linux stuff that is run on windows. The only issue is that you need to enclose the filepaths in double quotes when using the command line. — Ryan Vincent
Nice to know windows won't complain (I thought it would), but since I'm not writing the file location myself I think I'll stick with my solution below — p3tch

p3tch p3tch · Accepted Answer · 2016-11-07T00:35:16

Managed to get it working with this

        $db = $this->getDoctrine()->getEntityManager()->getConnection();
        $sql = "LOAD DATA INFILE ? INTO TABLE member_list CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;";
        $pdo = $db->prepare($sql);
        $pdo->execute(array($file));
        $pdo->closeCursor();

MySQL Prepared PDO statement is removing backslashes

1 Answers