PDO::pgsqlCopyToFile

(PHP 5 >= 5.3.3, PHP 7, PHP 8)

PDO::pgsqlCopyToFile — Copie des données d'une table dans un fichier

Description

public PDO::pgsqlCopyToFile(
    string $table_name,
    string $filename,
    string $delimiter = "\t",
    string $null_as = "\\\\N",
    string $fields = ?
): bool

Copie des données d'une table dans un fichié spécifié par filename en utilisant delimiter comme délimiteur de champs et la liste fields

Liste de paramètres

table_name: chaîne de caractères contenant le nom de la table.
filename: Nom de fichier où exporter les données.
delimiter: Délimiteur utilisé dans le fichier spécifié par filename.
null_as: Comment interpréter les valeurs nulles.
fields: Liste de champs à insérer.

Valeurs de retour

Retourne true en cas de succès, ou false si une erreur survient.

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 1 note

down

sage at sage dot sk ¶

2 months ago

So I have been trying to use COPY (SELECT...) TO STDOUT WITH (FORMAT CSV, HEADER) but:

- PDO::query() uses prepare, which COPY does not support
- PDO::exec() does not return results
- PDO::pgsqlCopyToFile() does not support additional parameters, only delimiter and nulls (and columns)

But do you know what it does support? SQL Injections!

https://github.com/php/php-src/blob/7dfbf4d1b74b5e629f2331261944fa072a92e1cb/ext/pdo_pgsql/pgsql_driver.c#L870

spprintf(&query, 0, "COPY %s TO STDIN WITH DELIMITER E'%c' NULL AS E'%s'", ...

There is no escaping around any of that, so you can call this method like so:

<?php
$db->pgsqlCopyToFile("(select * from t1 cross join t2 using (whatever))", '/tmp/test.csv', ',', '\' header--');
?>

and that actually produces:

COPY (select...) TO STDIN WITH DELIMITER E',' NULL AS E'' header--

It still uses local filesystem, which I can live with, but at least I don't have to dance around the CSV headers.

If anyone knows of a better way to do this without copying the (in my case gigantic) resultset into array and fputcsv(), I'm all ears. And I've tried every combination with pg_query and pg_copy_to to no avail.

＋add a note