2

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

4

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:

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.