ASP.net user data getting crosses between users accessing objects

Question

This is kind of a weird one. I'm looking for ideas on how to ask the right question as much as I am an actual solution.

I've got a website and we just had a huge jump in traffic. Now all of the sudden we're getting sql parameter errors left and right. We switched to a new sql server a few weeks ago and everything has been fine but the added traffic seems to be breaking us.

I have a data access class that is called when each user trys to logon. It runs through several tasks before finally updating the users last login date and forwarding them to the administration section.

What I'm seeing from the trace logs suggests that when I'm logging in the first several tasks are using my data (lets say user=birk pass=word). But at some point the accessor class starts sending over the data from someone else who's trying to login (lets say user=abcxyz)

Every connection we make to the server is closed when we're finished with it. I'm nulling out all of the accessor objects when I'm finished with them. But somehow different user data from different sessions are making their way into other peoples instances of the object.

I'm not using session/application/cache/viewstate to store the objects so I'm not really seeing how they could edit eachother... Its just really weird and I'm not sure how to even go about researching the problem.

This is roughly the accessor class... I trimmed out some of the parts that weren't reliant to the issue

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Text
Imports System.IO

Namespace ABC
    Public Class DataAccess
        Public Class SQL
            Dim objConnection As SqlConnection
            Dim objAdapter As SqlDataAdapter
            Dim objDataset As DataSet
            Dim objTable As DataTable
            Dim strSQL As String
            Dim strCommandType As String
            Shared sqlparams As List(Of param)
            Public params As New Parameters
            Shadows Application As HttpApplicationState = HttpContext.Current.Application
            Shadows Server As HttpServerUtility = HttpContext.Current.Server
            Shadows Response As HttpResponse = HttpContext.Current.Response
            Shadows Session As HttpSessionState = HttpContext.Current.Session

            Public Sub New()
                Connection()
            End Sub

            Public Sub New(ByVal startingSql As String)
                Connection()
                sql = startingSql
            End Sub

            Private Sub Connection()
                sqlparams = New List(Of param)
                objConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("sqlServerProd").ConnectionString)
            End Sub

            Public Function DataNQ(ByVal type As CommandType, Optional ByVal query As String = "") As Boolean
                If query <> "" Then
                    sql = query
                End If
                Dim objCommand As SqlCommand
                Try
                    objConnection.Open()
                Catch ex As Exception
                    objConnection.Close()
                    objConnection.Open()
                End Try
                objCommand = New SqlCommand(sql, objConnection)
                objCommand.CommandType = type
                Dim cmd As New SqlCommand
                HttpContext.Current.Trace.Warn(sql)
                'HttpContext.Current.Trace.Write("Adding " & sqlparams.Count & " parameters")
                HttpContext.Current.Trace.Warn(params.writeParams)
                If sqlparams.Count > 0 Then
                    For Each p As param In sqlparams
                        Dim sparam As SqlParameter = p.makeParam
                        HttpContext.Current.Trace.Write(sparam.DbType.ToString, sparam.ParameterName & "=" & sparam.Value)
                        objCommand.Parameters.Add(p.makeParam)
                    Next
                    sqlparams = New List(Of param)
                End If
                HttpContext.Current.Trace.Warn("Successfully added " & objCommand.Parameters.Count & " parameters")
                HttpContext.Current.Trace.Warn(params.writeParams)

                '-- Create a SqlParameter object to hold the output parameter value
                Dim paramRetVal As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
                '-- Must set .Direction as ReturnValue
                paramRetVal.Direction = ParameterDirection.ReturnValue
                '-- Finally, add the parameter to the Command's Parameters collection
                objCommand.Parameters.Add(paramRetVal)
                '-- Call the sproc...
                Dim reader As SqlDataReader = objCommand.ExecuteReader()
                'Now you can grab the output parameter's value...

                Dim intRetVal As Integer = Convert.ToInt32(paramRetVal.Value)
                If intRetVal = 0 Then
                    objConnection.Close()
                    objCommand = Nothing
                    reader = Nothing
                    Return True
                Else
                    objConnection.Close()
                    objCommand = Nothing
                    reader = Nothing
                    Return False
                End If
                objConnection.Close()

            End Function

            Public Sub freeResources()
                sqlparams = Nothing
                params = Nothing
                objConnection = Nothing
                objAdapter = Nothing
                objDataset = Nothing
                objTable = Nothing
                strSQL = Nothing
                strCommandType = Nothing
            End Sub

        Public Sub add(ByVal parameterName As String, ByVal dbType As System.Data.SqlDbType, ByVal size As Integer, ByRef value As Object)
            HttpContext.Current.Trace.Write("adding param name/type/size/value", parameterName & " " & value)
            Dim p As param
            p = New param(parameterName, dbType, size, value)
            p.Value = value
            sqlparams.Add(p)
        End Sub

            Private Class param
                Public name As String = Nothing
                Public size As Integer = Nothing
                Public type As System.Data.SqlDbType = Nothing
                Public value As Object = Nothing
                Public Function makeParam() As SqlParameter
                    HttpContext.Current.Trace.Warn("before make param name=" & name & " type=" & type.ToString & " value=" & value)
                    Dim p As New SqlParameter(name, type)
                    If size <> Nothing Then
                        p.Size = size
                    End If
                    p.Value = value
                    HttpContext.Current.Trace.Warn("after make param name=" & p.ParameterName & " type=" & p.DbType.ToString)
                    Return p
                End Function

        Public Sub New(ByVal pname As String, ByRef ptype As System.Data.SqlDbType, ByRef val As Object)
            'HttpContext.Current.Trace.Write("new param object name/type/value name=" & pname & " type=" & ptype.ToString)
            name = pname
            type = ptype
            value = val
            'HttpContext.Current.Trace.Warn("added param name=" & name & " type=" & type.ToString)
                End Sub
            End Class


        End Class
    End Class

ANY ideas or thoughts would be great. Thanks

Maryettamaryjane

Why is "sqlparams" declared as shared? – Latonialatoniah

I retrofitted the class to work with older pages without requiring a code change and at the time leaving this as shared seemed to fit the need better than adding a property named sqlparams. It's worth a rewrite though. – Maryettamaryjane

Answer 1

You have your sqlparams As List(Of param) declared as Shared. This means that there is only one instance of it created in memory, ever.

Every reference to it from every instantiated page is using the same instance of sqlparams.

This means that one instance of your page has populated it with some parameters, and some other instance of the page starts up, calls your Connection() method, and clears it out. When the processor switches back to the first page's thread, sqlparams is now new, (or even worse, Nothing if another thread just called your freeResources() method).

Your page is inherently non-thread safe with this code. Replace all your Shared variables with instance variables and it should solve the issues.

Sower

I removed the public flag from sqlparams and adjusted the rest of the code accordingly at the moment everything seems to be working. THANK YOU SO MUCH! – Maryettamaryjane

Answer 2

If your SQL class is instantiated only and contains no static/shared methods, then it shouldn't be a part of the problem.

EDIT: Your params collection (sqlparams) is Shared, which will allow cross-over between instantiations of SQL for their parameters.

Glen

Answer 3

static variables will also cause problems and are like Application State....I think Shared is like static in C#....I think your Shared variables is your problem. Make them instance variables and it should work.

Lomasi