SqlParameter不允许使用表名-没有SQL注入攻击的其他选项? [英] SqlParameter does not allows Table name - other options without sql injection attack?

问题描述

我遇到一个运行时错误，提示"Must declare the table variable "@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语句" 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.)

但是，不能，表或列名都不能使用参数-只能使用值.在数据库中通常就是这种情况.我不记得看到哪一个 did 支持该参数. (我敢说当然有一些……)

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