用于数据库备份的SQL参数化查询由sonarqube报告为SQL注入

Question

SonarQube报告了几个SQL注入安全热点。我已经将实现更改为使用参数化查询，但问题尚未解决。SonarQube正在以下行报告SQL注入：

SqlCommand cmd = new SqlCommand(cmdTxt, con);

我该如何解决？

String cmdTxt = String.Format(@"BAKUP DATABASE {0} TO DISK N'{1}'WITH FORMAT,COPY_ONLY,INIT, NAME ='{0} -Full Database Backup', SKIP", databasename, outputfile);
SqlCommand cmd = new SqlCommand(cmdTxt, con);
cmd.Parameters.Add("@database", SqlDbType.VarChar);
cmd.Parameters.Add("outputfile", SqlDbType.VarChar);
cmd.Parameters.["@database"].Value= database;
cmd.Parameters.["outputfile"] .Value= outfile;
Cmd.ExecuteNonQuery();

问题解决为：

string sqlQuery = "DECLARE @sql nvarchar (max) = (" +
"SELECT " +
"'BACKUP DATABASE '" +
"+ QUOTENAME(name) " +
"+ ' TO DISK=@outputfile WITH FORMAT,COPY_ONLY,INIT, NAME = @backup_set_name_var, SKIP' " +
"FROM sys.databases " +
"WHERE name = @database); "+
"EXEC sp_executesql " +
"@sql, " +
"N'@backup_set_name_var nvarchar(128), @outputfile nvarchar(128)', "+
"@backup_set_name_var = @backup_set_name_var, "+
"@outputfile = @outputfile; ";

using (SqlCommand sqlCmd = new SqlCommand(sqlQuery , _sqlConnection))
{
sqlCmd.Parameters.Add("@database", SqlDbType.NVarChar, 128).Value = databaseName;
sqlCmd.Parameters.Add("@backup_set_name_var", SqlDbType.NVarChar, 128).Value = databaseName + " -Full Database Backup";
sqlCmd.Parameters.Add("@outputfile", SqlDbType.NVarChar, 128).Value = outputFile;
sqlCmd.ExecuteNonQuery();
}

Answer 1

很难说允许数据库名称作为参数本身是否危险。可以说是的，但不一定是SQL注入。

因此，如果你想这样做，你可以通过它并创建一个经过验证的动态SQL语句(注意using块，以及正确的参数类型和大小(：

using(SqlCommand cmd = new SqlCommand(thebelowtext, con))
{
cmd.Parameters.Add("@database", SqlDbType.NVarChar, 128).Value= database;
cmd.Parameters.Add("@backup_set_name", SqlDbType.NVarChar, 128).Value= database + " -Full Database Backup";
cmd.Parameters.Add("@outputfile", SqlDbType.NVarChar, 128).Value= outfile;
cmd.ExecuteNonQuery();
}

DECLARE @sql = (
SELECT
'BACKUP DATABASE '
+ QUOTENAME(name)
+ ' TO DISK=@outputfile WITH FORMAT,COPY_ONLY,INIT, NAME = @backup_set_name, SKIP'
FROM sys.databases
WHERE name = @database);
IF (@sql IS NULL)
THROW 50000, N'Database does not exist', 0;
PRINT @sql;  --For testing in SSMS
EXEC sp_executesql
@sql,
N'@backup_set_name_var nvarchar(128), @outputfile nvarchar(128)',
@backup_set_name = @backup_set_name,
@outputfile = @outputfile;

---

EDIT碰巧BACKUP DATABASE允许数据库名称作为参数。因此，您实际上根本不需要动态SQL。

IF NOT EXISTS (SELECT 1
FROM sys.databases
WHERE name = @database);
THROW 50000, N'Database does not exist', 0;
BACKUP DATABASE @database
TO DISK = @outputfile
WITH FORMAT, COPY_ONLY, INIT,
NAME = @backup_set_name, SKIP;