Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-16
19
Medium Priority
?
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

Expert Comment

by:Kevin Cross
ID: 33448784
Use a replace around your variable that will replace all instances of ' with '' (two single quotes).
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 60

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 60

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 60

Assisted Solution

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

Accepted Solution

by:
Snarf0001 earned 1600 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 60

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 23

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 60

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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