Wednesday, February 27, 2013

Recovering auto-pruned posts in PHPbb forums...from SQL file backups, instead of the internal PHPbb tool

I've been using PHPbb since V2 It's an excellent tool if you want to create a standard forum without a fuss in record time. I recon that I«ve been off for a while since V2 , but recently i had the need to create 2 sites that, according to specs where just simple forums, so I returned to PHPbb and found my self on V3.
During the rush of putting the sites up A.S.A.P /BTW with install, full config, sample posts, creating emails invite emails, changing the email templates and code to include registration custom fields and template design and implementation of template design with google adds code embed, it took-me a total of 2 hours to get everything done. OOhh and it's 2 hours for the 2 forums, not 2 hours each! Not you have to respect that. Free code that saves you countless ours of coding and templating and configuring time. And mind that I was "rusty" (I didn't PHPbb since V2 and a lot has changed). This, is productivity that HAS to be appreciated: THANKS PHPbb team.

I decided to post this article after finding some misleading information on the PHPbb forums stating (and I quote): "to script to do that automatically has been created yet", "it's just too complex, better restore the entire database".

OK, you have instructions on how to restore and backup de database using PHPbb internal tools (makes sense as it IS the supported feature) but let's just assume that, just like me, you have much more to deal with and you configure your servers to backup automatically using cpanel, for instance.
That means that you will not be using the internal PHPbb backup tool and as a result you'll be able to restore or backup at MySQL (or your db of choice) files level. That is also a good way to work right until the point where you find out that you left "autoPrune = yes" on one of your forums.
You see Auto  Prune is a nice admin tool. Let's just say for instance that you create a forum for improvement requests... you want to have people writing there, but you don't want those posts to stay there for ever. They just loose sense when either you implement, or reject the improvements users request.
However if you just enable it in one of your other content forums, and please mind that by default it is set to prune posts over 7 days with no activity, you might just find your self visiting a ghost forums (especially if you are still starting it and you have few people writing on it.

Now picture you forget that setting on, and also use Cpanel to backup. Well than I guess you'll find your way into this post (or the other forums posts I've quoted") sooner or later.

It's easy to restore your pruned posts if you have an SQL for for your database restore.
Just open your administration tool (PHPMyadmin for instance). At the same time, open your SQL restore file on a text editor and then browse the tables and the respective restore code to check what has been deleted and out of those what do you want to restore.
Then use an SQL tool to run commands on your server and copy-past from the backup, file, clean up unwanted lines and run the command.
It's that simple. The ONLY thing you absolutely need to understand is that there are several tables that have post information and indexing to forums and topics.
So you'll have to perform this procedure in the following tables (and add the full lines to the missing table):
   - PHPbb_posts
   - PHPbb_topics
   - PHPbb_topics_posted

This will render your posts back on to your forum. However, you you also want the forum statistics and last post and poster up-to-date, you need to restore a part of your forums table. So inside PHPbb_forums, find the following fields and restore them from your backup (probably using either an update command or simple copy-paste into location, depending on just how much you need to restore)
   - forum_posts
   - forum_topics
   - forum_topics_real
   - forum_last_post_id
   - forum_last_poster_id
   - forum_last_post_subject
   - forum_last_post_time
   - forum_last_poster_name
   - forum_las_poster_colour

When you finish this procedure, now only you've recovered your posts and topics, you have also restored those statistics indicators on the forum's front page that will point your users to the most active topics.

That's it, that's all. Simple and not by all means in need of a script or a complicated procedure.

Sunday, February 24, 2013

Bad Bad hacker...Protect your JOOMLA website before someone without ethics takes it out.

Recently, there has been a huge wave of sites being hacked by the so-called "Your country's name here"CyberArmy.

I've placed the "Your country's name here"CyberArmy of purpose. First was the Bangladesh, then Pakistan... and I've lost count on the rest of them. I've been watching this from the distance through feeds from twitter and rss, described as a "hacker war between India and Pakistan". However, in this last month I've had to spend over 50% of my time cleaning-up, recovering, protecting and placing back online sites from friends of mine, that were hacked.

I'd like to state here and now that I'm affiliated with the Anonymous movement. However, I'm affiliated with the original concept of anonymous. NOT THE RADICAL fractions, nor the "It's cool to say we are anonymous but we're just a bunch of kids that destroy stuff fractions".
Anonymous has no organized leadership, or body of command, so as a result, a lot of fractions just wander around and follow their own path under the name of Anonymous (counter-sense, I know, but you get the picture).

Most of my friends sites are made on Joomla. Yes, others have .NET sites, or WordPress sites, but those really don't come asking me for help as they already know my answer.
Joomla people, on the other hand, I try to help as much as I can. I don't code components (can't afford the time) but I do customize them and customize the Joomla it self. So when a friend asks for help I try to help...I figure that this is one way to give-back to the community.

One of the things I've not explained is that I didn't charge for that 50% month worth of hard work. This part SHOULD clear my "Anonymous ideal alignment" and my understanding of this wave of STUPID and POINTLESS cyber terrorism.
-If someone hack a government website and brings down the IRS database or VAT reporting and registration services, I'll be the first to cheer and congratulate...partially because very little governments on earth are honest (so they deserve to be robbed in the good old Robin Hood style) and partially because a big organization should pay for the best in terms of security (and most just decide to hire people without technical value, like friends and their colleagues kids, for these high-pay jobs).
-If someone hacks a big company, especially if it's one of those company's that are truly worthless, and do nothing but damage with blind profit in sight (like moody's for instance), I'm the first to applause and pass the message.
-On the other hand, if someone just hacks or defaces a website from a small company, because they are trying to show off skill, in truth they are showing off TOTAL and COMPLETE lack of I.Q. ...and I ultimately hate them with a despise that I can't even compare to an insect. At least an insect does what it is biologically programmed to do...a human, on the other hand, SHOULD KNOW BETTER!
Small companies tend to serve their clients in the best possible way, because they can't afford to loose them; they try to have a lower margins because they are also lighter and with a leaner structure to support; they have very little profits compared to the big companies  so no Lobby is possible, creating LESS government corruption impact; They also pay the full tax load, because they can't "shake the system off" with good lawyers and tax experts on the payroll...They normally have such a lean system that it's almost impossible to access good security experts, making their efforts to migrate their business online extremely vulnerable.
As a result, most get hacked and the majority doesn't recover from the hit. Most had to pay for the website already, and did that at the lowest possible bid...it's unthinkable to rent an expert for 1 or 2 full weeks and pay 10k€.

Joomla is an excellent CMS to build up-on. I don't particularly like V3.0, but I love 2.5 and 1.7 .
Joomla gets patched regularly and it's actually very acceptable in terms of  security.
There are 2 main problems with Joomla in terms of security:
- you can install 3rd party components, and not all are safe form bugs.
- the system is protected at code level, this then assumes no attack gets through  as a result the data layer has very little protection.

The most common SQLinjection attack on Joomla, take advantage of one vulnerability on a form on one of the components (generally 3rd party) and Injects SQL code. So the community developing Joomla protected their code, but a part of code done outside their control just bypasses all those protection layers.
So what? Well, normally, the hacker will attack the users table on Joomla and just replace every user with the same username and password.
Why? When you install Joomla, the very first user in the database is the Admin and it's a super user. Anyone logging in with that user can do anything.
How to solve this: There are several protections (some more effective) but I'll go though them.
1-change the user name to something different than admin and description different than super user.
2-add a user called admin with no right at all (public or simple registered)
3-on your database, change the username and email fields to UNIQUE.
So now, next time the hacker tries an injection with a code like "UPDATE something_users SET username= 'admin', password='1234' WHERE 1=1", at the very first row, the database engine will issue an error because admin already exists.
If, on the other hand he tries "UPDATE something_users SET password='1234' WHERE username= 'admin'", he will end up with a user account that has no rights at all.
Is that it? No! a more knowledgeable hacker would simply replace the first row with a different user and still change both username and pass form your super user account. Something like "UPDATE something_users SET username= 'Pamela Anderson', password='1234' WHERE 1=1 LIMIT 1" or  "UPDATE TOP(1) something_users SET username= 'Pamela Anderson', password='1234' WHERE 1=1" or  "UPDATE something_users SET username= 'Pamela Anderson', password='1234' WHERE 1=1 ROWNUM=1"... this will change with the database engine, but you get the picture.
How to solve this: 
1- create a different account on your Joomla administrator (preferably NOT on the first users on your user list)
2-give full administrator permissions to this new account.
3-remove all permissions on the default administrator account (the first row on your table).
OR- just use SQL commands to copy that account to a lower ROW, and then just delete the first ROW.

What if: the hacker is patient enough to go and execute this last hack on the first row, try to log, if the user has no rights delete the first row and re-start the hack until he gets to a row with the real admin user? THIS IS HOW you should think... that what if the guy trying to hack-me is better and more persistent than I am?
Then, the only way to solve this in the database, is using TRIGGERS.
I personally protect MOST of my website databases with a BEFORE UPDATE and BEFORE DELETE or TRUNCATE or DROP:
1-On the Update trigger I simply don't enable updating of the username and email without a safety password input into a value.
2-About the DELETE, TRUNCATE and DROP, I just don't allow-it, period (you never know if the hacker just gets mad and decides to destroy).

So what about that good thinking that the hacker is better than me and can think around my defensive configurations, or that some entry-point on some module or component is wide open and beyond my comprehension until it is too late?
Remember my advice? Assume the hacker is better and more persistent than you are; don't take Joomla updates for granted as they can still have vulnerabilities and they do not patch 3rd party components.
Well, thinking about this I created a protection service. Think of it like a bouncer.
I created a webservice (with internal protection against hacking) that is put to work in a different domain than the one of the site being protected... that way, the hacker will have to hack 2 different websites to clean up his "digital footprints".
The webservice is then called by each index.php page on my clients website and administrative site. The call will send all the request data to the Webservice and wait for a forensic analysis. The reply may point the website visitor to the website, the hacker to a standard "stop trying to hack this site" page, or simply send the hacker or visitor to a "this site is blocked on your country" page.
Independently of the answer being a block, anti-hack or a go through, the webservice logs everything and if (here come the important assumption)  the hacker finds a hole, it will be recorded and later analysed to create a rule against that new vulnerability.

For the time being, I'm only allowing free usage of this tool for all my friends that have been hacked, as I'm still testing the hardening efficiency. But as soon as finish the mobile app to access reports and statistics, I'll place a price tag on it. If you like the project, please keep visiting Http://www.thesitecupid.com (still on final construction phase) under "enforcer" menu....or register and follow this blog.