CODEWITHSUNDEEP
phppostgresqlosx-mavericks
TeW
TeW

Reputation: 3

pg_query_params() and placeholders

Thank you for reading this. Would you provide some pointers as to how to understand and solve the two questions below? I am new to PHP and PostgreSQL. OS: OSX 10.9.5, PHP: 5.6.4, PostgreSQL: 9.4.0

  1. I would like to know the reason placing a placeholder in pg_query_params() gives the syntax error. How would you solve this? The reason is I want a user to specify a table name when copying additional data to the table.

Here is a snippet:

$params = array(strval($_POST['que_str2']));
$myresult = pg_query_params($connection, 'copy $1 from stdin', $params);
echo "<br />\n$params[0]   <br />\n";

Warning: pg_query_params(): Query failed: ERROR: syntax error at or near "$1" LINE 1: copy $1 from stdin ^ in /Library/WebServer/Documents/test.php

my_table
  1. Similar to the question above. I would like to solve the error because I want the user to type in a table name and a file path when copying the data from the file.

Here is a snippet:

$params = array(strval($_POST['que_str2']), strval($_POST['que_str1']));
$myresult = pg_query_params($connection, 'copy $1 from $2 DELIMITERS \',\' CSV', $params);
echo "<br />\n$params[0]   $params[1]<br />\n";

Warning: pg_query_params(): Query failed: ERROR: syntax error at or near "$1" LINE 1: copy $1 from $2 DELIMITERS ',' CSV ^ in /Library/WebServer/Documents/test.php on line 20

my_table /Library/WebServer/Documents/data2.csv

==

Upvotes: 0

Views: 543

Answers (1)

rchang
rchang

Reputation: 5246

As far as I know, PostgreSQL does not support parameterized COPY statements. Per the PG 9.4 docs on prepared statements (http://www.postgresql.org/docs/9.4/static/sql-prepare.html), only SELECT, INSERT, UPDATE, DELETE, or VALUES statements can be parameterized.

What you may have to do is construct the COPY statement and interpolate the parameters yourself (with appropriate scrubbing and escaping to mitigate injection, etc.) and then submit it with pg_query instead.

Upvotes: 2

Related Questions