Variable in an SQL Query

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
Gunit31
Posts: 4
Joined: Tue Feb 14, 2017 7:05 pm

Variable in an SQL Query

Post by Gunit31 » Wed Feb 15, 2017 6:19 pm

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

ClipArtGuy
Posts: 253
Joined: Wed Aug 19, 2015 4:29 pm

Re: Variable in an SQL Query

Post by ClipArtGuy » Wed Feb 15, 2017 7:09 pm

try this:

Code: Select all

 put ("SELECT Password FROM Login WHERE Username = "&tUsername) into tSQLQuery

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: Variable in an SQL Query

Post by SparkOut » Wed Feb 15, 2017 7:13 pm

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:

Code: Select all

put "SELECT Password FROM Login WHERE Username =" && tUsername into tSQLQuery
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.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Variable in an SQL Query

Post by Klaus » Wed Feb 15, 2017 10:29 pm

Hi Gunit31,

1. welcome to the forum! :D

2. what SparkOut said!

3. Will move this thread to the correct forum, this is definitively not an "Announcement"! 8)


Best

Klaus

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: Variable in an SQL Query

Post by SparkOut » Wed Feb 15, 2017 11:04 pm

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

Gunit31
Posts: 4
Joined: Tue Feb 14, 2017 7:05 pm

Re: Variable in an SQL Query

Post by Gunit31 » Thu Feb 16, 2017 6:29 pm

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?

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: Variable in an SQL Query

Post by SparkOut » Thu Feb 16, 2017 10:28 pm

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).

ittarter
Posts: 151
Joined: Sat Jun 13, 2015 2:13 pm

Re: Variable in an SQL Query

Post by ittarter » Fri Feb 17, 2017 6:32 pm

SparkOut wrote:I meant a password should never be stored in plaintext in the database.
I'm just learning about password hashing. I now understand its importance.
How do you hash a password in Livecode? Is there something that we could read to help us protect our users' information?

Thanks!

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Variable in an SQL Query

Post by Klaus » Fri Feb 17, 2017 6:36 pm

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.

ittarter
Posts: 151
Joined: Sat Jun 13, 2015 2:13 pm

Re: Variable in an SQL Query

Post by ittarter » Sun Feb 19, 2017 8:37 am

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.
Thanks, Klaus. What a great thing to know. I've already made the changes to my program and now sensitive user information is protected!

rinzwind
Posts: 135
Joined: Tue May 01, 2012 10:44 am

Re: Variable in an SQL Query

Post by rinzwind » Sun Feb 19, 2017 7:29 pm

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"

Gunit31
Posts: 4
Joined: Tue Feb 14, 2017 7:05 pm

Re: Variable in an SQL Query

Post by Gunit31 » Wed Mar 01, 2017 12:58 am

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.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”