9

How do I find user names with several run-on zero-width diacritics in Stack Exchange Data Explorer?

Related question about diacritics at DBA SE: How To Strip Hebrew Accent Marks

My present work-in-progress: https://data.stackexchange.com/stackoverflow/query/446513 (points to a test case in AboutMe, not in DisplayName).

Rationale: being slightly miffed at ZALGO abuse.

Zalgo in user names and effects on comments rendering

  • 1
    Well, not really ZALGO just yet but here is my quick peek at this, returning users whose first character in the display name is not English letter or a digit: data.stackexchange.com/stackoverflow/query/446516/…. – Shadow The Dragon Wizard Mar 7 '16 at 7:27
  • 1
    @ShadowWizard couldn't invent much else then this without having to resort to char lookups – rene Mar 7 '16 at 23:43
  • @rene nice! ้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้้ – Shadow The Dragon Wizard Mar 8 '16 at 7:06
8
+50

I'm not sure how to query for abuse because that would require a kind of fuzzy algorithm that can consume the vague rules about allowed usernames and the content of the database.

I assume your real question is:

How can I query the displayname of the users table that contain combining characters.

This query does that by doing a where clause with the LIKE operator for every single combining character from the UNICODE tables. That requires a somewhat lenghty where clause:

select -- top 10
       id as [User Link]
from users
where  displayname like N'%'+ nchar(0x300) + N'%' collate Latin1_General_BIN   
--  many, many, many, many more lines like that
or displayname like  N'%'+ nchar(0xfe2e) + N'%' collate Latin1_General_BIN 
or displayname like  N'%'+ nchar(0xfe2f) + N'%' collate Latin1_General_BIN 

To generate the where clause based on the tables shown in the wikipedia article I used this helper query and copied its result into the earlier mentioned main query:

-- diac1
select top 112
      'or displayname like N''%''+ nchar(0x' + Format(0x02ff+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 15
      'or displayname like  N''%''+ nchar(0x' + Format(0x1aaf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 64
      'or displayname like  N''%''+ nchar(0x' + Format(0x1dbf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 33
      'or displayname like  N''%''+ nchar(0x' + Format(0x20cf+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist
union  all    
select top 16
      'or displayname like  N''%''+ nchar(0x' + Format(0xFE1f+seq,'x') + ') + N''%'' collate Latin1_General_BIN'
      from (select row_number() over (order by id) as seq
      from votes) as seqlist

When run for Stack Oveflow, after a little patience, the result of the main query looks like this :

zalgo usernames

This should give you enough information to inspect the 103 results to judge which of those is considered abusive.

You must log in to answer this question.

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