SQL存储过程是否可以安全存储过程？

I have a method that inserts records into the database using normal parameterized SQL query.

我有一个方法,使用常规参数化SQL查询将记录插入数据库。

public static void Add(string name, string friendlyName)
    {
    using (var db = Database.Open(_connectionString))
    {
        var sql = "INSERT INTO Tags(Name, UrlFriendlyName) " +
            "VALUES(@0,@1)";
            db.Execute(sql, name, friendlyName);

    }
}

I changed the method so that it uses a stored procedure instead

我改变了方法,以便它使用存储过程

public static void Add(string name, string friendlyName)
{
    using (var db = Database.Open(_connectionString))
    {

        // USE STORED PROCEDURE
        var query = "Exec insertTags @name='" + name + "', @urlFriendlyName= '" + friendlyName + "'";
        db.Execute(query);

    }
}

The stored procedure looks like the following

存储过程如下所示

CREATE PROCEDURE insertTags
@name nvarchar(25),
@urlFriendlyName nvarchar(25)
AS
BEGIN
BEGIN Transaction
BEGIN TRY
INSERT INTO dbo.Tags(Name, UrlFriendlyName) 
VALUES(@name, @urlFriendlyName)
IF @@TRANCOUNT > 0
BEGIN COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN ROLLBACK TRANSACTION;
END
END CATCH

END

Both of these methods work just fine. My question is, does the method that utilize the stored procedure provide security against SQL injections like the first one does? Thanks.

这两种方法都可以正常工作。我的问题是,利用存储过程的方法是否能像第一个那样提供针对SQL注入的安全性?谢谢。

EDIT

I changed the second method to use parameters instead of string concatenation and it works. Is this safe against SQL injections?

我改变了第二种方法来使用参数而不是字符串连接,它可以工作。这对SQL注入是否安全?

public static void Add(string name, string friendlyName)
{
    using (var db = Database.Open(_connectionString))
    {

        // USE STORED PROCEDURE
        var query = "Exec insertTags @0, @1";
        db.Execute(query, name, friendlyName);

    }
}

3 个解决方案

#1

You need to use parameters instead of string concatenations. In this case you will be safe

您需要使用参数而不是字符串连接。在这种情况下,您将是安全的

#2

No, it doesn't. As soon as you construct a SQL statement by concatenating strings together, you are at risk of SQL injection.

不,它没有。只要通过将字符串连接在一起构造SQL语句,就会面临SQL注入的风险。

For example:

"Exec insertTags @name='" + name

Someone could pass: "Anything'; DROP TABLE xxx;" for name, and potentially break your database. You can still use parameterized queries with stored procedures, so they can offer the same protection, just not in the way you're calling it in your example.

有人可以通过:“任何事情; DROP TABLE xxx;”对于名称,可能会破坏您的数据库。您仍然可以对存储过程使用参数化查询,因此它们可以提供相同的保护,而不是在您的示例中调用它的方式。

#3

Any use of string concatenation in your application even when using a stored procedure automatically makes the execution of that code vulnerable to SQL injection.

即使在使用存储过程时,在应用程序中使用字符串连接也会使该代码的执行容易受到SQL注入的攻击。

In ADO.NET, the correct method of executing a stored procedure is to use the DbCommand classes with the CommandType set to CommandType.StoredProcedure and providing the stored procedure parameters in the Parameters collection.

在ADO.NET中,执行存储过程的正确方法是使用CommandType设置为CommandType.StoredProcedure的DbCommand类,并在Parameters集合中提供存储过程参数。

Link to MSDN: System.Data.Common.DbCommand

链接到MSDN:System.Data.Common.DbCommand

#1