Postgresql – Safely quoting type names to protect against SQL-injection

datatypesdynamic-sqlpostgresqlsql-injection

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

Best Answer

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:

Related Question