From 1c51c7d5ffd407426f314b2cd317ef77f14efb1f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 22 Feb 2011 23:33:44 +0200 Subject: [PATCH] Add PL/Python functions for quoting strings MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Add functions plpy.quote_ident, plpy.quote_literal, plpy.quote_nullable, which wrap the equivalent SQL functions. To be able to propagate char * constness properly, make the argument of quote_literal_cstr() const char *. This also makes it more consistent with quote_identifier(). Jan Urbański, reviewed by Hitoshi Harada, some refinements by Peter Eisentraut --- doc/src/sgml/plpython.sgml | 17 ++++++ src/backend/utils/adt/quote.c | 6 +- src/include/utils/builtins.h | 2 +- src/pl/plpython/Makefile | 1 + src/pl/plpython/expected/plpython_quote.out | 56 ++++++++++++++++++ src/pl/plpython/expected/plpython_test.out | 6 +- src/pl/plpython/plpython.c | 65 +++++++++++++++++++++ src/pl/plpython/sql/plpython_quote.sql | 33 +++++++++++ 8 files changed, 179 insertions(+), 7 deletions(-) create mode 100644 src/pl/plpython/expected/plpython_quote.out create mode 100644 src/pl/plpython/sql/plpython_quote.sql diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index e05c2937b1..7c869a8719 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -997,6 +997,23 @@ $$ LANGUAGE plpythonu; configuration variables. See for more information. + + + Another set of utility functions are + plpy.quote_literal(string), + plpy.quote_nullable(string), and + plpy.quote_ident(string). They + are equivalent to the built-in quoting functions described in . They are useful when constructing + ad-hoc queries. A PL/Python equivalent of dynamic SQL from would be: + +plpy.execute("UPDATE tbl SET %s = %s where key = %s" % ( + plpy.quote_ident(colname), + plpy.quote_nullable(newvalue), + plpy.quote_literal(keyvalue))) + + diff --git a/src/backend/utils/adt/quote.c b/src/backend/utils/adt/quote.c index 055d0a7ab8..2a6edeaafc 100644 --- a/src/backend/utils/adt/quote.c +++ b/src/backend/utils/adt/quote.c @@ -43,9 +43,9 @@ quote_ident(PG_FUNCTION_ARGS) * uses this for before thinking you know better. */ static size_t -quote_literal_internal(char *dst, char *src, size_t len) +quote_literal_internal(char *dst, const char *src, size_t len) { - char *s; + const char *s; char *savedst = dst; for (s = src; s < src + len; s++) @@ -99,7 +99,7 @@ quote_literal(PG_FUNCTION_ARGS) * returns a properly quoted literal */ char * -quote_literal_cstr(char *rawstr) +quote_literal_cstr(const char *rawstr) { char *result; int len; diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 8652ba03a0..72b0cdea7f 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -988,7 +988,7 @@ extern int32 type_maximum_size(Oid type_oid, int32 typemod); /* quote.c */ extern Datum quote_ident(PG_FUNCTION_ARGS); extern Datum quote_literal(PG_FUNCTION_ARGS); -extern char *quote_literal_cstr(char *rawstr); +extern char *quote_literal_cstr(const char *rawstr); extern Datum quote_nullable(PG_FUNCTION_ARGS); /* guc.c */ diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile index 3a2411bea1..17fa86880e 100644 --- a/src/pl/plpython/Makefile +++ b/src/pl/plpython/Makefile @@ -79,6 +79,7 @@ REGRESS = \ plpython_types \ plpython_error \ plpython_unicode \ + plpython_quote \ plpython_drop # where to find psql for running the tests PSQLDIR = $(bindir) diff --git a/src/pl/plpython/expected/plpython_quote.out b/src/pl/plpython/expected/plpython_quote.out new file mode 100644 index 0000000000..eed72923ae --- /dev/null +++ b/src/pl/plpython/expected/plpython_quote.out @@ -0,0 +1,56 @@ +-- test quoting functions +CREATE FUNCTION quote(t text, how text) RETURNS text AS $$ + if how == "literal": + return plpy.quote_literal(t) + elif how == "nullable": + return plpy.quote_nullable(t) + elif how == "ident": + return plpy.quote_ident(t) + else: + raise plpy.Error("unrecognized quote type %s" % how) +$$ LANGUAGE plpythonu; +SELECT quote(t, 'literal') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + ('xyzv')) AS v(t); + quote +----------- + 'abc' + 'a''bc' + '''abc''' + '' + '''' + 'xyzv' +(6 rows) + +SELECT quote(t, 'nullable') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + (NULL)) AS v(t); + quote +----------- + 'abc' + 'a''bc' + '''abc''' + '' + '''' + NULL +(6 rows) + +SELECT quote(t, 'ident') FROM (VALUES + ('abc'), + ('a b c'), + ('a " ''abc''')) AS v(t); + quote +-------------- + abc + "a b c" + "a "" 'abc'" +(3 rows) + diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out index d92c9876ee..961f6c0d18 100644 --- a/src/pl/plpython/expected/plpython_test.out +++ b/src/pl/plpython/expected/plpython_test.out @@ -43,9 +43,9 @@ contents.sort() return ", ".join(contents) $$ LANGUAGE plpythonu; select module_contents(); - module_contents -------------------------------------------------------------------------------------------- - Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, warning + module_contents +--------------------------------------------------------------------------------------------------------------------------------------- + Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, warning (1 row) CREATE FUNCTION elog_test() RETURNS void diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index 4e54d3e8b0..3013fc8a79 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -2637,6 +2637,10 @@ static PyObject *PLy_spi_execute_query(char *query, long limit); static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long); static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int); +static PyObject *PLy_quote_literal(PyObject *self, PyObject *args); +static PyObject *PLy_quote_nullable(PyObject *self, PyObject *args); +static PyObject *PLy_quote_ident(PyObject *self, PyObject *args); + static PyMethodDef PLy_plan_methods[] = { {"status", PLy_plan_status, METH_VARARGS, NULL}, @@ -2751,6 +2755,13 @@ static PyMethodDef PLy_methods[] = { */ {"execute", PLy_spi_execute, METH_VARARGS, NULL}, + /* + * escaping strings + */ + {"quote_literal", PLy_quote_literal, METH_VARARGS, NULL}, + {"quote_nullable", PLy_quote_nullable, METH_VARARGS, NULL}, + {"quote_ident", PLy_quote_ident, METH_VARARGS, NULL}, + {NULL, NULL, 0, NULL} }; @@ -3688,6 +3699,60 @@ PLy_output(volatile int level, PyObject *self, PyObject *args) } +static PyObject * +PLy_quote_literal(PyObject *self, PyObject *args) +{ + const char *str; + char *quoted; + PyObject *ret; + + if (!PyArg_ParseTuple(args, "s", &str)) + return NULL; + + quoted = quote_literal_cstr(str); + ret = PyString_FromString(quoted); + pfree(quoted); + + return ret; +} + +static PyObject * +PLy_quote_nullable(PyObject *self, PyObject *args) +{ + const char *str; + char *quoted; + PyObject *ret; + + if (!PyArg_ParseTuple(args, "z", &str)) + return NULL; + + if (str == NULL) + return PyString_FromString("NULL"); + + quoted = quote_literal_cstr(str); + ret = PyString_FromString(quoted); + pfree(quoted); + + return ret; +} + +static PyObject * +PLy_quote_ident(PyObject *self, PyObject *args) +{ + const char *str; + const char *quoted; + PyObject *ret; + + if (!PyArg_ParseTuple(args, "s", &str)) + return NULL; + + quoted = quote_identifier(str); + ret = PyString_FromString(quoted); + + return ret; +} + + /* * Get the name of the last procedure called by the backend (the * innermost, if a plpython procedure call calls the backend and the diff --git a/src/pl/plpython/sql/plpython_quote.sql b/src/pl/plpython/sql/plpython_quote.sql new file mode 100644 index 0000000000..346b5485da --- /dev/null +++ b/src/pl/plpython/sql/plpython_quote.sql @@ -0,0 +1,33 @@ +-- test quoting functions + +CREATE FUNCTION quote(t text, how text) RETURNS text AS $$ + if how == "literal": + return plpy.quote_literal(t) + elif how == "nullable": + return plpy.quote_nullable(t) + elif how == "ident": + return plpy.quote_ident(t) + else: + raise plpy.Error("unrecognized quote type %s" % how) +$$ LANGUAGE plpythonu; + +SELECT quote(t, 'literal') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + ('xyzv')) AS v(t); + +SELECT quote(t, 'nullable') FROM (VALUES + ('abc'), + ('a''bc'), + ('''abc'''), + (''), + (''''), + (NULL)) AS v(t); + +SELECT quote(t, 'ident') FROM (VALUES + ('abc'), + ('a b c'), + ('a " ''abc''')) AS v(t); -- 2.40.0