33

Environment

I'm logging in using the OpenID from http://openid.stackexchange.com and using Chrome 31.0.1650.57 m if that matters.

Steps to reproduce:

Log in to Data Explorer and compose a query, for example, like:

DECLARE @MyUserID INT = ##UserID:int##

SELECT TOP 1
    Id AS [Post Link],
    Score AS [Score],
    CreationDate AS [Creation Date]
FROM
    Posts
WHERE
    OwnerUserId = @MyUserID AND
    PostTypeId IN (2, 3)
ORDER BY
    CreationDate
DESC

Then press the Run Query button and see if the UserID field is filled with your own user ID like shown in the below screenshot:

Enter image description here

Question:

Did something changed on the ##UserID## parameter (for example, that it's not supported anymore)?

  • If so, then there is an outdated help paragraph about it (What's the point of logging in).
  • If not, it is a bug which is there about three days (I've been waiting since I noticed that you were going to do some changes to Data Explorer, but it remains there, hence my question).
24
  • works for me in IE11
    – rene
    Commented Dec 2, 2013 at 12:13
  • works for me in Chrome as well... sorry...
    – rene
    Commented Dec 2, 2013 at 12:19
  • Are you sure you are logged in? ie, your name is in the right top of your screen, not an login link?
    – rene
    Commented Dec 2, 2013 at 12:21
  • @rene, I'm logged in for sure. Now I've cleared also the whole history data (all of the types). No change... I'm using Data Explorer quite frequently and I noticed this about 3 days ago on 2 computers...
    – TLama
    Commented Dec 2, 2013 at 12:24
  • 1
    I think it's related to OpenID provider. With this account I'm using Google as my provider and it's indeed not working, field not populated. My loyal sock is using Stack Exchange OpenID and it's working. @rene can you confirm too? (i.e. what is your OpenID provider) Commented Dec 2, 2013 at 12:27
  • Hmmm, I'm using Google as OpenId provider and it is working @ShaWizDowArd
    – rene
    Commented Dec 2, 2013 at 12:33
  • @ShaWizDowArd, if we are talking about OpenID, then I'm using this one (I hope I got the point where to find this information).
    – TLama
    Commented Dec 2, 2013 at 12:36
  • @rene and TLama yeah, this pretty much nullifies my assumption. Oh well, hope a dev or Tim Stone will come for the rescue. Commented Dec 2, 2013 at 12:37
  • 1
    Can we blame this on caching? Maybe it depends on which side of the ocean you are? I'm on the good side...
    – rene
    Commented Dec 2, 2013 at 12:42
  • @rene nope, it's one of the rare occasions when cache is not to blame... ;) Commented Dec 2, 2013 at 12:53
  • It is, @TLama - neither login credentials nor account information are shared between data.SE and the rest of the network, so this was implemented by assuming that email hashes would be identical between them... However, as ShaWizDowArd's answer attests, this is becoming less and less of a valid assumption due to privacy concerns surrounding the use of Gravatar. It might be possible to reimplement this using the API to authenticate, but... That's probably overkill.
    – Shog9
    Commented Dec 3, 2013 at 1:21
  • @Shog9, ok, but isn't there any way to reset those EmailHash values to force them to refresh ?
    – TLama
    Commented Dec 3, 2013 at 1:24
  • Yes, @TLama - as Sha describes in his answer, if you manually set your avatar to Gravatar it should work. However, this should still be considered an implementation detail at this point and likely to break without notice. (I've updated Sha's answer to clarify all of this)
    – Shog9
    Commented Dec 3, 2013 at 1:25
  • @Shog9 API integration is actually on my todo list as a nice optional feature, especially if I can convince Nick to put network account IDs in the Users table too.
    – Tim Stone
    Commented Dec 3, 2013 at 2:15
  • 1
    @Shog9 I don't understand, Gravatar has nothing to do with this; that was just poor design choice, it's completely unnecessary for this function. You have my openId identity, which you have mapped to my userId in the database. It should be a simple matter to make a single call to retrieve that value and fill that in. There would be no way to use this to "spam users".
    – BlueRaja
    Commented May 15, 2014 at 15:13

2 Answers 2

32

OK, I cracked it.

The bottom line is it's because Stack Exchange changed the Gravatar hashing algorithm recently, see here:

A fairly recent article on ArsTachnica, regarding how using Gravatars can leak an email address has prompted us to make some changes to how we produce the gravatar hash (under certain circumstances).

And the code to find the UserID per site is: (source)

string hash = Util.GravatarHash(user.Email);
try
{
    return cnn.Query<int?>("select top 1 Id from Users where EmailHash = @hash order by Reputation desc", new {hash}).FirstOrDefault();
}
catch
{
    // Allow this to fail, its not critical
}

Which means Data Explorer is looking for hash that was changed. It didn't affect all users (see "under certain circumstances" in Oded's reply).

Note: at some point after writing this answer, SE removed the hash from the database and blanked out all existing Gravatar hashes, i.e. the value of EmailHash in Users table is always null now. This means there's no way to get the user id parameter auto populated any longer.

12
  • 3
    WIZARDRY!​​​​​​​​​​​​​​​ Commented Dec 2, 2013 at 14:40
  • @Shog thanks for editing! Any alternative to find UserID then, so that Data Explorer won't stay in the dark? Commented Dec 3, 2013 at 7:57
  • 3
    I'm affected and I've had a gravatar profile picture for years (so I can't change to using gravatar). Is there another workaround?
    – Bohemian
    Commented Jan 27, 2014 at 12:23
  • 1
    @Bohemian try changing to something else then back to Gravatar, hopefully it will "reset" the hash and next Monday (today if you're quick enough) it will be fine. Commented Jan 27, 2014 at 12:33
  • @ShadowWizard: I just hit this issue and none of the listed workarounds have fixed the problem. Any other ideas?
    – MrHen
    Commented Mar 31, 2014 at 14:11
  • @MrHen sorry, not really. Commented Mar 31, 2014 at 14:17
  • For me it stopped to work as well. I'll resolve this by modifying all the queries only for my private use by hardcocing my user ID. Not so cool, but I'm using that tool pretty frequently. You can do it too, @MrHen.
    – TLama
    Commented Mar 31, 2014 at 14:56
  • Maybe they changed the hash again... or ditched it as Shog warned. Commented Mar 31, 2014 at 14:59
  • 2
    That's why there are tons of duplicate queries with different user ids on data.SE
    – user692942
    Commented May 24, 2014 at 16:23
  • 1
    @Lankymart yep, good point! :/ Commented May 24, 2014 at 17:12
  • I am already using gravatar as my profile image but user id autofilling feature is still not working
    – Abdul Rauf
    Commented May 16, 2019 at 5:06
  • 1
    @AbdulRauf what I wrote in the last paragraph (before editing now) came true, they removed it. Edited the answer to be up to date. Commented May 16, 2019 at 7:34
1

I've written a small userscript, SEDE User ID Autofiller, to restore this functionality for users (like me) where the standard functionality doesn't work. As a bonus, it will autofill ##AccountId## parameters with your network account ID for cross-site queries.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .