Php postgresql pdo copy from stdin

Question

COPY table_name ( field1, field2, field3) FROM STDIN CSV; 1,2,"qw" 3,4,"as" 5,6,d \. 

How to execute this request using PDO?

Update:

The problem is that the PDO driver executes this request as an operator.
For example, if you paste it into pgAdmin, it causes an error.
I need to execute it in psql :

 C:\Users\User>psql -e -h localhost -U postgres db_name psql (9.1.2) db_name=# COPY table_name ( field1, field2, field3) FROM STDIN CSV; COPY table_name ( field1, field2, field3) FROM STDIN CSV; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1,2,"qw" >> 3,4,"as" >> 5,6,d >> \. 

Answer 1

Thanks to this book

Please note that the feature presented here effectively bypasses the security restrictions that exist for any reason. Your function should check the file path and table provided against the stringent whitelist conditions. This example is also open for SQL injection, as it does not provide a correct indication of its input.

Create a function that executes the COPY

 CREATE OR REPLACE FUNCTION copy_from_csv_ignoring_security(table_name text, table_fieds text, file_path text, oids boolean DEFAULT false, header boolean DEFAULT false, delimeter text DEFAULT ','::text, "null" text DEFAULT ''::text, quote text DEFAULT '"'::text, escape text DEFAULT '"'::text, force_not_null text DEFAULT ''::text) RETURNS void AS $BODY$ declare statement text; begin statement := 'COPY ' || table_name || ' (' || table_fieds || ') ' || 'FROM ''' || file_path || ''' WITH '; IF oids THEN statement := statement || 'OIDS '; end if; statement := statement || 'DELIMITER ''' || delimeter || ''' '; statement := statement || 'NULL ''' || "null" || ''' CSV '; IF header THEN statement := statement || 'HEADER '; end if; statement := statement || 'QUOTE ''' || "quote" || ''' '; statement := statement || 'ESCAPE ''' || "escape" || ''' '; IF force_not_null <> '' THEN statement := statement || 'FORCE NOT NULL ''' || force_not_null || ''' '; end if; execute statement; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; 

Grant feature rights

 revoke all on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) from public; grant execute on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) to db_user; 

Run from PHP

 $dbh->exec('SELECT copy_from_csv_ignoring_security(...)'); 

===== If version> = 9.1.7 of the trick above does not work. =====

Decision:

create a file . pgpass (avoid the password hint) in the home directory of the user who runs this script.

 #.pgpass contents (chmod 600 - requred) host:port:db_name:user_name:password 

create a php function that executes meta-command

 function executeMetaCommand($dbUser, $dbName, $dbPort, $command) { $command = sprintf( "psql -U %s -p %s -d %s -f - <<EOT\n%s\nEOT\n", $dbUser, $dbPort, $dbName, $command ); $streams = array( array('pipe', 'r'),// stdin array('pipe', 'w'),// stdout array('pipe', 'w') // stderr ); $process = proc_open($command, $streams, $pipes); if (!is_resource($process)) { throw new Exception("Cannot open process:\n$command"); } else { list(, $stdout, $stderr) = $pipes; $error = stream_get_contents($stderr); fclose($stderr); if (strlen($error) > 0) { throw new Exception("Process error:\n$error"); } else { $output = stream_get_contents($stdout); fclose($stdout); $returnCode = proc_close($process); if ($returnCode === -1) { throw new Exception("Process was completed incorrectly:\n$output"); } else { return array( $returnCode, $output ); } } } } //usage: $command = sprintf("\\copy table(field1, field2) FROM '%s' WITH CSV", $filePath); executeMetaCommand('postgres', 'test_db', '5432', $command); 

Answer 2

Just put the input lines in the csv file, say input.csv , and then try the following:

 <?php $dbh = new PDO($dsn,$username,$pw); $num_inserted=$dbh->exec("\copy table_name(field1,field2,field3) from /path/to/input.csv"); if($num_inserted===false) { die("Unable to insert rows into table_name from input.csv\n"); } echo("Inserted $num_inserted rows.\n"); ?>