sql command query for log in asp.net

Question

Hello i been looking around and i cant seem to find how to make a safe sql command ( vs injections ) for checking log in details from the database , i found something like this code which seem to be the thing i need but i cant seem to understand how to actully check if the user exists. This code happens on LogIn Button click , and i am suppose to redirect the user to another page + save some of the valuse from the row ( like userId , companyId and few others ) into sessions for later use . I just not so sure how .

Protected Sub enterBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

            Dim query As String = String.Format("select userName, userPassword, companyId  from " & "[users] where userName like '%+@userName+%', userBox.Text)


            Using con As New SqlConnection(connectionString)
                '
                ' Open the SqlConnection.
                '
                con.Open()
                '
                ' The following code uses an SqlCommand based on the SqlConnection.
                '
                Using da As New SqlDataAdapter()
                    Using command As New SqlCommand(query, con)
                        'pass the parameter
                        command.Parameters.Add(New SqlParameter("@userName", userBox.Text))
                        command.Parameters.Add(New SqlParameter("@userPassword", passwordInput.Text))
                        command.Parameters.Add(New SqlParameter("@companyId", companyIdBox.Text))

                        Dim ds As New DataSet()
                        da.SelectCommand = command
                        da.Fill(ds, "test")

                    End Using
                End Using
            End Using

Steve · Accepted Answer · 2014-11-05 19:05:30Z

1

Change your query string to

Dim query As String = "select userName, userPassword, companyId " & _ 
                      "from [users] " &  _
                      "where userName like @userName " & _
                      "userPassword = @userPassword " & _
                      "companyID = @companyID"

and then in the section where you add the parameters

command.Parameters.Add(New SqlParameter("@userName", "%" & userBox.Text "%"))

The trick is to write the query text as clean as possible and add the wildcard required by the like directly in the value passed to the SqlParameter constructor

I suggest also to use a different way to build your Parameters collection

command.Parameters.Add(New SqlParameter With 
{
    .ParameterName = "@userName", 
    .Value = "%" & userBox.Text "%",
    .SqlDbType = SqlDbType.NVarChar
})

This is more verbose but avoids the confusion between the two overloads of the Add method the one that accepts an SqlDbType and the one that accepts an object as second parameter.

Then if you want to know if a user with that name, password an company has been found just loop at the count of rows present in the first table of the DataSet

 If ds.Tables(0).Rows.Count > 0 then
     ... you have your user .....
 End if

However a better query would be

Dim query As String = "IF EXISTS(select 1 from [users] " &  _
                      "where userName like @userName " & _
                      "userPassword = @userPassword " & _
                      "companyID = @companyID) " & _
                      "SELECT 1 ELSE SELECT 0"

and instead of the SqlDataAdapter and DataSet you write simply

   Using con As New SqlConnection(connectionString)
   Using command As New SqlCommand(query, con)
        con.Open()
        command.Parameters.Add(New SqlParameter("@userName", userBox.Text))
        command.Parameters.Add(New SqlParameter("@userPassword", passwordInput.Text))
        command.Parameters.Add(New SqlParameter("@companyId", companyIdBox.Text))

        Dim userExists = Convert.ToInt32(command.ExecuteScalar())
        if userExists = 1 Then
           Session["UserValidated"] = "Yes"
        else
           Session["UserValidated"] = "No"
        End If

    End Using
    End Using

edited Nov 5, 2014 at 19:05

answered Nov 5, 2014 at 18:47

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kresa Over a year ago

Hey thanks for the replay i will use that , but what i really wanted to know is what am i suppose to do for using the table row with that userName and password on the database ( in case such a user exists ) . I need to add few values from that row into sessions for later use and redirect to another page, i just lack the knowledge on where and how to add that . This code suppose to happen upon clicking on the log in button

kresa Over a year ago

Thanks alot! Think i got it now

Steve Over a year ago

A last note. A login requires an exact match in the username field, so the LIKE operator could be acceptable if you don't mind the username case but I am pretty sure that the wildcards should not be used in this case

kresa Over a year ago

oh yeah i removed the "like" , i been reading about sql injections and realised the way i been working till now was very wrong , will take some time to replace it all but thanks alot for helping me figure this code out!

Collectives™ on Stack Overflow

sql command query for log in asp.net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related