SOLVED - login with username and password

[admin12]

I wrote the following code to login to the database. There is a table called UserValidation which holds the username and password info. I want this script to look up the user and allow access if they are in the system.

Right now, it always returns a "wrong username and password" even though I am entering the correct one.

=====================================

on mouseUp
--Create a global variable for the db
global dbID

-- put the Ip address of the MySQL Server
--put the name of your DB
-- then your login
--then your password

--this connects to the database
put revOpenDatabase("MySQL","www.website.com","client_database","username","password") into dbID

if dbID is not a number then
answer warning "Error "&dbID
exit to top
else
--answer "connection successful"
end if

--load username and password
put "SELECT UserName,Password FROM UserValidation" into theUN
put revDataFromQuery(,,dbID,theUN) into theUNData

if theUNData contains "revdberr" then
answer warning "There is an error "&theUNData
exit to top
end if

if theUNData = the text of field "fldUserName" and theUNData = the text of field "fldPassword1" then
go to card ReturnRegistration
else
answer "Incorrect username or password. Please try again."
exit to top
end if

end mouseUp

on mouseenter
set the text of field "lblHints" to "CLICK THIS BUTTON to login to the database"

end mouseenter

on mouseleave
set the text of field "lblHints" to empty
end mouseleave

=============================================

If anyone could help me with the syntax, it would be most appreciated.

Mike

[Mark]

Mike,

What does theUNData actually contain? A table of data or only garbage? Looking at your syntax, your variable theUNData should contain a return (linefeed) and tab-delimited list of user names and passwords. It would make much more sense to retrieve only the record with the right user name and check that the password is correct. Your MySQL syntax may look as following

SELECT UserName,Password FROM UserValidation WHERE Password = 'xxxxxxxx'

I hope this helps.

Mark

[admin12]

Mark,

Here's the line (I'd rather use the username instead of the password for the where statement):

put "SELECT UserName,Password FROM UserValidation WHERE UserName = " &UN into theUN
answer theUN

In the message box that comes up, the query looks perfect, but is giving me an sql error, saying there is no such column as (name of username here). How do I store the two fields in the variable theUN? Do I need to make it an array? If so, how?

Thanks for your help.

Mike

[Mark]

Hi Mike,

No, the query doesn't look perfect at all. Have you noticed that the single quotes are missing? Those are essential.

Please copy-paste any error messages.

Best,

Mark

[dglass]

Mark,

Here's the line (I'd rather use the username instead of the password for the where statement):

put "SELECT UserName,Password FROM UserValidation WHERE UserName = " &UN into theUN
answer theUN

In the message box that comes up, the query looks perfect, but is giving me an sql error, saying there is no such column as (name of username here). How do I store the two fields in the variable theUN? Do I need to make it an array? If so, how?

It's telling you there's no such column because you haven't escaped your username variable with single quotes so it is looking for a column called <<whatever the provided username is>>.

put "SELECT UserName, Password FROM UserValidation WHERE UserName = '" & UN & "'" into theUN

Note, that is

= <<single quote>><<double quote>> & UN & <<double quote>><<single quote>><<double quote>>

That is the minimum of escaping that has to be done. In practice you should be checking your 'UN' value and escaping any special characters within it as well.

[Mark]

Yeah, that's what I said, except that I wouldn't call it escaping. Those single quotes define a string. Escaping is done to make sure that characters are not parsed.

Best,

Mark