Solved

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

Posted on 2010-08-16
19
341 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
Comment Utility
you will have to either change the single quote or escape it out.
0
 

Author Comment

by:shelbyinfotech
Comment Utility
To what and how ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Use a replace around your variable that will replace all instances of ' with '' (two single quotes).
0
 
LVL 5

Expert Comment

by:JayFromPep
Comment Utility
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
Comment Utility
e.g., sDbWhere could be re-written to:
sDbWhere.replace("'", "''")

Open in new window

0
 

Author Comment

by:shelbyinfotech
Comment Utility
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
Comment Utility
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
Comment Utility
Already checked that out also. I
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:shelbyinfotech
Comment Utility
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
Comment Utility
your last comment was truncated.  Can you repost it?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Exactly what I was talking about. Thanks for showing code example @Snarf0001 to make it clearer.
0
 

Author Comment

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

Expert Comment

by:Snarf0001
Comment Utility
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
Comment Utility
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
Comment Utility
This works EXACTLY
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now