1 /*
2 * psql - the PostgreSQL interactive terminal
3 *
4 * Copyright (c) 2000-2016, PostgreSQL Global Development Group
5 *
6 * src/bin/psql/copy.c
7 */
8 #include "postgres_fe.h"
9 #include "copy.h"
10
11 #include <signal.h>
12 #include <sys/stat.h>
13 #ifndef WIN32
14 #include <unistd.h> /* for isatty */
15 #else
16 #include <io.h> /* I think */
17 #endif
18
19 #include "libpq-fe.h"
20 #include "pqexpbuffer.h"
21
22 #include "settings.h"
23 #include "common.h"
24 #include "prompt.h"
25 #include "stringutils.h"
26
27
28 /*
29 * parse_slash_copy
30 * -- parses \copy command line
31 *
32 * The documented syntax is:
33 * \copy tablename [(columnlist)] from|to filename [options]
34 * \copy ( query stmt ) to filename [options]
35 *
36 * where 'filename' can be one of the following:
37 * '<file path>' | PROGRAM '<command>' | stdin | stdout | pstdout | pstdout
38 * and 'query' can be one of the following:
39 * SELECT | UPDATE | INSERT | DELETE
40 *
41 * An undocumented fact is that you can still write BINARY before the
42 * tablename; this is a hangover from the pre-7.3 syntax. The options
43 * syntax varies across backend versions, but we avoid all that mess
44 * by just transmitting the stuff after the filename literally.
45 *
46 * table name can be double-quoted and can have a schema part.
47 * column names can be double-quoted.
48 * filename can be single-quoted like SQL literals.
49 * command must be single-quoted like SQL literals.
50 *
51 * returns a malloc'ed structure with the options, or NULL on parsing error
52 */
53
54 struct copy_options
55 {
56 char *before_tofrom; /* COPY string before TO/FROM */
57 char *after_tofrom; /* COPY string after TO/FROM filename */
58 char *file; /* NULL = stdin/stdout */
59 bool program; /* is 'file' a program to popen? */
60 bool psql_inout; /* true = use psql stdin/stdout */
61 bool from; /* true = FROM, false = TO */
62 };
63
64
65 static void
free_copy_options(struct copy_options * ptr)66 free_copy_options(struct copy_options * ptr)
67 {
68 if (!ptr)
69 return;
70 free(ptr->before_tofrom);
71 free(ptr->after_tofrom);
72 free(ptr->file);
73 free(ptr);
74 }
75
76
77 /* concatenate "more" onto "var", freeing the original value of *var */
78 static void
xstrcat(char ** var,const char * more)79 xstrcat(char **var, const char *more)
80 {
81 char *newvar;
82
83 newvar = psprintf("%s%s", *var, more);
84 free(*var);
85 *var = newvar;
86 }
87
88
89 static struct copy_options *
parse_slash_copy(const char * args)90 parse_slash_copy(const char *args)
91 {
92 struct copy_options *result;
93 char *token;
94 const char *whitespace = " \t\n\r";
95 char nonstd_backslash = standard_strings() ? 0 : '\\';
96
97 if (!args)
98 {
99 psql_error("\\copy: arguments required\n");
100 return NULL;
101 }
102
103 result = pg_malloc0(sizeof(struct copy_options));
104
105 result->before_tofrom = pg_strdup(""); /* initialize for appending */
106
107 token = strtokx(args, whitespace, ".,()", "\"",
108 0, false, false, pset.encoding);
109 if (!token)
110 goto error;
111
112 /* The following can be removed when we drop 7.3 syntax support */
113 if (pg_strcasecmp(token, "binary") == 0)
114 {
115 xstrcat(&result->before_tofrom, token);
116 token = strtokx(NULL, whitespace, ".,()", "\"",
117 0, false, false, pset.encoding);
118 if (!token)
119 goto error;
120 }
121
122 /* Handle COPY (query) case */
123 if (token[0] == '(')
124 {
125 int parens = 1;
126
127 while (parens > 0)
128 {
129 xstrcat(&result->before_tofrom, " ");
130 xstrcat(&result->before_tofrom, token);
131 token = strtokx(NULL, whitespace, "()", "\"'",
132 nonstd_backslash, true, false, pset.encoding);
133 if (!token)
134 goto error;
135 if (token[0] == '(')
136 parens++;
137 else if (token[0] == ')')
138 parens--;
139 }
140 }
141
142 xstrcat(&result->before_tofrom, " ");
143 xstrcat(&result->before_tofrom, token);
144 token = strtokx(NULL, whitespace, ".,()", "\"",
145 0, false, false, pset.encoding);
146 if (!token)
147 goto error;
148
149 /*
150 * strtokx() will not have returned a multi-character token starting with
151 * '.', so we don't need strcmp() here. Likewise for '(', etc, below.
152 */
153 if (token[0] == '.')
154 {
155 /* handle schema . table */
156 xstrcat(&result->before_tofrom, token);
157 token = strtokx(NULL, whitespace, ".,()", "\"",
158 0, false, false, pset.encoding);
159 if (!token)
160 goto error;
161 xstrcat(&result->before_tofrom, token);
162 token = strtokx(NULL, whitespace, ".,()", "\"",
163 0, false, false, pset.encoding);
164 if (!token)
165 goto error;
166 }
167
168 if (token[0] == '(')
169 {
170 /* handle parenthesized column list */
171 for (;;)
172 {
173 xstrcat(&result->before_tofrom, " ");
174 xstrcat(&result->before_tofrom, token);
175 token = strtokx(NULL, whitespace, "()", "\"",
176 0, false, false, pset.encoding);
177 if (!token)
178 goto error;
179 if (token[0] == ')')
180 break;
181 }
182 xstrcat(&result->before_tofrom, " ");
183 xstrcat(&result->before_tofrom, token);
184 token = strtokx(NULL, whitespace, ".,()", "\"",
185 0, false, false, pset.encoding);
186 if (!token)
187 goto error;
188 }
189
190 if (pg_strcasecmp(token, "from") == 0)
191 result->from = true;
192 else if (pg_strcasecmp(token, "to") == 0)
193 result->from = false;
194 else
195 goto error;
196
197 /* { 'filename' | PROGRAM 'command' | STDIN | STDOUT | PSTDIN | PSTDOUT } */
198 token = strtokx(NULL, whitespace, ";", "'",
199 0, false, false, pset.encoding);
200 if (!token)
201 goto error;
202
203 if (pg_strcasecmp(token, "program") == 0)
204 {
205 int toklen;
206
207 token = strtokx(NULL, whitespace, ";", "'",
208 0, false, false, pset.encoding);
209 if (!token)
210 goto error;
211
212 /*
213 * The shell command must be quoted. This isn't fool-proof, but
214 * catches most quoting errors.
215 */
216 toklen = strlen(token);
217 if (token[0] != '\'' || toklen < 2 || token[toklen - 1] != '\'')
218 goto error;
219
220 strip_quotes(token, '\'', 0, pset.encoding);
221
222 result->program = true;
223 result->file = pg_strdup(token);
224 }
225 else if (pg_strcasecmp(token, "stdin") == 0 ||
226 pg_strcasecmp(token, "stdout") == 0)
227 {
228 result->file = NULL;
229 }
230 else if (pg_strcasecmp(token, "pstdin") == 0 ||
231 pg_strcasecmp(token, "pstdout") == 0)
232 {
233 result->psql_inout = true;
234 result->file = NULL;
235 }
236 else
237 {
238 /* filename can be optionally quoted */
239 strip_quotes(token, '\'', 0, pset.encoding);
240 result->file = pg_strdup(token);
241 expand_tilde(&result->file);
242 }
243
244 /* Collect the rest of the line (COPY options) */
245 token = strtokx(NULL, "", NULL, NULL,
246 0, false, false, pset.encoding);
247 if (token)
248 result->after_tofrom = pg_strdup(token);
249
250 return result;
251
252 error:
253 if (token)
254 psql_error("\\copy: parse error at \"%s\"\n", token);
255 else
256 psql_error("\\copy: parse error at end of line\n");
257 free_copy_options(result);
258
259 return NULL;
260 }
261
262
263 /*
264 * Execute a \copy command (frontend copy). We have to open a file (or execute
265 * a command), then submit a COPY query to the backend and either feed it data
266 * from the file or route its response into the file.
267 */
268 bool
do_copy(const char * args)269 do_copy(const char *args)
270 {
271 PQExpBufferData query;
272 FILE *copystream;
273 struct copy_options *options;
274 bool success;
275
276 /* parse options */
277 options = parse_slash_copy(args);
278
279 if (!options)
280 return false;
281
282 /* prepare to read or write the target file */
283 if (options->file && !options->program)
284 canonicalize_path(options->file);
285
286 if (options->from)
287 {
288 if (options->file)
289 {
290 if (options->program)
291 {
292 fflush(stdout);
293 fflush(stderr);
294 errno = 0;
295 copystream = popen(options->file, PG_BINARY_R);
296 }
297 else
298 copystream = fopen(options->file, PG_BINARY_R);
299 }
300 else if (!options->psql_inout)
301 copystream = pset.cur_cmd_source;
302 else
303 copystream = stdin;
304 }
305 else
306 {
307 if (options->file)
308 {
309 if (options->program)
310 {
311 fflush(stdout);
312 fflush(stderr);
313 errno = 0;
314 disable_sigpipe_trap();
315 copystream = popen(options->file, PG_BINARY_W);
316 }
317 else
318 copystream = fopen(options->file, PG_BINARY_W);
319 }
320 else if (!options->psql_inout)
321 copystream = pset.queryFout;
322 else
323 copystream = stdout;
324 }
325
326 if (!copystream)
327 {
328 if (options->program)
329 psql_error("could not execute command \"%s\": %s\n",
330 options->file, strerror(errno));
331 else
332 psql_error("%s: %s\n",
333 options->file, strerror(errno));
334 free_copy_options(options);
335 return false;
336 }
337
338 if (!options->program)
339 {
340 struct stat st;
341 int result;
342
343 /* make sure the specified file is not a directory */
344 if ((result = fstat(fileno(copystream), &st)) < 0)
345 psql_error("could not stat file \"%s\": %s\n",
346 options->file, strerror(errno));
347
348 if (result == 0 && S_ISDIR(st.st_mode))
349 psql_error("%s: cannot copy from/to a directory\n",
350 options->file);
351
352 if (result < 0 || S_ISDIR(st.st_mode))
353 {
354 fclose(copystream);
355 free_copy_options(options);
356 return false;
357 }
358 }
359
360 /* build the command we will send to the backend */
361 initPQExpBuffer(&query);
362 printfPQExpBuffer(&query, "COPY ");
363 appendPQExpBufferStr(&query, options->before_tofrom);
364 if (options->from)
365 appendPQExpBufferStr(&query, " FROM STDIN ");
366 else
367 appendPQExpBufferStr(&query, " TO STDOUT ");
368 if (options->after_tofrom)
369 appendPQExpBufferStr(&query, options->after_tofrom);
370
371 /* run it like a user command, but with copystream as data source/sink */
372 pset.copyStream = copystream;
373 success = SendQuery(query.data);
374 pset.copyStream = NULL;
375 termPQExpBuffer(&query);
376
377 if (options->file != NULL)
378 {
379 if (options->program)
380 {
381 int pclose_rc = pclose(copystream);
382
383 if (pclose_rc != 0)
384 {
385 if (pclose_rc < 0)
386 psql_error("could not close pipe to external command: %s\n",
387 strerror(errno));
388 else
389 {
390 char *reason = wait_result_to_str(pclose_rc);
391
392 psql_error("%s: %s\n", options->file,
393 reason ? reason : "");
394 if (reason)
395 free(reason);
396 }
397 success = false;
398 }
399 restore_sigpipe_trap();
400 }
401 else
402 {
403 if (fclose(copystream) != 0)
404 {
405 psql_error("%s: %s\n", options->file, strerror(errno));
406 success = false;
407 }
408 }
409 }
410 free_copy_options(options);
411 return success;
412 }
413
414
415 /*
416 * Functions for handling COPY IN/OUT data transfer.
417 *
418 * If you want to use COPY TO STDOUT/FROM STDIN in your application,
419 * this is the code to steal ;)
420 */
421
422 /*
423 * handleCopyOut
424 * receives data as a result of a COPY ... TO STDOUT command
425 *
426 * conn should be a database connection that you just issued COPY TO on
427 * and got back a PGRES_COPY_OUT result.
428 *
429 * copystream is the file stream for the data to go to.
430 * copystream can be NULL to eat the data without writing it anywhere.
431 *
432 * The final status for the COPY is returned into *res (but note
433 * we already reported the error, if it's not a success result).
434 *
435 * result is true if successful, false if not.
436 */
437 bool
handleCopyOut(PGconn * conn,FILE * copystream,PGresult ** res)438 handleCopyOut(PGconn *conn, FILE *copystream, PGresult **res)
439 {
440 bool OK = true;
441 char *buf;
442 int ret;
443
444 for (;;)
445 {
446 ret = PQgetCopyData(conn, &buf, 0);
447
448 if (ret < 0)
449 break; /* done or server/connection error */
450
451 if (buf)
452 {
453 if (OK && copystream && fwrite(buf, 1, ret, copystream) != ret)
454 {
455 psql_error("could not write COPY data: %s\n",
456 strerror(errno));
457 /* complain only once, keep reading data from server */
458 OK = false;
459 }
460 PQfreemem(buf);
461 }
462 }
463
464 if (OK && copystream && fflush(copystream))
465 {
466 psql_error("could not write COPY data: %s\n",
467 strerror(errno));
468 OK = false;
469 }
470
471 if (ret == -2)
472 {
473 psql_error("COPY data transfer failed: %s", PQerrorMessage(conn));
474 OK = false;
475 }
476
477 /*
478 * Check command status and return to normal libpq state.
479 *
480 * If for some reason libpq is still reporting PGRES_COPY_OUT state, we
481 * would like to forcibly exit that state, since our caller would be
482 * unable to distinguish that situation from reaching the next COPY in a
483 * command string that happened to contain two consecutive COPY TO STDOUT
484 * commands. However, libpq provides no API for doing that, and in
485 * principle it's a libpq bug anyway if PQgetCopyData() returns -1 or -2
486 * but hasn't exited COPY_OUT state internally. So we ignore the
487 * possibility here.
488 */
489 *res = PQgetResult(conn);
490 if (PQresultStatus(*res) != PGRES_COMMAND_OK)
491 {
492 psql_error("%s", PQerrorMessage(conn));
493 OK = false;
494 }
495
496 return OK;
497 }
498
499 /*
500 * handleCopyIn
501 * sends data to complete a COPY ... FROM STDIN command
502 *
503 * conn should be a database connection that you just issued COPY FROM on
504 * and got back a PGRES_COPY_IN result.
505 * copystream is the file stream to read the data from.
506 * isbinary can be set from PQbinaryTuples().
507 * The final status for the COPY is returned into *res (but note
508 * we already reported the error, if it's not a success result).
509 *
510 * result is true if successful, false if not.
511 */
512
513 /* read chunk size for COPY IN - size is not critical */
514 #define COPYBUFSIZ 8192
515
516 bool
handleCopyIn(PGconn * conn,FILE * copystream,bool isbinary,PGresult ** res)517 handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
518 {
519 bool OK;
520 char buf[COPYBUFSIZ];
521 bool showprompt;
522
523 /*
524 * Establish longjmp destination for exiting from wait-for-input. (This is
525 * only effective while sigint_interrupt_enabled is TRUE.)
526 */
527 if (sigsetjmp(sigint_interrupt_jmp, 1) != 0)
528 {
529 /* got here with longjmp */
530
531 /* Terminate data transfer */
532 PQputCopyEnd(conn,
533 (PQprotocolVersion(conn) < 3) ? NULL :
534 _("canceled by user"));
535
536 OK = false;
537 goto copyin_cleanup;
538 }
539
540 /* Prompt if interactive input */
541 if (isatty(fileno(copystream)))
542 {
543 showprompt = true;
544 if (!pset.quiet)
545 puts(_("Enter data to be copied followed by a newline.\n"
546 "End with a backslash and a period on a line by itself, or an EOF signal."));
547 }
548 else
549 showprompt = false;
550
551 OK = true;
552
553 if (isbinary)
554 {
555 /* interactive input probably silly, but give one prompt anyway */
556 if (showprompt)
557 {
558 const char *prompt = get_prompt(PROMPT_COPY);
559
560 fputs(prompt, stdout);
561 fflush(stdout);
562 }
563
564 for (;;)
565 {
566 int buflen;
567
568 /* enable longjmp while waiting for input */
569 sigint_interrupt_enabled = true;
570
571 buflen = fread(buf, 1, COPYBUFSIZ, copystream);
572
573 sigint_interrupt_enabled = false;
574
575 if (buflen <= 0)
576 break;
577
578 if (PQputCopyData(conn, buf, buflen) <= 0)
579 {
580 OK = false;
581 break;
582 }
583 }
584 }
585 else
586 {
587 bool copydone = false;
588
589 while (!copydone)
590 { /* for each input line ... */
591 bool firstload;
592 bool linedone;
593
594 if (showprompt)
595 {
596 const char *prompt = get_prompt(PROMPT_COPY);
597
598 fputs(prompt, stdout);
599 fflush(stdout);
600 }
601
602 firstload = true;
603 linedone = false;
604
605 while (!linedone)
606 { /* for each bufferload in line ... */
607 int linelen;
608 char *fgresult;
609
610 /* enable longjmp while waiting for input */
611 sigint_interrupt_enabled = true;
612
613 fgresult = fgets(buf, sizeof(buf), copystream);
614
615 sigint_interrupt_enabled = false;
616
617 if (!fgresult)
618 {
619 copydone = true;
620 break;
621 }
622
623 linelen = strlen(buf);
624
625 /* current line is done? */
626 if (linelen > 0 && buf[linelen - 1] == '\n')
627 linedone = true;
628
629 /* check for EOF marker, but not on a partial line */
630 if (firstload)
631 {
632 /*
633 * This code erroneously assumes '\.' on a line alone
634 * inside a quoted CSV string terminates the \copy.
635 * http://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@w
636 * rigleys.postgresql.org
637 */
638 if (strcmp(buf, "\\.\n") == 0 ||
639 strcmp(buf, "\\.\r\n") == 0)
640 {
641 copydone = true;
642 break;
643 }
644
645 firstload = false;
646 }
647
648 if (PQputCopyData(conn, buf, linelen) <= 0)
649 {
650 OK = false;
651 copydone = true;
652 break;
653 }
654 }
655
656 if (copystream == pset.cur_cmd_source)
657 {
658 pset.lineno++;
659 pset.stmt_lineno++;
660 }
661 }
662 }
663
664 /* Check for read error */
665 if (ferror(copystream))
666 OK = false;
667
668 /*
669 * Terminate data transfer. We can't send an error message if we're using
670 * protocol version 2.
671 */
672 if (PQputCopyEnd(conn,
673 (OK || PQprotocolVersion(conn) < 3) ? NULL :
674 _("aborted because of read failure")) <= 0)
675 OK = false;
676
677 copyin_cleanup:
678
679 /*
680 * Clear the EOF flag on the stream, in case copying ended due to an EOF
681 * signal. This allows an interactive TTY session to perform another COPY
682 * FROM STDIN later. (In non-STDIN cases, we're about to close the file
683 * anyway, so it doesn't matter.) Although we don't ever test the flag
684 * with feof(), some fread() implementations won't read more data if it's
685 * set. This also clears the error flag, but we already checked that.
686 */
687 clearerr(copystream);
688
689 /*
690 * Check command status and return to normal libpq state.
691 *
692 * We do not want to return with the status still PGRES_COPY_IN: our
693 * caller would be unable to distinguish that situation from reaching the
694 * next COPY in a command string that happened to contain two consecutive
695 * COPY FROM STDIN commands. We keep trying PQputCopyEnd() in the hope
696 * it'll work eventually. (What's actually likely to happen is that in
697 * attempting to flush the data, libpq will eventually realize that the
698 * connection is lost. But that's fine; it will get us out of COPY_IN
699 * state, which is what we need.)
700 */
701 while (*res = PQgetResult(conn), PQresultStatus(*res) == PGRES_COPY_IN)
702 {
703 OK = false;
704 PQclear(*res);
705 /* We can't send an error message if we're using protocol version 2 */
706 PQputCopyEnd(conn,
707 (PQprotocolVersion(conn) < 3) ? NULL :
708 _("trying to exit copy mode"));
709 }
710 if (PQresultStatus(*res) != PGRES_COMMAND_OK)
711 {
712 psql_error("%s", PQerrorMessage(conn));
713 OK = false;
714 }
715
716 return OK;
717 }
718