Safely quoting type names to protect against SQL-injection

Question

How would I quote the type name to protect against SQL Injection. For example, take this

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
                                                 ^ Quote this

That works fine, but if 'int' is instead ''int); DROP DATABASE foo;SELECT (' you'll have some problems,

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int); DROP DATABASE foo;SELECT (');
                          format                          
----------------------------------------------------------
 SELECT CAST('5.42' AS int); DROP DATABASE foo;SELECT ();
(1 row)

How would I go about casting to a type provided in run time safely with Dynamic SQL? If I use %I instead of %s I get an identifier (safe-quoting with ", but type-names are not identifiers and do not work with double-quotes).

Inspired by this answer to "PostgreSQL alternative to SQL Server’s try_cast function" provided by Jasen

Answer 1

How would I quote the type name to protect against SQL Injection.

You do not need format_type() for the purpose. You may have misunderstood Andrew there. All you need is the cast to regtype:

SELECT format('SELECT cast(%L AS %s);', '5.42', 'int'::regtype);

The text representation of the regtype value is the standard SQL name and quoted automatically where needed - and the value is cast to text implicitly for the concatenation. Demo:

SELECT 'varchar(20)'::regtype, 'int'::regtype, '"char"'::regtype;

regtype           | regtype | regtype
------------------+---------+---------
character varying | integer | "char"

format_type() would only serve to add type modifiers (which are lost in the cast), an orthogonal matter. Related:

• PL/pgSQL regclass quoting of table named like keyword
• How to introspect materialized views
• PostgreSQL syntax error in parameterized query on “date $1”
• Table name as a PostgreSQL function parameter

Answer 2

RhodiumToad on irc.freenode.net/#postgresql provided the answer using format_type()

type names are sometimes identifiers to be precise, every type has a name which is an identifier, but some types also have parser aliases which are not if you want to accept only valid types, then use 'foo'::regtype format_type('foo'::regtype,-1) will return a string with the preferred form of the type

So you can do,

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', format_type('int'::regtype,-1) )

At that point, int is just a literal.