Profile spammers Stack Apps

1

Please login or register to vote for this query.

(click on this box to dismiss)

without valid accounts on other sites

Stack Overflow

Q&A for professional and enthusiast programmers

create table #validaccounts(accountid int primary key)

select accountid 
into #users
from users
where reputation = 1
and creationdate >= '##year##-01-01'
AND datediff(hour, Creationdate, Lastaccessdate) < 504
and not exists(select 1 from posts where owneruserid = users.id)
and not exists(select 1 from badges where userid = users.id and name <> 'Autobiographer')
and not exists(select 1 from votes where userid = users.id)
and users.accountid not in (7585603)


declare @sql nvarchar(max)
select @sql = concat(N'insert into #validaccounts 
select distinct accountid 
from (',
STRING_AGG(concat(N'
select #users.accountid 
from [', convert(nvarchar(max),name), N'].dbo.users uo
inner join #users on #users.accountid = uo.accountid
where uo.reputation > 1
or uo.upvotes > 0
or uo.downvotes > 0 
-- or exists (select 1 from badges where userid = uo.id and name <>''Autobiographer'')
' ) ,N' union all ')
,N' ) data')
from sys.databases
where database_id > 5
and not name = db_name()
and (name not like '%.Meta' or name = 'StackExchange.Meta')

exec(@sql)

delete from #users
where accountid in (select accountid from #validaccounts)

SELECT    U.Id                AS [User Link]
        , U.Displayname       AS 'Display'
        , U.Location          AS 'Location'
        , U.WebsiteUrl        AS 'URL'
        , U.Creationdate      AS 'Created'
        , U.Lastaccessdate    AS 'Accessed'
        , datediff(hour, U.Creationdate, U.Lastaccessdate) AS 'Duration'
        , U.Aboutme           AS 'About Me'

FROM users U
inner join #users au on au.accountid = u.accountid

WHERE 
       U.Aboutme LIKE '%[^i]phone:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%number:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%hotline:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[^0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%'
    OR U.Aboutme LIKE '%[^0-9][0-9][0-9] [0-9][0-9] [0-9][0-9]%'
    OR U.Aboutme LIKE '%keyword%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%searches:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%tags:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%category:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%description:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%address:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%location:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%email:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%web:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%website:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '% our website %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%hours:%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%HOUR%'
    OR U.Aboutme LIKE '%24[x-]7%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]escort%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]sexy%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%call girl' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]massag%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '% masaje%'
    OR U.Aboutme LIKE '%[> ]e[xr]otic%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]orgasm%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%sex toy%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%sextoy%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%sensual%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]prostitut%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%companion%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]drug%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]dispensar%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]pharmacy%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]pill%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]penis%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]cannabis%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%silkroad%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%cocaine%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%heroine%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]edible%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]thuốc%'
    OR U.Aboutme LIKE '%[> ]slot[s ]%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]poker%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%jackpot%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]lottery%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]lotto%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]prize%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]gambl%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]taruhan%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]judi%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]pejudi%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]togel%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]taruhan%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%permainan%'
    OR U.Aboutme LIKE '%memainkan%'
    OR U.Aboutme LIKE '%memenangkan%'
    OR U.Aboutme LIKE '%penjualan%'
    OR U.Aboutme LIKE '%[> ]obat %'
    OR U.Aboutme LIKE '%casino%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[Kk]emenangan%'
    OR U.Aboutme LIKE '%airline%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[Gg]ratis%'
    OR U.Aboutme LIKE '%[> ][Hh]arga%'
    OR U.Aboutme LIKE '%[> ][Nn]ikah%'
    OR U.Aboutme LIKE '%[> ]we %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]our %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]us %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]they %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]their %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ][Kk]ami %'
    OR U.Aboutme LIKE '%[> ][Kk]alian%'
    OR U.Aboutme LIKE '%[> ][Bb]ạn%'
    OR U.Aboutme LIKE '%[> ][Nn]osotros%'
    OR U.Aboutme LIKE '%[> ][Tt]hao%'
    OR U.Aboutme LIKE '%[> ][Cc]húng [Tt]ôi%'
    OR U.Aboutme LIKE '%[> ][Cc]húng [Tt]a%'
    OR U.Aboutme LIKE '%<a href="%' COLLATE Latin1_General_100_CI_AI
    OR U.Location LIKE '[0-9]%,%[0-9][0-9][0-9][0-9][0-9]%'
    OR U.WebsiteUrl LIKE '%[0-9]%.[0-9]%.[0-9]%.[0-9]%'
    OR U.WebsiteUrl LIKE '%business.site%' COLLATE Latin1_General_100_CI_AI
    OR U.WebsiteUrl LIKE '%goo.go/maps/%' COLLATE Latin1_General_100_CI_AI
    OR U.WebsiteUrl LIKE '%yelp.com%' COLLATE Latin1_General_100_CI_AI
    OR U.Displayname LIKE '%spam%' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%[> ]spam %' COLLATE Latin1_General_100_CI_AI
    OR U.Aboutme LIKE '%#spam%' COLLATE Latin1_General_100_CI_AI
    OR U.WebsiteUrl LIKE '%#spam%' COLLATE Latin1_General_100_CI_AI
  
  

ORDER BY Creationdate desc

select '##cachebreaker##'

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results