Insert CSV line by line Into Postgres using PHP PDO

Question

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!

try echoing the query rather than running it; you may be able to see the error in the query more clearly if you can see the actual query string. If that doesn't help, try pasting it from there into your favourite DB GUI for further analysis. — Spudley
– Spudley, Commented May 17, 2013 at 22:45
Thanks @Spudley that helped me to see the error. I echo'd the query then copied it into PGAdminIII and ran it. turns out I dont need any quotes around the query when I use heredoc. this worked: — ASan
– ASan, Commented May 17, 2013 at 22:53
$query=<<<eof INSERT INTO "public"."tblSedimentGrabEvent" VALUES ('$text_file[0]','$text_file[1]'etc..) — ASan
– ASan, Commented May 17, 2013 at 22:56

Community · Accepted Answer · 2023-11-17 20:08:53Z

1

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.

edited Nov 17, 2023 at 20:08

CommunityBot

11 silver badge

answered May 18, 2013 at 2:19

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:05:24Z

0

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.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered May 24, 2013 at 17:36

ASan

531 silver badge8 bronze badges

5 Comments

Craig Ringer Over a year ago

I do not understand why you don't just use COPY for this. It's tried, tested, simple, and it works.

ASan Over a year ago

that is what I originally was using but the lead on this project wants to enter the data line by line so I can check the data as its entered. For example, in the submitted column, if the entry equals yes then that line will not be entered into the database.

Craig Ringer Over a year ago

OK, so you want to filter the input. That's reasonable. Sometimes what I do when I want to do so is COPY the entire input into a staging TEMPORARY table then use SQL to merge it with the final table. This is often a lot faster and simpler, but I guess it depends on whether you're better with PHP or SQL.

ASan Over a year ago

I'm not really good at either, the temporary table idea sounds like a good way to get it done. This is my first "big" project programming so this is a learning experience. Thanks for your help.

cazort Over a year ago

If and when COPY is allowed, then it is often superior, but there are circumstances when it is not allowed. Such setups are common on web servers because exec() calls in PHP are frequently disallowed and even if they are, authentication to the psql interface may not be allowed. From a security standpoint, allowing these things on a web server is usually not best practice. So there can be a need for a line-by-line approach from STDIN.

Collectives™ on Stack Overflow

Insert CSV line by line Into Postgres using PHP PDO

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related