|
Subject:
|
Login Database - SQL Statement
|
|
Posted By:
|
acdsky
|
Post Date:
|
11/13/2003 6:33:21 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><font color='red' size='3'>"&blankError&"</font></center>")
end if
%>
</td></tr>
<tr>
<td><Strong><font face="courier new" size="3">Username:</font></strong></td>
<td><input type="text" name="username" size="35"></td>
</tr>
<tr>
<td><Strong><font face="courier new" size="3">Password</font></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!
|
|
Reply By:
|
katsarosj
|
Reply Date:
|
11/13/2003 8:20:46 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
|
|
Reply By:
|
katsarosj
|
Reply Date:
|
11/13/2003 8:23:52 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
|
|
Reply By:
|
pilmart
|
Reply Date:
|
11/13/2003 9:23:46 AM
|
Have a try with this...
sql = "SELECT * FROM tblusers WHERE UserName = '" & UCase(username) & "' AND Password='" & UCase(password) & " ' "
|
|
Reply By:
|
acdsky
|
Reply Date:
|
11/13/2003 10:39:34 AM
|
Thanks Guys...told ya it was something obvious
Such is Life!
|
|
Reply By:
|
katsarosj
|
Reply Date:
|
11/13/2003 10:40:38 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.
|
|
Reply By:
|
hoffmann
|
Reply Date:
|
11/13/2003 7:14:26 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
|
|
Reply By:
|
pgtips
|
Reply Date:
|
11/14/2003 7:09:41 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 
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
|
