kwitcom
asked on
Simple MS SQL table lookup for user ID and Password
I am doing it the hard way I know right now, but I want to convert this to pull from a table. I am going to create a new table (lbl_users) with the following fields
UserID
Password
Real Name
Rights (1, 2, 3, 4, 5)
Now what is the best way to right the code for it to check userID then check password and if correct take the number under rights and then redirect to correct file.
1- Admin
2- Supervisor
3- Manager
4- User
5- other
Currently I have code that looks like this:
<% ELSE IF Request.form("username")=" admin" AND Request.form("password")=" a2006" THEN
Response.Redirect "admin/index.asp"%>
<% Else IF Request.form("username")=" manager" AND Request.form("password")=" m2006" THEN
Response.Redirect "manager/index.asp"%>
<% Else IF Request.form("username")=" supervisor " AND Request.form("password")=" s2006" THEN
Response.Redirect "supervisor/index.asp"%>
<% ELSE %> <div align="center"><b><i><font size="5" color="#FF0000">Error in username or password!</font></i></b></ div>
<% END IF %>
<% END IF %>
<% END IF %>
<% END IF %>
UserID
Password
Real Name
Rights (1, 2, 3, 4, 5)
Now what is the best way to right the code for it to check userID then check password and if correct take the number under rights and then redirect to correct file.
1- Admin
2- Supervisor
3- Manager
4- User
5- other
Currently I have code that looks like this:
<% ELSE IF Request.form("username")="
Response.Redirect "admin/index.asp"%>
<% Else IF Request.form("username")="
Response.Redirect "manager/index.asp"%>
<% Else IF Request.form("username")="
Response.Redirect "supervisor/index.asp"%>
<% ELSE %> <div align="center"><b><i><font
<% END IF %>
<% END IF %>
<% END IF %>
<% END IF %>
ASKER
Ok I got the idea now, how do i edit this code to make it check the password and forward to the correct page?
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% 'Login
IF Request.form="" THEN %>
<html>
<head></head>
<title>Administrator Dashboard</title>
<body>
<%'CONNECTION
Set conn = Server.CreateObject("ADODB .Connectio n")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=88888888; Password=88888888"
Set ObjRs = Server.CreateObject("ADODB .Recordset ")
'DECLARATIONS
dim strSQL, rs
strSQL = "SELECT * FROM lbl_users "
set rs = Conn.Execute(strSQL)%>
<table align="center" class="login">
<tr><td>
<div >
<p align="center"><b><font size="4">Administrator Dashboard Login</font></b></div></td >
<tr></tr>
<tr><td><div >Please Log in Below</div></td></tr>
<tr><td align="left"><form method="post" action="index.asp">
<div >Username: <input type="text" name="username" size="20"> &nbs p; Pa ssword: <input type="password" name="password" size="15"></div></td></tr>
<tr><td align="left"></td></tr>
<tr><td><input type="Submit" value="Submit">
</form></td></tr>
</table>
</body>
</html>
<% ELSE %>
<div align="center"><b><i><font size="5" color="#FF0000">Error in username or password!</font></i></b></ div>
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% 'Login
IF Request.form="" THEN %>
<html>
<head></head>
<title>Administrator Dashboard</title>
<body>
<%'CONNECTION
Set conn = Server.CreateObject("ADODB
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=88888888; Password=88888888"
Set ObjRs = Server.CreateObject("ADODB
'DECLARATIONS
dim strSQL, rs
strSQL = "SELECT * FROM lbl_users "
set rs = Conn.Execute(strSQL)%>
<table align="center" class="login">
<tr><td>
<div >
<p align="center"><b><font size="4">Administrator Dashboard Login</font></b></div></td
<tr></tr>
<tr><td><div >Please Log in Below</div></td></tr>
<tr><td align="left"><form method="post" action="index.asp">
<div >Username: <input type="text" name="username" size="20"> &nbs
<tr><td align="left"></td></tr>
<tr><td><input type="Submit" value="Submit">
</form></td></tr>
</table>
</body>
</html>
<% ELSE %>
<div align="center"><b><i><font
change:
strSQL = "SELECT * FROM lbl_users "
to something along these lines:
strSQL = "SELECT * FROM lbl_users WHERE username = '" & replace(request.form("user name"),"'" ,"`") & "' AND password = '" & replace(request.form("pass word"),"'" ,"`") & "'"
you will have to add in a form submission check....
if len(request.form)>0 then
'do your SQL stuff
else
'display your form
end if
strSQL = "SELECT * FROM lbl_users "
to something along these lines:
strSQL = "SELECT * FROM lbl_users WHERE username = '" & replace(request.form("user
you will have to add in a form submission check....
if len(request.form)>0 then
'do your SQL stuff
else
'display your form
end if
lol...nevermind that last bit, I see in the top of your code you aleady check to see if the form was submitted
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
same basic idea. The big thing to remember (especially with a login script) is to do the replace(string,"'","''"). This will help prevent the SQL Injection Attack
@kevp75:
Didn't mean to post duplicate code from what you posted ... I made the mistake of not thoroughly reading what you had written there before I posted mine.
Regards,
Phil / peh803
Didn't mean to post duplicate code from what you posted ... I made the mistake of not thoroughly reading what you had written there before I posted mine.
Regards,
Phil / peh803
no biggee.
Good to see we were on the same page at least :)
Good to see we were on the same page at least :)
ASKER
Good Morning All Thnx for the Help I am getting the following errors now:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'cint'
/test/Admin/admin.asp, line 42
Any Ideas? What is cint is that a typo?
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'cint'
/test/Admin/admin.asp, line 42
Any Ideas? What is cint is that a typo?
This is not correct, from my original post:
if not rs.EOF then
sRedirectPage = cint(rs.fields("redirectPa ge").value )
end if
Change it to read this way:
if not rs.EOF then
sRedirectPage = rs.fields("redirectPage"). value
end if
"cint()" is a function that converts strings to integers. It is a valid function, but this is not the correct place to use it. Sorry for the mistake!
Regards,
Phil
if not rs.EOF then
sRedirectPage = cint(rs.fields("redirectPa
end if
Change it to read this way:
if not rs.EOF then
sRedirectPage = rs.fields("redirectPage").
end if
"cint()" is a function that converts strings to integers. It is a valid function, but this is not the correct place to use it. Sorry for the mistake!
Regards,
Phil
ASKER
Thnx..... I found this to also work:
sRedirectPage = rs("redirectPage")
Any reason I should use the other?
sRedirectPage = rs("redirectPage")
Any reason I should use the other?
rs("redirectPage") is just a shortened version of rs.fields("redirectPage"). value
the "fields" collection is the default collection of the recordset object, and the "value" property is the default property, so doing rs("redirectPage") gives the same result as rs.fields("redirectPage"). value.
It's just that being more explicit saves a tiny bit of processing power on the server -- and whenever I can use it, I've just gotten into that habit.
In practice, you'll probably never notice a huge performance difference between the two, but that's why both of them work...
Hope that helps!
Regards,
Phil
the "fields" collection is the default collection of the recordset object, and the "value" property is the default property, so doing rs("redirectPage") gives the same result as rs.fields("redirectPage").
It's just that being more explicit saves a tiny bit of processing power on the server -- and whenever I can use it, I've just gotten into that habit.
In practice, you'll probably never notice a huge performance difference between the two, but that's why both of them work...
Hope that helps!
Regards,
Phil
thanks Josh... :<
ID Type RedirectPage
1 Admin admin/index.asp
2 Supervisor supervisor/index.asp
... etc.
Then, when you query the db, you'll be able to tell everything in one shot:
1.) Is the user authenticated?
2.) What is the user type?
3.) Where should I redirect the user?
All done...
Let me know if you need help with any of that.
Regards,
Phil / peh803