Solved

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

Posted on 2010-08-16
19
344 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
 
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
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.

 

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

Backup Your Microsoft Windows Server®

Backup 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get the web content with different encodings 2 30
Add '#' to end of file 2 31
Update data using formula 22 23
Alter a column in sql 34 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

23 Experts available now in Live!

Get 1:1 Help Now