?
Solved

Secure login using Classic ASP and SQL db not working

Posted on 2006-05-04
34
Medium Priority
?
373 Views
Last Modified: 2012-06-27
I’m trying to add a secure login to my home page. After the user submits the username and password they are directed to a page unique to that user. Basically many users will have the same username and password that is unique to an on going project. The page returned is a PDF that has information about the ongoing project.  The data isn’t sensitive just trying to direct end users to the correct information a little easier.  Also in the future this login feature may be incorporated to other users and access if things work out.

I’m using Classic ASP and a SQL data base. I’m able to display the input text boxes on the home page and enter username and password. When I click the submit button it returns me to the home page and leaves the username in the username text box.

I have a form on the home page with the action (ACTION=”login”) that calls a page named login.asp where my code resides that performs the check against the database. I have a table named (“dev_login”) with the columns.
Username
Password
Destination

Can someone look through my syntax and give me a pointer or two on how to make this work?


Here is my code for the login form displayed on the home page

<FORM  ACTION="login.asp" METHOD="post">
                             <TR>
                                   <TD VALIGN="Top">Username:</TD>
              </TR>

                               <TR>
                               <TD VALIGN="Top"><asp:TextBox ID="txtUsername" runat="server" Columns="15" MaxLength="50" Width="145" /></TD>
                                </TR>

                                <TR>
                                        <TD VALIGN="Top">Password:</TD>
               </TR>
                                                                            <TR>
                                   <TD VALIGN="Top"><asp:TextBox ID="txtPassword"  runat="server" Columns="15" MaxLength="50" TextMode="Password" Width="145" /></TD>
                                </TR>

                                <TR>                                
                                   <TD VALIGN="Top"><INPUT TYPE="submit" VALUE="Login"></TD>
                                </TR>
</FORM>

Below is the code in my login.asp page.

Response.Buffer = True
<%
Dim objConn, objRS, errormsg
Session.Contents("status") = ""
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=Development;"
Set objRS = objConn.Execute("SELECT * FROM dev_login WHERE Username = '" & Request.Form("txtUsername") & "" and Password = '" & Request.Form("txtPassword") & "'")
if not objRS.EOF Then
Session.Contents("status") = objRS(2)
redir = objRS(3)
or: redir = objRS("destination")
Else
errormsg = "Sorry, but the username does not exist or the password was incorrect."
End If

'we want to close our recordset before redirecting. asp does not always clean itself up very good
objRS.Close
set objRS = Nothing
objConn.Close
Set objConn = Nothing

if errormsg = "" then
response.redirect( redir )
else
response.write errormsg
end if
%>
0
Comment
Question by:Eric_Trogdon
  • 17
  • 11
  • 6
34 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 16607458
one thing that i noticed was in the username section of the sql statement.  you have 2 double quotes instead of a single quote.

Set objRS = objConn.Execute("SELECT * FROM dev_login WHERE Username = '" & Request.Form("txtUsername") & "' and Password = '" & Request.Form("txtPassword") & "'")
0
 

Author Comment

by:Eric_Trogdon
ID: 16608184
Thanks, I correct the typo but it still doesn't work.  My guess is it's something in the code about my form. It's been a while since I've wrote anything and I think I'm mixing some ASP Classic and ASP.NET together.
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16608214
Make a dummy username/pw and response.write your query. Make sure and see if it is actually failing or not.

Another thing you might want to consider looking at is SQL injection. People could possibly bypass your loginpage using SQL queries in your form fields.
You may want to filter your username/password data to prevent that.

http://www.4guysfromrolla.com/webtech/061902-1.shtml
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16608260
redir = objRS(3)
or: redir = objRS("destination")
is this actually in your code or is it just a comment?

also print out each objRS you are grabbing to make sure it's correct.
i noticed you have status = objRS(2)
you listed username, password, destination as your record fields => objRS(0), ...1, 2
Also make sure you are grabbing the values in order. Sometimes it fails when you grab it out of order (i.e. objRS(2) first then objRS(1) )

..and what error are you actually getting?
0
 

Author Comment

by:Eric_Trogdon
ID: 16608539
made another dummy username and password and did the response.write. when I clicked submit nothing happened.


yes the following text was in my code

redir = objRS(3)
or: redir = objRS("destination")

I removed:
 redir = objRS(3)
 or:


and only left
 redir = objRS("destination")


should I change objRS(0) to objRS("username") objRS("password") and objRS("destination")


I've also changed the form to:

<FORM  ACTION="login.asp" METHOD="post">
        <TR>
              <TD VALIGN="Top">Username:</TD>
        </tr>
        <tr>
             <TD><INPUT TYPE="text" NAME="txtusername" width ="145" center ="50"></INPUT></TD>
        </TR>
        <TR>
             <TD VALIGN="Top">Password:</TD>
        </tr>
         <tr>
        <TD><INPUT TYPE="password" NAME="txtpassword"></INPUT></TD>
        </TR>
        <TR>                                
         <TD VALIGN="Top"><INPUT TYPE="submit" VALUE="Login"></TD>
         </TR>
     <tr>
</FORM>



re: error code.

I'm not reciving any error code. The login form is built into a table that is on the default.aspx page with other content. When I click submit it returns me to the default.aspx page.
0
 

Author Comment

by:Eric_Trogdon
ID: 16630739
I'm still having problems with this. Anyone else have any advice?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16632434
do you have "on error resume next" anywhere in your code?
0
 

Author Comment

by:Eric_Trogdon
ID: 16632842
no.  

where do i need this statment?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16632918
no, its not a good one to use.  i was curious if you had it somewhere.  it creates problems with debugging.  are you getting any errors?  how is it not working now?
0
 

Author Comment

by:Eric_Trogdon
ID: 16633000
could there be a problem that the form I wrote in Classic ASP resides on a page using ASP.NET?

I'm not getting any errors. The home page has a table with the login text boxes. I type the user name and password and the page refreshes to the home page again. the fields on the text boxes are empty. If i click the refresh button it returns the error this page cannot refresh without resending the information. and give you the option to click OK or CANCEL

I need this figured out today!! I'm will to give another 500 points for the answer
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16633317
lets start stepping through you processing page then with a couple lines of code.  start at the very top of the page to make sure that we are actually hitting it.  if you load the page and you see the text, move down to another section and try again.  if you see the text again, keep moving.  eventually we will discover where this is breaking, but you need to do this testing to find it.

response.write "made it"
response.end()
0
 

Author Comment

by:Eric_Trogdon
ID: 16633364
ok that works
0
 

Author Comment

by:Eric_Trogdon
ID: 16633370
nevermind it didn't work it returned all of the text
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16633411
please explain, i dont understand what "all of the text" means.  ill be back in 30 mins though, lunch time.
0
 

Author Comment

by:Eric_Trogdon
ID: 16633526
I started a new page named help.asp.

I typed the listed code and when I viewed it in IE it returned

response.write"made it"
responce.end()
0
 

Author Comment

by:Eric_Trogdon
ID: 16633560
Here is what my code currently looks like.




Here is the code for the form that resides on the default.aspx page.



<FORM  ACTION="login.asp" METHOD="post">
 <TR>
      <TD VALIGN="Top">Username:</TD>
 </tr>
 <tr>
               <TD><INPUT TYPE="text" NAME="txtusername"></INPUT></TD>
 </TR>
  <TR>
               <TD VALIGN="Top">Password:</TD>
  </tr>
  <tr>
      <TD><INPUT TYPE="password" NAME="txtpassword"></INPUT></TD>
 </TR>
  <TR>                                
              <TD VALIGN="Top"><INPUT TYPE="submit" VALUE="Login"></TD>
 </TR>
</FORM>




page "login.asp"

Response.Buffer = True
Dim objConn, objRS, errormsg
Session.Contents("status") = ""
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "FoundationSurgeryDev"
Set objRS = objConn.Execute("SELECT * FROM fsa_t_dev_login WHERE Username = '" & Request.Form("txtUsername") & "' and Password = '" & Request.Form("txtPassword") & "'")
if not objRS.EOF Then
Session.Contents("status") = objRS(2)
redir = objRS("destination")
 
Else
errormsg = "Sorry, but the username does not exist or the password was incorrect."
End If

'close recordset before redirecting.
objRS.Close
set objRS = Nothing
objConn.Close
Set objConn = Nothing

if errormsg = "" then
response.redirect( redir )
else
response.write errormsg
end if
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16633812
i was meaning to place that code on your login.asp page at the very top.
0
 

Author Comment

by:Eric_Trogdon
ID: 16633922
nothing happens???

my asp code is not on the same page as my form with the input boxes. the input boxes are on a page called "default.aspx". the asp code in on "login.asp"I put the code on the top of my "login asp" page.

I'm more lost
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16634194
nothing happens is not descriptive enough.  you put the code in the correct place, and you should have seen "made it" displayed once you submit the form.  is the page not going anywhere?  are you getting a blank page?
0
 

Author Comment

by:Eric_Trogdon
ID: 16634460
First thanks for you help!!

Ok I've tried a different approach.  I've put a new link on the home page that directs the user to a new stand alone log-in page. The page is named dev_login.asp.

This seems to be working a little better. When I’m redirected to the new dev_login.asp page the test boxes for “username” and ‘password” are present. I’m getting an error message in plain text visible that reads:



Microsoft VBScript runtime error '800a01a8'

Object required: ''

/foundationsurgerydev/dev_login.asp, line 62

Line 62=

Session.Contents("status") = ""


If i enter the test user name and password I get a "this page cannot be displayed"


<html>

      <head>
            <title>Development Log-in</title>
            <LINK href="Styles.css" type="text/css" rel="stylesheet">
      </head>
            <body>
                  <table align="center" cellpadding="0" cellspacing="0" class="MenuTable" ID="Table1">
                       <tr>
                        <td class="Menu1HeaderCell">
                        <span class="Menu2Title">Development Log-In</span>
                                  </td>
                        </tr>
                         <tr>
                             <td class="Menu1BodyCell">
                               <table align="center" cellpadding="0" cellspacing="0" class="MenuBodyTable" ID="Table2">
                         <TR>
                                 <FORM  ACTION="dev_login.asp" METHOD="post" ID="Form1">
                          <TR>
                           <TD VALIGN="Top">Username:</TD>
                           </tr>
                            <tr>
                                             <TD><INPUT TYPE="text" NAME="txtusername" ID="Text1"></INPUT></TD>
                               </TR>
                            <TR>
                                             <TD VALIGN="Top">Password:</TD>
                             </tr>
                              <tr>
                          <TD><INPUT TYPE="password" NAME="txtpassword" ID="Password1"></INPUT></TD>
                             </TR>
                              <TR>                                
                                                                 <TD VALIGN="Top"><INPUT TYPE="submit" VALUE="Login" ID="Submit1" NAME="Submit1"></TD>
                              </TR>
                     </FORM>
                  </TR>                                                                                    </table>
                     </td>
                 </tr>
                    <tr>
                         <td class="Menu1FooterCell"><img alt="" src="images/menu_6.jpg" /></td>
                    </tr>
                  </table>
      </body>
</html>




<%


Response.Buffer = True
Dim objConn, objRS, errormsg
Session.Contents("status") = ""
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "FoundationSurgeryDev"
Set objRS = objConn.Execute("SELECT * FROM fsa_t_dev_login WHERE Username = '" & Request.Form("txtUsername") & "' and Password = '" & Request.Form("txtPassword") & "'")
if not objRS.EOF Then
Session.Contents("status") = objRS(2)
redir = objRS("destination")
 
Else
errormsg = "Sorry, but the username does not exist or the password was incorrect."
End If

'close recordset before redirecting.
objRS.Close
set objRS = Nothing
objConn.Close
Set objConn = Nothing

if errormsg = "" then
response.redirect(redir)
else
response.redirect(errormsg)
end if
%>


0
 
LVL 22

Expert Comment

by:WMIF
ID: 16634533
sounds like you have friendly http messages enabled.  go to tools -> internet options... -> advanced tab.  then scroll down until you see "show friendly http errors" and uncheck that.  then run your page again and it will give you more details about that error.
0
 

Author Comment

by:Eric_Trogdon
ID: 16634590
I disabled the friendly http messages. I ran the page again and on loading it shows the same error.

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/foundationsurgerydev/dev_login.asp, line 62
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16635046
try using the session variable like this instead:

Session("status")
0
 

Author Comment

by:Eric_Trogdon
ID: 16636659
This is the error i get when I use

Session("status")

Microsoft VBScript compilation error '800a03f2'

Expected identifier

/foundationsurgerydev/dev_login.asp, line 8

Session.("status")




when I comment out the line

Session.Contents("status") = ""

I get this error
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'FoundationSurgeryDev'. Login fails.

/foundationsurgerydev/dev_login.asp, line 10



0
 

Author Comment

by:Eric_Trogdon
ID: 16638971
Ok with a little help I have all new code. this seems better than before. I don't get any error messages when the log-in page loads. I'm able to enter the test data. when I click submit I get a IE 404 type of error saying page cannot be diplayed.

my code below



<%
Option explicit
Dim iLogingRssult,objLogin,Password,LoginMessage,Username
Dim strMessageOut
Dim RS,RS2
Dim PW
Dim ptr,ptr2
Dim strConnString


      REsponse.Expires = 0

%>
<%

      Username = Request("Name")
      Password = Request("Password")
      LoginMessage = Request("LoginMessage")
      
      if Username > "" then
            set objLogin = Server.CreateObject("ADODB.Connection")
            objLogin.Open "FoundationSurgeryDev"
            
            Set RS = objLogin.Execute("SELECT * FROM fas_t_dev_login WHERE Username='" & ("Username") & "'")
            
            if RS.EOF = True and RS.BOF = True then
                              RS.Close
                              strMessageOut = SErver.URLEncode("<font size=4>Loign was unsuccessful, User Name was not found.</font>")
                              response.Redirect "dev_login.asp?LoginMessage="& strMessageOut
                              end if
                              
                              PW= RS.Fields("Password")
                              
                              If PW <> Password then
                                          StrMessageOut = Server.URLEncode("<font size=4>Login was unsucccessful, invalid password was entered.</font>")
                                          objLogin.Close
                                          response.Redirect "dev_login.asp?LoginMessage=" & strmessageOut
                                          
                                    else
                                          
                                          objLogin.Close
                                          response.Redirect "patient_education.aspx"
                                          
                              end if
                                    
            end if
            %>
            
            <html>
            
            <head>
            <title>Development Login</title>
            
            
            
            <center>
            <h2>Development Project Log-in</h2>
            
            <hr>
            
            <% If LoginMessage> "" then %>
            
            <p><strong><font color="RED" size="1"> <%= LoginMessage %></font></stong></p>
            
            <%Else%>
            
            <p>
            <font size ="3">Enter your User Name and Password.</font></p>
            
            <%End if%>
            
            
            <form action="dev_login.asp method="Post">
                  <blockquote>
                        <div align="center"><table border="0">
                              <tr>
                                    <td><div align="center"><p><font size="4"><strong>User Name:</strong></font></td>
                                    <td><div align="center"><p>
                                          <input type="text" size="20" name ="Name" tabindex="1">
                                    </td>
                              </tr>
                              <tr>
                                    <td><div align="center"><p><font size="4"><strong>Password:</strong></font></td>
                                    <td><div align ="center"><p><input type="password" size="20" name="Password" tabindex="2"></td>
                              </tr>
                        </table>
                        </div>
                  <blockquote>
                  
                  <blockquote>
                        <blockquote>
                              <p><font size="3"><input type ="submit" name="B1" value="Login" tabindex="3"></font></p>
                        </blockquote>
                  </blockquote>
               </blockquote>
              </blockquote>
             </form>
            
                              
            
            
            
            </center>



0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16639970
can you explain to me why you have this line?

  objLogin.Open "FoundationSurgeryDev"         <--------
         
          Set RS = objLogin.Execute("SELECT * FROM fas_t_dev_login WHERE Username='" & ("Username") & "'")

Looks like you're already executing a query with the 2nd line. What's the purpose of the first one?
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16640004
try commenting out your response.redirects and instead put a response.write there to make sure it went thru.
0
 

Author Comment

by:Eric_Trogdon
ID: 16640353
I put in responce.write statments and when i clicked submit it still returns the 404 error.

My guess is it's not going through.
0
 

Author Comment

by:Eric_Trogdon
ID: 16640677
I commented this line out

'if Username > "" then

Now when the page loads i get this error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'FoundationSurgeryDev'. Login fails.

When setting up the ODBC conection on the server what are the permissions needed?
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16642777
Maybe I'm wrong here, but I'm still confused as to how you have:

  objLogin.Open "FoundationSurgeryDev"         <--------
         
          Set RS = objLogin.Execute("SELECT * FROM fas_t_dev_login WHERE Username='" & ("Username") & "'")

what does objLogin.Open "FoundationSurgeryDev"  do?
What is "FoundationSurgeryDev" exactly?

I'm only used to using it like this:
objLogin.Open myquery, objConn

And why are you opening a query and then afterwards executing another query without doing anything with the first one or closing it?
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 16642804
Unless you're defining the connection string...

conn_string = "Driver={SQL Server}; Server=Address; Database=DBName; user ID=username; password=password;"
objConn.Open conn_string   (no quotes around it)
0
 
LVL 22

Expert Comment

by:WMIF
ID: 16642811
objLogin.Open "FoundationSurgeryDev"

this must be the connection object.  im not sure what its connecting to though.
0
 

Author Comment

by:Eric_Trogdon
ID: 16643604
yes that is the connection object. I RDP to the server and set up the OBDC connection and named it foundationSurgeryDev.  That points to my datatbase.
0
 
LVL 22

Accepted Solution

by:
WMIF earned 1500 total points
ID: 16644135
i believe for a dsn, it needs to be:

objLogin.Open "dsn=FoundationSurgeryDev"
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question