Help with asp login SQL

Question

I have a form which goes to the following login script when it is submitted.

<%
    Dim myConnection As System.Data.SqlClient.SqlConnection
    Dim myCommand As System.Data.SqlClient.SqlCommand
    Dim requestName As String
    Dim requestPass As String
    requestName = Request.Form("userName")
    requestPass = Request.Form("userPass")
    Dim queryString As String = "SELECT COUNT(*) AS Num_Of_User FROM tblusers WHERE username='" & requestName & "' AND password='" & requestPass & "'"
    myConnection = New System.Data.SqlClient.SqlConnection("Data Source=(local);InitialCatalog=dbtest;Integrated Security=True")
    myCommand = New System.Data.SqlClient.SqlCommand(queryString, myConnection)
    myConnection.Open()
    Dim reader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
%>

Now in theory, I should be able to get that Num_Of_User from the SQL Query and if it equals 1 than the login was successful. Is this the correct way? And how can I get the value that the SQL returns?

Answer 1

Try myCommand.ExecuteScalar(), which returns the value from the first column in the first row of the resultset - exactly the value you're after here.

Also, check into the ASP.Net 'built in' authentication methods - this might save you some effort.

Answer 2

You are wide open to SQL injection using that code.

See happens if you enter the username as ' OR 2>1--

You need to change the to use a parametrized query.

Dim queryString As String = "SELECT COUNT(*) AS Num_Of_User FROM tblusers WHERE username=@username AND password=@password"
myConnection = New System.Data.SqlClient.SqlConnection("Data Source=(local);InitialCatalog=dbtest;Integrated Security=True")
myCommand = New System.Data.SqlClient.SqlCommand(queryString, myConnection)
myCommand.Parameters.AddWithValue("@username", requestName)
myCommand.Parameters.AddWithValue("@password", requestPass) 

Also you are not handling any exceptions that might be thrown, nor disposing your objects.

Your code should look more like the following.

Dim numUsers as Integer
Using myConnection as New System.Data.SqlClient.SqlConnection("Data Source=(local);InitialCatalog=dbtest;Integrated Security=True")
   Dim queryString As String = "SELECT COUNT(*) AS Num_Of_User FROM tblusers WHERE username=@username AND password=@password"
   Using myCommand as New System.Data.SqlClient.SqlCommand(queryString, myConnection)
      myConnection.Open
      myCommand.Parameters.AddWithValue("@username", requestName)
      myCommand.Parameters.AddWithValue("@password", requestPass) 
      numUsers = myCommand.ExecuteScalar()
   End Using
End Using 

The above code will make sure your objects are disposed, but won't handle any exceptions that might be thrown.