Manual talk:User table

security

user_name being binary (probably in an effort to support multi-language) treats names in case-sensitive format. Thus, if there exists a trusted user 'Admin', and if a new user maliciously attempts to register as 'AdmiN', the user is most likely allowed to register with that username. I'm not quite too sure what consequences this will be other then possibly fooling few admins when browsing through the "Recent Changes" section and mistake a trusted user changes from somebody else... Any suggestions?

See bugzilla:2290 in general. --brion 23:51, 22 Jun 2005 (UTC)

I've created a user "Brion VIBBEr" thinking this homograph feature was already there, please delete that user.

user_touched column

Is user_touched a record of the last time a person logged in or the last they made a change? I don't think it's obvious - could someone fill it in? - Mpnolan 07:32, 26 March 2006 (UTC)

yes that will be nice... if anyone knows that...--84.58.212.214 18:45, 12 April 2006 (UTC)

in version 1.10, this is the date and time of the last change to any page on the wiki made by a particular user, i.e. the last time a user "touched" (modified) the content of the site, including user pages and user preferences. --74.167.38.9 15:46, 8 June 2007 (UTC)

Investigations have revealed

An example content of user_touched (in my wiki DB) is 20060806161207. This is a standard date format: YYYYMMDDHHMMSS and combining this with the title suggests the data is a "last visited" or "last edited" value. Going in and out of my wiki does not change this value, so I suggest that it's a "Last made an edit" value.--DeadMeatGF 17:03, 6 August 2006 (UTC)

Update

I've made an edit and checked again, but it's not changed - so it's not a "Last Edit" or a "Last Visit" value.--DeadMeatGF 17:04, 6 August 2006 (UTC)

There is some info in docs/user.txt and in maintenance/archives/patch-cache.sql Natmaka 17:15, 19 August 2006 (UTC)

Update

The value is updated whenever you change user preferences. Poupouille 14:45, 3 November 2006 (UTC)

http://mail.wikipedia.org/pipermail/mediawiki-l/2005-September/007216.html --217.230.179.4 20:51, 3 January 2007 (UTC)

deleting users

Can users be deleted without corrupting the database or whatever? -Eep 07:25, 31 May 2006 (UTC)

Answer: Seems like it, yes. If the user had made edits to the site, references to the user will be a red (missing) link.

user_real_name

The content page says use in mediawiki unknown; however, I'd like to point out that from an administrator standpoint, it's sure nice to be able to look up who users actually are (or say they are). Especially on smaller wikis where people are unlikely to lie and you're more likely to care who everyone is. -Semisomna 10:11, 6 November 2006 (UTC)

Added it today --74.167.38.9 15:31, 8 June 2007 (UTC)

Support for Custom User Attributes?

Does the user_options blob support custom attributes for users?

I am operating an industry association wiki (using MediaWiki of course), and it would be nice to be able to define other user attributes (such as the company the user is from) and display them on the Special:Userlist.

How would I go about doing this?

user_token

I'm just wondering what the user_token is used for any how is it calculated.. --60.241.201.106 13:13, 5 April 2008 (UTC)

So do I. Someone know it ? Azmeuk 09:32, 8 December 2008 (UTC)

Now documented in the article. —Emufarmers^(T|C) 11:13, 8 December 2008 (UTC)

user_password column

Hey all, it's not documented in the main article, but the current format of the user_password column is as follows. This all applies to version 1.13.1 of MediaWiki.

Edit: I've added it to the main article, and updated the table for MW 1.13. (Note the datatype changes in the user_email and user_real_name columns.) I honestly don't know when these changes went in and I'm too lazy to look it up, but at least the current version is correct. --TonyV 16:43, 21 November 2008 (UTC)

"B" type password (current default)

The user_password column contains the following components:

• A colon and the capital letter B, followed by
• A colon and a pseudo-random eight-digit hex salt, followed by
• A colon and the md5 hash of a concatenation of:
  • The eight-digit hex salt,
  • A dash ("-"), and
  • The md5 hash of the password

So, for example, if a user's password is "password," the md5 hash of it is 5f4dcc3b5aa765d61d8327deb882cf99. Let's say that the pseudo-random eight-digit hex salt generated when the password column is created is 838c83e1. The value stored in the password column is:

':B:838c83e1:' . md5('838c83e1-' . md5('password')) =
':B:838c83e1:' . md5('838c83e1-5f4dcc3b5aa765d61d8327deb882cf99') =
':B:838c83e1:e4ab7024509eef084cdabd03d8b2972c'

Why all the trouble in creating a salt and hashing the password twice? Because simply encoding it in md5 makes it vulnerable to discovery. For example, if someone managed to get the user database of a wiki and one or more of the users were actually dumb enough to set their password to "password," a simple Google search of the hash ("5f4dcc3b5aa765d61d8327deb882cf99") would turn up the password pretty easily, and in the wild, there are vast searchable databases of cracked passwords and their md5 hashes specifically for this purpose. On the other hand, Googling the double salted md5 hash with a random component ("e4ab7024509eef084cdabd03d8b2972c") turns up exactly zero results. (Or probably one, by the time you read this, if Google indexes this page.)

I don't know when, but at some point, I think that instead of using a pseudo-random salt, the user's id was used. Now, though, it uses a pseudo-random number between 0 and 0x7fffffff (2,147,483,647 decimal), inclusive.

"A" type password

The user_password column contains the following components:

• A colon and the capital letter A, followed by
• A colon and the md5 hash of the password

So in the above example, where the user's password is "password" (md5 hash "5f4dcc3b5aa765d61d8327deb882cf99"), the password column will contain:

':A:5f4dcc3b5aa765d61d8327deb882cf99'

While not as secure, this allows one to, for example, more easily write bridges for other software that validate against MediaWiki's database, since many other software packages use md5 password hashes as the format in which they store passwords.

The default "B" type password storage mechanism can be overridden by putting the following line in LocalSettings.php:

$wgPasswordSalt = false

Getting user_email

I have to urgently contact one of the users on my wiki by email, however when I go into the database it only says [BLOB - 22 B]. How do I extract the email? Thanks --Bluesoju 15:27, 13 September 2010 (UTC)

User rename

Should we add a section explaining what happens in the database when a user name is changed? Milimetric (WMF) (talk) 13:55, 11 May 2016 (UTC)

Why? Does something happen that is counterintuitive or different from what stated in the page? --Nemo 16:57, 11 May 2016 (UTC)

Auto-authenticate email addresses

This will authenticate the email addresses of every user on the wiki who has entered an address but not confirmed it.

$dbr = wfGetDB( DB_SLAVE );
$res = $dbr->select(
        'user',
        array( 'user_id', 'user_email', 'user_email_authenticated', 'user_registration' ),
        array(
              	'user_email_authenticated' => NULL,
                "user_email<>''",
        )
);
$dbw = wfGetDB( DB_MASTER );
foreach ( $res as $row ) {
        $dbw->update(
                'user',
                array( 'user_email_authenticated' => $row->user_registration ),
                array( 'user_id' => $row->user_id )
        );
}

Let me tell you hwhat (talk) 02:15, 22 September 2016 (UTC)

Typo?

Current text says "And finally the base64 encoded result of the hashing algorithm (that had a (default) length of 128 byte before base64 encoding). The hash of the plain text user password." Should that be 128 bits? Also it would be good to mention current length of salt.--ArnoldReinhold (talk) 23:18, 27 November 2018 (UTC)

Blocked user

I think the page should explain how a user record changes when they are blocked by an admin --Luca Mauri (talk) 20:18, 13 October 2019 (UTC)

user_id description

The current description of user_id is "user_id is the primary key, used to uniquely identify a user." I think it better to be explicit that the key is primary within the respective wiki_db, but not globally. Although it comes without saying for any table, that a primary key is unique within a database, but this can be a special case, because of the way we interpret a "user" in Wikimedia, to be global rather specific wikis. I think anything that clarifies a single user can have different ids on different wikis will be helpful. KCVelaga (WMF) (talk) 12:19, 27 July 2022 (UTC)