none
Parametrized SQL select with like command RRS feed

  • Question

  • I want to use parameters in my select command to immune my project to sql injection
    My sql select command includes a "like" command

    I couldn't find any samples that shows how to do such a thing,
    Here is what I could come up with:

     
     Dim strSQLQuery As String = "select column_id from table_searchenteries where (column_StdFamily like @strFamilyNameReverseFirst6%)"
            Dim nonqueryCommand1 As SqlCommand = objDatabase.MainConnection.CreateCommand() 
            nonqueryCommand1.CommandText = strSQLQuery 
            nonqueryCommand1.Parameters.Add("@strFamilyNameReverseFirst6", SqlDbType.NVarChar, 100) 

            nonqueryCommand1.Prepare() 
     
            nonqueryCommand1.Parameters("@strFamilyNameReverseFirst6").Value = objPerson.strFamilyNameReverseFirst6 

     
            Dim reader As SqlDataReader = nonqueryCommand1.ExecuteReader() 
     

    This is the error it generates:
    Incorrect syntax near ')'. Statement(s) could not be prepared.

    How am I supposed to do this?
    Should I use Quotes ? when I use quotes sql seems to ignore my parameters and does not replace them with the values I provide

    Any sample or correction would be highly appreciated
    • Moved by VMazurModerator Monday, March 16, 2009 10:12 AM Moving to correct forum (Moved from ADO.NET DataSet to ADO.NET Data Providers)
    Saturday, March 14, 2009 4:04 AM

All replies

  • Hi,

    Try this

    comm.CommandText =
    "select column_id from table_searchenteries where (column_StdFamily like " & _ 
                                     "   @strFamilyNameReverseFirst6 + '%')"

    For further reference please check this link
    http://stackoverflow.com/questions/303149/parameterized-queries-with-like-and-in-conditions


    Zahid
    Saturday, March 14, 2009 9:30 PM
  • Dear Zahid,
    Thank you for your reply
    I tried what you recommended it didn't work, it did not generate any errors but it did not return any results, when I try this:

    select column_id from table_searchenteries where (column_StdFamily like N'ستوده' + '%' )

    I get 3 results, but when I did what you said it didn't return anything

    I also had a look at the link you provided, someone had recommended "%" should be added when adding a value to the parameter
    So I tried this:

      Dim strSQLQuery As String = "select column_id from table_searchenteries where (column_StdFamily like @strFamilyNameReverseFirst6)" ' And (column_RmvStdName like @strFirstNameReverseFirst3%)" '' Or ISNULL(MBname) Or ISNULL(First3LettersOfSuitableName))" 
            Dim nonqueryCommand1 As SqlCommand = objDatabase.MainConnection.CreateCommand() 
            nonqueryCommand1.CommandText = strSQLQuery 
            nonqueryCommand1.Parameters.Add("@strFamilyNameReverseFirst6", SqlDbType.NVarChar, 100) 

            nonqueryCommand1.Prepare() 
     
            nonqueryCommand1.Parameters("@strFamilyNameReverseFirst6").Value = objPerson.strFamilyNameReverseFirst6 + "%" 
     

    Still didn't generate any errors but did not also return any results

    Thanks for your help
    Sunday, March 15, 2009 3:37 AM
  • To correct issue you ned to pass % wildcard as a part of the value, not as a part of your SQL statement. Something like

    Dim strSQLQuery As String = "select column_id from table_searchenteries where (column_StdFamily like @strFamilyNameReverseFirst6)"
    Dim nonqueryCommand1 As SqlCommand = objDatabase.MainConnection.CreateCommand() 
    nonqueryCommand1.CommandText = strSQLQuery 
    nonqueryCommand1.Parameters.Add("@strFamilyNameReverseFirst6", SqlDbType.NVarChar, 100) 
    nonqueryCommand1.Prepare() 
     
    nonqueryCommand1.Parameters("@strFamilyNameReverseFirst6").Value = objPerson.strFamilyNameReverseFirst6 &"%"

     
        Dim reader As SqlDataReader = nonqueryCommand1.ExecuteReader() 

    Val Mazur (MVP) http://www.xporttools.net
    Monday, March 16, 2009 10:12 AM
    Moderator