Início / dba / Perguntas / 78353
Accepted
beldaz
Asked: 2014-10-04 14:31:46 +0800 CST

DEFINIR PAPEL via consulta parametrizada

  • 772

Na resposta muito útil que recebi para uma pergunta anterior , estou tentando escrever algum código JDBC que primeiro defina a função para um usuário específico antes de executar consultas subsequentes. Por segurança, gostaria de evitar ataques de injeção de SQL parametrizando a SET ROLEinstrução. Minha abordagem no Groovy (que usa JDBC) foi:

def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver')
sql.execute 'SET ROLE ?', user

mas isso gera um erro de sintaxe. A documentação diz que SET ROLEpode levar uma string literal, mas não estou claro como posso passá-la validamente. Alguma sugestão?

postgresql jdbc

3 respostas

  1. Best Answer

    Com base em um caso de teste simples que acabei de escrever:

    @Test
public void test() throws SQLException {
    PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
    ps.setString(1, "someuser");
    ps.executeUpdate();
}

    Eu acho que o erro que você se refere é provavelmente:

    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 10
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2245)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1974)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:254)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:565)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    ....

    A razão para isso é que o protocolo do PostgreSQL só pode vincular parâmetros de posicionamento para instruções "planejáveis", que são SELECT, INSERT, UPDATEe DELETEalgumas outras. Para esses tipos de instrução, ele suporta apenas parâmetros de posicionamento para valores literais, não para identificadores ou outros elementos de sintaxe.

    Para outros tipos de instrução, os literais devem ser substituídos pelo cliente. Alguns drivers oferecem suporte à emulação do lado do cliente para preparar essas instruções, portanto, eles parecem funcionar de forma transparente, mas o PgJDBC atualmente não oferece suporte a isso.

    Na verdade, o PgJDBC oferece suporte à vinculação de parâmetros do lado do cliente se você estiver usando o protocolo legado v2, mas não expõe essa funcionalidade para conexões no protocolo v3. Mesmo se você não estiver usando instruções preparadas do lado do servidor, ele ainda usará a ligação de parâmetro do lado do servidor. Forçar o protocolo v2 funcionará, mas é uma solução ruim com muitas outras consequências em outros lugares - além disso, em algum momento, o PostgreSQL abandonará totalmente o suporte para o protocolo v2.

    Eu acho que isso é um problema com o driver e/ou protocolo de fio do PostgreSQL. O usuário não deve se preocupar se o servidor suporta vinculação de parâmetros para algumas instruções e não para outras, o driver deve cuidar disso - ou melhor, o servidor deve apenas suportá-lo para todos os tipos de instrução.

    Infelizmente, o PgJDBC também não expõe suas implementações internas de identificador seguro e escape literal para uso do aplicativo cliente. Se standard_conforming_stringsestiver ativado, porém, a regra é muito simples:

    • Substitua cada 'por ''; e
    • Envolva tudo entre aspas simples

    O mesmo se aplica aos identificadores, com aspas duplas.

    • 4

  2. Você pode criar uma função que executa SET ROLEcom SQL dinâmico, usando format para inserir com segurança o identificador de função ( %Iinsere um identificador, colocando aspas duplas em torno dele, se necessário, e escapando aspas duplas na string, dobrando-as, se necessário). Algo na linha de

    CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN EXECUTE format('SET ROLE %I', role); END $$ LANGUAGE plpgsql;

    Você pode então executar essa função com algo como SELECT setrole(?). Isso deve funcionar bem com os parâmetros.

    Aqui está um exemplo dele em uso em psql:

    > show role;
   role
-----------
 testuser1
(1 row)

> SELECT setrole('testuser2');
 setrole
---------

(1 row)

> show role;
   role
-----------
 testuser2
(1 row)
    • 2

  3. Tanto quanto pude testar com , SET ROLE role_nametambém pode ser obtido usando uma função set_config, por exemplo SELECT set_config('role', 'role_name', TRUE), e desta forma os parâmetros podem ser usados: SELECT set_config($1, $2, TRUE).

    Há também uma nota na documentação do Postgres que set_configfornece a mesma funcionalidade SET(mas mesmo que ROLEnão seja mencionada lá, ainda funcionou para mim).

    Versão do PostgreSQL: PostgreSQL 12.2 em x86_64-pc-linux-musl, compilado por gcc (Alpine 9.2.0) 9.2.0, 64 bits

    • 0

relate perguntas