SNOWFLAKE - How to bind variables with DDL inside a procedure?

Question

I've used binding variables on statement like INSERT or SELECT with the '?' or ':1' placeholders, but I cannot use it with instructions like CREATE ROLE because it throws an exception.

I need to bind the variable to create a procedure where I would create a role with the given name. I want to avoid CONCAT t or || for sql injection reasons.

This works

CREATE  PROCEDURE TEST (name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
DECLARE
  query VARCHAR;
BEGIN
  query:= 'SELECT * from DUMMY name =  ?';
  EXECUTE IMMEDIATE :query USING (name);
  RETURN  'OK';
END;
 
CALL TEST('OK');

But this returns an exception ´syntax error line 1 at position 12 unexpected '?'. when calling the procedure

CREATE PROCEDURE TEST2 (name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
DECLARE
  query VARCHAR;
BEGIN
  query:= 'CREATE ROLE ?';
  EXECUTE IMMEDIATE :query USING (name);
  RETURN  'OK';
END;
 
CALL TEST2('WRONG');

Answer 1

when you are referencing object names you need to use the IDENTIFIER keyword e.g.

query:= 'CREATE ROLE IDENTIFIER(?)';