Solved

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

Posted on 2010-08-16
19
346 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:shelbyinfotech
  • 6
  • 6
  • 3
  • +1
19 Comments
 
LVL 5

Expert Comment

by:JayFromPep
ID: 33448654
you will have to either change the single quote or escape it out.
0
 

Author Comment

by:shelbyinfotech
ID: 33448664
To what and how ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33448784
Use a replace around your variable that will replace all instances of ' with '' (two single quotes).
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Expert Comment

by:JayFromPep
ID: 33448791
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33448796
e.g., sDbWhere could be re-written to:
sDbWhere.replace("'", "''")

Open in new window

0
 

Author Comment

by:shelbyinfotech
ID: 33449023
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33449384
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
 

Author Comment

by:shelbyinfotech
ID: 33449416
Already checked that out also. I
0
 

Author Comment

by:shelbyinfotech
ID: 33449459
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
 
LVL 5

Expert Comment

by:JayFromPep
ID: 33449487
your last comment was truncated.  Can you repost it?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
ID: 33449712
SqlCommand has an AddParameter method. Use actually parameters and see if that helps versus concatenating as string.
0
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 400 total points
ID: 33449784
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33449805
Exactly what I was talking about. Thanks for showing code example @Snarf0001 to make it clearer.
0
 

Author Comment

by:shelbyinfotech
ID: 33449907
I am in process of testing and will get back with you all tommorow AM
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 33450061
Sorry for the trump there mwvisa1, didn't see your 1st comment before I posted exactly the same idea ;)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33450131
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
 

Author Closing Comment

by:shelbyinfotech
ID: 33455248
This works EXACTLY
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question