如何安全地删除带有变量名的表? [英] How to safely drop a table with a variable name?

问题描述

假设我想删除一个表，它的名字是一个变量值，比如#t".

Suppose I want to drop a table, whose name is a variable value, such as "#t".

SqlCommand cmd = new SqlCommand( "drop table " + TableName ); //Not safe
SqlCommand cmd = new SqlCommand( "drop table [" + TableName +"]" ); //Not safe
EXEC( 'drop table [' + @tablename +"]" ); --Not safe

以上都不能避免 SQL 注入，那么还有什么替代方案?

None of the above are safe from SQL injection, so what is the alternative?

人们说你必须使用动态 SQL"，但这些例子通常仍然涉及不安全的串联，就像上面的第三个例子一样.

People say "you have to use dynamic SQL", but the examples still usually involve unsafe concatenation like the third example above.

我能找到的最接近答案的东西 (https://stackoverflow.com/a/14026855/88409) 基本上是说你必须自己做检查，而且没有办法绕过它(即通过解析它或从受信任的来源获取它来确保变量是有效的)，但我对这个答案不满意.

The closest thing to an answer I can find (https://stackoverflow.com/a/14026855/88409) basically says you have to do the check yourself, and there's no way around it (i.e. ensure the variable is valid through parsing it or getting it from a trusted source), but I'm not satisfied with that answer.

推荐答案

QUOTENAME 函数 将确保您的变量被视为有效的 SQL Server 分隔标识符".

The QUOTENAME function will make sure your variable is treated as a "valid SQL Server delimited identifier".

然后由您决定给定的表是否应该允许删除.也许对表具有 SQL 权限或可以删除​​的表的白名单...

It is then up to you to decide if the table given is one they should be allowed to delete. Maybe with SQL permissions on the table or a whitelist of tables that can be deleted...

这篇关于如何安全地删除带有变量名的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！