避免对具有表名的查询进行SQL注入 [英] Avoid SQL Injections on query with tablename

问题描述

可能重复:
在.NET中的动态SQL? (防止SQL注入攻击)

Possible Duplicate:
Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)

我有这样的查询:

"SELECT * FROM MyTable_" + myID + " WHERE variable = @variable";

SQL参数化可与变量一起使用，但是如何使它与表名一起使用? myID是一个我传入和更改的int(可以转换为字符串)，但是如何在此处防止sql注入?

The SQL Parameterization works with variables, but how do I get it to work with table names? myID is an int I get passed in and changed (can be converted to string), but how do I protect against sql injections here?

推荐答案

我问为什么这样做，但是您可以在sys.tables中查看最终的白名单.

I question why you are doing this, but you can look at sys.tables for a conclusive whitelist.

DECLARE @TableName VARCHAR(100) = 'Table to Look for';
DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );

您可以参数化初始输入，但是白名单方法仍然很重要.否则，恶意用户可以在整个数据库中传递任何有效的表名，并且查询将针对该表名运行(假设他们具有SELECT权限).

You could parameterize the initial input, but the whitelist approach is still important. Otherwise, a malicious user could pass any valid table name in the entire database and the query would run against it (assuming they had SELECT permissions).

这篇关于避免对具有表名的查询进行SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！