How to handle empty (null-value) members_pass_salt fields

[Bonaparte]

I am currently working on getting a self-hosted Invision Community upgraded from 4.3.6 to the latest 4.7.19 on our test environment - everything is working fine so far and I was able to successfully upgrade to the latest 4.7.19, but I identified plenty of NULL valued `members_pass_salt` fields (~33k of ~180k users in `ibf_core_members`, Query was "SELECT count(*) from `ibf_core_members` WHERE `members_pass_salt` IS NULL;"). I checked the `joined` columns earlierst and latest dates of the affected lines and those are spread between 2004-2024 (but the latest member with issue was still created under 4.3.6 in our production environment). The self-hosted Invision platform is 20 years old, so I think they came from a Invision Community v3 - just mentioning that I found some similar, but helpful topics as I searched before posting.

 

As my (obvisously AdminCP enabled) account is "luckily" also affected, I tried (in the upgraded 4.7.19) with the password-reset functionality to set me a new password in the hope of the function setting a password hash aswell if empty - but with no luck.

 

Questions:

1. Is there a way to bulk fix from Invision AdminCP?
2. If 1. is not possible, what would be needed to fix the issue to have all `members_pass_salt` populated?

My solution approach in mind would be:

1. Retrieve the list of members being affected for later notification

   SELECT `name`,`email`,`members_pass_salt` FROM `ibf_core_members` WHERE `members_pass_salt` IS NULL;

    

2. Update all rows with NULLed fields:

   -- UPDATE Statement to set all found rows with NULL valued `members_pass_salt` fields to strong 22 char long salt values:
   
   UPDATE `ibf_core_members`
   SET `members_pass_salt` = (
       SELECT GROUP_CONCAT(
           CHAR(
               CASE 
                   WHEN RAND() < 0.7 THEN FLOOR(48 + (RAND() * 74)) -- Alphanumeric (48–122)
                   ELSE FLOOR(33 + (RAND() * 15)) -- Special chars (33–47)
               END
           )
           ORDER BY RAND() SEPARATOR ''
       )
       FROM (
           SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL 
           SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL 
           SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL 
           SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL 
           SELECT 21 UNION ALL SELECT 22
       ) t
   )
   WHERE `members_pass_salt` IS NULL;

   Examples of the salts:

   A1bC!3dE$fG%hI2jK@lM^n
   xY#9z8&7w!6vU*5tR^3q1p
   @mZ2d#5f6g$7k!9n3o^4L8
   r1t^u2v3w$x5y6z7!A#9B8C

3. Send the affected users a notification via Email for informing them of the need to change their password to be able to login again.

Would that work as a fix? I am open to any recommendations for improvements / hints / knowledge.

Also I hope I did search well enough before creating my thread here.

Best regards.

 

Edited December 29, 2024 by Bonaparte

[teraßyte]

What is the issue exactly? Are you getting some errors because of the NULL value?

 

If I remember correctly, the NULL value indicates that the member logged in through some other method (Facebook, Google, Microsoft, Twitter, etc) rather than a local email/password account.

[Bonaparte]

No errors, but by design this may be bad for the affected peoples' credentials (under some worst case preassumptions: Users using the same credentials for other sites) if the site would be hacked & attackers could re-use the credentials.

I am aware of re-using the same credentials over multiple sites is a bad practice of users but as the provider of the platform I would like to assure the safety for user's data (I am also aware of MD5 hashes could be de-hashed with rainbow tables when the salts would be retrieved by the attackers.).

 

Edit: Just default Auth Method enabled, as I just joined later, I can't tell if other Auth backends have been enabled in the past. Anyways I would like to bring it into the right state.

Edited December 29, 2024 by Bonaparte

[Jim M]

We do not explicitly set a password salt for the standard login provider. We utilize the default method for password_hash() in PHP. This utilizes the PASSWORD_BCRYPT method which automatically sets a salt in its hash.

The column you're seeing in the database is likely from version 3 when we did need to set a salt.

[Bonaparte]

Ok, so the column is deprecated and can be ignored when the default backend is being used?

Thanks for clarification!

Edited December 29, 2024 by Bonaparte

[Jim M]

5 minutes ago, Bonaparte said:

so the column is deprecated and can be ignored when the default backend is being used?

Yes, it can be ignored. 

[Bonaparte]

Thank you very much  - the topic can be put as solved / closed then from my side.