login

[bender]

I'm having a bit of problem with a login script

<%
Dim sql
Dim len
Dim username, password
username = request.form("username")
password = request.form("pass")

sql = "Select * from Users where User_ID = '" & username & "' AND password =
'" & password & "';"

set adoCon = server.CreateObject ("adodb.connection")
adoCon.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("something.mdb") & ";Persist Security Info=False"

Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open sql, adoCon
len = rs.recordcount
response.write len
%>

This outputs -1 regardless if I enter the correct username\password or not.
Wondering if my sql sentence is correct, not sure about the " and ' is
correct. Both User_ID and password are text fields in the database

 

[Ray at]

There are a number of issues with your code below that I notice.

1. When you have a column named "username" or "password" you should
[bracket] it so there are not issues with reserved SQL words.
2. Instead of selecting *, just select one column or even some arbitrary
data. If you need to pull in all the data for that user, name your columns
individually then.
3. Don't bother doing the createobject("adodb.recordset") thing.
4. For the -1 count, see here http://www.aspfaq.com/2193.

I suggest this code, assuming that all you want is to validate the login.


bUserLoggedIn = False
sUsername = Request.Form("username")
sPassword = Request.Form("password")

sUsername = Replace(sUsername, "'", "''")
sPassword = Replace(sUsername, "'", "''") ''or put this in a function

sSQL = "SELECT [id] FROM [User_ID] WHERE [Username]='" & sUsername & "' AND
[Password]='" & sPassword & "'"

set adoCon = server.CreateObject ("adodb.connection")
adoCon.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("something.mdb") & ";Persist Security Info=False"

Set rsLogin = adoCon.Execute(sSQL)
bUserLoggedIn = Not rsLogin.EOF
rsLogin.Close
Set rsLogin = Nothing
adoCon.Close
Set adoCon = Nothing

Ray at work

 

[Tom B]

If you actually "need" the recordcount then you need to use an appropriate
cursor.
If you just want to ensure you have a match for the combination than you can
do..

rs.Open sql, adoCon 'or better yet....Set rs=adoCon.Execute(sql)
if rs.EOF then
'there are no records, so no match
else
'there is a record, so it's a match
end if

You may want to change your sql to
"Select User_ID from Users where User_ID='" & username & "' AND password='"
& password & "'"

as using the * adds a bit of overhead.

You should ensure your users haven't passed in bad data.

Take a look at www.aspfaq.com

 

[Bob Barrows]

recordcount will not work with the default cursor type you are using for the
recordset. However, instead of switching to a more expensive cursor, I have
two alternative suggestions. The one you choose depends on what you need to
use the data in the recordset for:

1. If all you need to do is determine if the user_ID/password (bad!
"password" is very likely to be a reserved word - I don't have time to look
it up right now to verify this, but I think you should rename this field)
exists, then simply do this:
sql = "Select count(*) from Users where User_ID = '" & username & _
"' AND password ='" & password & "';"
'for debugging:
response.write sql
....
rs.Open sql, adoCon,,,1
len = rs(0).value
rs.close
set rs=nothing
adoCon.close
set adoCon = nothing
response.write len

2.If you need to do something with the data in the recordset, then first of
all, you should explicitly name the fields that you want to return from the
table instead of using "select *" and then use getrows to stuff the data
from the recordset into an array, allowing you to immediately close your
recordset and connection (a good thing for scalability), and to use this
code to determine the number of records ... actually, I assume there's only
one record per user, so recordcount is not needed in this case either: all
you really need to do is determine if the query returned any records. You
can test the EOF property ro figure this out:

rs.Open sql, adoCon,,,1
if not rs.EOF then
ar = rs.getrows
end if
rs.close
set rs = nothing
adoCon.close
set adoCon = nothing
if isarray(ar) then
for iRow = 0 to ubound(ar,2)
for iCol = 0 to ubound(ar,1)
response.write ar(iCol, iRow) & "; "
next
response.write "<BR>"
next
else
response.write "No records were returned"
end if

HTH,
Bob Barrows

 

[PB4FUN]

With RS
.CursorLocation = adUseServer
.Open QSL, adoCon
len = .RecordCount
End With

Meindert