How to detect phishing emails

This may come naturally to some of you but to others it can be very difficult to tell whether an email is legitimate or not. This is the reason that banks and other companies continuous say that you should never click on links (in emails) that would hint that you reset your password (or doing anything with your sensitive data) with this said company. You should always navigate to the official web page and then proceed to do whatever it is you need to from there.

Of course this doesn’t stop someone from hacking the actual site, but that’s a discussion on it’s own.

About 2 years ago ago I got this email from “Facebook” in my inbox (or rather my spam box). Now, I’m sure some of you have received emails like this in the past too (and some of you without a Facebook account I’m sure; I had recently deleted my Facebook account but here lay a message claiming I had unread messages on my account), but I thought it would be an interesting exercise to decompose the email and inspect everything in order to point out certain things that you should always be on the look out for when you receive an email and you are suspicious about it.

Let’s take a look at the following image:

facebook-phising

As you can see the email says that it is from FacebookAdminstration, but look at the email address between <>, that is where it actually came from. Always make sure that you inspect this first. Usually it will not come from the Facebook (or which ever company they claim to be from) domain. It is very possible that this can be spoofed (using a simple SMTP server, but use this as your first point of entry. If this does not match up to what you think it should be, then the email is definitely not legitimate.

One of the more obvious signs of a phishing email is the nonesense which appears in the subject line. This one read: “Contraction Your 2 unread messages will be deleted in a few days swerve”.

  1. This subject line does not make any sense
  2. The casing in the sentence is wrong

If you use gmail as your email client (as I do) you can see the original email as text. To do so:

  1. Click on the little arrow next to the reply button.
  2. Select “Show Original”.

Right at the bottom of this post I have included the full content of the original content that I received (I have omitted my email address). The following section is a breakdown of the important things to look out for.

In the original content you will see this:

DO NOT CLICK ON THE LINK.

The above href contains a URL to a unfamiliar site. It is definitely not a Facebook site. This link (as you can see from the original email below) appears in every single clickable part of the html from the email and should not be trusted.

Another thing that you can look out for is the delivery chain of the email. This will be found in the header of the email as shown below:

The way which you read this header is a little bit counterintuitive, as you have to read it from the bottom-up. I have replaced my original email address with “me” in the above header.

As you can see the email is directed to “me” and this is where the email would start it’s travel. Similar to how you would write a normal letter. You need to provide the location at which it needs to be delivered. The beauty of email is that it will record each domain that touches it. Imagine the receiver of your plain old fashioned letter could know who exactly touched the letter on the way to them. If all the people that touched the letter were legitimate, the receiver of your letter could guarantee that it could be trusted

With email, this is always the case.

If we move further up the header to line 14, we can see that the email came from: “FacebookAdministration <egal.hm@bongfaschist.de>”. This matches what was discussed earlier. At this point, this is the original sender as they would like to be viewed from the SMTP server. It is possible however, that this could appear to be legitimate. We need to further inspect the email

Let’s take a look at the next two lines of the header above that:

One of the most important things to note here is the SPF. SPF is Sender Policy Framework. Basically it is an email validation system that ensures that the sender of the email is authorised on the domain that they claim to be from. As you see from the above, the SPF failed. That means that the person that sent this email on “bongfaschist.de”, is not a valid user on that domain. Hence we cannot trust this person.

Here is a legitimate email header from MyBroadband:

Let’s take a look at the received part of the header:

Here we see the original exit point of the mail. This email was sent from “www-data by mybb-mail01.jnb1.chs.hetzner.co.za”. This my seem a little bit scary. It’s not mybroadband.co.za. However, a quick google search will tell you that this address is simply the reverse DNS of the hosting site mybroadbandmail. You can see this at this link here: http://whatmyip.co/info/whois/197.242.89.180/k/471857840/website/mybroadbandmail.co.za

Furthermore, if we inspect the SPF part of the header:

Here we can see that this client is authorized on that domain. This means that the sender is not claiming to be another entity


Lastly, below is the original content of my Facebook email that I received. I have included in the post for reference and completeness.

 

Approaches to data migration

When looking for approaches to data migration from one database management system (DBMS) to another DBMS, the most commonly suggested approach is to use SQL Server Integration Services (SSIS). This article will not be focusing on SSIS has there are many tutorials on the web relating to how you can achieve your desired goal using SSIS. This article will rather be focusing on using a code based approach (i.e. C#) in order to achieve your goals.

I was recently tasked with performing data migration from multiple DBMS’s. To name a few, these with Oracle SQL, Teradata and SQL Server. The destination DBMS is SQL Server. I had initially started using SSIS (as I mentioned, it is the most commonly suggested approach for this task), but soon came to realise that SSIS had some rather unsatisfactory pitfalls which can be avoided if you do the data migration via a code approach. One of the major pitfalls with SSIS is that it is very static in nature and you need to be able to define all your rules for the data migration up front. This relates to the column definition of tables as well as all the tables that you would like to migration data from. The problem with this is two fold:

  1. You may not know exactly which tables you want to migrated data for – so you would like to bring all of the tables into the new DBMS so that you can do further analysis on them
  2. There may be a huge number of tables in the source DBMS. Let’s take for example a table count of 100. This means that you need to statically define a source and a destination for 100 tables. This is just the basic reading in and writing of the tables, and excludes and tasks required to do processing on that data (should there be any). So, if there are no other tasks required, that will mean that you will need to define 200 tasks (at least) to perform this operation. If for any reason one of the tables is removed from the source or a column definition is changed, you will need to go back into the SSIS package, find those affected tables (amongst your 200 tasks) and refresh the meta-data on that task.

As you can see from the above, if your environment is dynamic in any nature at all, you will have major headaches trying to deal with this in SSIS. Not to mention the amount of time it will take you to set up a source and destination task for each and every table that exists.

It is for this reason that I decided to go with a code approach in doing the data migration. With code, you are able to define a process which will read the data from each of the source DBMS’s and gather the required information about those tables dynamically. If a table definition changes, it will automatically be updated in the migration. If a table no longer exists in the source DBMS, you will not receive any errors (as you would with SSIS), this table would simply not be carried through in the migration.

I will be taking you through two approaches that I have used, to perform this data migration. The first approach was a very naive, and it was simply to get the data across. The second approach was improved in order to do batch reads and batch inserts from source to destination. In both approaches the only section that differs is how we read the data from the source and how we write it to the destination. There is additional logic which needs to be applied first in order to get the definition of the tables from the source; this logic will be shown first.

Getting required information for the source DBMS

The first thing that you will need is to be able to get the table definition from the source DBMS. This may and will probably differ from source to source. As I mentioned, the three that I was using were Oracle DB, Teradata and SQL Server. Below is a snippet of each DBMS’s query to get the table definitions:

Oracle:

Teradata:

SQL Server:

You will notice that each query as a {0}. This is used for string format purposes. Simply replace {0}, with the database name or the owner name (or Oracle purposes, owner is the same as database name).

From the result set returned by these queries you will have a full list of all the table names that exist on that source. In order to create the tables in our destination DBMS, we will need to get the column information for each of these tables. Below is a query snippet for each DBMS to get the column information about a table. You will notice that each snippet contains two string format args (0 and 1). These args are the schema (owner) and table name respectively.

Oracle:

Teradata:

SQL Server:

Now that you have the table names and definitions for each source table you can create a looping function that will create each of these tables in your destination DBMS.

Creating the destination tables

For the purposes of what I was required to do, creating the destination tables as nvarchar’s was sufficient. However, as you might notice from running the above queries, it is also possible to get the data types of the source tables and use that information to create a table with the same data types.

SQL Server provides a neat way to do this in code which I shall detail below. For the other source DBMS’s, if you require the data types, you can attempt this SQL Server approach (but I do not know if it will work, as I have not tested it myself). If it does not work however, you should be able to get the data type information from the queries listed above if you select more than is provided in them. For example, you’ll note that in the Oracle example all we are selecting is the COLUMN_NAME however, there are other columns on that table which will contain the information that you are looking for.

Naive approach

Now, let’s take a look at the code for the simple table creation setting all of the data types to nvarchar(4000) (SQL Server’s max length, you are welcome to use nvarchar(max) if 4000 does not suit your needs).

You will notice that I am using the generic classes DbConnection, DbReader and DbCommand. I am using this to simply illustrate how the code will look. You will need to substitute that with the specific implementation of the DBMS that you are trying to connect to.

With data types approach (SQL Server specifically tested)

Now, let’s take a look at how we could get the column data type information for the tables.

The basic code changes as follows (you will notice that it references a method called GetColsForCreating. This method is defined below the current snippet.

GetColsForCreating method:

And there you go. You have now successfully created all of the source tables from one DBMS (dynamically) into another DBMS. This approach gives you great flexibility and allows you to create the tables in the destination DBMS at run time (rather than at build time, like it would be the case for SSIS).

Importing the data (migration)

Now that we’ve got all of the tables defined in our destination DBMS, we can proceed to perform the actual migration of the data from the source DBMS’s.

As mentioned previously, I have two approaches that can be used.

  1. The first approach is extremely naive and simple reads one row and inserts one row at a time into the destination. This approach will work sufficiently if the tables that you are reading from are not large.
  2. The second approach is slightly more optimized and runs the reads and inserts in batches of a pre-defined amount.
I must note that for my example, I used a batch size of 50 000. I ran the code again with a batch size of 100 000, but it did not seem to increase the performance of the query to read the batch. it is possible that the indexes on the tables that I was reading from could be defined better. You will have to pay around with this number and decide for yourself what you deem is a good batch amount.

Naive approach to inserting data

As you will see, this approach can be extremely memory intensive and will hold up your source table with a lock until all the data as been read. This would not be ideal in most cases, but might work fine if the table is less than 1000 rows.

Batch approach to inserting

Once again, you will notice that I have used the generic classes in the above code snippet. This will simply need to be replaced with whatever source DBMS you are reading from.

Final thoughts

SSIS is a good enough tool for someone who does not have development experience, however, if you are a developer – I would recommend that you always favor the development approach (at least for the problem of data migration).

I would like to say that the examples expressed above are simply approaches that I have recently go about implementing. The are by no means the best or worst ways to implement a data migration code approach, but I believe that it works fairly well. This is with regards to the second approach for each creating the source tables and importing the data into the destination tables from the source.

Lastly, if you are working on a data migration task for a source DBMS that has a large number of tables, or you are working with multiple source DBMS’s, my recommendation to you is don’t waste your time investigating how to do this with SSIS. Simply bite the bullet and use a code approach. You will always have more control over the flexibility of your implementation when using code.

If you have any suggestions or approaches that you would like to recommend, leave a comment so that future readers will have more examples to work with.

Git Behind The Proxy

If you work for a corporate, chances are that you will need to deal with a proxy when it comes to connecting to the internet (or any computers that are outside the domain).

This can be very frustrating at times, because you now need to jump through another hoop in order to get your work done.

I’m writing this article in the hopes that it will help someone in the future, should they come across a similar problem or even the exact problem. This article covers setting up automated builds with TeamCity and pulling down a git repository from Assembla – all while behind a corporate proxy.

The corporate proxy that is used in my case is squid proxy (a common choice these days).

TeamCity Setup

The usual way of setting up TeamCity is allowing the server and the agent/s to run under a system account. This allows you to avoid password changing problems (should the password for the user that you set to run the server and/or agent/s, change), which is very common in the corporate setting (with a normal minimum password reset required every 30 days or so).

Following the default settings I came across the following problem with nuget restore. When running nuget restore from TeamCity it would always log that it was starting the download but then it would say that the connection was closed by the remote host. This error message was not very useful to me.

I searched around for about 2 days and found a couple of ways to run the TeamCity server through a proxy, but was not able to find a way to apply this proxy route to the agent. The agent is where we actually need the proxy information be applied because the agent is the one that is running the build steps (in this case, nuget restore).

I then came across this stackoverflow question: http://stackoverflow.com/questions/21043355 and this was where I found an answer to the problem. It’s still not ideal, because basically what it means is that we need to make the agent run as a user on the domain.

Through my searches I also came across this stackoverflow question here and here. On the second link, I posted my own answer. The details in the answer is what I ended up doing and this worked for me.

If it possible (as it was my case) – get a user uses password does not expire on the domain. If at first they do not buy into this idea, you need to explain to them that by not having this in place you could run into problems further down the line for your automated builds, since each month (or whatever the password change policy is), the builds will start failing because it does not have access to run under the user that you have specified.

Git Setup

What we would like to do is use ssh over port 443, since port 22 (normal ssh port) is blocked for security reasons. One of the main reasons that I can think of is that port 22 is also used for FTPs.

I learnt that this was possible when I stumbled upon this stackoverflow question here and in particular look the answer provided here and here.

However, due to Assembla’s lack of documentation for using ssh through port 443, this task turned out to be more tricky than explained in the answer mentioned above. Luckily, I got the information from one of the support guys and have provided the details for you below.

  • The hostname to use is altssh.git.assembla.com (this reflects to IP: 64.25.188.37)

As such, following the information provided in the questions above what we can do is add the following to our ssh config file (located at: ~/.ssh/config – if you are on windows, simply open git bash and navigate to that folder)

OR

Once you’ve got this setup you can test the connection by running the following in git bash:

You’ll notice that it connects to the above mentioned IP address on port 443.

If you are lucky this will have solved your problem, if you are unlucky like me – you’ll get a connection timed out error.

Git Setup (Alternative 1)

If the above section doesn’t work, there is another option that you can try (which in my case also didn’t work and I needed to make changes to the proxy to allow this. You can see what I did for that in the section below titled: Proxy Changes)

You can try route the ssh traffic through the proxy using socat (a proxy client).

This requires you to download and install cygwin for windows (and also include the socat package – this can be down via the installer. If you need assistance with this, follow the guides on cygwin’s website)

There were a couple of methods that I came across (which I’ll list below) but by far the quickest way that I found was from this post here (i.e. Method 1)

Method 1

Following the link above from the post I mentioned you’ll notice that you need to make changes to the ssh config file. Here are the changes that I made:


Expanding the example


  • 10.10.10.10 – this is the actual proxy address and will be different in your case
  • 9418 – this is the port to communicate with on your proxy. In my case we are using 9418 (because this is how it was configured on the squid proxy – see this answer on stackoverflow for more information (it is important to make sure that the proxy is configured this way).
  • 8000 – the port that the proxy communicates on.

Method 2

The other method involves running socat through git rather than running all ssh connections through socat. You can find information on how to do this here

Important note. Make sure that the path to the socat file exists. If you’ve installed cygwin on a 64 bit machine you likely have the files sitting under C:\cygwin64 instead of C:\cygwin.

You can place the gitproxy.cmd file in any location, all that you need to do is when you run the command:

you need to provide the full path to the file. For example:

There is another example using socat from an .sh file which you can find here. The same information applies to this one. Make sure that cygwin’s bin folder is in your path for this to work (or change the socat in the file to be the full path – like with the previous example).

Proxy Changes

Finally, we managed to get the proxy and the server to play nice with each other.

Our investigations lead us to this web page here. One this page, it shows you that you can use ssh to ssh into another server as a proxy command. This basically means all we need to do is ssh into the proxy server from our server and viola! (Except not as easy as “viola!” as there are settings that need to be configured)

One of the most important configurations required for this to work (as you might notice from the web page linked above), is the presence of netcat (i.e. nc). Once you have installed (or confirmed that) netcat (is on) the server you will need to make changes to the ssh config file.


Expanding the example


You will notice that in our ProxyCommand we are now running the ssh command to connect to the proxy server directly using the user “user”.

By doing this when we run the test (namely running

) we will see that it asks us for a password. This password is the password for the user that you have provided in front of the @ in the ProxyCommand line.

You will notice that there is a -q after the ssh in the ProxyCommand line. This suppresses the “Killed by signal 1” message from the terminal of the ssh connection. You’ll find more information about this here.

Avoid using the password

To avoid using a password, simply create an ssh key for the user that you are using to ssh into the proxy server. Copy this key to the server that you are running the command from.

Using my previous examples as reference, create a key on 10.10.10.10 (the proxy server) for user “user”. Now copy this key to the server that you are running the “ssh -T -v git@git.assembla.com” command from.

This newly generated key must also be added to the authorized_keys in that user’s .ssh folder.

Once you have done this you will need to append your ssh config file with the following information:

This will make sure that when the ProxyCommand is run, the correct key (IdentityFile) is used for that host.

Final changes for TeamCity required

After you’ve gone through this entire ordeal, you might still be experiencing a problem with using the VCS from TeamCity (as I did). You might run into a situation where you receive a “Connection timed out” message when TeamCity tries to pull from your repository using the VCS settings.

Why this happens is beyond me. I can’t seem to find any information regarding it on the web.

I was able to come across the following link, which details how to change the TeamCity server proxy settings (via the environment settings). If that link helps you, that’s fantastic! Unfortunately, in my case it did not work.

There was another setting which I found that could be tried (but this also didn’t work). You can find the details for that here (there is an answer on this question which links to the TeamCity page which will explain how to make the required changes).

Finally, what I decided to do was to create a new build step (in TeamCity) that would run a “git pull origin master”. This is the first build step of course. This runs in a “hard-coded directory” which is then referenced as the path in all the other build steps.

If all of the above does not work for you and you would prefer not to specify the checkout folder directory in each build step after, I have some bad news for you. You will not be able to do a checkout using the git protocol. You will need to use HTTPS, which is explained in my final section.

This is of course to my knowledge. If anyone knows how to get it working beyond what I’ve mentioned in this post, please comment and let me know what you did to get it working.

If nothing above works for you

If all else fails (from the above) and you are unable to modify the proxy to allow the connections, there is one last saving grace.

This is simply to use https to clone the repo.

Of course, it’s still not just that simple, since we are behind the proxy. There is one additional change that you will need to make before you can clone out https.

You can see this answer on stackoverflow for more of the details.

Once you’ve made those changes you can simply do a git clone https://blahblah and you’ll be able to check out the code with your git username and password.

TeamCity Setup for HTTPS

In order to get this to work, you will need to set your fetch URL to the HTTPS version of the repo and then in the Authentication Mode section, select password.

In the username box, provide your username and in the password box your password.

Test the connection and all should be good to go now.

Hello World – WordPress Setup

Hello World!

This is my first blog entry and with that, I’d like to take some time to explain how I setup this wordpress site.

I followed these two tutorials on digital ocean. In the past, I have found that digital ocean write up excellent tutorials for linux based operations.