B4J Question Helping about null data

Discussion in 'B4J Questions' started by universengo, Jul 26, 2019.

  1. 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:
    Code:
    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: Jul 26, 2019
  2. Erel

    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?
    Code:
    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 likes this.
  3. universengo

    universengo Member

    Thanks Erel,
    I am sure. See my pic.
     

    Attached Files:

  4. universengo

    universengo Member

    I use your code but result is still failed
     
  5. OliverA

    OliverA Expert Licensed User

    Try
    Code:
    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
     
  6. universengo

    universengo Member

    Thanks OliverA, The result is failed, too.
    I choose cmbUserName.Value = "Guest" and txtPassword.text = "" but Count = 0.
     
    Last edited: Jul 28, 2019
  7. OliverA

    OliverA Expert Licensed User

    Let's do some logging:
    Code:
    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 likes this.
  8. universengo

    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.
     
    Last edited: Jul 29, 2019
  9. universengo

    universengo Member

    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.
     
  10. AnandGupta

    AnandGupta Active Member Licensed User

    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
     
  11. OliverA

    OliverA Expert Licensed User

    Try
    Code:
    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.
     
    Erel and universengo like this.
  12. universengo

    universengo Member

    Thanks for your suggestion! AnandGupta!
     
  13. universengo

    universengo Member

    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
     
  14. OliverA

    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 likes this.
  15. OliverA

    OliverA Expert Licensed User

    Change
    Code:
    "SELECT count(*) FROM Account WHERE UserName = ? AND Password = NULL"
    to
    Code:
    "SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
     
    AnandGupta and universengo like this.
  16. universengo

    universengo Member

    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: Jul 30, 2019
    OliverA likes this.
  17. OliverA

    OliverA Expert Licensed User

    universengo likes this.
  18. universengo

    universengo Member

  19. 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:
    Code:
    "SELECT count(*) FROM Account WHERE UserName = ? AND Password IS NULL"
    Else If you make data from your App then
    You use this code:
    Code:
    "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 )
     
    AnandGupta likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice