Home > Visual Basic 6 > ADO SQL strings automation

ADO SQL strings automation

2011/07/28

I was working on a old vb6 program, and was trying to optimize the speed.

In this old program many Sql strings was obtained by concatenating strings, exposing the program at an heavy risk from the point of view of Sql injection; and using parameterized calls the sql engine is able to construct an execution plan.

A tipical sample was:

    Set rsGiorni = New ADODB.Recordset
    With rsGiorni
        Set .ActiveConnection = ocLogin.cnApp
        .Open sSql, , adOpenStatic, adLockReadOnly, adCmdText
        If Not (.BOF And .EOF) Then
            sSql = "INSERT ContrGiorni (cgg_IdContr,cgg_Giorno,cgg_Inizio,cgg_Frequenza,"  _
                    "cgg_Fine,cgg_Visite,cgg_Note,cgg_dtModifica,cgg_uteModifica,cgg_dtIns,cgg_uteIns, cgg_CodStudio)"  _
                    " VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
            Set cmdGiorni = New ADODB.Command
            Set cmdGiorni.ActiveConnection = ocLogin.cnApp
            cmdGiorni.CommandText = sSql
            cmdGiorni.CommandType = adCmdText
            Do Until .EOF
                cmdGiorni.Parameters.Refresh
                cmdGiorni.Parameters(0).value = GetFields(Rs, "ctr_Id")
                cmdGiorni.Parameters(1).value = GetFields(rsGiorni, "cgg_giorno")
                cmdGiorni.Parameters(2).value = GetFields(rsGiorni, "cgg_Inizio")
                cmdGiorni.Parameters(3).value = GetFields(rsGiorni, "cgg_frequenza")
                cmdGiorni.Parameters(4).value = GetFields(rsGiorni, "cgg_Fine")
                cmdGiorni.Parameters(5).value = GetFields(rsGiorni, "cgg_Visite")
                cmdGiorni.Parameters(6).value = GetFields(rsGiorni, "cgg_Note")
                cmdGiorni.Parameters(7).value = dtNow
                cmdGiorni.Parameters(8).value = ocLogin.UserName
                cmdGiorni.Parameters(9).value = dtNow
                cmdGiorni.Parameters(10).value = ocLogin.UserName
                cmdGiorni.Parameters(11).value = GetFields(rsGiorni, "cgg_CodStudio")
                cmdGiorni.Execute
                .MoveNext
            Loop
        End If
    End With

The list of fields was easily obtained with SQL Management Studio (and converted on a single line with Notepad++) , but there were tables with many fields , and the generation of a sequence of “?” with a “,” as separator , in a number equal to the number of fields was a nightmare.

But i thinked: i’m a programmer … So with a regular expression i have instantly generated the list of “?”

My expression was:

[\[\w\]]+

Because my field list is generated from SQL management studio surrounded with brackets [] .

In Notepad ++ :


The same operation done in C#:

System.Text.RegularExpressions.Regex.Replace("[mac_IDMov] ,[mac_DataDal] ,[mac_OraDal] ,[mac_DataAl] ,[mac_OraAl] ,[mac_Note] ,[mac_dtIns] ,[mac_uteIns]", @"[\[\w\]]+", "?")

Advertisements
Categories: Visual Basic 6
%d bloggers like this: