How to make sql string safe in vb.net without using parameters

Ok, I think the issue is that one assumes that you can't have "optional" parameters, or you have to set them ahead of time. (you don't).

You can go list this:

dim strWhere  as string
strWhere = "(State =  @State)
cmdSQL.Parmaters.Add("@State",SqlDbType.NVarchar).Value = lstBrowse.SelectedItem.Value 

Now, I can add to both strWhere more conditations "on the fly" ,and also over time add parmaters to the cmdSQL.Parmaters collecton.

Now, lets assume you on some complex grid page, and the user selects some things, and now you need to pass this say to another form?

Well, it gets a wee bit messy to have:
Keep track of some field names
Keep track of some conditions (=, like, etc.)
Keep track of the parameters

But you can most certainly "string" together a list of parmaters, and some sql that also matches that parmeters. So there is NO need to hard code the sql with EXISTING paratmers.

Now, the other issue is you suggested that not only building some dynamic sql, but you need to pass that in the session (or you want that ability).

Well, I would just build a "clump" of VERY short code to put all of the above into a VERY short and simple class. And then SHOVE that class into the session.

So, drop this wee bit of code into one of your standard code modules in the project.

Public Class SqlParms

    Public cmdSQL As New SqlCommand("", GetCon)
    Public Where As String
    Private m_SQL As String

    Public Sub Add(FieldName As String, Cond As String, FieldValue As Object, Datatype As SqlDbType)
        Dim mycond As String
        Dim prex As String = "", sufx As String = ""
        If Left(Cond, 1) = "%" Then prex = "'%' + "
        If Right(Cond, 1) = "%" Then sufx = " + '%'"
        mycond = Replace(Cond, "%", "")

        If Where <> "" Then Where += " AND "
        Where += "(" & FieldName & " " & mycond & " " & prex & "@" & FieldName & sufx & ")"
        cmdSQL.Parameters.Add("@" & FieldName, Datatype).Value = FieldValue

    End Sub

    Public Property SQL As String
        Get
            Return m_SQL
        End Get
        Set(value As String)
            m_SQL = value & " WHERE " & Where
            cmdSQL.CommandText = m_SQL
        End Set
    End Property

End Class

So, all the above does? Lets you add a "field", a "condition" and then add some more. It quite simple.

So, now in code, I can go:

Dim MyParms As New SqlParms
Session("MyParms") = MyParms

Now, note that ONCE we pointed session to that class? Then I can add/modify in this current code bit, and NOT have to funnel/save/shove the MyParms back in - its a object, and it now in the session.

So, lets add your conditions.

MyParms.Add("State", "=", lstBrowse.SelectedItem.Value, SqlDbType.NVarChar)

ok, so the above is not only added, but it ALSO in our session!!!

Ok, lets pretend that we have TWO conditions, and the 2nd one was say ContactID (a PK value in the database - hence a integer data type). Ok, we now we go:

MyParms.Add("OrganziationID", "=", lstBrowse.SelectedItem.Value, SqlDbType.Int)

or maybe

MyParms.Add("OrganziationID", "=", droplist1.SelectedValue, SqlDbType.Int)

MyParms.SQL = "SELECT * from tblHotels"

again: note after running the above 1, or 2 conditions? its in session().

Ok say we jumped to a new different page. And now we need to fill a grid.

The code will be:

Note VERY VERY careful below how we do NOT use the new keyword!

    Dim MyParms As SqlParms = Session("MyParms")
    Dim rst As New DataTable
    Using MyParms.cmdSQL
        MyParms.cmdSQL.Connection.Open()
        rst.Load(MyParms.cmdSQL.ExecuteReader)
    End Using

    GridView1.DataSource = rst
    GridView1.DataBind()

So that little "helper" routine lets you build up the where clause. it in session() and now you can pass/jump all that stuff to another page.

Another VERY interesting thing that OFTEN people don't' realize? You always can add parameters to the sql command object and NOT YET have set the sql set. and you don't even need the connection set either.

So, you could have in fact dumped the above "helper" routine, and just created a sql command object, and shoved that into session.

Regardless of the above? You don't have to pre-hard code your sql. And those values can and will become parameter safe (sql injection safe), and this even applies if you going to add "optional" parameters. The only trick is to make sure you ALSO build up the sql with those optional parameters.

So, if I go like this:

dim MyParms as new SqlParms
MyParms.Add("City", "Like%", "E", SqlDbType.NVarChar)
MyParms.SQL = "SELECT * from tblHotels"
Dim rst As New DataTable
    Using MyParms.cmdSQL
        MyParms.cmdSQL.Connection.Open()
        rst.Load(MyParms.cmdSQL.ExecuteReader)
    End Using

    GridView1.DataSource = rst
    GridView1.DataBind()

So the above would fill our data grid with the resulting table.

The sql for above is now this:

SELECT * from tblHotels WHERE (City Like @City + '%')

Now because I wanted "like" support? Well, I could not add/include/have the % included in the parameter name (I use the same as field name with @ in front.

So, I use %like (for prefix wild card) and Like% (for suffix). And the code then strips out the % and adds it correctly to the where clause as per above.

So, you can/could code out the string as you "add" more conditions. But in all cases, it still not only a good idea to use parameters, but it not all that hard to do.

And I in that class also used a public function called GetCon that simply returns a connection object, but you can just replace "GetCon" in that class with your own routine that returns a connection string that is then used to create a sqlconneciton object.

Also, rather minor, but I do assume that the sql property is set LAST, since the sql string is then combined with the where clause. If you add more parameters then you need to set the sql property again.

In summary: You can and should add additonal values as parameters. There not a whole lot of reason I can see to not do this.

That cute helper routine can be shoved into session if you need to set all this up in one page, and then jump to another. (or even have the current page allow say some additional filtering.