Re: postgresql csv import

On 13/09/2021 23:07, ourdiaspora wrote:
> Readers,
>
> Please could someone help to explain why the following code fails:
>
> <?php
> 	$uploaddir = '~/tmp/cpac/cpactmp/';
> 	$uploadfile = $uploaddir . basename($_FILES['userfile']['name']);
> 	$dbconnect = pg_connect("dbname=cpacweb user=cpaca");
> 	$targetfilepath = $uploaddir . $uploadfile;
> 	if(isset($_POST["submit"])){
> 		$csvfile=$_FILES["userfile"]["tmp_name"];
> 		if($_FILES["userfile"]["tmp_name"] > 0) {
> 			$csvfileopen=fopen($csvfile, "r");
> 			while(($csvfiledata=fgetcsv($csvfileopen, 10000, ","))){
> 				$csvinsert = "INSERT into somedata(name, id, emailaddress, groupname) VALUES ('".$csvfiledata[0]."', '".$csvfiledata[1]."', '".$csvfiledata[2]."', '".$csvfiledata[3]."')";
> 				}
> 			}
> 			fclose($csvfileopen);
> 		}
> 	$query = "SELECT * FROM somedata";
> 	$result = pg_exec($dbconnect, $query);
> 	echo "Number of rows: " . pg_numrows($result);
> 	pg_freeresult($result);
> 	pg_close($dbconnect);
> ?>
>
> The trial csv file contains 2 rows of data, but the query result is zero.

It looks like you're creating the insert query string, but you're never 
doing anything with it, so the database _will_ be empty!

If I may make some suggestions:

You're checking to see if $_POST["submit"] is set, but you're not 
actually using anything from the POST data, you're directly using 
$_FILES instead, so it might be better checking that. Also, when you do 
end up executing the query, I'd avoid doing that from within a loop a 
single insert at a time, as that may be very slow. It's generally faster 
to do bulk inserts with your database. Finally, you're creating the 
insert query using standard string concatenation, with no sanitisation 
against bad input. Consider using PDO, which should automatically 
protect against SQL injection. It does also appear to have a method 
pgsqlCopyFromFile which might do what you're doing here.

-- 
Ashley Sheridan
https://www.ashleysheridan.co.uk