VB.NET regular expression to deal with single quotes

Hi all,

I am looking for a regular expression to be used in VB.NET to handle single quotes in SQL statements.

Obviously, this is not the best practice way of handling SQL statements but it is to deal with legacy code.

So, below are serveral SQL statement examples:

SELECT * FROM test_table WHERE col1 = ''      
SELECT * FROM test_table WHERE col1 = ' '
SELECT * FROM test_table WHERE col1 = 'Test Val'
SELECT * FROM test_table WHERE col1 = ' Test V''al'
SELECT * FROM test_table WHERE col1 = ' Test V'al'
SELECT * FROM test_table WHERE col1 = ' Te'st V'al'

Only the last 2 statement are incorrect so I need a regular expression that would leave the correct ones and adjust the two incorrect ones by doubling up the single quotes within the value text.

Any help is appreciated.

Thanks.

resourcesys.
resourcesysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MazdajaiCommented:
Try -

SELECT * FROM test_table WHERE col1 = ' Test V''al'
SELECT * FROM test_table WHERE col1 = ' Te''st V''al'

Open in new window

0
resourcesysAuthor Commented:
Hi Mazdajai,

I understand that the SQL statements you posted are correct, however, I'm looking for a regular expression that would correct them automatically.

Thanks.

resourcesys.
0
PrisonBrokenCommented:
Hi resourcesys,

I use these functions attached all the time to rescue errant data.

Install them and then use them like this

-- TEST TO SEE IF IT FINDS
SELECT * FROM TABLE WHERE dbo.regexFind( COLUMN, '''', 1 ) = 1;

--REPLACE THE quotes
UPDATE TABLE
SET COLUMN = (SELECT dbo.regexReplace(COLUMN, '''', '', 1, 1 ) FROM TABLE TU WHERE TU.UNIQUEID = TABLE.UNIQUEID);

Hope that helps
RegexFunctions.txt
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

resourcesysAuthor Commented:
Hi PrisonBroken,

This may prove useful in the future but for the moment the solution needs to be a VB.NET regular expression.

Thanks.

resourcesys.
0
PrisonBrokenCommented:
Why would

 SELECT * FROM test_table WHERE dbo.regexFind( Col1, 'Te['''']*st V['''']*al', 1 ) = 1;

not return what you want?
0
resourcesysAuthor Commented:
Hi PrisonBroken,

It would return what I want, however this would mean changing individual SQL statements.

In our code, all SQL statements are executed through a single database object, so, to avoid changing hundreds if not thousands of statements, I need a regular expression to handle it in the database object.

If I where to change the SQL statements I could just use the VB.NET string.replace method without SQL Server having to do the extra work of calling the regexFind function.

Thanks.

Brett.
0
PrisonBrokenCommented:
I'm obviously not understanding your set up mate, good luck!
0
Robert SchuttSoftware EngineerCommented:
If the queries used are not much more complex than your examples it's possible but to make a general fix for this presents a major problem, as you would need to parse any possible SQL statement and invalid ones at that!

I'm not sure but I feel you have a much bigger issue, needing to move away from SQL queries concatenated into strings like that.

Anyway, here's some code for you to experiment with:
    Private stmts() As String = {
            "SELECT * FROM test_table WHERE col1 = ''      ",
            "SELECT * FROM test_table WHERE col1 = ' '",
            "SELECT * FROM test_table WHERE col1 = 'Test Val'",
            "SELECT * FROM test_table WHERE col1 = ' Test V''al'",
            "SELECT * FROM test_table WHERE col1 = ' Test V'al'",
            "SELECT * FROM test_table WHERE col1 = ' Te'st V'al' " & vbCrLf & "    and col2 = 'abc'def'"
        }

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        TextBox1.Text = Join(stmts, vbCrLf)
        TextBox2.Text = CheckSQL(stmts)
    End Sub

    Private Function CheckSQL(sql() As String) As String
        Dim ret As String = String.Empty
        For Each s As String In sql
            ret += CheckSQL(s) + vbCrLf
        Next
        Return ret
    End Function

    ' this is the one you can use for 1 string
    Private Function CheckSQL(sql As String) As String
        Dim ret As String = String.Empty
        Static re As New Regex("'.*'", RegexOptions.Multiline)
        Dim mev As New MatchEvaluator(AddressOf ev)
        ret += re.Replace(sql, mev)
        Return ret
    End Function
    Private Function ev(m As Match) As String
        Return "'" & m.Value.Substring(1, m.Length - 2).Replace("''", "'").Replace("'", "''") & "'"
    End Function

Open in new window

As you can see, I made the last query a tiny bit more complex but added an enter so it can still be processed successfully (one string per line)... So that could be something that could be added to make this work

Definitely not the end-solution you're looking for, but maybe it helps you a little bit in the short term.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
hi.. this is the function for replace single quote. Try this and let me know further more.

Function MakeSQLSafe(byval MyStrData as string) as string
     MyStrData = Replace(MyStrData, "'", "''")
     return MyStrData
End Function
0
resourcesysAuthor Commented:
Hi robert_schutt,

As you said, this isn't perfect but it's a start and the closest answer to a possible solution.

Thanks.

resourcesys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.