构建System.Data.OracleClient的使用一个很好的搜索查询 [英] Constructing a good search query using system.data.oracleclient

问题描述

我在一个类中构造一个搜索功能，通过我们的一些ASP页面中使用。这个想法很简单，拿来自用户的搜索词，查询数据库的项目。目前我这个做了错误的方式，这是容易受到SQL注入式攻击（和 ELMAH 就在那里保存一天，如果出现错误）：

I am constructing a search function in a class to be used by several of our asp pages. The idea is simple, take a search term from the user and query the database for the item. Currently I am doing this the wrong way, which is vulnerable to SQL injection attacks (and ELMAH is in there to save the day if something goes wrong):

Public Shared Function SearchByName(ByVal searchterm As String) As DataTable
    SearchByName = New DataTable

    Dim con As New OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OracleDB").ConnectionString)



    Try
        con.Open()
        Dim SqlStr As String = "select ID_ELEMENT, ELEMENT_NAME from table_of_elements where upper(ELEMENT_NAME) like upper('%" & searchterm & "%')"
        Dim cmd As New OracleCommand(SqlStr, con)
        SearchByName.Load(cmd.ExecuteReader)





    Catch ex As Exception
        Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

    End Try
    con.Close()
    con.Dispose()




    Return SearchByName
End Function

字符串连接是坏的。接下来你知道，鲍比表沉船我的系统。
现在，为了做到这一点，正确的做法是作出正确的Oracle变量，通过投入：搜索关键词串并添加以下行：

String concatenation is BAD. Next thing you know, Bobby Tables wrecks my system. Now, the correct way to do this is to to make a proper oracle variable, by putting :searchterm in the string and adding the following line:

cmd.Parameters.Add(New OracleParameter("SEARCHTERM", searchterm))

问题是，因为我用的是一样的语句，我需要能够有对％的搜索词的两侧，而我似乎无法做到这一点与'％：搜索关键词％'，它只是给ORA-01036的错误：非法变量名/编号

The problem is since I am using a like statement, I need to be able to have % on either side of the search word, and I can't seem to do that with '%:searchterm%', it just gives an error of ORA-01036: illegal variable name/number.

我可以参数化，但仍然有我喜欢灵活声明成为它的一部分吗？

Can I parameterize but still have my flexible like statement be a part of it?

推荐答案

而不是在你的VB code做级联，做SQL语句中的串联。然后，你所要做的工作应该是什么。下面是一些SQL说明什么，我说什么：

Instead of doing the concatenation in your VB code, do the concatenation in the SQL statement. Then what you're trying to do should work. Here's some SQL illustrating what I'm talking about:

select ID_ELEMENT, ELEMENT_NAME 
from table_of_elements 
where upper(ELEMENT_NAME) like ('%' || upper(:searchterm) || '%')

顺便说一句，你可以，如果你打开​​ELEMENT_NAME的collaction不区分大小写的，然后删除对通话结束了更高效的查询上（）。

BTW, you might end up with more efficient queries if you switch the collaction on ELEMENT_NAME to case-insensitive and then remove the calls to upper().

这篇关于构建System.Data.OracleClient的使用一个很好的搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！