Compare Rows (SQLite)

Databases
1

Hi all,

I am fairly new to Databases (as you will clearly see) and I am more in exploratory mode at the moment.

I am trying to create a simple login window and want to compare two rows to open a second window.(again, just exploring. I will be looking at salt and hash next. I also do not intend to use this in an actual application.)

at the moment, I have my database set up and connected. The code in the open event of the window is as follows.

Var dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.child("BBPW.sqlite")

If dbFile.Exists Then
  DB = New SQLiteDatabase
  DB.DatabaseFile = dbFile
  db.EncryptionKey = "howdy+doody"
  If DB.Connect Then
    Try
      db.Connect
    Catch error As DatabaseException
      MessageBox("Connection error: " + error.Message)
    End Try
  End If
Else
  DB = New SQLiteDatabase
  DB.DatabaseFile = dbFile
  If DB.CreateDatabaseFile Then
    Var SQL As String
    SQL = "CREATE TABLE TestDB (ID INTEGER, Username TEXT, Password TEXT, PRIMARY KEY(ID));"
    Try
      db.Connect
      db.Encrypt("howdy+doody")
      DB.ExecuteSQL(SQL)
    Catch error As DatabaseException
      MessageBox("Error: " + error.Message)
    End Try
  End If
End If

In another window I create the Username and Password to be stored in the database. (This works fine)

This is as far as I have gotten, I have tried to retrieve the Username and password using the following code in the action event of a button.

Textfield1.Text // Username
Textfield2.text // Password

Var rs As RowSet
Var ds As RowSet
Var User As String = TextField1.text
Var Pword As String = Textfield2.text
Try
  rs = db.SelectSQL("Select * From TestDB Where Username =", User)
  ds = db.SelectSQL("Select * From TestDB Where Password =", Pword)
  
  if rs = ds then // This is my attempt at trying to compare the rows =(
    MainWindow.show
    login.close
    
  end if
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

This compiles but I get the below error when clicking the button after inputting the stored Username and Password

Error: Incomplete Input.

Could anyone offer any guidance or point me in the right direction?

Again I must stress, This is just me exploring, I do not intend to use this in an actual application without Salt and Hash, I just wish to learn how to compare the two rows.

I hope I haven’t babbled on too much, please let me know if you need any more information.

Thank you all in advance!

Robin

2

You either need single quotes around the User and Pword (these are strings) or you’re missing the ?. Thus:

 rs = db.SelectSQL("Select * From TestDB Where Username ='" + User + "'")
 ds = db.SelectSQL("Select * From TestDB Where Password ='" + Pword + "'")

or you need this:

 rs = db.SelectSQL("Select * From TestDB Where Username = ?", User)
 ds = db.SelectSQL("Select * From TestDB Where Password = ?", Pword)
1 Like
3

Hi Tim, thank you for the reply.

I’ve tried both your solutions and on both instances, the app doesn’t seem to do anything after the username and password is entered and the button is clicked.

Robin

4

Comparing rs with ds will always fail as they are different objects. The selects will succeed but you are not doing anything with the rowsets, so that’s it.

What comparison do you want to do?

BTW you should use the second form of select, not the first. And add “Must read up about prepared statements” to your to-do list.

1 Like
5

Hi Tim,

I was hoping to compare both the Username and Password (within the database) to the text in both textfield1 and textfield2

There is quite a lot I need to add to my “to-do” list.

Thank you again Tim

Robin

6

Side note: you never want to store the password in your database in any form. Instead, store a secure hash of the password, like that obtained from Crypto.PBKDF2, and compare that later.

1 Like
7

Hi Kem,

I do not intend to store passwords or usernames of any kind at all. I am rather using “Username” and “Password” as examples to try and figure out how to compare rows within the database using two textfields.

1 Like
8

You’ll need to extract the info from each rowset and then compare. Something like:

rs = db.SelectSQL("Select password From TestDB Where Username = ?", User)
if  (rs=Nil or rs.RowCount()=0)  then
  // That user not known
  Return
End if

if  (rs.column("password").StringValue<>Pword)  then
  // User not known
  return
end if

 // User's credentials compare OK to database

Hm, actually, unclear why you’d need more than one RowSet.

1 Like
9

Looking back at it again, I’ve no clue what I was attempting using two rowsets :man_facepalming:

Tim, thank you ever so much for your help. I’m away from the computer at the moment but will be trying your solution a bit later.

Robin

1 Like
10

Tim, SUCCESS!

I managed to get it working with your solution.

I keep saying it, but thank you again.

Robin