none
vb.net 2010 使用oledbparameter操作ACCESS数据库问题 RRS feed

  • 问题

  • 请教各位一个OLEDB比较奇怪的问题

    我写个了应用,使用oledb调用access数据库,一开始使用的拼接字符串形式操作sql语句,由于存在严重的sql注入问题,后来改为用oledbparameter,但是发现在一些稍微复杂点的sql语句中,like会出现奇怪的问题,

    比如下面的语句运行完全正常,不管是中文查找还是英文查找

    select * From OPERATELOGTITLE where

    OperateUserIndexNum = 400000002

    and [OutTarget] Like ?

    and [TITLE] Like ?

    order by ID


    但是下面一句带两层嵌套的

    select * From OPERATELOGTITLE where

    OperateUserIndexNum = 400000002

    [TITLE] Like ? and IndexNum in 

    (select IndexNum from OPERATELOGDETAIL where STORE=1 and OPERATE_IndexNum in

    (select IndexNum from [ZJ] where DESC Like ?))

    order by ID

    两个?参数不能有中文,输入中文就没有任何返回,输入英文就没有问题,传入的参数是标准的string

    而使用之前的字符串拼接方式执行完全正常,由于对字符编码方面了解的不是太多,请教下各位,这个情况如何处理,谢谢


    2018年8月17日 2:34

全部回复

  • 你好,

    请问你使用的是什么版本的access, 可以提供一下你的代码吗?

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年8月21日 2:29
    版主
  • 您好

    数据库是用的access 2007创建的,但是调用的话是在server2012上安装的OLEDB支持包,并没有安装access

    部分代码如下

                Dim cmd As OleDbCommand
                Dim sql As String
                If IndexNum > 0 Then
                    sql = "select * From OPERATELOGTITLE where IndexNum=" & IndexNum
                Else
                    sql = "select * From OPERATELOGTITLE where " &
                                      "OperateTime >= #" & TimeStart.ToString("yyyy/MM/dd HH:mm:ss") & "# and " &
                                      "OperateTime <= #" & TimeEnd.ToString("yyyy/MM/dd HH:mm:ss") & "# and " &
                                      If(UserIndexNum = -1, "NOT OperateUserIndexNum IS NULL", "OperateUserIndexNum = " & UserIndexNum.ToString) & " and " &
                                      GetCaseTypeSqlString(Casetype) & " and " &
                                      If(String.IsNullOrEmpty(OutTarget), "", "[OutTarget] Like ? and ") &
                                      If(String.IsNullOrEmpty(CaseTitle), "", "[TITLE] Like ? and ") &
                                      If(String.IsNullOrEmpty(BEIZHU), "", "Beizhu Like ? and ")
    
                    Select Case Store
                        Case e_Store.ZJ
                            If SN = "" And PN = "" And DESC = "" Then
                                sql &= "IndexNum in  " &
                                            "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                            Else
                                sql &= "IndexNum in  " &
                                            "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                    "(select IndexNum from [ZJ] where " &
                                                    If(String.IsNullOrEmpty(SN), "", "SN Like ? and ") &
                                                    If(String.IsNullOrEmpty(PN), "", "PN Like ? and ") &
                                                    If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                                If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                                sql &= "))"
                            End If
                        Case e_Store.XJ
                            If PN = "" And DESC = "" Then
                                sql &= "IndexNum in  " &
                                            "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                            Else
                                sql &= "IndexNum in  " &
                                            "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                    "(select IndexNum from [XJ] where " &
                                                    If(String.IsNullOrEmpty(PN), "", "PN Like ? and ") &
                                                    If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                                If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                                sql &= "))"
                            End If
                        Case e_Store.CCJ
                            If SPN = "" And DESC = "" Then
                                sql &= "IndexNum in  " &
                                             "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                            Else
                                sql &= "IndexNum in  " &
                                            "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                    "(select IndexNum from [CCJ] where " &
                                                    If(String.IsNullOrEmpty(PN), "", "SPN Like ? and ") &
                                                    If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                                If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                                sql &= "))"
                            End If
                    End Select
                End If
                If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                sql &= " order by ID" 
    
                cmd = New OleDbCommand(sql, cn)
                If InStr(sql, "[OutTarget] Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@OutTarget", "%" & OutTarget & "%"))
                If InStr(sql, "[TITLE] Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@CaseTitle", "%" & CaseTitle & "%"))
                If InStr(sql, "Beizhu Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@BEIZHU", "%" & BEIZHU & "%"))
                If InStr(sql, "SN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@SN", "%" & SN & "%"))
                If InStr(sql, "PN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@PN", "%" & PN & "%"))
                If InStr(sql, "SPN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@SPN", "%" & SPN & "%"))
                If InStr(sql, "DESC Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@DESC", "%" & DESC & "%"))



    2018年8月21日 3:34