postgresql - PreparedStatement 不起作用时如何在 JDBC 中转义 SQL 参数？

Question

我有一个要传递给 SQL 的字符串。为防止 SQL 注入(inject)和其他引用和转义问题，最佳做法是使用带有 ? 的 PreparedStatement。例如:

val ps = conn.prepareStatement("select * from foo where name = ?")
ps.setString(1, name)

但对于某些 SQL 代码，这将不起作用。例如，这里是 PostgreSQL，试图创建一个 View 。

 val ps = conn.prepareStatement("create temp view v1 as select * from foo where name = ?")
 ps.setString(1, name)
 val rs = ps.execute()

这会引发错误:

org.postgresql.util.PSQLException: ERROR: there is no parameter $1

它显然不允许参数创建 View 。您如何解决这个问题并安全地转义字符串？

Answer 1

准备好的语句用于计划一次复杂的语句，然后使用不同的参数值多次(=非常多次)执行它。使用准备好的语句对简单语句没有明显好处，因为规划它们很简单。根本不支持 DDL 语句，所以这很可能是错误的原因，尽管错误消息令人困惑。

来自documentation :

PREPARE name [ ( data_type [, ...] ) ] AS statement

statement
Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

PreparedStatement 类确实记录了您可以在 executeUpdate() 方法中使用 DDL，但从逻辑的角度来看这只是胡说八道，至少在 PostgreSQL 中是这样。

相反，您应该使用 Statement 然后调用 execute() 或 executeUpdate()(后一种方法有点奇怪支持 DDL 语句，因为没有执行更新)。

防止 SQL 注入(inject)

为了防止 SQL 注入(inject)，您可以使用一些 PostgreSQL 函数:

• quote_literal() - 正如所料，这将引用文字参数值以确保查询中的安全。这不仅会阻止您 Bobby Tables ，还有来自 Mr. 之类的人。奥布莱恩。
• quote_nullable() - 对于上面的文字，但当参数 IS NULL 时将生成正确的代码。
• quote_identifier() - 将双引号引起规划器问题的任何表名或列名，例如表名 from 和列 int, type and from: SELECT int, type, from FROM from WHERE int = type 变成 SELECT "int", "type ", "from"FROM "from"WHERE "int"= "type".

您可以直接在 SQL 语句中使用这些函数，然后让 PostgreSQL 处理讨厌的输入。