B4J Question Helping about null data

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:

Erel

Administrator
Staff member
Licensed 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
 

universengo

Member
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
Thanks Erel,
Are you sure that the field is stored as null in the database?
I am sure. See my pic.
 

Attachments

universengo

Member
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
I use your code but result is still failed
 

OliverA

Expert
Licensed 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
 

universengo

Member
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
Thanks OliverA, The result is failed, too.
I choose cmbUserName.Value = "Guest" and txtPassword.text = "" but Count = 0.
 
Last edited:

OliverA

Expert
Licensed 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.
 

universengo

Member
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.
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:

universengo

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

AnandGupta

Well-Known Member
Licensed User
If no one has any other solution, I think the best solution is to not allow password = "" or null to be the best.
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
 

OliverA

Expert
Licensed 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.
 

universengo

Member
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
Thanks for your suggestion! AnandGupta!
 

universengo

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

OliverA

Expert
Licensed 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.
 

universengo

Member
Change
B4X:
"SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL"
to
B4X:
"SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
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:

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