Gerando queries dinâmicas no PostgreSQL

Há algumas formas de gerar queries dinâmicas no PostgreSQL. Você pode filtrar queries em um backend ou pode filtrar dentro do próprio PostgreSQL.

Filtrando dentro do PostgreSQL

O PostgreSQL conta com uma função chamada EXECUTE, essa função executa o SQL a partir de uma string.

Aqui crio uma função que usa o EXECUTE:

CREATE OR REPLACE FUNCTION eval(_query TEXT)--RETURNS TABLE(value JSONB)RETURNS VOIDLANGUAGE 'plpgsql'VOLATILEAS $BODY$BEGIN--RETURN QUERY EXECUTE _query;EXECUTE _query;END;$BODY$;

A partir daí você pode executar queries via string:

SELECT eval('CREATE TABLE hello (oi TEXT);')

Protegendo o EXECUTE de SQL Injections

O exemplo anterior é extremamente perigoso. Ele não só abre brecha para SQL injection mas como remote code execution em geral. Para se ter um exemplo mais completo, totalmente protegido, vamos fazer um SELECT dinâmico através de uma função.

Vamos criar a tabela e populá-la:

CREATE TABLE IF NOT EXISTS fruit_preferences(	person TEXT PRIMARY KEY,	like_banana BOOLEAN NOT NULL,	like_apple BOOLEAN NOT NULL,	like_avocado BOOLEAN NOT NULL);INSERT INTO fruit_preferences (person, like_banana, like_apple, like_avocado) VALUES('João', true, false, true),('Maria', true, true, false),('Pedro', false, false, false);

Criando a função. Perceba que usamos a função format para proteger a string, impedindo que outros comandos sejam executados.

O %I substitui tudo que for um "identifier". Identifier são: nome de schemas, nome de tabelas, nome de funções, nome de colunas e nome de alias para colunas.

O %L substitui tudo que for um "literal". Literais são valores, tipo strings, números (double, integer, numeric), booleanos (true, false).

CREATE OR REPLACE FUNCTION get_fruit_preference(	person TEXT,	column_name TEXT)RETURNS TABLE (like BOOLEAN)LANGUAGE 'plpgsql'VOLATILEAS $BODY$BEGINRETURN QUERY EXECUTE format('SELECT %I FROM fruit_preferences WHERE person = %L', column_name, person);END;$BODY$;

Testando a função... A função irá retornar o valor da coluna like_banana nos registros em que a coluna person é igual a Maria:

SELECT get_fruit_preference('Maria','like_banana');/*get_fruit_preferencefalse*/

Referências

Como implementar Dynamic SQL no PostgreSQL
https://hub.packtpub.com/how-to-implement-dynamic-sql-in-postgresql-10/

Documentação oficial do EXECUTE
https://www.postgresql.org/docs/current/sql-execute.html

Documentação oficial do FORMAT
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT

Filtrando no Java

A partir de um JSON, executar um SQL, impedindo SQL injections.

O modelo atual implementa:

SELECT
É possível:
escolher colunas definidas dentro do FROM
fazer agregações nestas colunas
definir ALIAS
Não é possível:
fazer operações e chamar funções nas colunas

FROM
escolher uma tabela com schema
fazer operações e chamar funções nas colunas da tabela escolhida
Não é possível:
fazer joins

WHERE
fazer filtros nas colunas a partir de funções

Classes que moldam o JSON

public class Argument {    public final String value;    public final String type;    public Argument(String value, String type) {        this.value = value;        this.type = type;    }    @Override    public String toString() {        return "Argument{" +                "value=" + value +                ", type='" + type + '\'' +                '}';    }}public class Column {    public final String aggregation;    public final String identifier;    public final String alias;    public Column(String aggregation, String identifier, String alias) {        this.aggregation = aggregation;        this.identifier = identifier;        this.alias = alias;    }    @Override    public String toString() {        return "Column{" +                "aggregation='" + aggregation + '\'' +                ", identifier='" + identifier + '\'' +                ", alias='" + alias + '\'' +                '}';    }}public class ExtraFromElement {    public final String function;    public final Argument[] arguments;    public final String alias;    public ExtraFromElement(String function, Argument[] arguments, String alias) {        this.function = function;        this.arguments = arguments;        this.alias = alias;    }    @Override    public String toString() {        return "ExtraFromElement{" +                "function='" + function + '\'' +                ", arguments=" + Arrays.toString(arguments) +                ", alias='" + alias + '\'' +                '}';    }}public class Query {    public final Column[] columns;    public final String[] groupBy;    public final Where[] where;    public final Table from;    public final ExtraFromElement[] extraFrom;    public final Integer limit;    public Query(Column[] columns, String[] groupBy, Where[] where, Table from, ExtraFromElement[] extraFrom, Integer limit) {        this.columns = columns;        this.groupBy = groupBy;        this.where = where;        this.from = from;        this.extraFrom = extraFrom;        this.limit = limit;    }    @Override    public String toString() {        return "Query{" +                "columns=" + Arrays.toString(columns) +                ", groupBy=" + Arrays.toString(groupBy) +                ", where=" + Arrays.toString(where) +                ", from=" + from +                ", extraFrom=" + Arrays.toString(extraFrom) +                ", limit=" + limit +                '}';    }}public class Table {    public final String schema;    public final String table;    public Table(String schema, String table) {        this.schema = schema;        this.table = table;    }    @Override    public String toString() {        return "Table{" +                "schema='" + schema + '\'' +                ", table='" + table + '\'' +                '}';    }}public class Where {    public final String function;    public final Argument[] arguments;    public final String conditional;    public Where(String function, Argument[] arguments, String conditional) {        this.function = function;        this.arguments = arguments;        this.conditional = conditional;    }    @Override    public String toString() {        return "Where{" +                "function='" + function + '\'' +                ", arguments=" + Arrays.toString(arguments) +                ", conditional='" + conditional + '\'' +                '}';    }}

Classe com o limpador de query.

package io.ubivis.cloud.console_web.ubiBI.service;import com.fasterxml.jackson.databind.ObjectMapper;import io.ubivis.cloud.console_web.ubiBI.model.*;import java.util.Arrays;import com.google.gson.Gson;public class SanitizeQuery {    public static String sanitizeIdentifier(String identifier){        // Sanitize double quotes        return "\""+identifier.replace("\"","\"\"")+"\"";    }    public static String sanitizeLiteral(String literal){            return "'"+ literal.replace("'","''")+"'";    }    public static String sanitizeArgument(Argument argument){        if ("literal".equalsIgnoreCase(argument.type))            return sanitizeLiteral(argument.value);        if ("identifier".equalsIgnoreCase(argument.type)){            return sanitizeIdentifier(String.valueOf(argument.value));        }        return "";    }    public static String[] sanitizeArguments(Argument[] arguments){        String[] sanitizedArguments = new String[arguments.length];        for (int i = 0; i < arguments.length; i++) {            sanitizedArguments[i] = sanitizeArgument(arguments[i]);        }        return sanitizedArguments;    }    public static String sanitizeConditional(String conditional){        if(conditional == null){            return "";        }        return conditional.equalsIgnoreCase("AND") ?                "AND" : conditional.equalsIgnoreCase("OR") ?                "OR" : "";    }    public static String sanitizeSelect(Column[] columns) {        String res = "";        for (int i = 0; i < columns.length; i++) {             Column column = columns[i];             if(column.aggregation != null) {                 res += sanitizeIdentifier(column.aggregation.toLowerCase()) +                         "(" + sanitizeIdentifier(column.identifier) +                         ") AS " +                         (column.alias == null ?                                 sanitizeIdentifier(column.aggregation.toLowerCase() + "_" + column.identifier) :                                 sanitizeIdentifier(column.alias))                         + " ";             } else {                 res += sanitizeIdentifier(column.identifier) + " " + (column.alias == null ?                         ""                         : "AS " + sanitizeIdentifier(column.alias)                 );             }             if(i != columns.length-1) {                 res = res + ", ";             }         }         return res;     }     public static String sanitizeWhere(Where[] wheres) {         if(wheres == null){             return "";         }         String result = "";         if(wheres.length > 0){            result = result + "WHERE ";            for (int i = 0; i < wheres.length; i++) {                 result = result + "\n";                 Where where = wheres[i];                 String[] arguments = sanitizeArguments(where.arguments);                 switch(where.function){                     case ">":                    case "<":                    case "<>":                    case ">=":                    case "<=":                    case "=":                        result = result + " " + arguments[0] + " " + where.function + " " + arguments[1] + " ";                        break;                    case "OVERLAPS":                        result = result + " (" + arguments[0] + ", "+ arguments[1] + ") OVERLAPS ("+arguments[2]+", " + arguments[3] + ") ";                        break;                    case "IN":                        result = result + " " + arguments[0] + " IN (" + String.join(",", Arrays.copyOfRange(arguments, 1, arguments.length)) + ") ";                    case "IS NOT NULL":                        result = result + " " + arguments[0] + " IS NOT NULL ";                    case "IS NULL":                        result = result + " " + arguments[0] + " IS NULL ";                    default:                        // Treat it as a function                        result = result + " " + sanitizeIdentifier(where.function) + "(" + String.join(",", arguments) + ")";                        break;                }                result = result + (i != wheres.length-1 ? sanitizeConditional(where.conditional) + " " : " ");            }        }        return result;    }    public static String sanitizeExtraFrom(ExtraFromElement extraFrom){        String[] arguments = sanitizeArguments(extraFrom.arguments);        return ", "+sanitizeIdentifier(extraFrom.function) + "(" + String.join(",", arguments) + ") AS " + sanitizeIdentifier(extraFrom.alias) + "\n";    }    public static String sanitizeExtraFroms(ExtraFromElement[] extraFroms){        String[] froms = new String[extraFroms.length];        for (int i = 0; i < extraFroms.length; i++) {            froms[i] = sanitizeExtraFrom(extraFroms[i]);        }        return String.join("", froms);    }    public static String sanitizeGroupBy(String[] groupBys){        if(groupBys == null || groupBys.length==0)            return "";        return "GROUP BY ("+ String.join(", ", groupBys)+")";    }    public static String sanitizeLimit(Integer limit){        if(limit == null){            return "";        }        return "\nLIMIT " + limit;    }    public static String sanitizeQuery(Query query){        String fullQuery = "";        fullQuery = fullQuery + "SELECT " + sanitizeSelect(query.columns)+"\n";        fullQuery = fullQuery + "FROM " + sanitizeIdentifier(query.from.schema) + "." + sanitizeIdentifier(query.from.table) + "\n";        fullQuery = fullQuery + sanitizeExtraFroms(query.extraFrom);        fullQuery = fullQuery + sanitizeWhere(query.where)+"\n";        fullQuery = fullQuery + sanitizeGroupBy(query.groupBy);        fullQuery = fullQuery + sanitizeLimit(query.limit);        fullQuery = fullQuery + "\n;";        return fullQuery;    }    public static void main(String[] args){        ObjectMapper objectMapper = new ObjectMapper();        Gson gson = new Gson();        String jsonQuery = "{\n" +                "  \"columns\": [\n" +                "    {\"identifier\": \"mask_id\"},\n" +                "    {\"identifier\": \"head_id\"},\n" +                "    {\"aggregation\": \"sum\", \"identifier\": \"weight_time_delta\"},\n" +                "    {\"aggregation\": \"avg\", \"identifier\": \"weight_time_delta\"}\n" +                "  ],\n" +                "  \"from\" : {\"schema\": \"electrolux\", \"table\": \"foaming_line_1\"},\n" +                "  \"extraFrom\" : [\n" +                "    {\n" +                "      \"function\": \"-\",\n" +                "      \"arguments\": [\n" +                "        {\"value\": \"end_weight_time\", \"type\": \"identifier\"},\n" +                "        {\"value\": \"start_weight_time\", \"type\": \"identifier\"}\n" +                "      ],\n" +                "      \"alias\": \"weight_time_delta\"\n" +                "    }\n" +                "  ],\n" +                "  \"where\": [\n" +                "    {\n" +                "      \"function\": \"OVERLAPS\",\n" +                "      \"arguments\": [\n" +                "        {\"value\": \"2019-09-01\", \"type\": \"literal\"},\n" +                "        {\"value\": \"2019-09-05\", \"type\": \"literal\"},\n" +                "        {\"value\": \"start_weight_time\", \"type\": \"identifier\"},\n" +                "        {\"value\": \"end_weight_time\", \"type\": \"identifier\"}\n" +                "      ],\n" +                "      \"conditional\": \"and\"\n" +                "    },\n" +                "    {\n" +                "      \"function\": \"=\",\n" +                "      \"arguments\": [\n" +                "        {\"value\": \"mask_id\", \"type\": \"identifier\"},\n" +                "        {\"value\": 1, \"type\": \"literal\"}\n" +                "      ],\n" +                "      \"conditional\": null\n" +                "    }\n" +                "  ],\n" +                "  \"groupBy\": [\"mask_id\", \"head_id\"],\n" +                "  \"limit\" : 50\n" +                "}";        try {            System.out.println("---- JSON Query ----");            System.out.println(jsonQuery);            //Query query = objectMapper.readValue(jsonQuery, Query.class);            Query query = gson.fromJson(jsonQuery, Query.class);            String sanitizedQuery = sanitizeQuery(query);            System.out.println("\n--- Sanitized Query ----");            System.out.println(sanitizedQuery);        } catch (Exception e) {            e.printStackTrace();        }    }}

Exemplo de JSON aceito

{  "columns": [    {"identifier": "mask_id"},    {"identifier": "head_id"},    {"aggregation": "sum", "identifier": "weight_time_delta"},    {"aggregation": "avg", "identifier": "weight_time_delta"}  ],  "from" : {"schema": "electrolux", "table": "foaming_line_1"},  "extraFrom" : [    {      "function": "-",      "arguments": [        {"value": "end_weight_time", "type": "identifier"},        {"value": "start_weight_time", "type": "identifier"}      ],      "alias": "weight_time_delta"    }  ],  "where": [    {      "function": "OVERLAPS",      "arguments": [        {"value": "2019-09-01", "type": "literal"},        {"value": "2019-09-05", "type": "literal"},        {"value": "start_weight_time", "type": "identifier"},        {"value": "end_weight_time", "type": "identifier"}      ],      "conditional": "and"    },    {      "function": "=",      "arguments": [        {"value": "mask_id", "type": "identifier"},        {"value": 1, "type": "literal"}      ],      "conditional": null    }  ],  "groupBy": ["mask_id", "head_id"],  "limit" : 50}