diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 9915731..e47e8d5 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -3353,6 +3353,17 @@ bar + PARAMETERIZED_QUERIES + + + The psql's variables can be injected to query text (by default) or + passed as query parameters when this variable is set + on. + + + + + PORT diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index e1b04de..94c2993 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -119,9 +119,13 @@ setQFout(const char *fname) * If "escape" is true, return the value suitably quoted and escaped, * as an identifier or string literal depending on "as_ident". * (Failure in escaping should lead to returning NULL.) + * + * When "inside_query" is true, then the variable can be passed as query parameter, + * when it is not used as identifier (as_ident:false), when escape is not required + * (escaping changes the content). */ char * -psql_get_variable(const char *varname, bool escape, bool as_ident) +psql_get_variable(const char *varname, bool escape, bool as_ident, bool inside_query) { char *result; const char *value; @@ -130,6 +134,35 @@ psql_get_variable(const char *varname, bool escape, bool as_ident) if (!value) return NULL; + if (inside_query && pset.parameterized_queries) + { + if (!escape && !as_ident) + { + char printbuf[10]; + + if (pset.nparams >= pset.max_params) + { + /* create or realloc params array */ + if (pset.max_params > 0) + { + pset.max_params += 16; + pset.params = (const char **) pg_realloc(pset.params, + sizeof(const char *) * pset.max_params); + } + else + { + pset.max_params = 16; + pset.params = (const char **) pg_malloc(sizeof(const char *) * pset.max_params); + } + } + + pset.params[pset.nparams++] = value; + snprintf(printbuf, sizeof(printbuf) - 1, "$%d", pset.nparams); + + return pstrdup(printbuf); + } + } + if (escape) { char *escaped_value; @@ -1287,7 +1320,16 @@ SendQuery(const char *query) if (pset.timing) INSTR_TIME_SET_CURRENT(before); - results = PQexec(pset.db, query); + if (pset.nparams > 0) + results = PQexecParams(pset.db, query, + pset.nparams, + NULL, + (const char * const *) pset.params, + NULL, + NULL, + 0); + else + results = PQexec(pset.db, query); /* these operations are included in the timing result: */ ResetCancelConn(); @@ -1382,6 +1424,15 @@ SendQuery(const char *query) ClearOrSaveResult(results); + /* the number of query parameters are not necessary now */ + pset.nparams = 0; + if (pset.max_params > 0) + { + free(pset.params); + pset.params = NULL; + pset.max_params = 0; + } + /* Possible microtiming output */ if (pset.timing) PrintTiming(elapsed_msec); @@ -1488,7 +1539,16 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec) appendPQExpBuffer(&buf, "DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s", query); - results = PQexec(pset.db, buf.data); + if (pset.nparams > 0) + results = PQexecParams(pset.db, buf.data, + pset.nparams, + NULL, + (const char * const *) pset.params, + NULL, + NULL, + 0); + else + results = PQexec(pset.db, buf.data); OK = AcceptResult(results) && (PQresultStatus(results) == PGRES_COMMAND_OK); ClearOrSaveResult(results); diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h index dad0eb8..4dba1e1 100644 --- a/src/bin/psql/common.h +++ b/src/bin/psql/common.h @@ -18,7 +18,7 @@ extern bool openQueryOutputFile(const char *fname, FILE **fout, bool *is_pipe); extern bool setQFout(const char *fname); -extern char *psql_get_variable(const char *varname, bool escape, bool as_ident); +extern char *psql_get_variable(const char *varname, bool escape, bool as_ident, bool inside_query); extern void psql_error(const char *fmt,...) pg_attribute_printf(1, 2); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 09baf87..1f4ab2c 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -325,7 +325,7 @@ helpVariables(unsigned short int pager) * Windows builds currently print one more line than non-Windows builds. * Using the larger number is fine. */ - output = PageOutput(88, pager ? &(pset.popt.topt) : NULL); + output = PageOutput(90, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("List of specially treated variables\n\n")); @@ -352,6 +352,8 @@ helpVariables(unsigned short int pager) fprintf(output, _(" LASTOID value of the last affected OID\n")); fprintf(output, _(" ON_ERROR_ROLLBACK if set, an error doesn't stop a transaction (uses implicit savepoints)\n")); fprintf(output, _(" ON_ERROR_STOP stop batch execution after error\n")); + fprintf(output, _(" PARAMETERIZED_QUERIES\n" + " pass psql's variables as query parameters\n")); fprintf(output, _(" PORT server port of the current connection\n")); fprintf(output, _(" PROMPT1 specifies the standard psql prompt\n")); fprintf(output, _(" PROMPT2 specifies the prompt used when a statement continues from a previous line\n")); diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c index bb306a4..c4828fe 100644 --- a/src/bin/psql/mainloop.c +++ b/src/bin/psql/mainloop.c @@ -403,6 +403,15 @@ MainLoop(FILE *source) psql_scan_finish(scan_state); free(line); + /* reset a number of query parameters */ + pset.nparams = 0; + if (pset.max_params > 0) + { + free(pset.params); + pset.params = NULL; + pset.max_params = 0; + } + if (slashCmdStatus == PSQL_CMD_TERMINATE) { successResult = EXIT_SUCCESS; diff --git a/src/bin/psql/psqlscanslash.l b/src/bin/psql/psqlscanslash.l index 5b7953b..3e58303 100644 --- a/src/bin/psql/psqlscanslash.l +++ b/src/bin/psql/psqlscanslash.l @@ -243,6 +243,7 @@ other . yyleng - 1); value = cur_state->callbacks->get_variable(varname, false, + false, false); free(varname); @@ -271,7 +272,7 @@ other . ECHO; else { - psqlscan_escape_variable(cur_state, yytext, yyleng, false); + psqlscan_escape_variable(cur_state, yytext, yyleng, false, false); *option_quote = ':'; } unquoted_option_chars = 0; @@ -283,7 +284,7 @@ other . ECHO; else { - psqlscan_escape_variable(cur_state, yytext, yyleng, true); + psqlscan_escape_variable(cur_state, yytext, yyleng, true, false); *option_quote = ':'; } unquoted_option_chars = 0; diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h index 4c7c3b1..7d21e46 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -120,6 +120,7 @@ typedef struct _psqlSettings * functions. */ bool autocommit; + bool parameterized_queries; bool on_error_stop; bool quiet; bool singleline; @@ -135,6 +136,9 @@ typedef struct _psqlSettings const char *prompt3; PGVerbosity verbosity; /* current error verbosity level */ PGContextVisibility show_context; /* current context display level */ + int nparams; /* number of query parameters */ + int max_params; /* max size of current parameters array */ + const char **params; /* query parameters */ } PsqlSettings; extern PsqlSettings pset; diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c index 85aac4a..441a0ca 100644 --- a/src/bin/psql/startup.c +++ b/src/bin/psql/startup.c @@ -139,6 +139,10 @@ main(int argc, char *argv[]) pset.last_error_result = NULL; pset.cur_cmd_source = stdin; pset.cur_cmd_interactive = false; + pset.parameterized_queries = false; + pset.nparams = 0; + pset.max_params = 0; + pset.params = NULL; /* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */ pset.popt.topt.format = PRINT_ALIGNED; @@ -793,6 +797,12 @@ autocommit_hook(const char *newval) } static void +parameterized_queries_hook(const char *newval) +{ + pset.parameterized_queries = ParseVariableBool(newval, "PARAMETERIZED_QUERIES"); +} + +static void on_error_stop_hook(const char *newval) { pset.on_error_stop = ParseVariableBool(newval, "ON_ERROR_STOP"); @@ -990,6 +1000,7 @@ EstablishVariableSpace(void) SetVariableAssignHook(pset.vars, "ON_ERROR_ROLLBACK", on_error_rollback_hook); SetVariableAssignHook(pset.vars, "COMP_KEYWORD_CASE", comp_keyword_case_hook); SetVariableAssignHook(pset.vars, "HISTCONTROL", histcontrol_hook); + SetVariableAssignHook(pset.vars, "PARAMETERIZED_QUERIES", parameterized_queries_hook); SetVariableAssignHook(pset.vars, "PROMPT1", prompt1_hook); SetVariableAssignHook(pset.vars, "PROMPT2", prompt2_hook); SetVariableAssignHook(pset.vars, "PROMPT3", prompt3_hook); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 7709112..6f614df 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3255,8 +3255,8 @@ psql_completion(const char *text, int start, int end) } else if (TailMatchesCS2("\\set", MatchAny)) { - if (TailMatchesCS1("AUTOCOMMIT|ON_ERROR_STOP|QUIET|" - "SINGLELINE|SINGLESTEP")) + if (TailMatchesCS1("AUTOCOMMIT|ON_ERROR_STOP|PARAMETERIZED_QUERIES|" + "QUIET|SINGLELINE|SINGLESTEP")) COMPLETE_WITH_LIST_CS2("on", "off"); else if (TailMatchesCS1("COMP_KEYWORD_CASE")) COMPLETE_WITH_LIST_CS4("lower", "upper", @@ -3748,7 +3748,7 @@ complete_from_variables(const char *text, const char *prefix, const char *suffix "AUTOCOMMIT", "COMP_KEYWORD_CASE", "DBNAME", "ECHO", "ECHO_HIDDEN", "ENCODING", "FETCH_COUNT", "HISTCONTROL", "HISTFILE", "HISTSIZE", "HOST", "IGNOREEOF", "LASTOID", "ON_ERROR_ROLLBACK", "ON_ERROR_STOP", - "PORT", "PROMPT1", "PROMPT2", "PROMPT3", "QUIET", + "PARAMETERIZED_QUERIES", "PORT", "PROMPT1", "PROMPT2", "PROMPT3", "QUIET", "SHOW_CONTEXT", "SINGLELINE", "SINGLESTEP", "USER", "VERBOSITY", NULL }; diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l index 1b29341..6ab9c0b 100644 --- a/src/fe_utils/psqlscan.l +++ b/src/fe_utils/psqlscan.l @@ -700,7 +700,8 @@ other . if (cur_state->callbacks->get_variable) value = cur_state->callbacks->get_variable(varname, false, - false); + false, + true); else value = NULL; @@ -736,11 +737,11 @@ other . } :'{variable_char}+' { - psqlscan_escape_variable(cur_state, yytext, yyleng, false); + psqlscan_escape_variable(cur_state, yytext, yyleng, false, true); } :\"{variable_char}+\" { - psqlscan_escape_variable(cur_state, yytext, yyleng, true); + psqlscan_escape_variable(cur_state, yytext, yyleng, true, true); } /* @@ -1401,7 +1402,7 @@ psqlscan_extract_substring(PsqlScanState state, const char *txt, int len) */ void psqlscan_escape_variable(PsqlScanState state, const char *txt, int len, - bool as_ident) + bool as_ident, bool from_query) { char *varname; char *value; @@ -1409,7 +1410,8 @@ psqlscan_escape_variable(PsqlScanState state, const char *txt, int len, /* Variable lookup. */ varname = psqlscan_extract_substring(state, txt + 2, len - 3); if (state->callbacks->get_variable) - value = state->callbacks->get_variable(varname, true, as_ident); + value = state->callbacks->get_variable(varname, + true, as_ident, from_query); else value = NULL; free(varname); diff --git a/src/include/fe_utils/psqlscan.h b/src/include/fe_utils/psqlscan.h index 21c4f22..e14764d 100644 --- a/src/include/fe_utils/psqlscan.h +++ b/src/include/fe_utils/psqlscan.h @@ -53,7 +53,8 @@ typedef struct PsqlScanCallbacks { /* Fetch value of a variable, as a pfree'able string; NULL if unknown */ /* This pointer can be NULL if no variable substitution is wanted */ - char *(*get_variable) (const char *varname, bool escape, bool as_ident); + char *(*get_variable) (const char *varname, + bool escape, bool as_ident, bool from_query); /* Print an error message someplace appropriate */ /* (very old gcc versions don't support attributes on function pointers) */ #if defined(__GNUC__) && __GNUC__ < 4 diff --git a/src/include/fe_utils/psqlscan_int.h b/src/include/fe_utils/psqlscan_int.h index 0fddc7a..1fb2793 100644 --- a/src/include/fe_utils/psqlscan_int.h +++ b/src/include/fe_utils/psqlscan_int.h @@ -139,6 +139,7 @@ extern char *psqlscan_extract_substring(PsqlScanState state, const char *txt, int len); extern void psqlscan_escape_variable(PsqlScanState state, const char *txt, int len, - bool as_ident); + bool as_ident, + bool from_query); #endif /* PSQLSCAN_INT_H */ diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 464436a..4f3c580 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2714,3 +2714,36 @@ NOTICE: foo CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE ERROR: bar CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE +-- parameterized queries +\set PARAMETERIZED_QUERIES off +\set a1 'AHOJ SVETE' +-- should fail +SELECT :a1; +ERROR: column "ahoj" does not exist +LINE 1: SELECT AHOJ SVETE; + ^ +-- ok +SELECT :'a1'; + ?column? +------------ + AHOJ SVETE +(1 row) + +\set PARAMETERIZED_QUERIES on +-- should fail - unknown type +SELECT :a1; +ERROR: could not determine data type of parameter $1 +-- ok +SELECT :a1::text; + text +------------ + AHOJ SVETE +(1 row) + +-- returns true, when value passed as parameter is same as client side evaluated variable +SELECT :a1 = :'a1'; + ?column? +---------- + t +(1 row) + diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 900aa7e..fbc9302 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -379,3 +379,23 @@ begin raise notice 'foo'; raise exception 'bar'; end $$; + +-- parameterized queries +\set PARAMETERIZED_QUERIES off +\set a1 'AHOJ SVETE' + +-- should fail +SELECT :a1; + +-- ok +SELECT :'a1'; + +\set PARAMETERIZED_QUERIES on +-- should fail - unknown type +SELECT :a1; + +-- ok +SELECT :a1::text; + +-- returns true, when value passed as parameter is same as client side evaluated variable +SELECT :a1 = :'a1';