Username not case sensitive, causing problems

New Member Joined: Jul 07, 2007 Posts: 10

I noticed that my journal was not updating the journal stats table. New posts were being written to the journal table, but the stats were not being updated in the stats table. I noticed that the user had some posts with all lower case user name and some with capitolized user name. He registered with the capitalized version so that was in the nuke_user table.

I checked this myself by looking in with my regular user name as all lower case and all upper case.

Before the journal writes the data to the stats table it checks the username variable against the username in the nuke_user table. Since the all lower case version is not an exact match it fails to write the data to the stats table.

If the user does not login with the exact case he registered with any module that checks against the nuke_user table for a match will not validate against the cookie username.

I'm not sure how to handle this. If I force the case sensitivity in the login then this will fix the problem everywhere, but would force people to know exactly what case they registered with and some of the usernames can be all over the place with case. Some users will get blocked for sure.

Does anybody know if the Journal is the only module that would be affected by this? If so I could just set the validation to "LIKE" instead of "=".

Also has this been addressed in 2.20?

Thanks,
Rick

Posted: Tue Jan 22, 2008 12:18 pm

There is an option at the bottom of the rnconfig.php to force new usernames to be lowercase. I'm not sure if this will help, but I am trying to replicate the problem you are getting.

Can you confirm your current setup? e.g. Windows/Unix, PHP version etc.

Posted: Tue Jan 22, 2008 12:21 pm

When you are talking about the stats table where exactly do you mean?

Posted: Tue Jan 22, 2008 12:30 pm

There is an option at the bottom of the rnconfig.php to force new usernames to be lowercase. I'm not sure if this will help, but I am trying to replicate the problem you are getting.

Can you confirm your current setup? e.g. Windows/Unix, PHP version etc.

Posted: Tue Jan 22, 2008 12:55 pm

I was looking at this too (besides Jakec). String comparisons within mysql are not case sensitive. I tried adding a user "Zilch" to my test system and then "zilch" and it (Your Account administration) rejects it (but I did find a duplicate constant we need to fix). My test system is 2.20.

Humm. It appears that we respect the setting forcelowercaseusername from rnconfig in Your Account/index.php but not in Your Account/admin/index.php. So an admin can create a mixed case name even if the system is set up to force lowercase. I don't know whether to consider this a bug or a feature.

I don't see any fixes to the journal module for 2.20 that explicitly address this issue. I think the best solution might be to go thru phpmyadmin and make all the usernames lowercase and set the rnconfig value so any new ones are created as lowercase and when you are in admin and create one create it as lowercase.

Posted: Tue Jan 22, 2008 1:46 pm

Thanks for taking a look at this.

Specifically I was talking about the nuke_journal_stats table not getting updated.

I haven't backtraced this all the way through yet, but I see that line 104 in Journal/savenew.php tries to update the table. The query string says;

Code:

$query = "UPDATE ".$prefix."_journal_stats SET nop='$nnnop', ldp='$ndate', ltp='$mtime' micro='$micro' WHERE joid='$username'";

I'm using php 5 and mysql 5 on the installation in question. The reason I mention this is I found this problem because the journal module was working fine on another server with php 4 and mysql 4. This may only involve newer version of php and MySql.

By the way both installations are RN 2.10.01.

I should mention that I've also gotten reports of people not able to logout. I suspect that this is related to the username case issue too.

My vote would be to ignor case for the user name in all situations. Resetting all of the usernames to lower case and reseting them would not be an option. This site has been active for years as a nuke site. I just went to RN and love it.

Posted: Tue Jan 22, 2008 2:04 pm

It looks like the variable $username is derived from the getuserinfo function in mainfile which in turn does a cookiedecode. We'd really need to trace this thru and isolate where the correct value is not being returned. I was looking in the MYSQL 5 manual earlier and it says that case would not be a factor in a comparison like this (joid = $username). I will log this in as an issue to be looked at for the release after 2.20, I'm not sure we can resolve it in time for this release unless one of the gurus sees something obvious.

Sorry but Nuke is riddled with bugs like this and we can't get rid of them all at once.

Posted: Tue Jan 22, 2008 2:16 pm

I can't replicate this. I created a username "Zilsch" with a capital Z in it. It is stored that way in the user table. I logged in to that user and created a journal entry. I monitored thru phpmyadmin and the journal stats table was updated. Then I added another journal entry. Again the table was updated.

Maybe we can just wait and see if you experience the same thing in 2.20 when it comes out and if you update. There will be a SQL logging facility in there that we can use if you still have problems ... we'll be able to see exactly what SQL is getting executed.

Posted: Tue Jan 22, 2008 2:43 pm

No problem. I just wanted to bring the issue to light. I am using the user block to login for testing. I'm not sure if this would be different. I haven't tried it with the Your Account login form yet.

I should give a little history on how I found this, because it might shed some light on it. I'm not a php wiz, but don't mind messing around with something that is broken to see if I can fix it.

There must be some slight differences in php 4 and 5. As I mentioned the 4 installation was working fine and the 5 had the issue. The problem came to my attention because the editor for the php 5 site said the journal wasn't displaying correctly. The Last20 active wasn't accurate. Obviousl this was due to the journal_stats table not updating.

While I was in there I clicked on the list all journals link and it didn't display them alphabetically, instead it just showed the same broken results as the last20.

In looking at the code in Journal/index.php on line 118 I saw this query for "function all" that displays all of the journals.

Code:

   $sql = "SELECT j.id, j.joid, j.nop, j.ldp, j.ltp, j.micro, u.user_id FROM ".$prefix."_journal_stats j, ".$user_prefix."_users u where u.username=j.joid ORDER BY 'joid'";

I removed the single quotes from 'joid' like this:

Code:

$sql = "SELECT j.id, j.joid, j.nop, j.ldp, j.ltp, j.micro, u.user_id FROM ".$prefix."_journal_stats j, ".$user_prefix."_users u where u.username=j.joid ORDER BY joid";

Now it shows all of the journal alphabetically.

Posted: Tue Jan 22, 2008 3:10 pm

That's a good catch -- removing the quotes. I just double checked and that issue has been fixed for RN2.20.

Posted: Tue Jan 22, 2008 3:50 pm

I've been having trouble replicating this also on RN 2.10.01 and using PHP 5.

May be I am missing something? Confused

Posted: Tue Jan 22, 2008 7:45 pm

I'm still checking this and now I see that my journal_stats table is not updating on php 5 site or the php 4 site.

I logged in with proper case, same as user table.

I've never posted in the journal as this user.
I then Made a post in the journal.
My post was added to the nuke_journal table and my post info was added to the nuke_journal_stats table with nop (number of post) set to one.
I went back and added a second post to the journal still logged in as the correct case for username. This time the journal post was added to nuke_journal table, but the nuke_journal_stats table did not update. Number of posts still showed 1 while there were two entries in the nuke_journal table.

The journal on the site doesn't get used that much so I think I will just wait until ver 2.20 comes out. This could be caused by too many things including the fact I use IE7 to my php.ini setup.

I may poke at it some more. If I find anything I will report back.

Thanks for all of the help. The work that you are doing on RN is greatly appreciated. To state that nuke is full of bugs is a gross understatement in my opinion. It's a process, I know.