我的用户可以注入我的动态 sql 吗? [英] Can my users inject my dynamic sql?

问题描述

我是为内部用户编写的桌面开发人员，所以我不担心恶意黑客，但我想知道在更新将在服务器上执行 sql 的值时，他们是否可以输入任何内容.

I'm a desktop developer writing for internal users, so I'm not worried about malicious hackers, but I would like to know if there's anything they could enter when updating a value that would execute sql on the server.

业务定义了他们的内容架构，我为他们提供了一个 CRUD 应用程序，当他们的架构更改时不必更改，因为验证细节是表驱动的，更新是使用动态 SQL.我必须在他们的数据输入中支持单引号，所以当他们输入它们时，我在服务器上执行 SQL 之前将它们加倍.然而，根据我的阅读，这不足以停止注射.

The business defines their content schema and I have a CRUD application for them that doesn't have to be changed when their schema changes because the validation details are table-driven and the updates are with dynamic SQL. I have to support single quotes in their data entry, so when they enter them, I double them before the SQL is executed on the server. From what I've read, however, this shouldn't be enough to stop an injection.

所以我的问题是，他们可以在自由格式的文本字段中输入哪些文本来更改服务器上的某些内容而不是存储为文字值?

So my question is, what text could they enter in a free-form text field that could change something on the server instead of being stored as a literal value?

基本上，我正在运行时构建一个遵循以下模式的 SQL 语句:

Basically, I'm building an SQL statement at runtime that follows the pattern:

update table set field = value where pkField = pkVal

update table set field = value where pkField = pkVal

使用此 VB.NET 代码:

with this VB.NET code:

Friend Function updateVal(ByVal newVal As String) As Integer
    Dim params As Collection
    Dim SQL As String
    Dim ret As Integer

    SQL = _updateSQL(newVal)
    params = New Collection
    params.Add(SQLClientAccess.instance.sqlParam("@SQL", DbType.String, 0, SQL))
    Try
        ret = SQLClientAccess.instance.execSP("usp_execSQL", params)
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
    Return ret
End Function

Private Function _updateSQL(ByVal newVal As String) As String
    Dim SQL As String
    Dim useDelimiter As Boolean = (_formatType = DisplaySet.formatTypes.text)
    Dim position As Integer = InStr(newVal, "'")
    Do Until position = 0
        newVal = Left(newVal, position) + Mid(newVal, position)       ' double embedded single quotes '
        position = InStr(position + 2, newVal, "'")
    Loop
    If _formatType = DisplaySet.formatTypes.memo Then
        SQL = "declare @ptrval binary(16)"
        SQL = SQL & " select @ptrval = textptr(" & _fieldName & ")"
        SQL = SQL & " from " & _updateTableName & _PKWhereClauses
        SQL = SQL & " updatetext " & _updateTableName & "." & _fieldName & " @ptrval 0 null '" & newVal & "'"
    Else
        SQL = "Update " & _updateTableName & " set " & _fieldName & " = "
        If useDelimiter Then
            SQL = SQL & "'"
        End If
        SQL = SQL & newVal
        If useDelimiter Then
            SQL = SQL & "'"
        End If
        SQL = SQL & _PKWhereClauses
    End If
    Return SQL
End Function

当我将文本字段更新为值时

when I update a text field to the value

雷德蒙德';删除表 OrdersTable--

Redmond'; drop table OrdersTable--

它生成:

Update caseFile set notes = 'Redmond''; drop table OrdersTable--' where guardianshipID = '001168-3'

并将值更新为他们输入的文字值.

and updates the value to the literal value they entered.

他们还能输入什么来注入 SQL?

What else could they enter that would inject SQL?

同样，我不担心有人想在他们的工作中入侵服务器，但想知道他们是否会不小心从其他地方粘贴文本并破坏某些内容.

Again, I'm not worried that someone wants to hack the server at their job, but would like to know how if they could accidentally paste text from somewhere else and break something.

谢谢.

推荐答案

假设您转义字符串文字(根据您所说的内容)，您应该是安全的.我唯一能想到的另一件事是，如果您使用基于 unicode 的字符集与数据库进行通信，请确保您发送的字符串在该编码中是有效的.

Assuming you escape string literals (which from what you said you are doing), you should be safe. The only other thing I can think of is if you use a unicode-based character set to communicate with the database, make sure the strings you send are valid in that encoding.

这篇关于我的用户可以注入我的动态 sql 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！