proposal: using PQexecParams in psql (using variables as real params)
Hello
I propose to add possibility to use psql variables as real query
parameters. The goal of this proposal is simplification of creating
psql based commands. Current using of psql variables based on
substitution has large area of using, but has some risks. a) there are
possible sql injection, b) we have to have to do some special (not too
much readable quoting) - see Bruce's book, psql chapter.
I checked, so this doesn't need much work. Attachment contains a prototype.
[pavel@nemesis ~]$ echo "select upper(:message)" | psql -r -v
message="Pavel's cat" postgres
upper
─────────────
PAVEL'S CAT
(1 row)
[pavel@nemesis ~]$ psql -v message="Pavel's cat" postgres
psql (8.5devel)
Type "help" for help.
postgres=# \pexec
Separately passing parameters is on.
postgres=# select upper(:message);
upper
─────────────
PAVEL'S CAT
(1 row)
This small feature simplify integration psql to shell environment.
comments, notes??
Regards
Pavel Stehule
Attachments:
parametrisedexec.difftext/x-patch; charset=US-ASCII; name=parametrisedexec.diffDownload
*** ./command.c.orig 2009-10-13 23:04:01.000000000 +0200
--- ./command.c 2009-11-15 21:53:25.418639611 +0100
***************
*** 1127,1132 ****
--- 1127,1152 ----
free(pattern);
}
+ /* \pexec -- pass parameters separately */
+ else if (strcmp(cmd, "pexec") == 0)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (opt)
+ pset.use_parameters = ParseVariableBool(opt);
+ else
+ pset.use_parameters = !pset.use_parameters;
+ if (!pset.quiet)
+ {
+ if (pset.use_parameters)
+ puts(_("Separately passing parameters is on."));
+ else
+ puts(_("Separately passing parameters is off."));
+ }
+ free(opt);
+ }
+
/* \! -- shell escape */
else if (strcmp(cmd, "!") == 0)
{
*** ./common.c.orig 2009-04-11 20:38:54.000000000 +0200
--- ./common.c 2009-11-15 22:28:25.036648416 +0100
***************
*** 852,858 ****
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
--- 852,876 ----
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! if (!pset.use_parameters)
! results = PQexec(pset.db, query);
! else
! {
! /* use PQexecParams function instead */
! results = PQexecParams(pset.db, query,
! pset.nparameters,
! NULL,
! pset.parameters,
! NULL,
! NULL,
! 0);
! if (pset.nparameters)
! {
! pset.nparameters = 0;
! pset.maxparameters = 0;
! free(pset.parameters);
! }
! }
/* these operations are included in the timing result: */
ResetCancelConn();
*** ./psqlscan.l.orig 2009-11-15 21:28:55.000000000 +0100
--- ./psqlscan.l 2009-11-15 22:06:04.814641928 +0100
***************
*** 693,701 ****
if (value)
{
! /* It is a variable, perform substitution */
! push_new_buffer(value);
! /* yy_scan_string already made buffer active */
}
else
{
--- 693,716 ----
if (value)
{
! if (pset.use_parameters)
! {
! char buffer[10];
!
! /* add new parameter */
! if (pset.nparameters == pset.maxparameters)
! {
! pset.maxparameters += 100;
! pset.parameters = malloc(sizeof(char *) * pset.maxparameters);
! }
! pset.parameters[pset.nparameters++] = value;
! sprintf(buffer, "$%d", pset.nparameters);
! push_new_buffer(buffer);
! }
! else
! /* It is a variable, perform substitution */
! push_new_buffer(value);
! /* yy_scan_string already made buffer active */
}
else
{
*** ./settings.h.orig 2009-02-26 17:02:38.000000000 +0100
--- ./settings.h 2009-11-15 21:54:23.321640498 +0100
***************
*** 111,116 ****
--- 111,120 ----
const char *prompt2;
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
+ bool use_parameters;
+ int nparameters;
+ int maxparameters;
+ const char **parameters;
} PsqlSettings;
extern PsqlSettings pset;
*** ./startup.c.orig 2009-04-05 06:19:58.000000000 +0200
--- ./startup.c 2009-11-15 22:45:02.654643678 +0100
***************
*** 122,127 ****
--- 122,131 ----
pset.queryFoutPipe = false;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.use_parameters = false;
+ pset.parameters = NULL;
+ pset.maxparameters = 0;
+ pset.nparameters = 0;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
***************
*** 322,327 ****
--- 326,332 ----
{"port", required_argument, NULL, 'p'},
{"pset", required_argument, NULL, 'P'},
{"quiet", no_argument, NULL, 'q'},
+ {"pexec", no_argument, NULL, 'r'},
{"record-separator", required_argument, NULL, 'R'},
{"single-step", no_argument, NULL, 's'},
{"single-line", no_argument, NULL, 'S'},
***************
*** 346,352 ****
memset(options, 0, sizeof *options);
! while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxX?1",
long_options, &optindex)) != -1)
{
switch (c)
--- 351,357 ----
memset(options, 0, sizeof *options);
! while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:no:p:P:qrR:sStT:U:v:VwWxX?1",
long_options, &optindex)) != -1)
{
switch (c)
***************
*** 432,437 ****
--- 437,445 ----
case 'q':
SetVariableBool(pset.vars, "QUIET");
break;
+ case 'r':
+ pset.use_parameters = true;
+ break;
case 'R':
pset.popt.topt.recordSep = pg_strdup(optarg);
break;
Pavel Stehule <pavel.stehule@gmail.com> wrote:
I propose to add possibility to use psql variables as real query
parameters. The goal of this proposal is simplification of creating
psql based commands.
postgres=# \pexec
Separately passing parameters is on.
postgres=# select upper(:message);
There might be good if we can use both old and new behaviors at once
instead of \pexec switch. Something like:
SELECT * FROM :tablename WHERE id = $value
BTW, this proposal is a partial solution for scripting in psql.
But we will want other goodies soon - assignment, if, loop...
If there is a total solution, it would be better than a partial one.
I've heard pgAdmin uses pgScript as a client-side language.
Should we also take the language (or client-side plpgsql) in psql?
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
2009/11/16 Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>:
Pavel Stehule <pavel.stehule@gmail.com> wrote:
I propose to add possibility to use psql variables as real query
parameters. The goal of this proposal is simplification of creating
psql based commands.postgres=# \pexec
Separately passing parameters is on.
postgres=# select upper(:message);There might be good if we can use both old and new behaviors at once
instead of \pexec switch. Something like:SELECT * FROM :tablename WHERE id = $value
BTW, this proposal is a partial solution for scripting in psql.
But we will want other goodies soon - assignment, if, loop...
If there is a total solution, it would be better than a partial one.
I've heard pgAdmin uses pgScript as a client-side language.
Should we also take the language (or client-side plpgsql) in psql?
I don't would to reimplement pgScript. Personally I prefer some
scripting possibilities in psql too, but when pgScript exists, then we
could to use it. What more - we have a DO statement. So there are
tools. DO is great, now I missing some like USING clause:
DO (tablename varchar, value varchar)
$$
EXECUTE 'SELECT * FROM '|| tablename || 'WHERE id = $1' USING value;
$$ USING :tablename, :value;
I don't would to create new syntax for identifiers - see parallel
thread. Proposed feature is maximally simple and transparent.
Show quoted text
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center