Opened 10 years ago

Closed 6 years ago

Last modified 6 years ago

#565 closed bug (wontfix)

Synchronize user table (MyTB) and members table (BW), and make login more reliable.

Reported by: lemon-head Owned by:
Priority: major Milestone: unassigned
Component: BW Database Keywords:
Cc:

Description

If you have a look at

you will see that the user and members tables are totally out of sync.

  • Usually, two records in members and user should be associated with each other if they have the same username.
  • Sometimes our software uses the id instead, which is destined to break.
  • The old login has a function MOD_bw_user_Auth::updateUser(), which tries to repair the table structure with every login - not a good place to do this, if you ask me.
  • for many bw members records there is no tb user with the same username
  • for some tb users there is no bw member with the same username
  • a lot of tb users have empty username (tb "handle" is not set as unique)
  • etc

Conclusion: It's a total mess, and noone really knows how it is supposed to be.

From the updateUser function (a bit modified, to make it more readable)

    protected function updateUser($handle, $password)
    {
        $Auth = new MOD_user_Auth;
        
        $esc_handle = $this->dao->escape($handle);
        $esc_pwenc = $this->dao->escape(MOD_user::passwordEncrypt($password));
        $member_id = $_SESSION['IdMember'];
        $int_authId = (int)($Auth->checkAuth('defaultUser'));
        
        if ($this->dao->exec(
            "
UPDATE
    user
SET
    auth_id = $int_authId,
    pw      = '$esc_pwenc'
WHERE
    handle = '$esc_handle'
            "
        )) {
            // cool
        } else if ($this->dao->query(
            "
REPLACE INTO
    user
    (id        , auth_id    , handle       , email, pw          , active)
VALUES
    ($member_id, $int_authId, '$esc_handle', ''   , '$esc_pwenc', 1     )
            "
        )) {
            // cool again?
        } else {
            // not so cool?
        }
    }

see also

Very critical about this piece of code:
It tries to create a user record with the same id as the members record had. What if the id already exists?? This will make a mess!!!


The fact that we have two tables to keep in sync is nasty, but it is a quite common problem with common solutions. If done right, the only thing we need to be afraid of is some redundancy in the code.

Change History (5)

comment:1 Changed 10 years ago by lemon-head

One thing we should find out is which tables have foreign keys pointing to the tb user table.

comment:2 follow-up: Changed 10 years ago by lemon-head

And then, if any records inside these tables point to orphan tb users - which means, tb user records with empty username (="handle" in tb), or with a username that does not exist in members table.

If any such records are found in the live db, we have to find a solution for them.

comment:3 in reply to: ↑ 2 Changed 6 years ago by TimLoal

Replying to [lemon-head]: Is there any need for this ticket, any more? If not cant it be closed?

comment:4 Changed 6 years ago by TimLoal

  • Milestone changed from Future to 1.0
  • Resolution set to wontfix
  • Status changed from new to closed

Closing due to age.

LnP

comment:5 Changed 6 years ago by globetrotter_tt

  • Milestone changed from 1.0 to unassigned
Note: See TracTickets for help on using tickets.