Avoid SQL Injections on query with tablename
Possible Duplicate:
Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
I have a query like so:
"SELECT * FROM MyTable_" + myID + " WHERE variable = @variable";
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?
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' );
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).
As long as myID is a numeric variable, it can not contain any harmful code.
The only other thing that you would need to do, is to make sure that an error message from trying to read a table that doesn't exist, doesn't leak information about the database layout which could possibly aid in some other kind of attack.
获取数据库中的表的列表，并检查"MyTable_" + myID是否在该列表中。
上一篇: pdo和哈希来防止sql注入
下一篇: 使用表名查询时避免SQL注入