此代码是否可以防止 SQL 注入? [英] Does this code prevent SQL injection?

问题描述

我已签约分析现有的数据提供程序，但我知道以下代码有问题；但为了指出它有多糟糕，我需要证明它容易受到 SQL 注入的影响.

I've been contracted to analyze an existing Data Provider and I know the following code is faulty; but in order to point out how bad it is, I need to prove that it's susceptible to SQL injection.

什么钥匙"?参数可能会破坏 PrepareString 函数并允许我执行 DROP 语句?

What "Key" parameter could break the PrepareString function and allow me to execute a DROP statement?

Public Shared Function GetRecord(ByVal Key As String) As Record
    Dim Sql As New StringBuilder()

    With Sql
        .Append("SELECT * FROM TableName")
        If String.IsNullOrEmpty(Agency) Then
            .Append(" ORDER BY DateAdded")
        Else
            .Append(" WHERE Key = '")
            .Append(PrepareString(Key))
            .Append("'")
        End If
    End With

    Return ExecuteQuery(Sql.ToString())
End Function

Public Shared Function PrepareString(ByVal Value As String) As String
    Return Value.Replace("''", "'") _
                .Replace("'", "''") _
                .Replace("`", "''") _
                .Replace("´", "''") _
                .Replace("--", "")
End Function

推荐答案

回答您的直接问题:此代码是否防止 SQL 注入:否

In answer to your direct question: Does this code prevent SQL injection: No

这是证明 - 通过 PrepareString 方法推送此字符串:

Here's the proof - push this string through the PrepareString method:

Dim input = "'" & Chr(8) & "; Drop Table TableName; - " & Chr(8) & "-"
Dim output = PrepareString(input)

Console.WriteLine(input)
Console.WriteLine(output)

我修改了您发布的 GetRecord 方法以返回完全准备好的 SQL 字符串，而不是从数据库中获取记录:

I modified the GetRecord method you posted to return the fully prepared SQL string rather than get the record from the database:

Console.WriteLine(GetRecord(output))

这是输出

Input  = ; Drop Table TableName; --
Output = '; Drop Table TableName; --
Query  = SELECT * FROM TableName WHERE Key = ''; Drop Table TableName; --'

添加 1 行额外代码:

Add 1 extra line of code:

My.Computer.Clipboard.SetText(input)

并且您已经将需要的字符串直接复制到剪贴板以粘贴到网站上的输入字段中以完成 SQL 注入:

And you've got the string you need copied right to your clipboard to paste into your input field on the website to complete your SQL injection:

'; Drop Table TableName; - -

[注意 StackOverflow 的后输出中省略了控制字符，因此您必须按照代码示例创建输出]

[Noting that the control characters have been omitted from the post output by StackOverflow, so you'll have to follow the code example to create your output]

在 PrepareString 方法运行后，它将具有完全相同的输出 - Chr(8) ASCII 代码是退格键，它将删除您附加到我的额外的'"，这将关闭您的字符串，然后最后我可以随意添加任何我想要的东西.您的 PrepareString 没有看到我的 -- 因为我实际上正在使用 -- 带有退格字符来删除空格.

After the PrepareString method is run, it will have the exact same output - the Chr(8) ASCII code is the backspace which will remove the extra "'" that you're appending to mine which will close your string and then I'm free to add whatever I want on the end. Your PrepareString doesn't see my -- because I'm actually using - - with a backspace character to remove the space.

您正在构建的结果 SQL 代码将不受阻碍地执行我的 Drop Table 语句，并立即忽略查询的其余部分.

The resulting SQL code that you're building will then execute my Drop Table statement unhindered and promptly ignore the rest of your query.

有趣的是，您可以使用不可打印的字符基本上绕过您可以发明的任何字符检查.因此，使用参数化查询是最安全的(这不是您所要求的，但却是避免这种情况的最佳途径).

The fun thing about this is that you can use non-printable characters to basically bypass any character check you can invent. So it's safest to use parameterized queries (which isn't what you asked, but is the best path to avoid this).

这篇关于此代码是否可以防止 SQL 注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！