当语句具有动态表名时，如何防止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 是环境的名称（可以随时间更改）和表是表名（将被修复）。

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

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

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

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

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?

注意：我们将来可以迁移到DB2，因此解决方案应该兼容Oracle和DB2（如果可能的数据库独立）。

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不允许你使表名成为语句中的绑定变量。 （这有其原因）。

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

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

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 ?

1）在代码中一劳永逸地创建所有有效语句。

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= ?");

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

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）选择地图内的语句

2) Select the statement inside the map

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

查看 unsafeUserContent 变量如何永远不会到达DB。

See how the unsafeUserContent variable never reaches the DB.

3）制定某种策略或单元测试，检查所有 statementByTableName 是否有效针对你的模式进行未来的演变，并且没有表丢失。

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.

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

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 = ?

并在此处将您的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.

这是一种混合1和2.
您创建一个名为TABLES_ALLOWED_FOR_DELETION的表，并使用适合删除的所有表静态填充它。

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.

然后你进行验证步骤

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

如果有结果，则执行safe_table_name。为了更加安全，此表不可写标准应用程序用户。

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注入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！