如何创建参数化 SQL 查询?我为什么要? [英] How do I create a parameterized SQL query? Why Should I?

问题描述

我听说每个人"都在使用参数化 SQL 查询来防止 SQL 注入攻击，而无需验证用户输入的每一部分.

I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.

你是怎么做到的?使用存储过程时会自动获取吗?

How do you do this? Do you get this automatically when using stored procedures?

所以我的理解是非参数化:

So my understanding this is non-parameterized:

cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz)

这会被参数化吗?

cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz)

或者我是否需要做一些更广泛的事情来保护自己免受 SQL 注入?

Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?

With command
    .Parameters.Count = 1
    .Parameters.Item(0).ParameterName = "@baz"
    .Parameters.Item(0).Value = fuz
End With

除了安全考虑之外，使用参数化查询还有其他好处吗?

Are there other advantages to using parameterized queries besides the security considerations?

更新:这篇很棒的文章链接在 Grotok 的一个问题参考中.http://www.sommarskog.se/dynamic_sql.html

Update: This great article was linked in one of the questions references by Grotok. http://www.sommarskog.se/dynamic_sql.html

推荐答案

您的 EXEC 示例不会被参数化.您需要参数化查询(某些圈子中准备好的语句)以防止这样的输入造成损坏:

Your EXEC example would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:

';删除表格栏;--

试着把它放在你的 fuz 变量中(或者不要，如果你重视你的条形表).更微妙和破坏性的查询也是可能的.

Try putting that in your fuz variable (or don't, if you value your bar table). More subtle and damaging queries are possible as well.

以下是如何使用 Sql Server 设置参数的示例:

Here's an example of how you do parameters with Sql Server:

Public Function GetBarFooByBaz(ByVal Baz As String) As String
    Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@Baz", SqlDbType.VarChar, 50).Value = Baz
        Return cmd.ExecuteScalar().ToString()
    End Using
End Function

存储过程有时被认为可以防止 SQL 注入.但是，大多数时候您仍然必须使用查询参数调用它们，否则它们无济于事.如果您独占使用存储过程，那么您可以关闭应用程序用户帐户的 SELECT、UPDATE、ALTER、CREATE、DELETE 等(几乎除了 EXEC 之外的所有内容)的权限，并通过这种方式获得一些保护.

Stored procedures are sometimes credited with preventing SQL injection. However, most of the time you still have to call them using query parameters or they don't help. If you use stored procedures exclusively, then you can turn off permissions for SELECT, UPDATE, ALTER, CREATE, DELETE, etc (just about everything but EXEC) for the application user account and get some protection that way.

这篇关于如何创建参数化 SQL 查询?我为什么要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！