CODEWITHSUNDEEP
phppostgresqlcsvpdoinsert-into
ASan
ASan

Reputation: 53

Insert CSV line by line Into Postgres using PHP PDO

I am trying to have the user upload a file and then have the file be inserted into a postgres database line by line.

I have this code and I am getting the following error with my syntax:

Array ( [0] => 42601 [1] => 7 [2] => ERROR: syntax error at or near ""INSERT INTO "" LINE 1: "INSERT INTO "public"."tblSedimentGrabEvent" VALUES ('A1000'... ^ )

I have been looking at the PHP PDO manual and the Postgres site as well as searching for the error code but still no success. I know its probably something small but its got me stumped.

<?php
try { 
    $db = new PDO("pgsql:dbname=name;host=host","user","pass");
}   
catch(PDOException $e) {
    echo $e->getMessage();
}

if ($_FILES["file"]["error"] > 0)
{
    echo "Error: " . $_FILES["file"]["error"] . "<br>";
}
else
{
    echo "Uploaded Successfully!<br>";
    echo "File Name: " . $_FILES["file"]["name"] . "<br>";
    echo "Type: " . $_FILES["file"]["type"] . "<br>";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
    echo "Temp file: " . $_FILES["file"]["tmp_name"]. "<br>";
    {
        move_uploaded_file($_FILES["file"]["tmp_name"],
        "D:/Apapub/public_html/databases/B13/temp/" . $_FILES["file"]["name"]);
        echo "Stored in: " . "D:\Apapub\public_html\databases\B13\temp" . $_FILES["file"]["name"]. "<br>";
    }

 }

$file_handle = fopen("D:/Apapub/public_html/databases/B13/temp/grabevents.txt", "r");

while (!feof($file_handle) ) {

    $text_file = fgetcsv($file_handle);

    print_r($text_file);

    $query=<<<eof
"INSERT INTO "public"."tblSedimentGrabEvent" VALUES ('$text_file[0]','$text_file[1]','$text_file[2]','$text_file[3]','$text_file[4]','$text_file[5]','$text_file[6]','$text_file[7]','$text_file[8]','$text_file[9]','$text_file[10]','$text_file[11]','$text_file[12]','$text_file[13]','$text_file[14]','$text_file[15]','$text_file[16]','$text_file[17]','$text_file[18]','$text_file[19]','$text_file[20]','$text_file[21]','$text_file[22]','$text_file[23]')"
    eof;
    $sth = $db->query($query);
}
if (!$sth) {
    echo "<p>\nPDO::errorInfo():\n</p>";
    print_r($db->errorInfo());
} 
fclose($file_handle);

echo "<p><b>Records Imported</b></p>";

?>

Here is the sample array:

A1000,24,M,28/Mar/2013,11:45:27,1,SCCWRP,Singel Van Veen,33.1234,-118.523,AGPS,12,cm,Olive Green,Sand,None,Yes,No,No,No,None,Test Record # 1,No,22/May/2013
A1000,24,M,28/Mar/2013,11:45:27,1,SCCWRP,Singel Van Veen,33.1334,-118.543,AGPS,12,cm,Olive Green,Sand,None,Yes,No,No,No,None,Test Record # 1,No,15/May/2013
A1000,24,M,28/Mar/2013,11:56:33,2,SCCWRP,Single Van Veen,33.1635,-118.593,AGPS,12,cm,Olive Green,Sand,None,No,Yes,No,No,None,Test Record #2,No,15/May/2013
A1000,24,M,28/Mar/2013,12:06:33,3,SCCWRP,Single Van Veen,33.1534,-118.563,AGPS,12,cm,Olive Green,Sand,None,No,No,Yes,No,None,Test Record #3,No,15/May/2013
A1000,24,M,28/Mar/2013,12:12:33,4,SCCWRP,Single Van Veen,33.1034,-118.503,AGPS,12,cm,Olive Green,Sand,None,No,No,No,yes,None,Test Record #4,No,15/May/2013

I was successful using the COPY command but my supervisor wants it to parse in line by line to run some checks later on

!!ANSWER!!

I dont know how to give props to @Spudley but he helped me to answer the question.

After doing what he said I found that I dont need quotes around the query ( I guess its becuase I'm using heredoc?).

This worked (but: edit by Craig Ringer, this is still dangerously insecure and should never be used, do not copy this as example code):

$query=<<<eof
INSERT INTO "public"."tblSedimentGrabEvent" VALUES ('$text_file[0]','$text_file[1]','$text_file[2]','$text_file[3]','$text_file[4]','$text_file[5]','$text_file[6]','$text_file[7]','$text_file[8]','$text_file[9]','$text_file[10]','$text_file[11]','$text_file[12]','$text_file[13]','$text_file[14]','$text_file[15]','$text_file[16]','$text_file[17]','$text_file[18]','$text_file[19]','$text_file[20]','$text_file[21]','$text_file[22]','$text_file[23]')
eof;
$sth = $db->query($query);

Thanks Again!

Upvotes: 3

Views: 3099

Answers (2)

ASan
ASan

Reputation: 53

I got the answer from this page: PHP PDO: Can I bind an array to an IN() condition?

My code ended up like so:

$file_handle = fopen("D:/Apapub/public_html/databases/B13/temp/grabevents.txt", "r");

while (!feof($file_handle) ) {

$text_file = fgetcsv($file_handle);

foreach($text_file as &$val)
$val=$db->quote($val);

$data = implode(',',$text_file);

$q = $db->prepare('Insert Into "public"."tblSedimentGrabEvent" Values ('.$data.')');

$q->execute();
}

And now it works perfectly while hopefully preventing SQL Injections.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324511

Doing text substitution into SQL is error prone, insecure, hard to debug, and terrible style. Use parameterised statements, either with PDO or with pg_query_params. Your "answer" is totally insecure, please do not use it.

That will not only fix your problem, it will protect you against a whole class of severe security vulnerabilities including SQL injection. See wikipedia, bobby tables and the PHP manual.

See this prior answer too.

While you're at it, consider using COPY, which is vastly more efficient, to just stream the CSV directly into a table in PostgreSQL. See pg_copy_from or the lower level pg_put_line function.

Upvotes: 1

Related Questions