通過參數化查詢設置角色

在我收到的對上一個問題的非常有用的回復中，我正在嘗試編寫一些 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字元串文字，但我只是不清楚如何有效地傳遞它。有什麼建議？

基於我剛剛寫的一個簡單的測試案例：

@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打開，則規則非常簡單：

• 將每個替換'為''; 和
• 將整個內容用單引號括起來

這同樣適用於帶有雙引號的標識符。

引用自：https://dba.stackexchange.com/questions/78353