当语句具有动态表名称时如何防止SQL注入？(How to prevent SQL injection when the statement has a dynamic table name?)

当语句具有动态表名称时如何防止SQL注入？(How to prevent SQL injection when the statement has a dynamic table name?)

我有这样的代码。

final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName);

fullTableName计算如下所示：

public String getFullTableName(final String table) { if (this.schemaDB != null) { return this.schemaDB + "." + table; } return table; }

这里schemaDB是环境的名称（可以随时间改变）， table是表名（它将被修复）。

schemaDB值来自XML文件，这使得查询容易受到SQL注入的影响。

查询：我不确定表名可以如何用作准备语句（如本示例中使用的name ），这是针对SQL注入的100％安全措施。

任何人都可以请建议我，有什么可能的方法来处理这个问题？

注意：我们将来可能会迁移到DB2，所以解决方案应该与Oracle和DB2兼容（并且如果可能的话，与数据库无关）。

I am having code something like this.

final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName);

Calculation of fullTableName is something like:

public String getFullTableName(final String table) { if (this.schemaDB != null) { return this.schemaDB + "." + table; } return table; }

Here schemaDB is the name of the environment(which can be changed over time) and table is the table name(which will be fixed).

Value for schemaDB is coming from an XML file which makes the query vulnerable to SQL injection.

Query: I am not sure how the table name can be used as a prepared statement(like the name used in this example), which is the 100% security measure against SQL injection.

Could anyone please suggest me, what could be the possible approach to deal with this?

Note: We can be migrated to DB2 in future so the solution should compatible with both Oracle and DB2(and if possible database independent).

最满意答案

不幸的是，JDBC不允许你在语句内使表名成为绑定变量。 （这有其原因）。

所以你不能编写或实现这种功能：

connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123);

并且将TUSER绑定到声明的表名。

因此，你唯一安全的方法就是验证用户输入。 然而，最安全的方式并不是验证它，并允许用户输入数据库，因为从安全角度来看，您始终可以指望用户比验证更智能。 永远不要相信一个动态的，用户生成的字符串，在您的语句中连接起来。

那么什么是安全验证模式？

模式1：预建安全查询

1）用代码一次性创建所有有效的语句。

Map<String, String> statementByTableName = new HashMap<>(); statementByTableName.put("table_1", "DELETE FROM table_1 where name= ?"); statementByTableName.put("table_2", "DELETE FROM table_2 where name= ?");

如果需要，可以使用select * from ALL_TABLES;的select * from ALL_TABLES;来使此创建本身变为动态select * from ALL_TABLES; 声明。 ALL_TABLES将返回您的SQL用户有权访问的所有表，并且您还可以从中获取表名和模式名称。

2）选择地图内的语句

String unsafeUserContent = ... String safeStatement = statementByTableName.get(usafeUserContent); conn.prepareStatement(safeStatement, name);

查看unsafeUserContent变量如何不会到达数据库。

3）进行某种策略或单元测试，检查您的statementByTableName对于您的模式是否有效，以便将来进一步发展，并且不缺少任何表。

模式2：重复检查

你可以1）验证用户输入确实是一个表名，使用免注入查询（我在这里输入伪sql代码，你必须调整它以使其工作，因为我没有Oracle实例来实际检查有用） ：

select * FROM (select schema_name || '.' || table_name as fullName FROM all_tables) WHERE fullName = ?

并在此处将您的fullName作为预先准备的语句变量绑定。 如果你有结果，那么它是一个有效的表名。 然后你可以使用这个结果来建立一个安全的查询。

模式3

这是1和2之间的混合。您创建一个名为例如“TABLES_ALLOWED_FOR_DELETION”的表，并且您将静态填充所有适合删除的表。

然后你让你的验证步骤成为可能

conn.prepareStatement(SELECT safe_table_name FROM TABLES_ALLOWED_FOR_DELETION WHERE table_name = ?", unsafeDynamicString);

如果这有结果，那么你执行safe_table_name。 为了更加安全，此表不应由标准应用程序用户写入。

我不知何故觉得第一种模式更好。

JDBC, sort of unfortunately, does not allow you to make the table name a bound variable inside statements. (It has its reasons for this).

So you can not write, or achieve this kind of functionnality :

connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123);

And have TUSER be bound to the table name of the statement.

Therefore, your only safe way forward is to validate the user input. The safest way, though, is not to validate it and allow user-input go through the DB, because from a security point of view, you can always count on a user being smarter than your validation. Never trust a dynamic, user generated String, concatenated inside your statement.

So what is a safe validation pattern ?

Pattern 1 : prebuild safe queries

1) Create all your valid statements once and for all, in code.

Map<String, String> statementByTableName = new HashMap<>(); statementByTableName.put("table_1", "DELETE FROM table_1 where name= ?"); statementByTableName.put("table_2", "DELETE FROM table_2 where name= ?");

If need be, this creation itself can be made dynamic, with a select * from ALL_TABLES; statement. ALL_TABLES will return all the tables your SQL user has access to, and you can also get the table name, and schema name from this.

2) Select the statement inside the map

String unsafeUserContent = ... String safeStatement = statementByTableName.get(usafeUserContent); conn.prepareStatement(safeStatement, name);

See how the unsafeUserContent variable never reaches the DB.

3) Make some kind of policy, or unit test, that checks that all you statementByTableName are valid against your schemas for future evolutions of it, and that no table is missing.

Pattern 2 : double check

You can 1) validate that the user input is indeed a table name, using an injection free query (I'm typing pseudo sql code here, you'd have to adapt it to make it work cause I have no Oracle instance to actually check it works) :

select * FROM (select schema_name || '.' || table_name as fullName FROM all_tables) WHERE fullName = ?

And bind your fullName as a prepared statement variable here. If you have a result, then it is a valid table name. Then you can use this result to build a safe query.

Pattern 3

It's sort of a mix between 1 and 2. You create a table that is named, e.g., "TABLES_ALLOWED_FOR_DELETION", and you statically populate it with all tables that are fit for deletion.

Then you make your validation step be

conn.prepareStatement(SELECT safe_table_name FROM TABLES_ALLOWED_FOR_DELETION WHERE table_name = ?", unsafeDynamicString);

If this has a result, then you execute the safe_table_name. For extra safety, this table should not be writable by the standard application user.

I somehow feel the first pattern is better.

当语句具有动态表名称时如何防止SQL注入？(How to prevent SQL injection when the statement has a dynamic table name?)

我有这样的代码。

final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName);

fullTableName计算如下所示：

public String getFullTableName(final String table) { if (this.schemaDB != null) { return this.schemaDB + "." + table; } return table; }

这里schemaDB是环境的名称（可以随时间改变）， table是表名（它将被修复）。

schemaDB值来自XML文件，这使得查询容易受到SQL注入的影响。

查询：我不确定表名可以如何用作准备语句（如本示例中使用的name ），这是针对SQL注入的100％安全措施。

任何人都可以请建议我，有什么可能的方法来处理这个问题？

注意：我们将来可能会迁移到DB2，所以解决方案应该与Oracle和DB2兼容（并且如果可能的话，与数据库无关）。

I am having code something like this.

final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName);

Calculation of fullTableName is something like:

public String getFullTableName(final String table) { if (this.schemaDB != null) { return this.schemaDB + "." + table; } return table; }

Here schemaDB is the name of the environment(which can be changed over time) and table is the table name(which will be fixed).

Value for schemaDB is coming from an XML file which makes the query vulnerable to SQL injection.

Query: I am not sure how the table name can be used as a prepared statement(like the name used in this example), which is the 100% security measure against SQL injection.

Could anyone please suggest me, what could be the possible approach to deal with this?

Note: We can be migrated to DB2 in future so the solution should compatible with both Oracle and DB2(and if possible database independent).

最满意答案

不幸的是，JDBC不允许你在语句内使表名成为绑定变量。 （这有其原因）。

所以你不能编写或实现这种功能：

connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123);

并且将TUSER绑定到声明的表名。

因此，你唯一安全的方法就是验证用户输入。 然而，最安全的方式并不是验证它，并允许用户输入数据库，因为从安全角度来看，您始终可以指望用户比验证更智能。 永远不要相信一个动态的，用户生成的字符串，在您的语句中连接起来。

那么什么是安全验证模式？

模式1：预建安全查询

1）用代码一次性创建所有有效的语句。

Map<String, String> statementByTableName = new HashMap<>(); statementByTableName.put("table_1", "DELETE FROM table_1 where name= ?"); statementByTableName.put("table_2", "DELETE FROM table_2 where name= ?");

如果需要，可以使用select * from ALL_TABLES;的select * from ALL_TABLES;来使此创建本身变为动态select * from ALL_TABLES; 声明。 ALL_TABLES将返回您的SQL用户有权访问的所有表，并且您还可以从中获取表名和模式名称。

2）选择地图内的语句

String unsafeUserContent = ... String safeStatement = statementByTableName.get(usafeUserContent); conn.prepareStatement(safeStatement, name);

查看unsafeUserContent变量如何不会到达数据库。

3）进行某种策略或单元测试，检查您的statementByTableName对于您的模式是否有效，以便将来进一步发展，并且不缺少任何表。

模式2：重复检查

你可以1）验证用户输入确实是一个表名，使用免注入查询（我在这里输入伪sql代码，你必须调整它以使其工作，因为我没有Oracle实例来实际检查有用） ：

select * FROM (select schema_name || '.' || table_name as fullName FROM all_tables) WHERE fullName = ?

并在此处将您的fullName作为预先准备的语句变量绑定。 如果你有结果，那么它是一个有效的表名。 然后你可以使用这个结果来建立一个安全的查询。

模式3

这是1和2之间的混合。您创建一个名为例如“TABLES_ALLOWED_FOR_DELETION”的表，并且您将静态填充所有适合删除的表。

然后你让你的验证步骤成为可能

conn.prepareStatement(SELECT safe_table_name FROM TABLES_ALLOWED_FOR_DELETION WHERE table_name = ?", unsafeDynamicString);

如果这有结果，那么你执行safe_table_name。 为了更加安全，此表不应由标准应用程序用户写入。

我不知何故觉得第一种模式更好。

JDBC, sort of unfortunately, does not allow you to make the table name a bound variable inside statements. (It has its reasons for this).

So you can not write, or achieve this kind of functionnality :

connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123);

And have TUSER be bound to the table name of the statement.

Therefore, your only safe way forward is to validate the user input. The safest way, though, is not to validate it and allow user-input go through the DB, because from a security point of view, you can always count on a user being smarter than your validation. Never trust a dynamic, user generated String, concatenated inside your statement.

So what is a safe validation pattern ?

Pattern 1 : prebuild safe queries

1) Create all your valid statements once and for all, in code.

Map<String, String> statementByTableName = new HashMap<>(); statementByTableName.put("table_1", "DELETE FROM table_1 where name= ?"); statementByTableName.put("table_2", "DELETE FROM table_2 where name= ?");

If need be, this creation itself can be made dynamic, with a select * from ALL_TABLES; statement. ALL_TABLES will return all the tables your SQL user has access to, and you can also get the table name, and schema name from this.

2) Select the statement inside the map

String unsafeUserContent = ... String safeStatement = statementByTableName.get(usafeUserContent); conn.prepareStatement(safeStatement, name);

See how the unsafeUserContent variable never reaches the DB.

3) Make some kind of policy, or unit test, that checks that all you statementByTableName are valid against your schemas for future evolutions of it, and that no table is missing.

Pattern 2 : double check

You can 1) validate that the user input is indeed a table name, using an injection free query (I'm typing pseudo sql code here, you'd have to adapt it to make it work cause I have no Oracle instance to actually check it works) :

select * FROM (select schema_name || '.' || table_name as fullName FROM all_tables) WHERE fullName = ?

And bind your fullName as a prepared statement variable here. If you have a result, then it is a valid table name. Then you can use this result to build a safe query.

Pattern 3

It's sort of a mix between 1 and 2. You create a table that is named, e.g., "TABLES_ALLOWED_FOR_DELETION", and you statically populate it with all tables that are fit for deletion.

Then you make your validation step be

conn.prepareStatement(SELECT safe_table_name FROM TABLES_ALLOWED_FOR_DELETION WHERE table_name = ?", unsafeDynamicString);

If this has a result, then you execute the safe_table_name. For extra safety, this table should not be writable by the standard application user.

I somehow feel the first pattern is better.