Dynamic where clause in stored procedure bombs with single qoutes SQL Server

I have a stored procedure which attempts to use a dynamic where clause, but it will not work as the where sometimes in cases with like and string variables has a single quote in it.

SQL Server 2000, VB.NET 1.1
Public Function ewRecordCountNew(ByVal sSql As String, ByVal sDbWhere As String) As Integer
            Dim SqlCommand As String = "exec DataPagerCount @datasrc=" + sSql + " ," + "@filter = " + sDbWhere

            Try

                ' Create a new Connection Object using the Connection String
                Dim oConn As New SqlConnection(ewConnStr)

                ' Create a new DataAdapter using the Connection Object and SQL statement
                Dim oDa As New SqlDataAdapter(SqlCommand, oConn)

                ' Create a new DataSet object to fill with Data
                Dim oDs As New DataSet

                ' Fill the DataSet with Data from the DataAdapter Object
                oDa.Fill(oDs, "ewDataSet")
                Return oDs.Tables(0).Rows(0)(0)
            Catch oErr As SqlException
                _DataErrorMessage = ewDataErrorMessage(oErr)
                Return 0
            End Try
        End Function

Open in new window

Public Function ewRecordCountNew(ByVal sSql As String, ByVal sDbWhere As String) As Integer
            Dim SqlCommand As String = "exec DataPagerCount @datasrc=" + sSql + " ," + "@filter = " + sDbWhere

            Try

                ' Create a new Connection Object using the Connection String
                Dim oConn As New SqlConnection(ewConnStr)

                ' Create a new DataAdapter using the Connection Object and SQL statement
                Dim oDa As New SqlDataAdapter(SqlCommand, oConn)

                ' Create a new DataSet object to fill with Data
                Dim oDs As New DataSet

                ' Fill the DataSet with Data from the DataAdapter Object
                oDa.Fill(oDs, "ewDataSet")
                Return oDs.Tables(0).Rows(0)(0)
            Catch oErr As SqlException
                _DataErrorMessage = ewDataErrorMessage(oErr)
                Return 0
            End Try
        End Function

Open in new window

shelbyinfotechAsked:
Who is Participating?
 
Snarf0001Connect With a Mentor Commented:
Using the embedded SqlCommand object makes escaping quotes like this far easier, would suggest just changing the .net function to use a SqlCommand object of CommandType.StoredProcedure


    Public Function ewRecordCountNew(ByVal sSql As String, ByVal sDbWhere As String) As Integer
        'Dim SqlCommand As String = "exec DataPagerCount @datasrc=" + sSql + " ," + "@filter = " + sDbWhere


        Try

            ' Create a new Connection Object using the Connection String
            Dim oConn As New SqlConnection(ewConnStr)

            Dim SqlCommand As New SqlCommand("DataPagerCount", oConn)
            SqlCommand.CommandType = CommandType.StoredProcedure
            SqlCommand.Parameters.Add(New SqlParameter("@datasrc", sSql))
            SqlCommand.Parameters.Add(New SqlParameter("@filter", sDbWhere))

            ' Create a new DataAdapter using the Connection Object and SQL statement
            'Dim oDa As New SqlDataAdapter(SqlCommand, oConn)
            Dim oDa As New SqlDataAdapter(SqlCommand)


            ' Create a new DataSet object to fill with Data
            Dim oDs As New DataSet

            ' Fill the DataSet with Data from the DataAdapter Object
            oDa.Fill(oDs, "ewDataSet")
            Return oDs.Tables(0).Rows(0)(0)
        Catch oErr As SqlException
            _DataErrorMessage = ewDataErrorMessage(oErr)
            Return 0
        End Try
    End Function

Open in new window

0
 
JayFromPepCommented:
you will have to either change the single quote or escape it out.
0
 
shelbyinfotechAuthor Commented:
To what and how ?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kevin CrossChief Technology OfficerCommented:
Use a replace around your variable that will replace all instances of ' with '' (two single quotes).
0
 
JayFromPepCommented:
When you do the query, are you using the single quote as part of a search string, as in....

Select * from myTable where col1="Don't eat the brownies"

or is it just part of text that is being returned?

If you are using it as a search string, then the problem is that when you render the sql query in your code behind, the single quote acts as a text delimiter.

I beleive you can use a forward slash "/" to escape the character. (" /' ").

If the single quote is not important, then just do a replace on the string that is causing you problems and remove it...

myString.replace("'", "")

the replace uses the following format

Mystring -- the string to work with
First string in () -- the string to find and replace
second string in () -- the string to put in the string to work with.

0
 
Kevin CrossChief Technology OfficerCommented:
e.g., sDbWhere could be re-written to:
sDbWhere.replace("'", "''")

Open in new window

0
 
shelbyinfotechAuthor Commented:
Thank you all but none of these seem to work...the clause


([ACCOUNT_ID] LIKE '%fun%' OR [YEAR] LIKE '%fun%' OR [COMPANY] LIKE '%fun%')


returns Message: Line 1: Incorrect syntax near '('.
Line Number: 1
Source: .Net SqlClient Data Provider
0
 
Kevin CrossChief Technology OfficerCommented:
That is not pointing to quote but to parenthesis. Have you tried running the SQL query that you are trying to push through .NET directly in SSMS? Just make sure you have valid SQL syntax to begin with and then you can focus on the quoting issue.
0
 
shelbyinfotechAuthor Commented:
Already checked that out also. I
0
 
shelbyinfotechAuthor Commented:
In fact, when passing the parameters exactly like that in SMSS, it works, just not from my code, is thir some limitations i am not aware  of ?
0
 
JayFromPepCommented:
your last comment was truncated.  Can you repost it?
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
SqlCommand has an AddParameter method. Use actually parameters and see if that helps versus concatenating as string.
0
 
Kevin CrossChief Technology OfficerCommented:
Exactly what I was talking about. Thanks for showing code example @Snarf0001 to make it clearer.
0
 
shelbyinfotechAuthor Commented:
I am in process of testing and will get back with you all tommorow AM
0
 
Snarf0001Commented:
Sorry for the trump there mwvisa1, didn't see your 1st comment before I posted exactly the same idea ;)
0
 
Kevin CrossChief Technology OfficerCommented:
No worries. It is hopefully more clearer in code plus shows it is Parameters.Add which is what I meant but miss typed so that was a genuine thanks.
0
 
shelbyinfotechAuthor Commented:
This works EXACTLY
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.