Validating PostgreSQL naming and values

Questions / Help
, , ,
1

Hi, after a long time things are going more stable now, so I would like to be more focused on forum and open source. I’m working on next release candidate for one of my libraries and I would need a small help …

I’m working on generating SQL code. It would be a little similar to fragment in Ecto.Query API, but standalone i.e. it would generate a whole query.

See: Ecto.Adapters.SQL.query/4

The things I need are escaoe and/or validate functions for passed values. It should allow literally any name/value supported by PostgreSQL. I want to validate such parameter types:

  1. Enum name
  2. Enum value
  3. Function name
  4. PostgreSQL schema or prefix in ecto naming
  5. Table column name
  6. Table name

In one of use cases I’m expecting Enum value to be passed by user, but most probably somebody would found also other use cases for rest of them, so for sure I would like to perform a proper escape or validation before generating SQL code and ensure that there would be no surprise like SQL Injection problem.

Feel free to link to specific code if it’s solved in other project already. I know that ecto avoids SQL Injection, but I had a trouble searching for it in its source code.

What I would like to have is something like:

defmodule Example do
  def sample(fragment, args) do
    escaped_args = escape(args)
    generate_sql(fragment, escaped_args)
  end

  defp escape(args) when is_list(args), do: Enum.map(args, &escape/1)

  defp escape(arg) do
    # Here goes your proposal
  end

  defp generate_sql(fragment, escaped_args) do
    # some code goes here
  end
end

Also alternative version:

defmodule Example do
  def sample(fragment, table_name, col_name, …) do
    validated_args = validate(table: table_name, column: col_name, …)
    generate_sql(fragment, validated_args)
  end

  defp validate(args) when is_list(args), do: Enum.map(args, &validate1)

  defp validate({:table, table_name}) do
    # Here goes your proposal
  end

  defp generate_sql(fragment, escaped_args) do
    # some code goes here
  end
end

I’m open to your suggestions.

2

Based on:

I have written such example code:

defmodule Example do
  @reserved_words ~w[A ABORT ABS ABSENT ABSOLUTE ACCESS ACCORDING ACTION ADA ADD ADMIN AFTER AGGREGATE ALL ALLOCATE ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARE ARRAY ARRAY_AGG ARRAY_MAX_CARDINALITY AS ASC ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC AT ATOMIC ATTRIBUTE ATTRIBUTES AUTHORIZATION AVG BACKWARD BASE64 BEFORE BEGIN BEGIN_FRAME BEGIN_PARTITION BERNOULLI BETWEEN BIGINT BINARY BIT BIT_LENGTH BLOB BLOCKED BOM BOOLEAN BOTH BREADTH BY C CACHE CALL CALLED CARDINALITY CASCADE CASCADED CASE CAST CATALOG CATALOG_NAME CEIL CEILING CHAIN CHAR CHARACTER CHARACTERISTICS CHARACTERS CHARACTER_LENGTH CHARACTER_SET_CATALOG CHARACTER_SET_NAME CHARACTER_SET_SCHEMA CHAR_LENGTH CHECK CHECKPOINT CLASS CLASS_ORIGIN CLOB CLOSE CLUSTER COALESCE COBOL COLLATE COLLATION COLLATION_CATALOG COLLATION_NAME COLLATION_SCHEMA COLLECT COLUMN COLUMNS COLUMN_NAME COMMAND_FUNCTION COMMAND_FUNCTION_CODE COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONDITION CONDITION_NUMBER CONFIGURATION CONFLICT CONNECT CONNECTION CONNECTION_NAME CONSTRAINT CONSTRAINTS CONSTRAINT_CATALOG CONSTRAINT_NAME CONSTRAINT_SCHEMA CONSTRUCTOR CONTAINS CONTENT CONTINUE CONTROL CONVERSION CONVERT COPY CORR CORRESPONDING COST COUNT COVAR_POP COVAR_SAMP CREATE CROSS CSV CUBE CUME_DIST CURRENT CURRENT_CATALOG CURRENT_DATE CURRENT_DEFAULT_TRANSFORM_GROUP CURRENT_PATH CURRENT_ROLE CURRENT_ROW CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE CURRENT_USER CURSOR CURSOR_NAME CYCLE DATA DATABASE DATALINK DATE DATETIME_INTERVAL_CODE DATETIME_INTERVAL_PRECISION DAY DB DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINED DEFINER DEGREE DELETE DELIMITER DELIMITERS DENSE_RANK DEPTH DEREF DERIVED DESC DESCRIBE DESCRIPTOR DETERMINISTIC DIAGNOSTICS DICTIONARY DISABLE DISCARD DISCONNECT DISPATCH DISTINCT DLNEWCOPY DLPREVIOUSCOPY DLURLCOMPLETE DLURLCOMPLETEONLY DLURLCOMPLETEWRITE DLURLPATH DLURLPATHONLY DLURLPATHWRITE DLURLSCHEME DLURLSERVER DLVALUE DO DOCUMENT DOMAIN DOUBLE DROP DYNAMIC DYNAMIC_FUNCTION DYNAMIC_FUNCTION_CODE EACH ELEMENT ELSE EMPTY ENABLE ENCODING ENCRYPTED END END-EXEC END_FRAME END_PARTITION ENFORCED ENUM EQUALS ESCAPE EVENT EVERY EXCEPT EXCEPTION EXCLUDE EXCLUDING EXCLUSIVE EXEC EXECUTE EXISTS EXP EXPLAIN EXPRESSION EXTENSION EXTERNAL EXTRACT FALSE FAMILY FETCH FILE FILTER FINAL FIRST FIRST_VALUE FLAG FLOAT FLOOR FOLLOWING FOR FORCE FOREIGN FORTRAN FORWARD FOUND FRAME_ROW FREE FREEZE FROM FS FULL FUNCTION FUNCTIONS FUSION G GENERAL GENERATED GET GLOBAL GO GOTO GRANT GRANTED GREATEST GROUP GROUPING GROUPS HANDLER HAVING HEADER HEX HIERARCHY HOLD HOUR ID IDENTITY IF IGNORE ILIKE IMMEDIATE IMMEDIATELY IMMUTABLE IMPLEMENTATION IMPLICIT IMPORT IN INCLUDING INCREMENT INDENT INDEX INDEXES INDICATOR INHERIT INHERITS INITIALLY INLINE INNER INOUT INPUT INSENSITIVE INSERT INSTANCE INSTANTIABLE INSTEAD INT INTEGER INTEGRITY INTERSECT INTERSECTION INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN K KEY KEY_MEMBER KEY_TYPE LABEL LAG LANGUAGE LARGE LAST LAST_VALUE LATERAL LEAD LEADING LEAKPROOF LEAST LEFT LENGTH LEVEL LIBRARY LIKE LIKE_REGEX LIMIT LINK LISTEN LN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCATOR LOCK LOCKED LOGGED LOWER M MAP MAPPING MATCH MATCHED MATERIALIZED MAX MAXVALUE MAX_CARDINALITY MEMBER MERGE MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MESSAGE_TEXT METHOD MIN MINUTE MINVALUE MOD MODE MODIFIES MODULE MONTH MORE MOVE MULTISET MUMPS NAME NAMES NAMESPACE NATIONAL NATURAL NCHAR NCLOB NESTING NEW NEXT NFC NFD NFKC NFKD NIL NO NONE NORMALIZE NORMALIZED NOT NOTHING NOTIFY NOTNULL NOWAIT NTH_VALUE NTILE NULL NULLABLE NULLIF NULLS NUMBER NUMERIC OBJECT OCCURRENCES_REGEX OCTETS OCTET_LENGTH OF OFF OFFSET OIDS OLD ON ONLY OPEN OPERATOR OPTION OPTIONS OR ORDER ORDERING ORDINALITY OTHERS OUT OUTER OUTPUT OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER P PAD PARAMETER PARAMETER_MODE PARAMETER_NAME PARAMETER_ORDINAL_POSITION PARAMETER_SPECIFIC_CATALOG PARAMETER_SPECIFIC_NAME PARAMETER_SPECIFIC_SCHEMA PARSER PARTIAL PARTITION PASCAL PASSING PASSTHROUGH PASSWORD PATH PERCENT PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK PERIOD PERMISSION PLACING PLANS PLI POLICY PORTION POSITION POSITION_REGEX POWER PRECEDES PRECEDING PRECISION PREPARE PREPARED PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM PUBLIC QUOTE RANGE RANK READ READS REAL REASSIGN RECHECK RECOVERY RECURSIVE REF REFERENCES REFERENCING REFRESH REGR_AVGX REGR_AVGY REGR_COUNT REGR_INTERCEPT REGR_R2 REGR_SLOPE REGR_SXX REGR_SXY REGR_SYY REINDEX RELATIVE RELEASE RENAME REPEATABLE REPLACE REPLICA REQUIRING RESET RESPECT RESTART RESTORE RESTRICT RESULT RETURN RETURNED_CARDINALITY RETURNED_LENGTH RETURNED_OCTET_LENGTH RETURNED_SQLSTATE RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINE_CATALOG ROUTINE_NAME ROUTINE_SCHEMA ROW ROWS ROW_COUNT ROW_NUMBER RULE SAVEPOINT SCALE SCHEMA SCHEMA_NAME SCOPE SCOPE_CATALOG SCOPE_NAME SCOPE_SCHEMA SCROLL SEARCH SECOND SECTION SECURITY SELECT SELECTIVE SELF SENSITIVE SEQUENCE SEQUENCES SERIALIZABLE SERVER SERVER_NAME SESSION SESSION_USER SET SETOF SETS SHARE SHOW SIMILAR SIMPLE SIZE SKIP SMALLINT SNAPSHOT SOME SOURCE SPACE SPECIFIC SPECIFICTYPE SPECIFIC_NAME SQL SQLCODE SQLERROR SQLEXCEPTION SQLSTATE SQLWARNING SQRT STABLE STANDALONE START STATE STATEMENT STATIC STATISTICS STDDEV_POP STDDEV_SAMP STDIN STDOUT STORAGE STRICT STRIP STRUCTURE STYLE SUBCLASS_ORIGIN SUBMULTISET SUBSTRING SUBSTRING_REGEX SUCCEEDS SUM SYMMETRIC SYSID SYSTEM SYSTEM_TIME SYSTEM_USER T TABLE TABLES TABLESAMPLE TABLESPACE TABLE_NAME TEMP TEMPLATE TEMPORARY TEXT THEN TIES TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO TOKEN TOP_LEVEL_COUNT TRAILING TRANSACTION TRANSACTIONS_COMMITTED TRANSACTIONS_ROLLED_BACK TRANSACTION_ACTIVE TRANSFORM TRANSFORMS TRANSLATE TRANSLATE_REGEX TRANSLATION TREAT TRIGGER TRIGGER_CATALOG TRIGGER_NAME TRIGGER_SCHEMA TRIM TRIM_ARRAY TRUE TRUNCATE TRUSTED TYPE TYPES UESCAPE UNBOUNDED UNCOMMITTED UNDER UNENCRYPTED UNION UNIQUE UNKNOWN UNLINK UNLISTEN UNLOGGED UNNAMED UNNEST UNTIL UNTYPED UPDATE UPPER URI USAGE USER USER_DEFINED_TYPE_CATALOG USER_DEFINED_TYPE_CODE USER_DEFINED_TYPE_NAME USER_DEFINED_TYPE_SCHEMA USING VACUUM VALID VALIDATE VALIDATOR VALUE VALUES VALUE_OF VARBINARY VARCHAR VARIADIC VARYING VAR_POP VAR_SAMP VERBOSE VERSION VERSIONING VIEW VIEWS VOLATILE WHEN WHENEVER WHERE WHITESPACE WIDTH_BUCKET WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE XML XMLAGG XMLATTRIBUTES XMLBINARY XMLCAST XMLCOMMENT XMLCONCAT XMLDECLARATION XMLDOCUMENT XMLELEMENT XMLEXISTS XMLFOREST XMLITERATE XMLNAMESPACES XMLPARSE XMLPI XMLQUERY XMLROOT XMLSCHEMA XMLSERIALIZE XMLTABLE XMLTEXT XMLVALIDATE YEAR YES ZONE]
  @unquoted_identifier_regex ~r/^[\pL_][\pL\pM_0-9$]*$/u

  def sample(fragment, args) do
    escaped_args = escape(args)
    generate_sql(fragment, escaped_args)
  end

  defp escape(args) when is_list(args), do: Enum.map(args, &escape/1)

  defp escape(arg) do
    upcased_arg = String.upcase(arg)

    cond do
      String.starts_with?(upcased_arg, "PG_") -> inspect(arg)
      arg =~ @unquoted_identifier_regex and upcased_arg not in @reserved_words -> arg
      true -> inspect(arg)
    end
  end

  defp generate_sql(fragment, args) do
    # …
  end
end

I would have two questions regarding this code …

First of all this code is based on PostgreSQL documentation which says:

Tokens such as SELECT , UPDATE , or VALUES in the example above are examples of key words , that is, words that have a fixed meaning in the SQL language. The tokens MY_TABLE and A are examples of identifiers .

Source: PostgreSQL: Documentation: 16: 4.1. Lexical Structure

I’m not sure if I get it properly … Does it mean that it applies to all cases I have mentioned (from simple table name up to enum value)?

Secondly I would like to know if simple inspect call is enough to avoid SQL Injections or any other possible attacks which ecto_sql prevents. If not then please give me a proper reference link to specified piece of source code which solves it.

3

After some time most probably I finally found what I was looking for …

Here goes my complete code example:

defmodule Example do
  @reserved_words ~w[A ABORT ABS ABSENT ABSOLUTE ACCESS ACCORDING ACTION ADA ADD ADMIN AFTER AGGREGATE ALL ALLOCATE ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARE ARRAY ARRAY_AGG ARRAY_MAX_CARDINALITY AS ASC ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC AT ATOMIC ATTRIBUTE ATTRIBUTES AUTHORIZATION AVG BACKWARD BASE64 BEFORE BEGIN BEGIN_FRAME BEGIN_PARTITION BERNOULLI BETWEEN BIGINT BINARY BIT BIT_LENGTH BLOB BLOCKED BOM BOOLEAN BOTH BREADTH BY C CACHE CALL CALLED CARDINALITY CASCADE CASCADED CASE CAST CATALOG CATALOG_NAME CEIL CEILING CHAIN CHAR CHARACTER CHARACTERISTICS CHARACTERS CHARACTER_LENGTH CHARACTER_SET_CATALOG CHARACTER_SET_NAME CHARACTER_SET_SCHEMA CHAR_LENGTH CHECK CHECKPOINT CLASS CLASS_ORIGIN CLOB CLOSE CLUSTER COALESCE COBOL COLLATE COLLATION COLLATION_CATALOG COLLATION_NAME COLLATION_SCHEMA COLLECT COLUMN COLUMNS COLUMN_NAME COMMAND_FUNCTION COMMAND_FUNCTION_CODE COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONDITION CONDITION_NUMBER CONFIGURATION CONFLICT CONNECT CONNECTION CONNECTION_NAME CONSTRAINT CONSTRAINTS CONSTRAINT_CATALOG CONSTRAINT_NAME CONSTRAINT_SCHEMA CONSTRUCTOR CONTAINS CONTENT CONTINUE CONTROL CONVERSION CONVERT COPY CORR CORRESPONDING COST COUNT COVAR_POP COVAR_SAMP CREATE CROSS CSV CUBE CUME_DIST CURRENT CURRENT_CATALOG CURRENT_DATE CURRENT_DEFAULT_TRANSFORM_GROUP CURRENT_PATH CURRENT_ROLE CURRENT_ROW CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE CURRENT_USER CURSOR CURSOR_NAME CYCLE DATA DATABASE DATALINK DATE DATETIME_INTERVAL_CODE DATETIME_INTERVAL_PRECISION DAY DB DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINED DEFINER DEGREE DELETE DELIMITER DELIMITERS DENSE_RANK DEPTH DEREF DERIVED DESC DESCRIBE DESCRIPTOR DETERMINISTIC DIAGNOSTICS DICTIONARY DISABLE DISCARD DISCONNECT DISPATCH DISTINCT DLNEWCOPY DLPREVIOUSCOPY DLURLCOMPLETE DLURLCOMPLETEONLY DLURLCOMPLETEWRITE DLURLPATH DLURLPATHONLY DLURLPATHWRITE DLURLSCHEME DLURLSERVER DLVALUE DO DOCUMENT DOMAIN DOUBLE DROP DYNAMIC DYNAMIC_FUNCTION DYNAMIC_FUNCTION_CODE EACH ELEMENT ELSE EMPTY ENABLE ENCODING ENCRYPTED END END-EXEC END_FRAME END_PARTITION ENFORCED ENUM EQUALS ESCAPE EVENT EVERY EXCEPT EXCEPTION EXCLUDE EXCLUDING EXCLUSIVE EXEC EXECUTE EXISTS EXP EXPLAIN EXPRESSION EXTENSION EXTERNAL EXTRACT FALSE FAMILY FETCH FILE FILTER FINAL FIRST FIRST_VALUE FLAG FLOAT FLOOR FOLLOWING FOR FORCE FOREIGN FORTRAN FORWARD FOUND FRAME_ROW FREE FREEZE FROM FS FULL FUNCTION FUNCTIONS FUSION G GENERAL GENERATED GET GLOBAL GO GOTO GRANT GRANTED GREATEST GROUP GROUPING GROUPS HANDLER HAVING HEADER HEX HIERARCHY HOLD HOUR ID IDENTITY IF IGNORE ILIKE IMMEDIATE IMMEDIATELY IMMUTABLE IMPLEMENTATION IMPLICIT IMPORT IN INCLUDING INCREMENT INDENT INDEX INDEXES INDICATOR INHERIT INHERITS INITIALLY INLINE INNER INOUT INPUT INSENSITIVE INSERT INSTANCE INSTANTIABLE INSTEAD INT INTEGER INTEGRITY INTERSECT INTERSECTION INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN K KEY KEY_MEMBER KEY_TYPE LABEL LAG LANGUAGE LARGE LAST LAST_VALUE LATERAL LEAD LEADING LEAKPROOF LEAST LEFT LENGTH LEVEL LIBRARY LIKE LIKE_REGEX LIMIT LINK LISTEN LN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCATOR LOCK LOCKED LOGGED LOWER M MAP MAPPING MATCH MATCHED MATERIALIZED MAX MAXVALUE MAX_CARDINALITY MEMBER MERGE MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MESSAGE_TEXT METHOD MIN MINUTE MINVALUE MOD MODE MODIFIES MODULE MONTH MORE MOVE MULTISET MUMPS NAME NAMES NAMESPACE NATIONAL NATURAL NCHAR NCLOB NESTING NEW NEXT NFC NFD NFKC NFKD NIL NO NONE NORMALIZE NORMALIZED NOT NOTHING NOTIFY NOTNULL NOWAIT NTH_VALUE NTILE NULL NULLABLE NULLIF NULLS NUMBER NUMERIC OBJECT OCCURRENCES_REGEX OCTETS OCTET_LENGTH OF OFF OFFSET OIDS OLD ON ONLY OPEN OPERATOR OPTION OPTIONS OR ORDER ORDERING ORDINALITY OTHERS OUT OUTER OUTPUT OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER P PAD PARAMETER PARAMETER_MODE PARAMETER_NAME PARAMETER_ORDINAL_POSITION PARAMETER_SPECIFIC_CATALOG PARAMETER_SPECIFIC_NAME PARAMETER_SPECIFIC_SCHEMA PARSER PARTIAL PARTITION PASCAL PASSING PASSTHROUGH PASSWORD PATH PERCENT PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK PERIOD PERMISSION PLACING PLANS PLI POLICY PORTION POSITION POSITION_REGEX POWER PRECEDES PRECEDING PRECISION PREPARE PREPARED PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM PUBLIC QUOTE RANGE RANK READ READS REAL REASSIGN RECHECK RECOVERY RECURSIVE REF REFERENCES REFERENCING REFRESH REGR_AVGX REGR_AVGY REGR_COUNT REGR_INTERCEPT REGR_R2 REGR_SLOPE REGR_SXX REGR_SXY REGR_SYY REINDEX RELATIVE RELEASE RENAME REPEATABLE REPLACE REPLICA REQUIRING RESET RESPECT RESTART RESTORE RESTRICT RESULT RETURN RETURNED_CARDINALITY RETURNED_LENGTH RETURNED_OCTET_LENGTH RETURNED_SQLSTATE RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINE_CATALOG ROUTINE_NAME ROUTINE_SCHEMA ROW ROWS ROW_COUNT ROW_NUMBER RULE SAVEPOINT SCALE SCHEMA SCHEMA_NAME SCOPE SCOPE_CATALOG SCOPE_NAME SCOPE_SCHEMA SCROLL SEARCH SECOND SECTION SECURITY SELECT SELECTIVE SELF SENSITIVE SEQUENCE SEQUENCES SERIALIZABLE SERVER SERVER_NAME SESSION SESSION_USER SET SETOF SETS SHARE SHOW SIMILAR SIMPLE SIZE SKIP SMALLINT SNAPSHOT SOME SOURCE SPACE SPECIFIC SPECIFICTYPE SPECIFIC_NAME SQL SQLCODE SQLERROR SQLEXCEPTION SQLSTATE SQLWARNING SQRT STABLE STANDALONE START STATE STATEMENT STATIC STATISTICS STDDEV_POP STDDEV_SAMP STDIN STDOUT STORAGE STRICT STRIP STRUCTURE STYLE SUBCLASS_ORIGIN SUBMULTISET SUBSTRING SUBSTRING_REGEX SUCCEEDS SUM SYMMETRIC SYSID SYSTEM SYSTEM_TIME SYSTEM_USER T TABLE TABLES TABLESAMPLE TABLESPACE TABLE_NAME TEMP TEMPLATE TEMPORARY TEXT THEN TIES TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO TOKEN TOP_LEVEL_COUNT TRAILING TRANSACTION TRANSACTIONS_COMMITTED TRANSACTIONS_ROLLED_BACK TRANSACTION_ACTIVE TRANSFORM TRANSFORMS TRANSLATE TRANSLATE_REGEX TRANSLATION TREAT TRIGGER TRIGGER_CATALOG TRIGGER_NAME TRIGGER_SCHEMA TRIM TRIM_ARRAY TRUE TRUNCATE TRUSTED TYPE TYPES UESCAPE UNBOUNDED UNCOMMITTED UNDER UNENCRYPTED UNION UNIQUE UNKNOWN UNLINK UNLISTEN UNLOGGED UNNAMED UNNEST UNTIL UNTYPED UPDATE UPPER URI USAGE USER USER_DEFINED_TYPE_CATALOG USER_DEFINED_TYPE_CODE USER_DEFINED_TYPE_NAME USER_DEFINED_TYPE_SCHEMA USING VACUUM VALID VALIDATE VALIDATOR VALUE VALUES VALUE_OF VARBINARY VARCHAR VARIADIC VARYING VAR_POP VAR_SAMP VERBOSE VERSION VERSIONING VIEW VIEWS VOLATILE WHEN WHENEVER WHERE WHITESPACE WIDTH_BUCKET WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE XML XMLAGG XMLATTRIBUTES XMLBINARY XMLCAST XMLCOMMENT XMLCONCAT XMLDECLARATION XMLDOCUMENT XMLELEMENT XMLEXISTS XMLFOREST XMLITERATE XMLNAMESPACES XMLPARSE XMLPI XMLQUERY XMLROOT XMLSCHEMA XMLSERIALIZE XMLTABLE XMLTEXT XMLVALIDATE YEAR YES ZONE]
  @unquoted_identifier_regex ~r/^[\pL_][\pL\pM_0-9$]*$/u

  def sample(fragment, args) do
    escaped_args = escape(args)
    generate_sql(fragment, escaped_args)
  end

  defp escape(args) when is_list(args), do: Enum.map(args, &escape/1)

  defp escape({:identifier, arg}) do
    upcased_arg = String.upcase(arg)

    cond do
      String.starts_with?(upcased_arg, "PG_") -> inspect(arg)
      arg =~ @unquoted_identifier_regex and upcased_arg not in @reserved_words -> arg
      true -> inspect(arg)
    end
  end

  defp escape({:value, value}) do
    [?\', :binary.replace(value, "'", "''", [:global]), ?\']
  end

  defp generate_sql(fragment, args) do
    fragment
    |> split_fragment("")
    |> merge_fragments(args)
    |> IO.iodata_to_binary()
  end

  defp merge_fragments([h1|t1], [h2|t2]) do
    [h1, h2 | merge_fragments(t1, t2)]
  end

  defp merge_fragments([h1], []), do: [h1]

  defp split_fragment(<<>>, consumed), do: [consumed]

  defp split_fragment(<<??, rest :: binary>>, consumed) do
    [consumed | split_fragment(rest, "")]
  end

  defp split_fragment(<<?\\, ??, rest :: binary>>, consumed) do
    split_fragment(rest, consumed <> <<??>>)
  end

  defp split_fragment(<<first :: utf8, rest :: binary>>, consumed) do
    split_fragment(rest, consumed <> <<first :: utf8>>)
  end
end

Such example call:

> fragment = "alter type ?.? rename value ? to ?;"                                      
> args = [identifier: "my_schema", identifier: "my_enum", value: "old_value", value: "new_value"]
> Example.sample(fragment, args)

produces:

alter type my_schema.my_enum rename value 'old_value' to 'new_value';

Based on: