How to escape this mysql query in php script?

Question

This query works directly in mysql:

LOAD DATA LOCAL INFILE '/var/www/html/parsecsv/test.csv'
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

I'm need to use it in a php script. I'm trying to escape the special characters. But I can't get it to work. Here's the script:

$query = "LOAD DATA LOCAL INFILE '/var/www/html/parsecsv/test.csv'
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
ESCAPED BY '\"'
LINES TERMINATED BY '\\n'
IGNORE 1 LINES";
$link = mysqli_connect("localhost", "wptest", "p@ssw0rd");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
if (!mysqli_select_db($link, 'wptest')) {
    echo 'Could not select database';
    exit;
}
/* Select queries return a resultset */
if ($result = mysqli_query($link, $query)) {
    echo "It's working";
    /* free result set */
    mysqli_free_result($result);
}

Use a heredoc string like described [here](http://stackoverflow.com/a/5673478/2629998). — , Mar 17 '15 at 11:20
I tried using heredoc. It's still not working. Maybe the problem isn't escaping. — anwarabq, Mar 17 '15 at 12:18
@anwarabq to figuire out if the problem is escaping or not - use this query in phpmyadmin to check the query first. — Alex, Mar 17 '15 at 13:22
If it's not the query, be sure the web server can read the file. — OddEssay, Mar 17 '15 at 13:27
My question has been answered. But my script is still not working. Maybe I need to create another question. — anwarabq, Mar 17 '15 at 16:08
The query works directly in mysql terminal. But when I run the script it doesn't pass the last if statement and the table is still empty. — anwarabq, Mar 17 '15 at 16:11

score 0 · Accepted Answer · answered Mar 17 '15 at 13:19

0

Here is the trick I use often:

$query = <<<QUERY
LOAD DATA LOCAL INFILE '/var/www/html/parsecsv/test.csv'
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
QUERY;

answered Mar 17 '15 at 13:19

Alex

16,739
1
28
51

How to escape this mysql query in php script?

1 Answers1