Login Database

acdsky · November 13th, 2003, 07:33 AM

Hi

I am probably missing something obvious here but for some reason it does not verify the username and password when login in. It defaults to the "Invalid Username" even though the username should be valid.

I suspect a problem with the sintax in the SQL statement. Below is the code....any help appreciated

<%
dim username, password, logButton
username=TRIM(Request("username"))
password=TRIM(Request("password"))
logButton=Request("loginButton")="Login"
if logButton then
   Dim Con, sql, rec
   set Con = Server.CreateObject("ADODB.Connection")
   Con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")
   'Select the record matching the username.
sql = "SELECT * FROM tblusers WHERE UCase(username)=' "& UCase(username) & "' AND UCase(password)=' " & UCase(password) & " ' "
   set rec=Con.execute(sql)
   'If no match found, EOF is not true.
   if NOT rec.EOF then
      Response.Redirect("Test_redirect.asp") 'Change to page redirect to after login
   else
      blankError="Invalid username." 'EOF is true, no match found.
   end if
end if
%>
<html>
<head>
<title>Login</title>
</head>
<body>
<form name="productForm" method="post" action="<%=Request.ServerVariables("URL")%>">
<center>
<table border =1>
<tr><td colspan="2">
<%

if blankError<>"" then
Response.Write("<center>"&blankError&"</center>")
end if
%>
</td></tr>
<tr>
<td><Strong>Username:</strong></td>
<td><input type="text" name="username" size="35"></td>
</tr>
<tr>
<td><Strong>Password</strong></td>
<td><input type="password" name="password" size="35"></td>
</tr>
<tr><td colspan="2" align="center"><input type="submit" name="loginButton" value="Login">
<input type="reset" name="reset" value="Clear"></td>
</tr>
</table>
</center>
</form>
</body>
</html>

Such is Life!

katsarosj · November 13th, 2003, 09:20 AM

Are there supposed to be spaces in your SQL statement?

sql = "SELECT * FROM tblusers WHERE UCase(username)=' "& UCase(username) & "' AND UCase(password)=' " & UCase(password) & " ' "

If not change this to:

sql = "SELECT * FROM tblusers WHERE UCase(username)= '" & UCase(username) & "' AND UCase(password)= '" & UCase(password) & "'"

-Just

katsarosj · November 13th, 2003, 09:23 AM

-Sorry, the post jumped on me before I could finish it. I wanted to show you the changes...

sql = "SELECT * FROM tblusers WHERE UCase(username)=' "& UCase(username) & "' AND UCase(password)=' " & UCase(password) & " ' "

If not change this to:

sql = "SELECT * FROM tblusers WHERE UCase(username)= '" & UCase(username) & "' AND UCase(password)= '" & UCase(password) & "'"

-Just a thought

J

pilmart · November 13th, 2003, 10:23 AM

Have a try with this...

sql = "SELECT * FROM tblusers WHERE UserName = '" & UCase(username) & "' AND Password='" & UCase(password) & " ' "

acdsky · November 13th, 2003, 11:39 AM

Thanks Guys...told ya it was something obvious

Such is Life!

katsarosj · November 13th, 2003, 11:40 AM

Just a side note-

I ran the following 3 SQL statements against a database and they returned the same thing:

SELECT *
FROM namelist
where Ucase(fname) = 'Teresa';
____________________________

SELECT *
FROM namelist
where UCase(fname) = UCase('Teresa');
____________________________

SELECT *
FROM namelist
where fname = 'Teresa';

I had one of the "Teresa's" in all upper case letters and one in lower case, so I would check your single quotation placement rather than the UCase statements.

hoffmann · November 13th, 2003, 08:14 PM

Many times I will insert a line between the sql creation and its execution to view the sql string

sql = "SELECT * FROM tblusers WHERE UCase(username)=' "& UCase(username) & "' AND UCase(password)=' " & UCase(password) & " ' "
%>
SQL = <%=SQL%>
<%
set rec=Con.execute(sql)

(I'm used to JaveScript rather than VBScript, so excuse the syntax issues)
This will print out the SQL string on the screen right above the error message and give me an opportunity to look for the spacing errors, etc.

Rich

pgtips · November 14th, 2003, 08:09 AM

Hi acdsky,

I can't help but point out something about your approach. I assume you've got the requirement for a user login because your site contains content that is of some value, and hence you wish to restrict its access? If not, ignore the rest of this post :D

There are 2 main things about your approach which make it inherently insecure:
1. your database is located within your web root, so anyone can download your database just by typing in <<your URL>>/database.mdb (not hard to guess that name)
2. couple this with the fact that you store passwords in plain-text makes it a 2-second job to crack your site.

rgds
Phil