SqlParameter 不允许表名 - 没有 sql 注入攻击的其他选项?

问题描述

我收到一个运行时错误，提示 必须声明表变量@parmTableName".这意味着不允许在 sql 语句中将表名作为 sql 参数.

I got a runtime error saying "Must declare the table variable "@parmTableName". Meaning having table name as sql parameter in the sql-statement is not allowed.

是否有比允许 sql 注入攻击更好的选择或建议?我不想为 sql 语句执行这个 C# 脚本 " DELETE FROM " + tableName + " ";

Is there a better option or suggestion than allowing sql injection attack? I don't want to do this C# script for sql statement " DELETE FROM " + tableName + " ";

using(var dbCommand = dbConnection.CreateCommand())
{
   sqlAsk = "";
   sqlAsk += " DELETE FROM @parmTableName ";
   sqlAsk += " WHERE ImportedFlag = 'F' ";

   dbCommand.Parameters.Clear();
   dbCommand.Parameters.AddWithValue("@parmTableName", tableName);

   dbConnection.Open();

   rowAffected = dbCommand.ExecuteNonQuery();
}

推荐答案

寻找白名单.无论如何，表名只能有一组固定的可能正确值 - 至少，所以我希望如此.

Go for a white list. There can only be a fixed set of possible correct values for the table name anyway - at least, so I'd hope.

如果您没有表名白名单，您可以从字符白名单开始 - 如果您将其限制为 AZ、az 和 0-9(根本没有标点符号)那么这应该消除很多担忧.(当然，这意味着您不支持具有奇数名称的表……我们在这里并不真正了解您的要求.)

If you don't have a white list of table names, you could start with a whitelist of characters - if you restrict it to A-Z, a-z and 0-9 (no punctuation at all) then that should remove a lot of the concern. (Of course that means you don't support tables with odd names... we don't really know your requirements here.)

但是不行，您不能为表名或列名使用参数——只能使用值.在数据库中通常就是这种情况；我不记得看到一个确实支持参数.(我敢说当然有一些……)

But no, you can't use parameters for either table or column names - only values. That's typically the case in databases; I don't remember seeing one which did support parameters for that. (I dare say there are some, of course...)

这篇关于SqlParameter 不允许表名 - 没有 sql 注入攻击的其他选项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！