Display sqlite Data

[Quinton B.]

Hello, I'm having trouble displaying the password of an account. I have just made the insert statement to put new account information into the database. Now once you have an account and you forget the password, I'm trying to search the database for the password of a row where the username, security question, and security question answer.

Here is the following code:

global gDatabaseID
on mouseUp
put the text of field "field_Forgot_Password_UserName" into tForgotPasswordUserName
answer tForgotPasswordUserName
put the label of button "button_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestion
answer tForgotPasswordSecurityQuestion
put the text of field "field_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestionAnswer
answer tForgotPasswordSecurityQuestionAnswer
##revExecuteSQL gDatabaseID, "SELECT Password FROM MentorAccount Where UserName = tForgotPasswordUserName, SecurityQuestion = tForgotPasswordSecurityQuestion, SecurityQuestionAnswer = tForgotPasswordSecurityQuestionAnswer"
put "SELECT Password FROM MentorAccount Where UserName = tForgotPasswordUserName, SecurityQuestion = tForgotPasswordSecurityQuestion, SecurityQuestionAnswer = tForgotPasswordSecurityQuestionAnswer" into tSQL
put revDataFromQuery(tab,return,gDatabaseID,tSQL) into field "field_Forgot_Password_Password"
end mouseUp

[Klaus]

Hi Quinton,

you are passing the e.g. STRING tForgotPasswordUserName and not its content to the SQL command and also do not use AND to "connect" the conditions!
...
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion = " & "'" & tForgotPasswordSecurityQuestion & "'" && AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tSQL
...
I recommend this page to learn some more SQL, I always visit that page when I need to script database stuff
https://www.w3schools.com/sql/default.asp

Best

Klaus

[SparkOut]

The "password" is really the hashed and salted digest when you store and compare, isn't it? Please say it is. Please!
Storing passwords in the real in the database is really a bad idea.

[Klaus]

Yes, sure, but one step after another!

[Quinton B.]

Hey, Klaus. That didn't work.

[Klaus]

Yep, sorry, wrote that out of my head and forgot a QUOTE before the last AND, this one will work:

Code: Select all

...
## In one line of course!
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion = " & "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'"  into tSQL
...

Hint: To avoid this inconvenience, I ususally use two little functions:

Code: Select all

function q tString
  return QUOTE & tString & QUOTE
end q

function q2 tString
  return "'" & tString & "'"
end q2

Saves a lot of typing and the neccessity to count all closing quotes and single quotes

[Quinton B.]

Okay, it doesn't post any errors. But how do I display the search result in a text box?

[Klaus]

Okay, it doesn't post any errors.

Sure? Please take a look at field "Password" in the screenshot.
What exactly does the error say?

[Quinton B.]

Yeah, I'm sure, I forgot to clear that field.

[Klaus]

Hi Quinton,

ah, I see, OK, fine then.

When using this:
...
put revDataFromQuery(tab,return,gDatabaseID,tSQL) into tData
## Means: return all found data one line per record (cr), database fields of one record separated by tab
...
The returned data from the database will be one line with three items separated by TAB and you can e.g.:
---
set itemdel to TAB
piut item 1 of tDAta into fld "username"
put item 2 of tData into fld "security question"
put item 3 of tData into fld "security question answer"
...
You get the picture


Best

Klaus

[Quinton B.]

I fixed some of your grammar errors, but it still didn't work, so I tried what another form had:

put revQueryDatabase(gDatabaseID, "SELECT * FROM MentorAccount Where UserName="& field "field_Forgot_Password_UserName" & ";") into field "field_Forgot_Password_Password"

But I get the following error

[Klaus]

Hi Quinton,

not sure, but since you did not use single quotes, the database obviously interprets the content of
field "field_Forgot_Password_UserName" as a column name, which in fact does not exist.

Code: Select all

Try this, using my function q2:
...
put revQueryDatabase(gDatabaseID, "SELECT * FROM MentorAccount Where UserName="& q2(field "field_Forgot_Password_UserName") & ";") into field "field_Forgot_Password_Password"
...

Best

Klaus

[AxWald]

Hi,

UserName is quite sure VARCHAR or TEXT, so you need single quotes.
And "revQueryDatabase" only returns a recordset ID, which is not what you want here.

Besides, single quoting table names has proven helpful, too. And error checking is worth its weight in gold!

I wrote a quick demo with lots of comments for you. Try it!
You'll see that I use the "revDB_*" functions, this is just a matter of taste - they're easy to check, and easy to search in code. You could as well use "revOpenDataBase" and "revDataFromQuery".
(Hint: I do professional work with databases, and all I ever need (of all the rev DB stuff) are: "revDB_connect", "revDB_disconnect", revDB_queryList" and "revDB_execute" ...)

Code: Select all

on mouseUp
   --  Preparing:  --
   put field "UserName_fld" into myUserName
   put "SELECT * FROM 'MentorAccount' WHERE UserName = '" & myUserName & "';" into StrSQL
   put StrSQL  --  for development only, so that we see what we did!
   --> SELECT * FROM 'MentorAccount' WHERE UserName = 'Tate.Tech';
   --  UserName is TEXT or VARCHAR, so single quotes are required!
   
   --  The DB stuff: __
   put revDB_connect("SQLite","D:/Databases/myDB.sqlite") into myDBId  --  open DB
   if myDBId is not a number then
      answer error "DB connect failed!" & CR & myDBId
      exit mouseUp
   end if
   
   put revDB_QueryList("tab","return",myDBId,StrSQL) into myData  --  get Data
   get revDB_disconnect(myDBId)  --  close DB before processing. No need to check result.
   
   set itemdel to comma --  just to be sure ...
   if item 1 of myData = "revdberr" then
      answer error "DB query failed: " & item 1 of myData & CR & StrSQL
      --  having the SQL in the errStr is priceless!
      exit mouseUp
   end if
   
   --  Data processing: --
   put myData  --  for development only, so that we see what we did!
   --> a record, tab-delimited, easy to check in a spread sheet or good text editor
   
   set itemdel to tab --  since now we work with tabbed items
   if the number of lines of myData <> 1 then           --  always carefully check the results of your queries!
      if the number of lines of myData = 0 then
         answer error "No data found!"
      else
         answer error "Multiple records found!"
      end if
   else
      put item 1 of myData into field "FieldForItem_1"   --  you got the idea?
      put item 2 of myData into field "FieldForItem_2"
      --  ...
   end if
end mouseUp

Hope this helps, have fun!

[Quinton B.]

So now I'm trying to update my database with a new password after I have validated that it is their. I have done the validation part but cannot update the password.