From d0cfc018233b4cdcab28d460ee0e14dbf87ac4ce Mon Sep 17 00:00:00 2001
From: Robert Haas
Date: Fri, 29 Jan 2010 17:44:12 +0000
Subject: Allow psql variables to be interpolated with literal or identifier
escaping.
Loosely based on a patch by Pavel Stehule.
---
doc/src/sgml/ref/psql-ref.sgml | 80 +++++++++++++++++++++++-------------------
1 file changed, 44 insertions(+), 36 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5b8bb836b18..a15b6a8df08 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1,5 +1,5 @@
@@ -658,7 +658,12 @@ testdb=>
If an unquoted argument begins with a colon (:),
it is taken as a psql variable and the value of the
- variable is used as the argument instead.
+ variable is used as the argument instead. If the variable name is
+ surrounded by single quotes (e.g. :'var'), it
+ will be escaped as an SQL literal and the result will be used as
+ the argument. If the variable name is surrounded by double quotes,
+ it will be escaped as an SQL identifier and the result will be used
+ as the argument.
@@ -2711,18 +2716,35 @@ bar
An additional useful feature of psql
variables is that you can substitute (interpolate
)
- them into regular SQL statements. The syntax for
- this is again to prepend the variable name with a colon
+ them into regular SQL statements.
+ psql provides special facilities for
+ ensuring that values used as SQL literals and identifiers are
+ properly escaped. The syntax for interpolating a value without
+ any special escaping is again to prepend the variable name with a colon
(:):
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;
- would then query the table my_table. The value of
- the variable is copied literally, so it can even contain unbalanced
- quotes or backslash commands. You must make sure that it makes sense
- where you put it. Variable interpolation will not be performed into
- quoted SQL entities.
+ would then query the table my_table. Note that this
+ may be unsafe: the value of the variable is copied literally, so it can
+ even contain unbalanced quotes or backslash commands. You must make sure
+ that it makes sense where you put it.
+
+
+
+ When a value is to be used as an SQL literal or identifier, it is
+ safest to arrange for it to be escaped. To escape the value of
+ a variable as an SQL literal, write a colon followed by the variable
+ name in single quotes. To escape the value an SQL identifier, write
+ a colon followed by the variable name in double quotes. The previous
+ example would be more safely written this way:
+
+testdb=> \set foo 'my_table'
+testdb=> SELECT * FROM :"foo";
+
+ Variable interpolation will not be performed into quoted
+ SQL entities.
@@ -2730,40 +2752,26 @@ testdb=> SELECT * FROM :foo;
copy the contents of a file into a table column. First load the file into a
variable and then proceed as above:
-testdb=> \set content '''' `cat my_file.txt` ''''
-testdb=> INSERT INTO my_table VALUES (:content);
-
- One problem with this approach is that my_file.txt
- might contain single quotes. These need to be escaped so that
- they don't cause a syntax error when the second line is processed. This
- could be done with the program sed:
-
-testdb=> \set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''
-
- If you are using non-standard-conforming strings then you'll also need
- to double backslashes. This is a bit tricky:
-
-testdb=> \set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''
+testdb=> \set content `cat my_file.txt`
+testdb=> INSERT INTO my_table VALUES (:'content');
- Note the use of different shell quoting conventions so that neither
- the single quote marks nor the backslashes are special to the shell.
- Backslashes are still special to sed, however, so
- we need to double them. (Perhaps
- at one point you thought it was great that all Unix commands use the
- same escape character.)
+ (Note that this still won't work if my_file.txt contains NUL bytes.
+ psql does not support embedded NUL bytes in variable values.)
- Since colons can legally appear in SQL commands, the following rule
- applies: the character sequence
- :name
is not changed unless name is the name
- of a variable that is currently set. In any case you can escape
- a colon with a backslash to protect it from substitution. (The
- colon syntax for variables is standard SQL for
+ Since colons can legally appear in SQL commands, an apparent attempt
+ at interpolation (such as :name,
+ :'name', or :"name") is not
+ changed unless the named variable is currently set. In any case you
+ can escape a colon with a backslash to protect it from substitution.
+ (The colon syntax for variables is standard SQL for
embedded query languages, such as ECPG.
The colon syntax for array slices and type casts are
PostgreSQL extensions, hence the
- conflict.)
+ conflict. The colon syntax for escaping a variable's value as an
+ SQL literal or identifier is a psql
+ extension.)
--
cgit v1.2.3