语句有动态表名时如何防止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是表名(会被修复).

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)，这是100%的安全性防止 SQL 注入.

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

如果需要，可以使用 select * from 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 变量如何永远不会到达数据库.

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屋！