Display sqlite Data
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Display sqlite Data
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
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
Re: Display sqlite Data
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
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
Re: Display sqlite Data
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.
Storing passwords in the real in the database is really a bad idea.
Re: Display sqlite Data
Yes, sure, but one step after another!
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Re: Display sqlite Data
Hey, Klaus. That didn't work.
Re: Display sqlite Data
Yep, sorry, wrote that out of my head and forgot a QUOTE before the last AND, this one will work:
Hint: To avoid this inconvenience, I ususally use two little functions:
Saves a lot of typing and the neccessity to count all closing quotes and single quotes
Code: Select all
...
## In one line of course!
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion = " & "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tSQL
...
Code: Select all
function q tString
return QUOTE & tString & QUOTE
end q
function q2 tString
return "'" & tString & "'"
end q2
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Re: Display sqlite Data
Okay, it doesn't post any errors. But how do I display the search result in a text box?
Re: Display sqlite Data
Sure? Please take a look at field "Password" in the screenshot.Quinton B. wrote:Okay, it doesn't post any errors.
What exactly does the error say?
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Re: Display sqlite Data
Yeah, I'm sure, I forgot to clear that field.
Re: Display sqlite Data
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
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
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Re: Display sqlite Data
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
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
Re: Display sqlite Data
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.
Best
Klaus
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"
...
Klaus
Re: Display sqlite Data
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" ...)
Hope this helps, have fun!
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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
-
- Posts: 108
- Joined: Mon Mar 20, 2017 5:13 am
Re: Display sqlite Data
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.