Variable in an SQL Query
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Variable in an SQL Query
Hello my name is Garrett and I am trying to build a login system but I don't know how to incorporate my variable into the query. My variable is "tUsername" and i need help if anyone knows how to get around this. As I said it is for an SQL Database.
put "SELECT Password FROM Login WHERE Username = tUsername" into tSQLQuery
put revDataFromQuery(tab,return,gConnectionID,tsglquerys) into field "Password(check)"
Thank You
put "SELECT Password FROM Login WHERE Username = tUsername" into tSQLQuery
put revDataFromQuery(tab,return,gConnectionID,tsglquerys) into field "Password(check)"
Thank You
-
- Posts: 253
- Joined: Wed Aug 19, 2015 4:29 pm
Re: Variable in an SQL Query
try this:
Code: Select all
put ("SELECT Password FROM Login WHERE Username = "&tUsername) into tSQLQuery
Re: Variable in an SQL Query
You are mixing variables and literals together, this works in PHP but doesn't work that way in LiveCode.
You need to construct the statement by concatenating the literal with the variable, as per:
Then you have a typo in the variable name in the revDataFromQuery statement.
But above all, it is REALLY bad practice to store the unhashed password in the database. Please, please, please, tell me you are storing a password hash in the database and will be using that to compare against the hash of the password the user enters in your login form.
You need to construct the statement by concatenating the literal with the variable, as per:
Code: Select all
put "SELECT Password FROM Login WHERE Username =" && tUsername into tSQLQuery
But above all, it is REALLY bad practice to store the unhashed password in the database. Please, please, please, tell me you are storing a password hash in the database and will be using that to compare against the hash of the password the user enters in your login form.
Re: Variable in an SQL Query
Hi Gunit31,
1. welcome to the forum!
2. what SparkOut said!
3. Will move this thread to the correct forum, this is definitively not an "Announcement"!
Best
Klaus
1. welcome to the forum!
2. what SparkOut said!
3. Will move this thread to the correct forum, this is definitively not an "Announcement"!
Best
Klaus
Re: Variable in an SQL Query
Actually needs a small correction, since the parameter will need to be quoted with single quotes in the sql query:
Code: Select all
put "SELECT Password FROM Login WHERE Username = '" & tUsername & "'" into tSQLQuery
Re: Variable in an SQL Query
Thank you to everyone who helped me with this. It now works the way that i intended however, SparkOut brought up the issue I hadn't thought about which is how bad it is to localize and check the password within the app. If i were to check it within the database would I need to add the input information into the database or just write a statement to do this?
Re: Variable in an SQL Query
I meant a password should never be stored in plaintext in the database.
When creating the user account and a password is chosen, typed by the user in plaintext, that string should be hashed before being stored. Then when a user attempts login, the plaintext password should be hashed again and the two hashes compared ( one retrieved from the database and one based on the login attempt).
When creating the user account and a password is chosen, typed by the user in plaintext, that string should be hashed before being stored. Then when a user attempts login, the plaintext password should be hashed again and the two hashes compared ( one retrieved from the database and one based on the login attempt).
Re: Variable in an SQL Query
I'm just learning about password hashing. I now understand its importance.SparkOut wrote:I meant a password should never be stored in plaintext in the database.
How do you hash a password in Livecode? Is there something that we could read to help us protect our users' information?
Thanks!
Re: Variable in an SQL Query
Check "md5digest" in the dictionary!
Basically you store the md5digest of the plain password into the db and later let
the user enter the password again and check its md5digest against the stored one.
If they are identical, the password is correct.
Basically you store the md5digest of the plain password into the db and later let
the user enter the password again and check its md5digest against the stored one.
If they are identical, the password is correct.
Re: Variable in an SQL Query
Thanks, Klaus. What a great thing to know. I've already made the changes to my program and now sensitive user information is protected!Klaus wrote:Check "md5digest" in the dictionary!
Basically you store the md5digest of the plain password into the db and later let
the user enter the password again and check its md5digest against the stored one.
If they are identical, the password is correct.
Re: Variable in an SQL Query
You need to use parameters, unless you want to be vulnerable to sql injections...
Example copied from other thread:
revExecuteSQL tConnectionID, "INSERT INTO TableA (Col1,Col2,Col3,Col4) VALUES (:1,:2,:3,:4)", "t1","t2","t3","t4"
Example copied from other thread:
revExecuteSQL tConnectionID, "INSERT INTO TableA (Col1,Col2,Col3,Col4) VALUES (:1,:2,:3,:4)", "t1","t2","t3","t4"
Re: Variable in an SQL Query
I've changed a lot of the rest of the app but when I went back it was broken again. Now when I type it in, I get "revdberr,Database Error: no such table: Login" which doesn't make sense due to the fact that there is a table called Login and if I run the same type of query in Sql it works.
put "SELECT Password FROM Login WHERE Username = '" & tUsername & "'" into tSQLQuery
put revDataFromQuery( tab, return, gConnectionID, tSQLQuery) into field "Password(check)"
Pls help and thanks in advance.
put "SELECT Password FROM Login WHERE Username = '" & tUsername & "'" into tSQLQuery
put revDataFromQuery( tab, return, gConnectionID, tSQLQuery) into field "Password(check)"
Pls help and thanks in advance.