MySQL Query

[Jamie37]

Hello,

I have an SQL code that checks against two field which are Username and Password. It then checks to see if the records exists and allow somebody to log in if they do. However, it will only work when I have 1 record in the database and when I add another record, it will then take every record and only the first record work. Is there a way to get around this and for it to search through the column without it taking the first record.

Here is the code on the card:

Code: Select all

    if tResult is a number then
        put tResult into gConnectionID
    else
        put empty into gConnectionID
        answer error "Unable to connect to the database:" & cr & tResult
     end if
     
     //SELECTS USER DATA FROM DATABASE AND PUTS THEM INTO VARIABLES 'theUserData & thePassData'
     
     put "SELECT Username FROM Logininfo" into theUser
     put revDataFromQuery(,,gConnectionID,theUser) into theUserData
     put "SELECT Password FROM Logininfo" into thePass
     put revDataFromQuery(,,gConnectionID,thePass) into thePassData
     
     answer theUserData
      
      //CHECKS TO SEE IF THE INPUTS MATCH THE DATABASE RECORDS
   
     if theUserData = the text of field "username" and thePassData = the text of field "password"  then
     go to card LoadingScreen
      else
         answer "Incorrect username or password. Please try again."
         global gConnectionID
    
    -- if we have a connection, close it and clear the global connection ID
    if gConnectionID is a number then
        revCloseDatabase gConnectionID
        put empty into gConnectionID
    end if
         exit to top
         end if
end loginCheck

Thanks
Jamie

[quailcreek]

Try something like this. You'll want to do some error checking to see if the user is in the database too.

Code: Select all

 put fld "username" into tUserName
   put fld "password" into tUserPassword
   
   put "SELECT Username FROM Logininfo" into theUsers
   put revDataFromQuery(,,gConnectionID,theUsers) into theUserList
   
   if tUserName is among the lines of theUserList then
      
      put "SELECT Password FROM Logininfo WHERE UserName =:1" into theQuery
      put revDataFromQuery(tab,cr, gConnectionID ,theQuery,"tUserName") into theUserPassword
      
      if tUserPassword is theUserPassword then
         go to card LoadingScreen
      else 
         answer "Incorrect username or password. Please try again."
      end if
   else
      answer "username does not exist. Please try again."
   end if

[phaworth]

Code: Select all

put "SELECT rowid FROM Logininfo WHERE Username=:1 AND Password=:2" into theUsers
put revDataFromQuery(,,gConnectionID,theUsers,"tUserName","tUserPassword") into theUserList

If theUSerList is empty, there is no matching entry for the username/password. If it is an integer, there is a matching entry.

Be sure to use the variableslist parameter of revDataFromQuery if you are sending this query to mySQL or any server based SQL implementation otherwise you'll likely be hacked and all your username/passwords will be compromised.

Pete

[quailcreek]

Much better, Pete. I was working longhand and you use the cliff notes version.

[Jamie37]

Thank you both for your help.

It is now working so thanks very much for your help and suggestions for security. Is it easy to implement the security into the code phaworth. Sorry if its a simple question but I am still relatively new to SQL and livecode.

Thanks
Jamie