在SQL Server中使用存储过程中的变量为参数赋值

I have a simple stored procedure with one parameter @Name which I want to replace with another variable.

我有一个带有一个参数@Name的简单存储过程,我想用另一个变量替换它。

I am actually looking for SQL injection character and if name contains -- then it should replace it with blank. The stored procedure shown below, it is executing without an error, but not replacing the string for example let is say user searches for EXEC John'''select * FROM TEST2 -- which has SQL injection statement in it

我实际上正在寻找SQL注入字符,如果名称包含 - 那么它应该用空格替换它。下面显示的存储过程,它正在执行而没有错误,但是没有替换字符串,例如,让我们说用户搜索EXEC John'''select * FROM TEST2 - 其中有SQL注入语句

CREATE PROCEDURE GetStudentDetails
    @Name nvarchar(300)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Name = REPLACE(@Name ,'--','');
    SET @Name = REPLACE(@Name ,'--','');

    SELECT * 
    FROM TABLENAME 
    WHERE Name LIKE N'%'+ @Name +'%'
END

Updated stored procedure:

更新的存储过程:

CREATE PROCEDURE GetStudentDetails
    @Name nvarchar(300)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SafeSearchItem   nvarchar(30);

    SELECT  @SafeSearchItem = REPLACE(@Name ,N'--',N'')
    SET  @SafeSearchItem = REPLACE(@Name ,N'--',N'')

    SELECT * 
    FROM TABLENAME 
    WHERE Name LIKE N'%'+ @SafeSearchItem +'%'
END

EXEC

EXEC John'''select * FROM TEST2 --

In the second stored procedure, I am always able to inject SQL - not sure it is my system?

在第二个存储过程中,我总是能够注入SQL - 不确定它是我的系统吗?

1 个解决方案

#1

As it stands, we can't answer the question, as, well there isn't a question applicable for information we're been provided. There is no risk of injection in the SP we have, thus, there is not answer on how to avoid it.

目前,我们无法回答这个问题,因为我们提供的信息不存在适用的问题。在我们的SP中没有注射风险,因此,没有答案如何避免它。

Anyway, instead, what i'm going to do is show firstly why that SP isn't subject to injection and then change it so it would be, and how the limited "fix" in it could easily be avoided.

无论如何,相反,我将要做的是首先展示为什么SP不受注射然后改变它所以它将如何,以及如何可以容易地避免其中有限的“修复”。

Firstly, let's start with a simple table and data (I strongly suggest running any following scripts in a Sandbox environment!):

首先,让我们从一个简单的表和数据开始(我强烈建议在Sandbox环境中运行以下任何脚本!):

USE Sandbox;
GO

CREATE TABLE InjectionReady (ID int IDENTITY(1,1), SomeText varchar(500));

INSERT INTO InjectionReady
VALUES ('Here is some text'),
       ('Life is like a box a chocolates'),
       ('Milk Chocolate is my favourite'),
       ('Cheese is dairy product'),
       ('Chocolate is a dairy product'),
       ('Cows say "moo"!'),
       ('English Cat says "Meow"'),
       ('Japanese Cat says "Nyaa"');
GO

OK, and now let's create your SP (amended for our object). and then do some tests:

好了,现在让我们创建你的SP(针对我们的对象进行修改)。然后做一些测试:

CREATE PROCEDURE NonInjectionSearch @Wildcard nvarchar(100) AS


    SELECT @Wildcard = REPLACE(@Wildcard ,N'--',N'');
    SET @Wildcard = REPLACE(@Wildcard ,N'--',N'');

    SELECT *
    FROM InjectionReady
    WHERE SomeText LIKE N'%'+ @Wildcard +N'%';

GO
EXEC NonInjectionSearch 'Chocolate';
EXEC NonInjectionSearch '''; DROP TABLE InjectionReady;--';
EXEC NonInjectionSearch '''; DROP TABLE InjectionReady; SELECT ''';

No injection. Great! Ok, now for an SP that could suffer injection:

没有注射。大!好的,现在对于可能遭受注射的SP:

CREATE PROCEDURE InjectionSearch @Wildcard nvarchar(100) AS


    SELECT @Wildcard = REPLACE(@Wildcard ,N'--',N'');
    SET @Wildcard = REPLACE(@Wildcard ,N'--',N'');

    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'
    SELECT *
    FROM InjectionReady
    WHERE SomeText LIKE N''%'+ @Wildcard + N'%'';'; --Yes, intentional non parametrisation
    PRINT @SQL;
    EXEC (@SQL);

GO
EXEC InjectionSearch 'Chocolate';
GO
EXEC InjectionSearch '''; CREATE TABLE Injection1(ID int);--'; --This'll fail
GO
EXEC InjectionSearch '''; CREATE TABLE Injection2(ID int); SELECT '''; --Oh! This worked!

GO

So, how could you avoid this? Well, Parametrise your dynamic SQL:

那么,你怎么能避免这种情况呢?那么,参数化您的动态SQL:

CREATE PROCEDURE ParamSearch @Wildcard nvarchar(100) AS

    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'
    SELECT *
    FROM InjectionReady
    WHERE SomeText LIKE N''%'' + @pWildCard +''%'';'; --Yes, intentional non parametrisation
    PRINT @SQL;
    EXEC sp_executesql @SQL, N'@pWildcard nvarchar(500)', @pWildCard = @Wildcard;
GO

EXEC ParamSearch 'Chocolate';
GO
EXEC ParamSearch '''; CREATE TABLE Injection1(ID int);--'; --Won't inject
GO
EXEC ParamSearch '''; CREATE TABLE Injection2(ID int); SELECT '''; --Oh! this didn't inject either

Dynamic objects bring another level to this, however, I'll only cover this if required; as it stands (like I said at the start) the question asked can't happen for the scenario we have.

动态对象为此带来了另一个层次,但是,如果需要,我只会覆盖它;就像它一样(就像我在开始时所说的那样)问题不会发生在我们的情景中。

Clean up:

DROP TABLE Injection2;
DROP PROC ParamSearch;
DROP PROC InjectionSearch;
DROP PROC NonInjectionSearch;
DROP TABLE InjectionReady;

#1