How to dump your Whatsapp chat messages to a csv file

Pre-requisites:
Linux pc with java jdk installed (Does not work with oracle jdk).
adb in the path

First get your whatsapp database:
adb pull /sdcard/WhatsApp/Databases/msgstore.db.crypt12
Decrypt the file:
git clone https://gitlab.com/digitalinternals/whatsapp-crypt12.git
javac -classpath “lib/whatsapp_spongycastle.jar:.” crypt12.java
Now pull the key for whatsapp from your phone (Requires root):
adb shell
su
cp /data/data/com.whatsapp/files/key /sdcard
exit
adb pull /sdcard/key
java -cp “lib/whatsapp_spongycastle.jar:.” crypt12

sqlite3
sqlite> .open msgstore.db
sqlite> .tables
chat_list messages_fts_content
frequents messages_fts_segdir
group_participants messages_fts_segments
group_participants_history messages_links
media_refs messages_quotes
media_streaming_sidecar messages_vcards
message_thumbnails messages_vcards_jids
messages props
messages_edits receipts
messages_fts status_list
sqlite> .schema messages
sqlite> .mode csv
sqlite> .headers on


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install latest version of wine on Ubuntu

wget https://dl.winehq.org/wine-builds/Release.key
sudo apt-key add Release.key
sudo apt-add-repository https://dl.winehq.org/wine-builds/ubuntu/
sudo apt-get update
sudo apt-get install --install-recommends winehq-stable

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Browse a remote folder visually in Linux (ssh)

In Windows, you need to resort to other programs like WinSCP or Filezilla to visually browse ssh shares. In Linux, file managers natively support this.

First add your ssh key to the agent:

ssh-add ~/.ssh/mykey_openssh.pvt

Next, for convenience, create a config file so you can use aliases to connect.
Eg: To connect to 35.180.101.49 as root, we will use the alias ovh. So config file looks like this:

Host ovh
     Hostname 35.180.101.49
     User root
     Port 22

Now open a file manager, and in the address bar type this:

sftp://ovh4

It will momentarily show the root directory of the remote server. You can now edit remote scripts on your favorite locally installed IDEs. Enjoy!
Thunar seems slow for this. Nemo is perfect.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Unlock Motorola Moto devices bootloader

Note: Unlocking deletes all data. So take backup of all your required data.

Download the Android SDK or the minimal ADB and Fastboot (The latter is recommended).
Install Motorola adb and fastboot drivers.
Power down the phone.
Press down Vol Down button, and press Power down too. Keep them pressed and release Power button followed by Vol down button. This lets you enter the Fastboot mode.
Connect the phone to PC with a cable, enter command prompt (In Win 8+, type cmd in Start screen).
Add fastboot.exe and adb.exe to the path.
In a commmand prompt, enter the following, and copy the required data:

fastboot oem get_unlock_data

Visit the unlocking site.
Sign in using your Google account or Motorola ID.
Paste the string copied earlier, in the field.
Click on ‘Can my device be unlocked?’.
Click on the “REQUEST UNLOCK KEY” button.
Select the ‘I Agree’ option. The unlock key will be delivered to your email.
With your device still connected, in command prompt, type:

fastboot oem unlock KEY

replacing KEY with the actual key you received.

For more, Read this XDA post by Lucas Eugene.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install laravel on Debian

First install composer (Tested upto 5.1):

curl -sS https://getcomposer.org/installer | php

Or without curl:

php -r "readfile('https://getcomposer.org/installer');" | php

Now install Laravel’s dependencies:

sudo apt-get update
apt-get install libapache2-mod-php5 php5-gd 

Install Laravel itself:

composer global require "laravel/installer=~1.1"

Now create an application using laravel:

$ laravel new project
Crafting application...
> php -r "copy('.env.example', '.env');"
> php artisan clear-compiled
> php artisan optimize
Generating optimized class loader
> php artisan key:generate
Application key [---] set successfully.
Application ready! Build something amazing.

Here are a couple of links to get started on Laravel:
Laracasts


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

mysql ready reckoner

Create database:

CREATE DATABASE IF NOT EXISTS dbl;

List databases:

SHOW DATABASES;

Eg:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tbl        |
+--------------------+
2 rows in set (0.00 sec)

Work on a database (Use a database):

USE database;

Eg:

mysql> use tbl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Create a user:

CREATE USER 'username'@'localhost';

Set Password for user:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('mypasswordintext');

Grant all privileges on database to user:

GRANT ALL ON dbl.* TO 'username'@'localhost';

Note that you shouldn’t use quotes for the table name.

Create table with fields:

 CREATE TABLE tbl (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> userid VARCHAR(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> role VARCHAR(20) NOT NULL,
    -> email VARCHAR(50)
    -> );

List tables in the database:

SHOW TABLES;

Eg:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_episodestbl |
+-----------------------+
| Animals               |
+-----------------------+
1 row in set (0.00 sec)

Show the structure of a table:

DESCRIBE table;

Eg:

mysql> DESCRIBE users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| userid   | varchar(30) | NO   | PRI | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| role     | varchar(20) | NO   |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Insert (Replace if it already exists) data:

 REPLACE INTO `users`
    -> SET `user` = "myusr",
    -> `pass` = "mypass",
    -> `role` = "admin";

Replace works only if the primary key has a different value. If primary key is autoincremented (a bad idea), it duplicates rows.

Alternately,

INSERT INTO `episodestbl`.`users` (`userid`, `password`, `role`, `email`) VALUES ('someone', 'password', 'user', [email protected]');

Deleting a column:

alter table auth drop id;

Deleting duplicate rows:
SELECT * FROM names;

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

Show all table data:

SELECT * FROM TableName;
mysql> SELECT * FROM Authentication;
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| user   | pword           | who           | email                 | ip            | loginfailures |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| jack   | gobbledegook    | administrator | [email protected]     | 116.68.73.178 |             0 |
| jones  | otheroall       | user          | [email protected]| 163.47.12.220 |             0 |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
2 rows in set (0.00 sec)

Deleting a column:

alter table auth drop id;

Change a field to a unique id:

ALTER TABLE auth ADD UNIQUE uid (userid);

Add a field to a table:

USE databasename;
ALTER TABLE tablename ADD newfield VARCHAR(15) NOT NULL;

Useful links:
https://en.wikibooks.org/wiki/MySQL/CheatSheet
http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install ownloud on a Debian box running apache server and an i-mscp panel

Add a DNS entry for cloud.xyz.com in your DNS manager. I will choose Cloudflare.
Then, login to the i-mscp panel, create a domain alias, say cloud.xyz.com

Install owncloud:

echo 'deb http://download.opensuse.org/repositories/isv:/ownCloud:/community/Debian_8.0/ /' >> /etc/apt/sources.list.d/owncloud.list
wget http://download.opensuse.org/repositories/isv:ownCloud:community/Debian_8.0/Release.key
apt-key add - < Release.key
apt-get update
apt-get install owncloud

It will get installed to /var/www/ownlcoud
We will have to move the files to the directory of the new domain alias:

cd /var/www/
cp -a owncloud/* virtual/xyz.com/cloud/htdocs/
cp -a owncloud/.* virtual/xyz.com/cloud/htdocs/
rm -rf owncloud

Set permissions (Check the username for your directory virtual/xyz.com/cloud/htdocs first):

chown -R vu2004.vu2004 virtual/xyz.com/cloud/htdocs/*

Install owncloud by visiting http://cloud.xyz.com, and creating a new admin user and password.
Now that owncloud is confirmed to work, we will add SSL support.
In i-mscp, enable SSL support by visiting Admin settings.
Now, use the “Add/Edit SSL Certificate” to generate a new SSL certificate.

Enable Cloudflare for the domain.
Now you will be able to login to https://cloud.xyz.com


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Setting up OpenVZ and the OpenVZ Web panel on Debian

First, you’d need to install Debian on your server. For DelimiterVPS, go to https://clients.delimitervps.com/clientarea.php, and login to your account.
Choose the Reinstall server option.
Under ‘Installation Profile’, choose ‘Debian Wheezy’.
Choose a good enough root password. From personal experience, I’d avoid special characters in my root password. I had setup a very complex password once, and found that it was not accepted by the ssh login shell. It’s likely that WHMCS does not escape special characters very well.
Choose ‘Provision Server (Warning)’.

At this point, go back to the email you received from DelimiterVPS, and read the part about KVM/ILO Configuration. You can login to ILO and watch the installation progress. Once installation is done, Proxmox is supposed to be up and running at https://yourip::8006. However we need a couple of steps and a reboot before we can use it.

Run the following:

cat << EOF > /etc/apt/sources.list.d/openvz-rhel6.list
deb http://download.openvz.org/debian wheezy main
EOF
wget http://ftp.openvz.org/debian/archive.key
apt-key add archive.key
apt-get update

Install OpenVZ kernel:

apt-get install linux-image-openvz-amd64

First, you have to remove the default Linux kernel, and set the OpenVZ kernel to start at boot.
Run the following from the shell:

apt-get remove linux-image-amd64 linux-image-3.2.0-4-amd64 linux-base
update-grub

You’ll notice that openvz based kernels have now been added to grub (the bootloader).

Enable IP forwarding and other rules are setup:
Edit the file /etc/sysctl.conf, and uncomment/add (Remove the # at the beginning) the following lines:

# On Hardware Node we generally need
# packet forwarding enabled and proxy arp disabled
net.ipv4.ip_forward = 1
net.ipv6.conf.default.forwarding = 1
net.ipv6.conf.all.forwarding = 1
net.ipv4.conf.default.proxy_arp = 0

# Enables source route verification
net.ipv4.conf.all.rp_filter = 1

# Enables the magic-sysrq key
kernel.sysrq = 1

# We do not want all our interfaces to send redirects
net.ipv4.conf.default.send_redirects = 1
net.ipv4.conf.all.send_redirects = 0

run the following:

sysctl -p
apt-get install vzctl vzquota ploop vzstats

Now, restart the server.

reboot

Run the following:

wget -O - http://ovz-web-panel.googlecode.com/svn/installer/ai.sh | sh

After some time the installation may stop with the following message:
Fatal error: Panel requires Ruby 1.8 (Ruby 1.9 is not supported).

Run:

update-alternatives --config ruby
# update-alternatives --config ruby
There are 2 choices for the alternative ruby (providing /usr/bin/ruby).

  Selection    Path                Priority   Status
------------------------------------------------------------
* 0            /usr/bin/ruby1.9.1   51        auto mode
  1            /usr/bin/ruby1.8     50        manual mode
  2            /usr/bin/ruby1.9.1   51        manual mode

Press enter to keep the current choice[*], or type selection number: 1

Now rerun the installation:

wget -O - http://ovz-web-panel.googlecode.com/svn/installer/ai.sh | sh

At the end, you will get the message:

Panel should be available at:
http://x.droidzone.in:3000
Default credentials: admin/admin

Now login, and change the default password.

You can now install OpenVZ templates(Physical servers>Localhost>OS Templates>Install new OS Template).
Assign ips to the pool (Ip Addresses>Create new IP pool). Add the IPs assigned to you. You may need to buy more from your provider.

To create a VPS:
Localhost>Virtual servers list>Create virtual server

Creating a custom template.
Provision a VPS using a default template.
Login to the VPS, setup everything including resolvconf, tzdata, locales, dialog, .bashrc etc.

vzctl stop 1
vzctl set 1 --ipdel all --save
cd /var/lib/vz/private/1
tar --numeric-owner -czf /var/lib/vz/template/cache/debian-7.0-x86_64-minimal-custom.tar.gz .

Change default port and enable SSL on Openvz web panel:
Change the following in /etc/owp.conf:

# web server port
PORT=3000

to

PORT=2096

and

# SSL support, on - enable, off - disable
SSL=off

to

SSL=on

Restart the service. Now it is accessible at https://yourdomain.com:2096

I chose 2096 because it is one of the ports that Cloudflare supports and thus you get a free SSL support with Cloudflare.

Create a symlink to /vz because most of the vz tools expects the OpenVZ folders to reside there. This step is not necessary, but can eliminate further problems when other vz related components are installed.

ln -s /var/lib/vz /vz

You also probably need to change:

#NEIGHBOUR_DEVS="detect"

to

NEIGHBOUR_DEVS="all"

at /etc/vz/vz.conf

and do a

service vz restart

For Reference, my network config on node is as below:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
allow-hotplug eth0
iface eth0 inet static
        address 157.266.186.60
        netmask 255.255.255.192
        network 157.266.186.0
        broadcast 157.266.186.63
        gateway 157.266.186.62
        # dns-* options are implemented by the resolvconf package, if installed
        dns-nameservers 8.8.8.8
        dns-search droidzone.in

And my two addon ips are:

157.266.187.162/32
157.266.187.163/32

Obviously the ips have been scrambled.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install YOURLS URL shortener on your own server

Requirements:
Shared server/VPS with the following installed:
A webserver with php installed
mysql

Set up YOURLS url shortening service on your domain.
I’ve added the domain to i-mscp to create a web hosting directory

ssh to the shell

cd /var/www/virtual/mysite.com/htdocs
rm -rf ./*
git clone https://github.com/YOURLS/YOURLS
mv YOURLS/* ./
rm -rf YOURLS
cp user/config-sample.php user/config.php

Login to phpmyadmin and create a new database named yourlsdb.
Under Privileges tab, create a new user, name it yoursusr
Generate a secure password like this: [email protected]&Cc^95gMY#6Bhd
Tick the box to Grant all privileges on ‘yourlsdb’ to yoursusr

Now edit the config file, and add database details:

emacs user/config.php

Visit http://yourls.org/cookie and generate a cookie key and update it in this same file.

Add appopriate passwords at this block:

$yourls_user_passwords = array(
        'username' => 'password',

Now point the browser to http://yoursite.com/admin/ and click on ‘INSTALL YOURLS’.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

How to migrate or transfer a gitlab installation from one server to another

On the new server running Debian 8, we’re proceed to create a fresh copy of Gitlab Community edition. For demonstration, I have created a Digitalocean droplet. (Aside: Digitalocean is a cloud based on-the-fly VPS service. To try this tutorial, you may Signup to DO using my referral code and get a free $10 credit to try it out. Click here).

Create a Debian 8 jessie VPS with 512 MB RAM (512 MB is enough for running Gitlab. If you wish to try with more RAM, you’re free to).

https://downloads-packages.s3.amazonaws.com/debian-8.0/gitlab-ce_7.10.1~omnibus.2-1_amd64.deb

For latest package, refer: https://about.gitlab.com/downloads/archives/

Now install essential packages:

apt-get update && apt-get install openssh-server postfix emacs -y
wget https://downloads-packages.s3.amazonaws.com/debian-8.0/gitlab-ce_7.10.1~omnibus.2-1_amd64.deb

For Debian 7 64bit, the package is at https://downloads-packages.s3.amazonaws.com/debian-7.8/gitlab-ce_7.10.1~omnibus.2-1_amd64.deb

I install emacs because it is my preferred editor. You may use your favorite editor-vim, nano etc.

Before backing up, you need to upgrade the version of gitlab on the old server to the same version as the one you will be installing to the new server.
So on the old server, I will upgrade 7.9.2 to 7.10.1 by following instructions given here: https://gitlab.com/gitlab-org/omnibus-gitlab/blob/master/doc/update.md#updating-from-gitlab-6-6-x-and-higher-to-the-latest-version
For choosing update instructions, see here: https://about.gitlab.com/update/

Login via ssh to the old server where gitlab is already running, and issue the following command from shell:

sudo gitlab-ctl stop unicorn && sudo gitlab-ctl stop sidekiq && sudo gitlab-ctl stop nginx
wget https://downloads-packages.s3.amazonaws.com/debian-7.8/gitlab-ce_7.10.1~omnibus.2-1_amd64.deb
sudo dpkg -i gitlab-ce_7.10.1~omnibus.2-1_amd64.deb

Once you receive a message of “Upgrade complete!”, issue the following:

sudo gitlab-ctl restart

Confirm that you can login, to confirm that everything went smoothly.

Now, backup gitlab.

sudo gitlab-rake gitlab:backup:create

On the new server, download and install gitlab CE:

wget https://downloads-packages.s3.amazonaws.com/debian-8.0/gitlab-ce_7.10.1~omnibus.2-1_amd64.deb
dpkg -i gitlab-ce_7.10.1~omnibus.2-1_amd64.deb
gitlab-ctl reconfigure

Once it’s completed, navigate to http://yourip.com/ and login with username root and password 5iveL!fe. Choose a new password when prompted. Gitlab install is now over. Now we’ll transfer data from the old server.

The following instructions document setting up key authentication between two servers. This is optional. Debian jessie by default removes password based ssh by default. If you still have password based ssh authentication (which was default on previous debian versions), you can just do scp of files and provide passwords. If the latter is true in your case, skip ahead to the scp command below.

In your ssh terminal in the new server, send the public key for your ssh key to the new server.
Since I usually change the ssh port for security reasons, and also disable password login for root ssh user, I have to setup keybased login.

In your old server, make changes to ~/.ssh/config

emacs ~/.ssh/config
Host git.yourserver.com
     Hostname 42.54.136.211
     User root
     Identityfile /root/.ssh/id_rsa
     Port 65678

This sets up ssh so that you can connect to your server with just ssh git.yourserver.com, and ssh will take care of choosing the proper key and port. Otherwise, specifying the port and key each time is tedious. Here, 42.54.136.211 should be replaced by your server’s ip.

In your new server, make similiar changes corresponding to the old server.

On your old server, transfer the latest backup from /var/opt/gitlab/backups/ to the new server:

scp /var/opt/gitlab/backups/1430619604_gitlab_backup.tar [email protected]:/var/opt/gitlab/backups/

Now on the new server, execute the following to restore from the transferred backup.

sudo gitlab-rake gitlab:backup:restore BACKUP=1430619604

If you receive an error about unable to unpack due to a permission problem, just run a chmod go+r on the backup file.

When everything is over, run the following for good measure:

sudo gitlab-ctl restart

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.