Skip to content

Instantly share code, notes, and snippets.

@lsauer

lsauer/a.sql

Created September 4, 2012 13:32
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save lsauer/3621183 to your computer and use it in GitHub Desktop.
Save lsauer/3621183 to your computer and use it in GitHub Desktop.
Download ZIP
Analyzed: AntiSec Hackers 1 Million Apple Device ID Leak
Raw
a.sql
##free use :) lo sauer 2012; writeup: http://www.lsauer.com/2012/09/analyzed-antisec-hackers-leaked-1.html
DROP TABLE IF EXISTS `test`.`iosleak`;
CREATE TABLE `test`.`iosleak` (
`appleUUID` varchar(45) NOT NULL,
`appleToken` varchar(68) NOT NULL,
`deviceName` varchar(100) NOT NULL,
`deviceType` varchar(45) NOT NULL,
`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=latin1;
LOAD DATA INFILE '~\\Downloads\\iphonelist.txt' INTO TABLE test.iosleak
FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY '';
SELECT DISTINCT deviceType, COUNT(deviceType ) FROM iosleak i GROUP BY deviceType;
Raw
b.sql
##Doing histograms in raw SQL is awkward ->
##so we extract the data and use a flexible scripting environment e.g. PiPal@Ruby 1.9
SELECT DISTINCT deviceName INTO OUTFILE '~\\iosDevnames.txt' FROM iosleak;
##rather than using SQL as follows:
## x-axis: DISTINCT LENGTH(deviceName) ;
## y-axis: COUNT( LENGTH(deviceName) ) ;
## ...SELECT DISTINCT LENGTH(deviceName) AS x FROM iosleak i GROUP BY x;
SELECT DISTINCT LENGTH(deviceName) AS udnlength, COUNT(*) AS occurances FROM iosleak i GROUP BY len ORDER BY udnlength;
#result:
udnlength, occurances
0, 51
1, 1504
2, 2271
3, 6747
4, 15250
5, 22208
6, 67434
7, 23660
8, 21093
9, 26194
10, 37806
11, 46720
12, 69044 #max!
13, 84977
14, 91913
15, 78467
16, 67491
17, 47494
18, 37302
19, 34569
20, 32856
21, 34267
22, 35548
23, 29625
24, 24550
25, 17822
26, 11773
27, 11482
28, 5259
29, 5940
30, 2274
31, 1611
32, 1064
33, 822
34, 606
35, 414
36, 393
37, 262
38, 224
39, 150
40, 138
41, 103
42, 86
43, 102
44, 49
45, 34
46, 38
47, 29
48, 20
49, 19
50, 11
51, 16
52, 18
53, 7
54, 11
55, 8
56, 9
57, 11
58, 8
59, 4
60, 17
61, 12
62, 7
63, 7
64, 4
Raw
lendistrib.txt
▓
▓▓▓
▓▓▓▓▓ ▓▓
▓▓▓▓▓ ▓▓▓
▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
000000000011111111112222222222333333333344444444445555555555666666666677
012345678901234567890123456789012345678901234567890123456789012345678901
Raw
pipal.txt
Total entries = 567816
Total unique entries = 567463
Top 10 passwords
iPod = 3 (0.0%)
iPad 1 = 3 (0.0%)
iPhone = 3 (0.0%)
Brian's iPad 2 = 2 (0.0%)
zahra = 2 (0.0%)
Yulia = 2 (0.0%)
Yogi’s iPad = 2 (0.0%)
Yi's iPad = 2 (0.0%)
xing 的 iPad = 2 (0.0%)
Wilf’s iPad = 2 (0.0%)
Top 10 base words
iphone = 4671 (0.82%)
ipad = 783 (0.14%)
s ipad = 400 (0.07%)
s iphone = 397 (0.07%)
ipod = 392 (0.07%)
lfcs = 283 (0.05%)
s ipod = 73 (0.01%)
iphone4s = 49 (0.01%)
ipad de = 42 (0.01%)
ituned = 33 (0.01%)
Password length (length ordered)
1 = 50 (0.01%)
2 = 613 (0.11%)
3 = 3067 (0.54%)
4 = 5550 (0.98%)
5 = 9546 (1.68%)
6 = 12952 (2.28%)
7 = 12888 (2.27%)
8 = 14571 (2.57%)
9 = 17403 (3.06%)
10 = 18999 (3.35%)
11 = 23807 (4.19%)
12 = 30503 (5.37%)
13 = 33146 (5.84%)
14 = 36765 (6.47%)
15 = 36634 (6.45%)
16 = 34702 (6.11%)
17 = 30772 (5.42%)
18 = 26972 (4.75%)
19 = 28647 (5.05%)
20 = 29011 (5.11%)
21 = 30675 (5.4%)
22 = 30491 (5.37%)
23 = 27051 (4.76%)
24 = 21587 (3.8%)
25 = 16471 (2.9%)
26 = 11142 (1.96%)
27 = 7420 (1.31%)
28 = 4969 (0.88%)
29 = 3461 (0.61%)
30 = 2113 (0.37%)
31 = 1512 (0.27%)
32 = 1010 (0.18%)
33 = 743 (0.13%)
34 = 547 (0.1%)
35 = 405 (0.07%)
36 = 353 (0.06%)
37 = 252 (0.04%)
38 = 218 (0.04%)
39 = 140 (0.02%)
40 = 130 (0.02%)
41 = 158 (0.03%)
42 = 79 (0.01%)
43 = 62 (0.01%)
44 = 46 (0.01%)
45 = 30 (0.01%)
46 = 36 (0.01%)
47 = 30 (0.01%)
48 = 19 (0.0%)
49 = 17 (0.0%)
50 = 10 (0.0%)
51 = 12 (0.0%)
52 = 13 (0.0%)
53 = 5 (0.0%)
54 = 5 (0.0%)
55 = 6 (0.0%)
56 = 8 (0.0%)
57 = 12 (0.0%)
58 = 6 (0.0%)
59 = 2 (0.0%)
60 = 8 (0.0%)
61 = 5 (0.0%)
62 = 3 (0.0%)
63 = 5 (0.0%)
64 = 3 (0.0%)
65 = 3 (0.0%)
66 = 3 (0.0%)
67 = 4 (0.0%)
68 = 2 (0.0%)
69 = 2 (0.0%)
75 = 2 (0.0%)
87 = 2 (0.0%)
95 = 2 (0.0%)
Password length (count ordered)
14 = 36765 (6.47%)
15 = 36634 (6.45%)
16 = 34702 (6.11%)
13 = 33146 (5.84%)
17 = 30772 (5.42%)
21 = 30675 (5.4%)
12 = 30503 (5.37%)
22 = 30491 (5.37%)
20 = 29011 (5.11%)
19 = 28647 (5.05%)
23 = 27051 (4.76%)
18 = 26972 (4.75%)
11 = 23807 (4.19%)
24 = 21587 (3.8%)
10 = 18999 (3.35%)
9 = 17403 (3.06%)
25 = 16471 (2.9%)
8 = 14571 (2.57%)
6 = 12952 (2.28%)
7 = 12888 (2.27%)
26 = 11142 (1.96%)
5 = 9546 (1.68%)
27 = 7420 (1.31%)
4 = 5550 (0.98%)
28 = 4969 (0.88%)
29 = 3461 (0.61%)
3 = 3067 (0.54%)
30 = 2113 (0.37%)
31 = 1512 (0.27%)
32 = 1010 (0.18%)
33 = 743 (0.13%)
2 = 613 (0.11%)
34 = 547 (0.1%)
35 = 405 (0.07%)
36 = 353 (0.06%)
37 = 252 (0.04%)
38 = 218 (0.04%)
41 = 158 (0.03%)
39 = 140 (0.02%)
40 = 130 (0.02%)
42 = 79 (0.01%)
43 = 62 (0.01%)
1 = 50 (0.01%)
44 = 46 (0.01%)
46 = 36 (0.01%)
45 = 30 (0.01%)
47 = 30 (0.01%)
48 = 19 (0.0%)
49 = 17 (0.0%)
52 = 13 (0.0%)
51 = 12 (0.0%)
57 = 12 (0.0%)
50 = 10 (0.0%)
60 = 8 (0.0%)
56 = 8 (0.0%)
55 = 6 (0.0%)
58 = 6 (0.0%)
53 = 5 (0.0%)
54 = 5 (0.0%)
63 = 5 (0.0%)
61 = 5 (0.0%)
67 = 4 (0.0%)
66 = 3 (0.0%)
62 = 3 (0.0%)
64 = 3 (0.0%)
65 = 3 (0.0%)
59 = 2 (0.0%)
75 = 2 (0.0%)
68 = 2 (0.0%)
69 = 2 (0.0%)
87 = 2 (0.0%)
95 = 2 (0.0%)
||
|||
||||| ||
||||| |||
|||||||||
|||||||||
||||||||||
|||||||||||
|||||||||||||
||||||||||||||
|||||||||||||||
|||||||||||||||||
|||||||||||||||||
|||||||||||||||||||
||||||||||||||||||||
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
000000000011111111112222222222333333333344444444445555555555666666666677
012345678901234567890123456789012345678901234567890123456789012345678901
One to six characters = 31772 (5.6%)
One to eight characters = 59229 (10.43%)
More than eight characters = 508587 (89.57%)
Only lowercase alpha = 13323 (2.35%)
Only uppercase alpha = 5403 (0.95%)
Only alpha = 18726 (3.3%)
Only numeric = 0 (0.0%)
First capital last symbol = 3341 (0.59%)
First capital last number = 21058 (3.71%)
Months
january = 9 (0.0%)
march = 130 (0.02%)
april = 157 (0.03%)
may = 1097 (0.19%)
june = 137 (0.02%)
july = 38 (0.01%)
august = 164 (0.03%)
september = 5 (0.0%)
october = 6 (0.0%)
november = 3 (0.0%)
december = 4 (0.0%)
Days
monday = 9 (0.0%)
wednesday = 4 (0.0%)
thursday = 1 (0.0%)
friday = 16 (0.0%)
saturday = 1 (0.0%)
sunday = 11 (0.0%)
Months (Abreviated)
jan = 3195 (0.56%)
feb = 53 (0.01%)
mar = 17375 (3.06%)
apr = 288 (0.05%)
may = 1097 (0.19%)
jun = 1231 (0.22%)
jul = 1767 (0.31%)
aug = 733 (0.13%)
sept = 26 (0.0%)
oct = 183 (0.03%)
nov = 745 (0.13%)
dec = 337 (0.06%)
Days (Abreviated)
mon = 4863 (0.86%)
tues = 5 (0.0%)
wed = 100 (0.02%)
thurs = 22 (0.0%)
fri = 676 (0.12%)
sat = 487 (0.09%)
sun = 1221 (0.22%)
Includes years
1975 = 6 (0.0%)
1976 = 4 (0.0%)
1977 = 10 (0.0%)
1978 = 8 (0.0%)
1979 = 7 (0.0%)
1980 = 6 (0.0%)
1981 = 3 (0.0%)
1982 = 5 (0.0%)
1983 = 7 (0.0%)
1984 = 5 (0.0%)
1985 = 7 (0.0%)
1986 = 2 (0.0%)
1987 = 6 (0.0%)
1988 = 8 (0.0%)
1989 = 6 (0.0%)
1990 = 5 (0.0%)
1991 = 4 (0.0%)
1992 = 6 (0.0%)
1993 = 4 (0.0%)
1994 = 2 (0.0%)
1995 = 5 (0.0%)
1996 = 4 (0.0%)
1997 = 5 (0.0%)
1998 = 2 (0.0%)
1999 = 6 (0.0%)
2000 = 47 (0.01%)
2001 = 21 (0.0%)
2002 = 14 (0.0%)
2003 = 7 (0.0%)
2004 = 6 (0.0%)
2005 = 9 (0.0%)
2006 = 3 (0.0%)
2007 = 14 (0.0%)
2008 = 17 (0.0%)
2009 = 37 (0.01%)
2010 = 249 (0.04%)
2011 = 368 (0.06%)
2012 = 57 (0.01%)
2013 = 6 (0.0%)
2014 = 3 (0.0%)
2015 = 1 (0.0%)
2016 = 2 (0.0%)
2017 = 1 (0.0%)
2018 = 1 (0.0%)
2019 = 2 (0.0%)
2020 = 4 (0.0%)
Years (Top 10)
2011 = 368 (0.06%)
2010 = 249 (0.04%)
2012 = 57 (0.01%)
2000 = 47 (0.01%)
2009 = 37 (0.01%)
2001 = 21 (0.0%)
2008 = 17 (0.0%)
2007 = 14 (0.0%)
2002 = 14 (0.0%)
1977 = 10 (0.0%)
Single digit on the end = 21230 (3.74%)
Two digits on the end = 2913 (0.51%)
Three digits on the end = 1049 (0.18%)
Last number
0 = 1061 (0.19%)
1 = 2802 (0.49%)
2 = 13498 (2.38%)
3 = 1607 (0.28%)
4 = 5874 (1.03%)
5 = 713 (0.13%)
6 = 600 (0.11%)
7 = 703 (0.12%)
8 = 558 (0.1%)
9 = 558 (0.1%)
|
|
|
|
|
|
|
|
|
| |
| |
| |
|| |
|| |
|||||
||||||||||
0123456789
Last digit
2 = 13498 (2.38%)
4 = 5874 (1.03%)
1 = 2802 (0.49%)
3 = 1607 (0.28%)
0 = 1061 (0.19%)
5 = 713 (0.13%)
7 = 703 (0.12%)
6 = 600 (0.11%)
8 = 558 (0.1%)
9 = 558 (0.1%)
Last 2 digits (Top 10)
11 = 497 (0.09%)
01 = 374 (0.07%)
10 = 324 (0.06%)
64 = 218 (0.04%)
02 = 182 (0.03%)
12 = 168 (0.03%)
00 = 156 (0.03%)
23 = 150 (0.03%)
32 = 144 (0.03%)
13 = 135 (0.02%)
Last 3 digits (Top 10)
011 = 253 (0.04%)
010 = 160 (0.03%)
001 = 80 (0.01%)
000 = 72 (0.01%)
007 = 55 (0.01%)
012 = 49 (0.01%)
002 = 47 (0.01%)
123 = 42 (0.01%)
101 = 30 (0.01%)
009 = 30 (0.01%)
Last 4 digits (Top 10)
2011 = 237 (0.04%)
2010 = 146 (0.03%)
2012 = 43 (0.01%)
2000 = 20 (0.0%)
2009 = 15 (0.0%)
3000 = 13 (0.0%)
9000 = 11 (0.0%)
0000 = 10 (0.0%)
1111 = 9 (0.0%)
2001 = 9 (0.0%)
Last 5 digits (Top 10)
42229 = 8 (0.0%)
58811 = 5 (0.0%)
11111 = 5 (0.0%)
22222 = 3 (0.0%)
62010 = 3 (0.0%)
82836 = 3 (0.0%)
12011 = 3 (0.0%)
11003 = 3 (0.0%)
10112 = 3 (0.0%)
01017 = 3 (0.0%)
Character sets
mixedalpha: 30829 (5.43%)
loweralpha: 13323 (2.35%)
mixedalphaspecial: 6905 (1.22%)
upperalpha: 5403 (0.95%)
mixedalphanum: 2477 (0.44%)
loweralphaspecial: 2157 (0.38%)
loweralphanum: 1657 (0.29%)
mixedalphaspecialnum: 1418 (0.25%)
upperalphanum: 1114 (0.2%)
upperalphaspecial: 989 (0.17%)
upperalphaspecialnum: 782 (0.14%)
loweralphaspecialnum: 555 (0.1%)
special: 72 (0.01%)
Character set ordering
othermask: 503580 (88.69%)
allstring: 49555 (8.73%)
stringspecialstring: 7505 (1.32%)
stringdigit: 4015 (0.71%)
stringdigitstring: 1016 (0.18%)
stringspecial: 916 (0.16%)
stringspecialdigit: 820 (0.14%)
specialstringspecial: 242 (0.04%)
specialstring: 95 (0.02%)
allspecial: 72 (0.01%)
Hashcat masks (Top 10)
?u?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 5949 (1.05%)
?u?l?l?l?l?s?s?s?l?s?l?u?l?l: 5791 (1.02%)
?u?l?l?l?l?s?u?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 4803 (0.85%)
?u?l?l?l?l?l: 4314 (0.76%)
?u?l?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 4276 (0.75%)
?u?l?l?l?l: 4139 (0.73%)
?u?l?l?l?l?s?u?l?l?l?l?s?s?s?l?s?l?u?l?l: 3988 (0.7%)
?u?l?l?l?s?u?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 3939 (0.69%)
?u?l?l?l?l?s?u?l?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 3857 (0.68%)
?u?l?l?l?l?l?s?u?l?l?l?l?l?s?s?s?l?s?l?u?l?l: 3489 (0.61%)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment