Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple MS SQL table lookup for user ID and Password

Posted on 2006-07-14
13
Medium Priority
?
337 Views
Last Modified: 2013-12-03
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 %>
0
Comment
Question by:kwitcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 19

Expert Comment

by:peh803
ID: 17110492
Put the page to redirect each user to in the rights table in SQL Server -- so, something like this:

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
0
 

Author Comment

by:kwitcom
ID: 17110709
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.Connection")
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">&nbsp;&nbsp;&nbsp;&nbsp;Password: <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>
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17111036
change:
strSQL = "SELECT * FROM lbl_users "


to something along these lines:
strSQL = "SELECT * FROM lbl_users WHERE username = '" & replace(request.form("username"),"'","`") & "' AND password = '" & replace(request.form("password"),"'","`") & "'"

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
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 25

Expert Comment

by:kevp75
ID: 17111039
lol...nevermind that last bit, I see in the top of your code you aleady check to see if the form was submitted
0
 
LVL 19

Accepted Solution

by:
peh803 earned 2000 total points
ID: 17111432
Try something like this......

Regards,
Phil / peh803



<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% 'Login
IF Request.form="" THEN %>
<html>
<head></head>
<title>Administrator Dashboard</title>
<body>

<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">&nbsp;&nbsp;&nbsp;&nbsp;Password: <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

'CONNECTION
Dim sUserName : sUserName = Request.Form("userName")
Dim sUserPwd : sUserPwd = Request.Form("password")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=88888888; Password=88888888"
Dim lCount : lCount = 0
Set rs = Server.CreateObject("ADODB.Recordset")
Dim sRedirectPage : sRedirectPage = ""
'DECLARATIONS
dim strSQL, rs
if len(trim(sUserName&""))>0 AND len(trim(sUserPwd&""))>0 then
  strSQL = "SELECT * FROM lbl_users where UserID='" & replace(sUserName, "'", "''") & "' AND UserPassword='" & replace(sUserPwd, "'", "''") & "'"
  rs.open strSQL, conn , 2, 2
  if not rs.EOF then
    sRedirectPage = cint(rs.fields("redirectPage").value)
  end if
  rs.close
  set rs = nothing
end if
if sRedirectPage = "" then
%>
<div align="center"><b><i><font size="5" color="#FF0000">Error in username or password!</font></i></b></div>
<%
else
  Response.Redirect sRedirectPage
end if
%>
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17111556
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
0
 
LVL 19

Expert Comment

by:peh803
ID: 17111596
@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
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17111798
no biggee.
Good to see we were on the same page at least  :)
0
 

Author Comment

by:kwitcom
ID: 17121943
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?
0
 
LVL 19

Expert Comment

by:peh803
ID: 17122038
This is not correct, from my original post:

  if not rs.EOF then
    sRedirectPage = cint(rs.fields("redirectPage").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
0
 

Author Comment

by:kwitcom
ID: 17122101
Thnx..... I found this to also work:

  sRedirectPage = rs("redirectPage")

Any reason I should use the other?
0
 
LVL 19

Expert Comment

by:peh803
ID: 17122218
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
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17122524
thanks Josh...  :<
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

610 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