ASP Classic - 记录集对象与命令对象

发布于 2024-09-13 04:38:03 字数 397 浏览 1 评论 0原文

我正在使用 ASP Classic 和 SQL Server 2000 创建动态网站。

我对查询数据库时何时使用记录集对象以及何时使用命令对象有点困惑。

有人告诉我，如果存储过程将从 SELCT 语句返回记录，那么我应该使用记录集，但是如果我要更新或插入，那么我应该使用命令对象并将所有数据作为参数传递给存储过程。

使用记录集时，我经常传递任何所需的数据，如下所示：

rs.Source = "spTest "   & id

我总是验证传递的数据以确保它是我所期望的并将其转换为正确的类型。

然而，后来我被告知，上述方法使我的代码容易受到 SQL 注入攻击，并且我应该始终使用命令对象。

这是正确的吗？

谢谢

原文

I am using ASP Classic and SQL Server 2000 to create dynamic websites.

I am a bit confused about when to use a recordset object and when to use a command object when querying the database.

I was told that if the stored procedure would be returning records from a SELCT statement then I should use a recordset, however if I am up updating or inserting then I should use a command object and pass all data as parameters to the stored procedure.

When using a recordset I often pass any required data like so:

rs.Source = "spTest "   & id

I alway validate the data that I am passing to make sure it is what I am expecting and cast it to its correct type.

I have since been told however that the above method leaves my code open to SQL Injection attacks and that I should always use a command object.

Is this correct?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乱了心跳 2024-09-20 04:38:03

是的，没错。

想象一下有人传递字符串：'0;从用户中删除*；'

那么您的查询将是：

spTest 0; delete * from users;

如果幸运的话，您将不会有用户表。就我个人而言，为了保持一致性，我会一直使用命令对象。你可以从中得到你需要的一切。

以下是如何使用命令对象执行此操作的快速示例：

    Dim oStoredProc : Set oStoredProc = Server.CreateObject("ADODB.Command")

    With oStoredProc
        .ActiveConnection = oDBConnection
        .CommandType = adCmdStoredProc
        .CommandText = "up_procname"
        .Parameters.Append(.CreateParameter("@Param1", ADODB.adInteger, ADODB.adParamInput, 22, 11))
        .Parameters.Append(.CreateParameter("@Param2", ADODB.adInteger, ADODB.adParamOutput, 22, 12)

        Call .Execute()

        myVal = .Parameters("@Param2")
    End With

    Set oStoredProc = Nothing

Yes, that's right.

Imagine someone passing the string: '0; delete * from users;'

You query would then be:

spTest 0; delete * from users;

If you're lucky you won't have a users table. Personally, I would use the command object all the time for consistency. You can get everything you need from it.

Here is a quick example of how you might do it with the command object:

    Dim oStoredProc : Set oStoredProc = Server.CreateObject("ADODB.Command")

    With oStoredProc
        .ActiveConnection = oDBConnection
        .CommandType = adCmdStoredProc
        .CommandText = "up_procname"
        .Parameters.Append(.CreateParameter("@Param1", ADODB.adInteger, ADODB.adParamInput, 22, 11))
        .Parameters.Append(.CreateParameter("@Param2", ADODB.adInteger, ADODB.adParamOutput, 22, 12)

        Call .Execute()

        myVal = .Parameters("@Param2")
    End With

    Set oStoredProc = Nothing

回复收藏 0 原文

蹲墙角沉默 2024-09-20 04:38:03

您被告知的确实是正确的：您应该始终使用命令对象来防止 SQL 注入。，甚至可能会获得一些性能改进（这些参数化查询由 SQL Server 缓存）。

使用参数化查询，您可以将参数的所有安全性和验证工作留给 ADO 层（尽管您仍然应该自己进行适当的验证）一个命令有两个选择：要么执行的 SQL 返回行（SELECT 语句或某些存储过程），然后必须使用记录集来存储这些行，要么不返回行（UPDATES、DELETES、其他过程），那么你只需执行命令而不用担心记录集。

编辑：只是为了确保一切对你来说都清楚，我使用了上面的 James Wiseman 的代码并将其改编为你的示例：

Dim oStoredProc : Set oStoredProc = Server.CreateObject("ADODB.Command")

With oStoredProc
    .ActiveConnection = oDBConnection
    .CommandType = adCmdStoredProc
    .CommandText = "spTest ?"
    .Parameters.Append(.CreateParameter("id", ADODB.adInteger, ADODB.adParamInput, id, 11))
    Dim rs : Set rs = .Execute()
End With

Set oStoredProc = Nothing

没有测试它，但应该没问题:-)

最后但并非最不重要的：即使你很好现在受到保护，不要忘记，如果您在存储过程中使用动态 SQL，您可能仍然存在 SQL 注入安全漏洞（我想说，一旦您连接字符串来创建 SQL，您可能很容易受到攻击）！

What you were told is correct indeed : you should always use commande objects to prevent SQL Injection. Using parameterized queries, you leave all the security and validation of parameters to the ADO layer (though you should still do your own proper validation), and you may even get some performance improvement (these parameterized queries are cached by SQL Server)

When you execute a command you have two options : either the SQL you execute returns rows (A SELECT Statement, or some stored procedures), then you have to use a recordset to store these rows, either it doesn't (UPDATES, DELETES, other procedures), then you juste execute the command and do not worry about recordsets.

Edit : just to make sure everything is clear for you, I used James Wiseman's code above and adapted it to your example :

Dim oStoredProc : Set oStoredProc = Server.CreateObject("ADODB.Command")

With oStoredProc
    .ActiveConnection = oDBConnection
    .CommandType = adCmdStoredProc
    .CommandText = "spTest ?"
    .Parameters.Append(.CreateParameter("id", ADODB.adInteger, ADODB.adParamInput, id, 11))
    Dim rs : Set rs = .Execute()
End With

Set oStoredProc = Nothing

Didn't test it, but should be ok :-)

Last but not least : even though you're pretty well protected now, don't forget that if you're using dynamic SQL inside your stored procedure you may still have an SQL Injection security hole (as soon as you're concatenating strings to create SQL you may be vulnerable I would say) !

回复收藏 0 原文

~没有更多了~