未设置参数类型时是否可以进行 SQL 注入？
仅将 SQL 参数传递给存储过程是否可以确保不会发生 SQL 注入，或者还需要执行类型检查？
举个例子 -
ADO.NET代码：
Database DBObject = DataAccess.DAL.GetDataBase();
DbCommand command = DBObject.GetStoredProcCommand("usp_UpdateDatabase");
List<DbParameter> parameters = new List<DbParameter>();
parameters.Add(new SqlParameter("@DbName", txtName.Text));
parameters.Add(new SqlParameter("@DbDesc", txtDesc.Text));
command.Parameters.AddRange(parameters.ToArray());
rowsAffected = DBObject.ExecuteNonQuery(command);
SP：
ALTER PROCEDURE [dbo].[usp_GetSearchResults]
-- Add the parameters for the stored procedure here
@DbName NVARCHAR(50) = ''
,@DbDesc NVARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT [RegionName]
,[AppName]
FROM [ApplicationComponent]
WHERE [DBName] LIKE ('%' + @DbName+ '%')
OR [DBDesc] LIKE ('%' + @DbDesc+ '%')
END
在上面的代码中，我没有提到任何参数类型或验证逻辑。它还能防止 SQL 注入吗？
感谢您的指导！
Does passing SQL Parameters to a stored procedure alone ensure that SQL injection won't happen or the type checks also need to be performed?
As an example -
ADO.NET Code:
Database DBObject = DataAccess.DAL.GetDataBase();
DbCommand command = DBObject.GetStoredProcCommand("usp_UpdateDatabase");
List<DbParameter> parameters = new List<DbParameter>();
parameters.Add(new SqlParameter("@DbName", txtName.Text));
parameters.Add(new SqlParameter("@DbDesc", txtDesc.Text));
command.Parameters.AddRange(parameters.ToArray());
rowsAffected = DBObject.ExecuteNonQuery(command);
SP:
ALTER PROCEDURE [dbo].[usp_GetSearchResults]
-- Add the parameters for the stored procedure here
@DbName NVARCHAR(50) = ''
,@DbDesc NVARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT [RegionName]
,[AppName]
FROM [ApplicationComponent]
WHERE [DBName] LIKE ('%' + @DbName+ '%')
OR [DBDesc] LIKE ('%' + @DbDesc+ '%')
END
In the above code, I havent mentioned any parameter types or validation logic. Would it still preevnt SQL injection?
Thanks for the guidance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不，那应该没问题。 LIKE 子句中的值仍然构建为字符串值，而不是解释为 SQL 语句的一部分。它仍然被视为数据而不是代码，这是避免 SQL 注入攻击的关键部分。
No, that should be fine. The value in the LIKE clause is still built up as a string value, rather than being interpreted as part of the SQL statement. It's still being treated as data rather than code, and that's the crucial part of avoiding SQL injection attacks.
是的，这仍然可以保护您免受 SQL 注入。
您没有在 .NET 代码中动态构建 SQL 字符串，也没有使用 sp_execute 在存储过程中动态构建和执行 SQL 语句。
Yes, that should still protect you from SQL Injection.
You're not dynamically building the SQL String in your .NET code and you're not using sp_execute to dynamically build and execute a SQL statement in your stored procedure.
SQLParameter的默认DbType为NVarChar（根据 docs)，因此这就是您的参数将具有的类型。无论如何，即使参数的类型错误，最糟糕的事情也将是类型转换异常，而不是 SQL 注入。
Default
DbTypefor anSQLParameterisNVarChar(as per the docs), so this is the type your parameters will have.Anyway, even if the parameters were of the wrong type, the worst thing you would have would be the type cast exception, not
SQLinjection.我仍然建议使用类型化参数。
虽然目前的实现捕获了任何注入尝试，但并不能真正保证最终会出现这种情况 - 希望您的应用程序将拥有漫长而繁荣的生命周期...=）
关于 SQL Server，MSDN有关该主题的文章可以在这里找到：
http://msdn.microsoft.com/en-us/library/ff648339。 ASPX
I would still suggest using typed parameters.
While the implementation catches any injection attempts AS FOR NOW, there is no real guarantee that this will be the case down the line - and hopefully your application will lead a long and prosperous lifetime... =)
With regard to SQL Server, the MSDN article on the subject can be found here:
http://msdn.microsoft.com/en-us/library/ff648339.aspx