Quoting issues with createdb

[Michael Paquier]

Hi all,

createdb has a couple of issues with its quoting.  For example take
that, which can be confusing:
$ createdb --lc-ctype="en_US.UTF-8';create table aa();select '1" popo
createdb: error: database creation failed: ERROR:  CREATE DATABASE
cannot run inside a transaction block

The root of the issue is that any values added by the command caller
with --lc-collate, --lc-ctype or --encoding are not quoted properly,
and in all three cases it means that the quoting needs to be
encoding-sensitive (Tom mentioned me directly that part).  This proper
quoting can be achieved using appendStringLiteralConn() from
string_utils.c, at the condition of taking the connection to the
server before building the CREATE DATABASE query.

Note that for --encoding, this is less of a problem as there is some
extra validation with pg_char_to_encoding(), but it seems better to me
to be consistent.

So this gives the patch attached, where the error becomes:
ERROR:  invalid locale name: "en_US.UTF-8';create table aa();select '1"

Any opinions?
--
Michael

diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.c
index 68de2078e9..4733af8e97 100644
--- a/src/bin/scripts/createdb.c
+++ b/src/bin/scripts/createdb.c
@@ -176,6 +176,13 @@ main(int argc, char *argv[])
 			dbname = get_user_name_or_exit(progname);
 	}
 
+	/* No point in trying to use postgres db when creating postgres db. */
+	if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0)
+		maintenance_db = "template1";
+
+	conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
+									  prompt_password, progname, echo);
+
 	initPQExpBuffer(&sql);
 
 	appendPQExpBuffer(&sql, "CREATE DATABASE %s",
@@ -186,23 +193,25 @@ main(int argc, char *argv[])
 	if (tablespace)
 		appendPQExpBuffer(&sql, " TABLESPACE %s", fmtId(tablespace));
 	if (encoding)
-		appendPQExpBuffer(&sql, " ENCODING '%s'", encoding);
+	{
+		appendPQExpBufferStr(&sql, " ENCODING ");
+		appendStringLiteralConn(&sql, encoding, conn);
+	}
 	if (template)
 		appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
 	if (lc_collate)
-		appendPQExpBuffer(&sql, " LC_COLLATE '%s'", lc_collate);
+	{
+		appendPQExpBufferStr(&sql, " LC_COLLATE ");
+		appendStringLiteralConn(&sql, lc_collate, conn);
+	}
 	if (lc_ctype)
-		appendPQExpBuffer(&sql, " LC_CTYPE '%s'", lc_ctype);
+	{
+		appendPQExpBufferStr(&sql, " LC_CTYPE ");
+		appendStringLiteralConn(&sql, lc_ctype, conn);
+	}
 
 	appendPQExpBufferChar(&sql, ';');
 
-	/* No point in trying to use postgres db when creating postgres db. */
-	if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0)
-		maintenance_db = "template1";
-
-	conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
-									  prompt_password, progname, echo);
-
 	if (echo)
 		printf("%s\n", sql.data);
 	result = PQexec(conn, sql.data);

signature.asc
Description: PGP signature