主页 / dba / 问题 / 78353
Accepted
beldaz
Asked: 2014-10-04 14:31:46 +0800 CST

通过参数化查询设置角色

  • 772

在我收到的对上一个问题的非常有用的回复中，我正在尝试编写一些 JDBC 代码，在执行后续查询之前首先将角色设置为特定用户。为了安全起见，我想通过参数化SET ROLE语句来防止 SQL 注入攻击。我在 Groovy（使用 JDBC）中的方法是：

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

但这会产生语法错误。文档说可以采用SET ROLE字符串文字，但我只是不清楚如何有效地传递它。有什么建议么？

postgresql jdbc

3 个回答

  1. Best Answer

    基于我刚刚写的一个简单的测试用例：

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

    我认为您提到的错误可能是：

    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)
    ....

    这样做的原因是 PostgreSQL 的协议只能绑定“可规划”语句的放置参数，这些语句是SELECT、INSERT、UPDATE和DELETE其他一些。对于这些语句类型，它仅支持文字值的放置参数，而不支持标识符或其他语法元素。

    对于其他语句类型，必须由客户端替换文字。一些驱动程序支持这些语句的客户端模拟，因此它们似乎透明地工作，但 PgJDBC 目前不支持这一点。

    如果您使用旧版 v2 协议，PgJDBC 实际上支持客户端参数绑定，但它不会为 v3 协议上的连接公开此功能。即使您不使用服务器端准备好的语句，它仍然会使用服务器端参数绑定。强制 v2 协议会起作用，但这是一个糟糕的解决方法，会在其他地方产生许多其他后果 - 加上在某些时候 PostgreSQL 将完全放弃对 v2 协议的支持。

    我认为这是驱动程序和/或 PostgreSQL 的有线协议的问题。用户不必关心服务器是否支持某些语句的参数绑定，而不是其他语句，驱动程序应该处理这一点 - 或者更好的是，服务器应该只支持所有语句类型。

    不幸的是，PgJDBC 也没有公开其安全标识符和文字转义的内部实现以供客户端应用程序使用。但是，如果standard_conforming_strings打开，则规则非常简单：

    • 将每个替换'为''; 和
    • 将整个内容用单引号括起来

    这同样适用于带有双引号的标识符。

    • 4

  2. SET ROLE您可以创建一个使用动态 SQL执行的函数，format 用于安全地插入角色标识符（%I插入一个标识符，必要时在其周围放置双引号，并在必要时通过将它们加倍来转义字符串中的双引号）。类似的东西

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

    然后，您可以使用类似SELECT setrole(?). 这应该适用于参数。

    这是一个使用它的例子psql：

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

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

(1 row)

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

  3. 据我测试，SET ROLE role_name也可以使用 set_config 函数来实现，例如SELECT set_config('role', 'role_name', TRUE)，这样可以使用参数：SELECT set_config($1, $2, TRUE)。

    Postgres 文档中还有一个注释，它set_config提供了相同的功能SET（但即使ROLE没有提到，它仍然对我有用。）

    PostgreSQL 版本：x86_64-pc-linux-musl 上的 PostgreSQL 12.2，由 gcc (Alpine 9.2.0) 9.2.0 编译，64 位

    • 0

相关问题