B4J Question Helping about null data

universengo

universengo

Member
Hello Friends!
I have a question as below:
I use access database file and I have 1 table as Acount with 2 Col as UserName (as Text), PassWord (as Text with Input Mask = password)
2 records as
UserName______________PassWord
Admin ______________ Admin
Guest ______________ 'empty
I make an app with 2 Object as cmbUserName and txtPassWord
I load data from USerName data to cmbUserName
My code btLogin:
B4X: 
Dim User,Pass As String
    User=cmbUserName .Value
    Pass=txtPassWord.Text
    If txtPassWord.Text="" Then
        Pass=Null
    End If
    'Test data
    Query="Select UserName , PassWord FROM Acount"
    Dim Cursor As ResultSet
    Cursor = Main.gSQL.ExecQuery(Query)
    Do While Cursor.NextRow
        If User=Cursor.GetString("UserName ") And Pass=Cursor.GetString("PassWord ") Then
            fx.Msgbox(Main.MainForm,"Login Successfull.","Infomation")
            Return
        End If
    Loop
    Cursor.Close
 
    fx.Msgbox(Main.MainForm,"Login Fail.","Infomation")
    cmbUserName .RequestFocus

If I choose Admin user with pass: Admin then Login Successfull
If I choose Guest user with pass: empty then Login Fail.
I don't know how to fix, I check log and see true all with User = Guest; pass = null but Login Fail.

Please help me!
 
Last edited:
Sort by date Sort by votes
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
You are returning without closing the ResultSet.
Don't use nulls with strings. It is a mistake. Are you sure that the field is stored as null in the database?
B4X: 
Dim password As Object
If txtPassword.Text = "" Then password = Null Else password = txtPassword.Text
If Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, password)) > 0 Then
 Log("success")
Else
 Log("failed")
End If
 
Upvote 0
universengo

universengo

Member
Erel said:
You are returning without closing the ResultSet.
Don't use nulls with strings. It is a mistake. Are you sure that the field is stored as null in the database?
B4X: 
Dim password As Object
If txtPassword.Text = "" Then password = Null Else password = txtPassword.Text
If Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, password)) > 0 Then
 Log("success")
Else
 Log("failed")
End If
Click to expand...
Thanks Erel,
Are you sure that the field is stored as null in the database?
Click to expand...
I am sure. See my pic.
 

Attachments

  • MyData.png
    MyData.png
    100.3 KB · Views: 155
Upvote 0
universengo

universengo

Member
Erel said:
You are returning without closing the ResultSet.
Don't use nulls with strings. It is a mistake. Are you sure that the field is stored as null in the database?
B4X: 
Dim password As Object
If txtPassword.Text = "" Then password = Null Else password = txtPassword.Text
If Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, password)) > 0 Then
 Log("success")
Else
 Log("failed")
End If
Click to expand...
I use your code but result is still failed
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Try
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND (Password = NULL OR Password ="")"$, Array(cmbUserName.Value))
Else
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Notes:
1) Untested code and SQL. May need slight modification to actually run
2) If you just check for NULL passwords and not "" passwords, you're results will not be as expected, since technically, in your case a NULL password and "" password should produce the same results. See point #3
3) SQL Nulls sucks unless you are 100% consistent. Even then, they suck
4) Java Nulls suck
5) Nulls just plain suck. See https://www.lucidchart.com/techblog/2015/08/31/the-worst-mistake-of-computer-science/
6) String Nulls are not what you expect them to be. You expect a Null string object, but B4J gives a a String with Null value. I have seen cases were external libs return Null string objects, and then things really get complicated in the whole Null checking. Therefore heed @Erel's advice: Avoid Null Strings
 
Upvote 0
universengo

universengo

Member
OliverA said:
Try
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND (Password = NULL OR Password ="")"$, Array(cmbUserName.Value))
Else
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Notes:
1) Untested code and SQL. May need slight modification to actually run
2) If you just check for NULL passwords and not "" passwords, you're results will not be as expected, since technically, in your case a NULL password and "" password should produce the same results. See point #3
3) SQL Nulls sucks unless you are 100% consistent. Even then, they suck
4) Java Nulls suck
5) Nulls just plain suck. See https://www.lucidchart.com/techblog/2015/08/31/the-worst-mistake-of-computer-science/
6) String Nulls are not what you expect them to be. You expect a Null string object, but B4J gives a a String with Null value. I have seen cases were external libs return Null string objects, and then things really get complicated in the whole Null checking. Therefore heed @Erel's advice: Avoid Null Strings
Click to expand...
Thanks OliverA, The result is failed, too.
I choose cmbUserName.Value = "Guest" and txtPassword.text = "" but Count = 0.
 
Last edited:
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Let's do some logging:
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 Log($"Checking if user ${cmbUserName.Value} exists"$)
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ?", Array(cmbUserName.Value))
 If count > 0 Then
  Log($"Checking if user ${cmbUserName.Value} has a NULL password in the database"$)
  count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL", Array(cmbUserName.Value))
  If count = 0 Then
   Log($"No user ${cmbUserName.Value} found with NULL password, checking for blank password"$)
   count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND Password = """$, Array(cmbUserName.Value))
   If count = 0 Then
      Log($"No matching password entry (neither blank, nor NULL) found for user ${cmbUserName.Value}"$)
   Else
      Log($"User ${cmbUserName.Value} had a blank password"$)
   End If
  Else
    Log($"Password in database for user ${cmbUserName.Value} contained a NULL value and we found a match"$)
  End If
 Else
  Log($"Could not find user ${cmbUserName.Value}!"$)
Else
 Log("txtPassword is not blank")
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Note: Untested code, may need adjustments to run. Post log output.
 
Upvote 0
universengo

universengo

Member
OliverA said:
Let's do some logging:
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 Log($"Checking if user ${cmbUserName.Value} exists"$)
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ?", Array(cmbUserName.Value))
 If count > 0 Then
  Log($"Checking if user ${cmbUserName.Value} has a NULL password in the database"$)
  count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL", Array(cmbUserName.Value))
  If count = 0 Then
   Log($"No user ${cmbUserName.Value} found with NULL password, checking for blank password"$)
   count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND Password = """$, Array(cmbUserName.Value))
   If count = 0 Then
      Log($"No matching password entry (neither blank, nor NULL) found for user ${cmbUserName.Value}"$)
   Else
      Log($"User ${cmbUserName.Value} had a blank password"$)
   End If
  Else
    Log($"Password in database for user ${cmbUserName.Value} contained a NULL value and we found a match"$)
  End If
 Else
  Log($"Could not find user ${cmbUserName.Value}!"$)
Else
 Log("txtPassword is not blank")
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Note: Untested code, may need adjustments to run. Post log output.
Click to expand...
Thanks OliverA so much!
I think that Your code miss "End If" of "If count > 0 Then".
so, then I added "End if" below the code Log($"Could not find user ${cmbUserName.Value}!"$)
And The log result is below

Checking if user Guest exists
Checking if user Guest has a NULL password in the database
No user Guest found with NULL password, checking for blank password
No matching password entry (neither blank, nor NULL) found for user Guest
failed

P/s: I login with User: Administrator and Pass: admin
The result is OK -> Log is txtPassword is not blank -> success

Now help me for this situation!
Thanks.
 
Last edited:
Upvote 0
universengo

universengo

Member
universengo said:
Thanks OliverA so much!
I think that Your code miss "End If" of "If count > 0 Then".
so, then I added "End if" below the code Log($"Could not find user ${cmbUserName.Value}!"$)
And The log result is below

Checking if user Guest exists
Checking if user Guest has a NULL password in the database
No user Guest found with NULL password, checking for blank password
No matching password entry (neither blank, nor NULL) found for user Guest
failed

P/s: I login with User: Administrator and Pass: admin
The result is OK -> Log is txtPassword is not blank -> success

Now help me for this situation!
Thanks.
Click to expand...
If no one has any other solution, I think the best solution is to not allow password = "" or null to be the best.
Thanks all of you.
 
Upvote 0
AnandGupta

AnandGupta

Expert
Licensed User
Longtime User
universengo said:
If no one has any other solution, I think the best solution is to not allow password = "" or null to be the best.
Click to expand...
This I think is the best approach when connecting from any programming language, as I have faced similar situations in different programming languages when connecting to a database. Having a password, just 'pass' even, works smoothly.

Regards,

Anand
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Try
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 Log($"Checking if user ${cmbUserName.Value} exists"$)
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ?", Array(cmbUserName.Value))
 If count > 0 Then
  Log($"Checking if user ${cmbUserName.Value} has a NULL password in the database"$)
  count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL", Array(cmbUserName.Value))
  If count = 0 Then
   Log($"No user ${cmbUserName.Value} found with NULL password, checking for blank password"$)
   count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND Password = """$, Array(cmbUserName.Value))
   If count = 0 Then
      Log($"No matching password entry (neither blank, nor NULL) found for user ${cmbUserName.Value}"$)
      Log($"Determining Password value for user ${cmbUserName.Value}"$)
      Dim q As String = $"SELECT Password FROM Account WHERE UserName = "Guest""$
      Dim r as ResultSet
      r = Main.gSQL.ExecQuery(q)
      If r.NextRow Then
       Log($"Column returned: ${r.GetColumnName(0)}"$)
       Log($"User ${cmbUserName.Value} has following password: [${r.GetString(r.GetColumnName(0))}]"$)
      Else
        Log("Something fishy going on here")
      End If
   Else
      Log($"User ${cmbUserName.Value} had a blank password"$)
   End If
  Else
    Log($"Password in database for user ${cmbUserName.Value} contained a NULL value and we found a match"$)
  End If
 Else
  Log($"Could not find user ${cmbUserName.Value}!"$)
 End If
Else
 Log("txtPassword is not blank")
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Again: Untested code. This just adds code to see what the password is in the database for the given user.
 
Upvote 0
universengo

universengo

Member
AnandGupta said:
This I think is the best approach when connecting from any programming language, as I have faced similar situations in different programming languages when connecting to a database. Having a password, just 'pass' even, works smoothly.

Regards,

Anand
Click to expand...

Thanks for your suggestion! AnandGupta!
 
Upvote 0
universengo

universengo

Member
OliverA said:
Try
B4X: 
Dim count As Int
If txtPassword.Text = "" Then
 Log($"Checking if user ${cmbUserName.Value} exists"$)
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ?", Array(cmbUserName.Value))
 If count > 0 Then
  Log($"Checking if user ${cmbUserName.Value} has a NULL password in the database"$)
  count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL", Array(cmbUserName.Value))
  If count = 0 Then
   Log($"No user ${cmbUserName.Value} found with NULL password, checking for blank password"$)
   count = Main.gSQL.ExecQuerySingleResult2($"SELECT count(*) FROM Account WHERE UserName = ? AND Password = """$, Array(cmbUserName.Value))
   If count = 0 Then
      Log($"No matching password entry (neither blank, nor NULL) found for user ${cmbUserName.Value}"$)
      Log($"Determining Password value for user ${cmbUserName.Value}"$)
      Dim q As String = $"SELECT Password FROM Account WHERE UserName = "Guest""$
      Dim r as ResultSet
      r = Main.gSQL.ExecQuery(q)
      If r.NextRow Then
       Log($"Column returned: ${r.GetColumnName(0)}"$)
       Log($"User ${cmbUserName.Value} has following password: [${r.GetString(r.GetColumnName(0))}]"$)
      Else
        Log("Something fishy going on here")
      End If
   Else
      Log($"User ${cmbUserName.Value} had a blank password"$)
   End If
  Else
    Log($"Password in database for user ${cmbUserName.Value} contained a NULL value and we found a match"$)
  End If
 Else
  Log($"Could not find user ${cmbUserName.Value}!"$)
 End If
Else
 Log("txtPassword is not blank")
 count = Main.gSQL.ExecQuerySingleResult2("SELECT count(*) FROM Account WHERE UserName = ? AND Password = ?", Array(cmbUserName.Value, txtPassword.Text))
End If
If count > 0 Then
 Log("success")
Else
 Log("failed")
End If
Again: Untested code. This just adds code to see what the password is in the database for the given user.
Click to expand...

Thanks OliverA very very much!

I tried your code and this is result:

The result is still failed.

This is the log:

Checking if user Guest exists
Checking if user Guest has a NULL password in the database
No user Guest found with NULL password, checking for blank password
No matching password entry (neither blank, nor NULL) found for user Guest
Determining Password value for user Guest
Column returned: PassWord
User Guest has following password: [null]
failed
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Ok. What DB are you using, version, etc? Which driver are you using, version, etc? Looks like count(*) is not doing what we think it should be doing in regards to NULLs.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Change
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL"
to
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
 
Upvote 0
universengo

universengo

Member
OliverA said:
Change
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL"
to
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
Click to expand...

The result is Success!
You are a wonderfull man, OliverA.
Thank you very much. Wish you lots of health and success at work.:D:D:D

I think the Thread is Solved!
 
Last edited:
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Upvote 0
universengo

universengo

Member
Hello friends!
Finally, I can summarize how to fix this error (the "" or null data) as follows.
If you make data directly from Microsoft Office Access then
You use this code:
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
Else If you make data from your App then
You use this code:
B4X: 
"SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL"
I have practiced both cases and the results were successful!

Finally, Many thanks to Erel, OliverA, AnandGupta ... very much (Especially OliverA )
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Mashiane
  • Article
B4J Tutorial [Web][SithasoDaisy] PocketBase - Configuring & Adding Google Authentication to your Apps
Replies
1
Views
1K
Mashiane
Mashiane
M
  • Question
Android Question [B4X] Cast Map to Custom Type
Replies
8
Views
2K
Mike1970
M
Daestrum
  • Article
B4J Code Snippet Sneaky JavaObject
Replies
1
Views
881
stevel05
stevel05
Dianzoa
  • Question
B4J Question [ABMaterial] Change label height in Chart.
Replies
0
Views
790
Dianzoa
Dianzoa
K
  • Question
B4J Question Get text from textfield in an accordion pane
Replies
2
Views
1K
kostas_skarl
K
Top