Heavy Metal comments edit

Wow, it’s been a year already? It’s time for another Bloodstock mayhem! This year it’s even stronger and faster: Lamb of God and Slayer are two of the headliners. ‘Nuff said!

Bloodstock 2013

I checked my notes from last year while preparing for the next one. I wrote to buy the same tent but I think I have now a better solution:

Tent

which can be bought from here: http://www.amazon.co.uk/gp/product/B00BW3XB0K

The best thing about it is it can be setup in seconds. Just open the zipper and it pops up instantly and you’re done. I had a good deal and got it for £20. At the time of this writing it is £35 on Amazon so I guess I was just lucky to seal the deal back then.

Apart from that, I’ll stick to the plan. I’m packing both suntan cream and poncho as this British weather is completely unpredictable.

Hope everything goes well and I can have a good time at BOA 2013 just like I did last year. I’ll post my reviews after the festival.

Programming comments edit

I need a large dataset to practice on SQL server while preparing to exams. One idea is to generate random data but there is no fun in that. Because it is hard to create real-life scenarios but gibberish data. Then I found out that IMDB actually releases all their data and allows usage for non-commercial applications.

Data Analysis

Unfortunately, IMDB data doesn’t come in a very structured format. You have to deal with the exceptional cases and create your own data structures. I found some nice info in a Github repo called imdb-blaster (link in the resources section).

I started with the main file to parse: Movies.lst. This file contains movies, TV shows and their episodes. I want to store these in separate tables so I wanted to treat them separately. What they look like in a nutshell is as follows:

  • Movies: The Lord of the Rings: The Return of the King (2003)    2003
  • TV Shows: “Seinfeld” (1990)                    1990-1998
  • Episodes: “Seinfeld” (1990) {Male Unbonding (#1.4)}        1990

All TV Shows are enclosed with double-quotes. There are also some other optional attributes that need to be taken into account. Such as deleted items are marked with like “Seinfeld” (1990) {Good News, Bad News (#1.0)}     1989

Start parsing

I don’t like regular expressions too much as they are very hard to read. But they are very helpful and powerful in scenarios like this. The goal is validating and parsing it to groups so that it can be inserted easily to a relational database. So I developed 3 regular expression patterns for the type of data in the file. One thing I learned about regular expressions is that you can group parts by using parenthesises. This creates an unnamed group. If you want to name them you can use angle brackets and assign a name to group such as: (subexpression)

Parsing movies

As most regular expressions tend to wind up with hard-to-read complex queries this was no exception! Even if the structure is simple because of the optional parameters and exceptional cases it became very complex very soon. Here’s the sample code to run in LINQPad to parse a sample line for a movie:

string line = @"America the Punchline (2009) 		2009";
string pattern = @"(^.*)\((\d{4}|\?{4})(/I|II|III)?\)([ ]?)()(\(V\)|\(TV\))?\t*(\d{4})$";
Console.WriteLine(Regex.IsMatch(line, pattern));

MatchCollection matches = Regex.Matches(line, pattern);
foreach (Match match in matches)
{
	Console.WriteLine("Group count: {0}", match.Groups.Count);
	foreach (Group matchGroup in match.Groups)
	{
		Console.WriteLine("{0}", matchGroup.Value);
	}
}

And the output is:

True
Group count: 8
America the Punchline (2009)     2009
America the Punchline
2009

2009

The first group is the whole string. The second one is the title, the third is the year. There is an optional group because some titles come with I, II or III after the release date. I have no idea what that means but had to break it into a group to avoid an unmatch. The last two parameters are the type (V: Video, TV: Television, VG: Video game) and another date. I don’t know why the movies have two dates and they almost always match so I’ll ignore one of them. So the actual code is very simple:

if (regexMovie.IsMatch(line))
{
	Match match = regexMovie.Match(line);

	commandMovie.Parameters.Clear();
	commandMovie.Parameters.AddWithValue("@Name", match.Groups[1].Value);
	commandMovie.Parameters.AddWithValue("@ProductionYear", match.Groups[2].Value);
	commandMovie.Parameters.AddWithValue("@IsDeleted", (match.Groups[5].Value == ""));
	commandMovie.Parameters.AddWithValue("@ReleasedFor", match.Groups[6].Value);
	commandMovie.ExecuteNonQuery();
}

All the hard work is done by regular expressions already. All we have to do is to check is a line matches a movie definition and if it does just insert the data that is already broken up into groups.

Parsing the rest

I’ll publish the full source code in a Github repository and will update this post with the link. But I guess to start parsing the movies file the other two patterns suffice for now: TV Show Pattern: (^””.””) (((\d{4}|\?{4})(/I|II|III)?))\t.(\d{4}-\d{4}|\?{4})$ Episode Pattern: (^””.””) ((\d{4}|\?{4})(/I|II|III)?) {(.?)(#(\d).(\d))}\t*(\d{4}|\?{4})$

Resources

Game comments edit

I’ve been playing this game for months now and I believe it’s time to move on. I think it is safe to say that I finished the game already. I bought all the buildings that don’t require donuts. I haven’t spent one penny playing this game so this is as far as a freeloader like me can get! No, just kidding. The prices they set are preposterous and I would never spend that much money on some imaginary donuts. Actually Leo Laporte said in one of his shows that he spend over $300 for this game! That’s how much you can spend if you want to buy all premium items.

TSTO

I’m happy with the Springfield I built. As you can see in the images below, I unlocked all the lands, bought all the buildings money can buy including the million dollar buildings. The problem with the game is there is no challenge. You just tap on buildings when they have signs on top of them and tap on characters when there is a exclamation mark on top. If this wasn’t about Simpsons I wouldn’t be playing it at all

TSTO

The beginning of the end: Krustyland!

The worst part is they started to repeat themselves. After the last update they introduced a new place called Krustyland. It is exactly like the first level of the game. A blank field and you clean up the place and build some buildings. Only this time you earn Krustyland tickets instead of money. It’s like the mini version of the same game inside it.

TSTO

So.. I’m calling it off. I don’t mind wasting a little time on games to blow off steam but not with this one anymore. Maybe I can move on to something more challenging like MindCraft.

Electronics comments edit

I was trying to avoid soldering but looks like at some point it is mandatory to learn how to do it. Got myself a basic toolkit:

Soldering Tools

There are plenty of tutorials around. I enojyed Iyaz Akhtar’s KnowHow episode dedicated to soldering and EEVBlog’s tutorial. After spending some time around here’s the fruits of my efforts:

Soldered Circuit

Of course the components soldered on this board are meaningless and they don’t do anything. But it was a good practice.

I was surprised to find out that desoldering was an easy process. All you have to do is heat the iron, touch the solder and liquify it and then vacuum it using the desoldering pump.

Desoldered Resistor

Resources

Arduino, Gadget comments edit

Basically the fun with playing gadgets such as Arduino, Gadgeteer and others comes from sensors. Because they are how your system interacts with the real world. And writing code that acts upon some readings from the real world is fascinating!

So let’s get started with using the motion sensor. I have a very generic sensor from Parallax and fortunate enough to find an article that explains exactly what I needed: Using motion sensor with Arduino. Check out the resources section down below to access the original article. Unlike the LCD, it’s quite simple to connect the motion sensor to Arduino. The sample code lights the onboard LED when the motion sensor detects a motion. Check out the video for a short demo. When the sensor “senses” motion, it goes red and the LED starts blinking.

I think the next step would be to connect both LCD and motion sensor to display the alarms on the display. Hope I can make that project happen without too much delay.

Resources

Arduino, Gadget comments edit

I’ve been looking for a good tutorial to connect the LCD screen to Arduino. There are a lot of tutorials everywhere but Interestingly most of them just show the final version with all the cables jumbled up. After a lot of looking around I finally found a simple, step-by-step tutorial with clear illustrations: http://www.codingcolor.com/microcontrollers/connecting-a-lcd-to-arduino/

This is a great tutorial and I recommend it to anyone who wants how to connect LCD to an Arduino.

Arduino with LCD

Resources

Arduino, Gadget comments edit

The most popular development boards is without a doubt Arduino. It is open-source and it’s been around since 2005. There are lots of different models and libraries. My goal for now is just get started and see it in action. To achieve this, first stop is the official site where you can download the drivers and IDE. After download simply unzip the contents to a folder.

A great place to start playing with it is Adafruit. You can find the link for Arduino tutorials in the resources section below. I simply started with Lesson 1 and changed the speed of the onboard LED.

Arduino

So it takes minutes to get started with it and comes loaded with a bunch of examples so they made it very easy for beginners.

Troubleshooting

Error: stk500_getsync() not in sync resp=0x00 This error means that Arduino couldn’t be found in the COM port. To resolve the issue:

  • Use Device Manager and make sure Arduino is connected. It should be listed under Ports node. Note the COM port number
  • Then from Tools –> Serial Port make sure the correct port is selected.

Resources

Development, Game comments edit

Kodu is basically a tool for kids to develop games without writing code. You can select form a bunch of items to place in your “world” and use the visual programming language to program the actions. The best part is you don’t have to start from scratch. You can browse the existing worlds play with it and start editing it right away. For example below is a screenshot of a game developed by California Institute of Technology:

Kodu Mars Rover

You can download any game, play and modify to your liking. It’s very easy and fun way to build games. Especially if you have young children or you are one!

Resources

.NET Gadgeteer comments edit

Soon after I started playing with Gadgeteer it became a bit messy. Components were dangling by the cables and there was no way of keeping them steady. But it is about to change as I have discovered the Tamiya Universal Plate.

Although it’s main purpose is not for Gadgeteer it works great with it. I bought mine from Proto-Pic for around £10 so it’s a small price to pay to keep my design tidy.

Here’s the link for it: http://proto-pic.co.uk/tamiya-70172-universal-plate-l-210x160mm/?gclid=CLT44pym1bgCFbHKtAoddxEAjw

And here’s how I can organize my Gadgeteer designs:

Gadgeteer_Board_01

Gadgeteer_Board_02

I bought 3mm screws and nuts to pin the components but saw some plastic long pins that can be used for the same purpose and would work better as screwing all components is a bit tedious. I’ll update the post if I find those pins.

Development, Networking, Security comments edit

HSTS (HTTP Strict Transport Security) is a security policy where a web server instructs the client that the website they are calling can be used with HTTPS only. By using tools like Wi-Fi PineApple and sslstrip a man-in-the-middle attack can be carried out easily. The attacker can redirect the victim to HTTP version of the site they are connecting to and after the login info is submitted in clear-text and captured the victim is redirected back to HTTPS. By using HSTS the client browser always makes the calls over HTTPS hence reducing the risk of MitM attack. It’s not bullet-proof but still an improvement over not using it.

Implementation

HSTS is basically a response header. In an ASP.NET application it can be added in the web.config file as below:

  <system.webserver>
    <httpprotocol>
      <customheaders>
        <add name="Strict-Transport-Security" value="max-age=31536000" />
      </customheaders>
    </httpprotocol>
  </system.webserver>

And when you visit this site over HTTPS response looks like:

HSTS

This header tells the browser to use only HTTPS from now on for 1 year. Also, the response header can be added in the BeginRequest event handler:

protected void Application_BeginRequest(object sender, EventArgs e)
{
	protected void Application_BeginRequest(object sender, EventArgs e)
	{
		switch (Request.Url.Scheme)
		{
			case "https":
				Response.AddHeader("Strict-Transport-Security", "max-age=31536000");
				break;
			case "http":
				var path = "https://" + Request.Url.Host + Request.Url.PathAndQuery;
				Response.Status = "301 Moved Permanently";
				Response.AddHeader("Location", path);
				break;
		}
	}
}

HSTS header only works with HTTPS. So you can not observe it HTTP responses. That’s why redirecting to HTTPS is required. Because if the client uses HTTP all the time and if there is nothing forcing the user to use HTTPS, even if you have HSTS turned on, the client will never receive those responses. So at this point even if an attacker is redirecting the user to HTTP, the browser will always make the requests over HTTPS.

Shortcomings

  • Not all browsers support it. It works with Chrome and Firefox but not with IE and Safari
  • Cannot protect for the first call: If the user is making a call to a site for the first time and there is an attacker in between it wouldn’t work. At least it would start working after the attacker is finished with the attack and redirected the user back to HTTPS.

Resources

Electronics comments edit

If you’re going to do something you need the right tools. The last time I was dabbling with I didn’t quite enjoy it but that was mostly because I didn’t have the right tools. The most daunting part for me was cabling. With the jumper cable set I had it was just too painful.

Get your cables right

This time I’m prepared for all occasions: I have Male – Male, Male – Female and Female – Females cables which cover all combinations.

Cables

Essentials

Multimeter: I realized a multimeter is an essential tool if you want to build your own circuits. I added a reference for an excellent tutorial which teaches you how to use one in a few minutes. By using it, I was able to measure voltage and resistor values in no time.

LCD Display: Also, I think it’s almost impossible to build a cool project without a display. It can be used for debugging and informing the user so very helpful to have it in the mix. They are so cheap that I ordered 5 of them for about £7 on eBay. So I guess there is no excuse for not buying one!

Resistors: Real resistors! I hated my old blue resistors with 5 bands. I couldn’t read the values. Colors were not clear. But now finally I got a bunch of standard 3-band yellow ones which make it easier to read. (Actually I have a multimeter now, so I can just use it to measure the resistance anyway.)

Breadboards: I had a big nice one but I think in order to run things in parallel I’ll need a few more of the smaller ones. Also I’m trying to learn how to solder so I now have a couple of solder boards to practice. Push buttons: I think to control the flow they are definitely needed.

New Components

Let’s get crackin’

I just built a simple circuit to light a LED when the push button is pressed. I know it’s not impressive by any means but I enjoyed building something from scratch. Familiarized myself with my tool shed and looking forward to build more complicated things.

Resources

Gadget comments edit

Yes!!! Finally it arrived. It looks very tiny and elegant. This is what the package and contents look like:

Leap Motion

Installation

Good thing it comes with 2 USB cables of different lengths. One is bout half a meter, probably for laptop users and the other is about 2 meters, better suited for desktop users like myself. It doesn’t come with any instructions or user guide but a card that tells you to go to leapmotion.com/setup. And that’s where I went.

The setup file is about 56MB. As this is a new file, Norton 360 didn’t like it and issued a warning. Another interesting note is right after I plugged it in Skype icon started blinking. I checked the window and it was saying “Your video works fine”.  as I don’t normally keep my webcam plugged in, the video doesn’t work. But it became happy to detect a camera! I’m not sure if it would work with Skype though. Probably not.

App Store

After the installation you have to login to Airspace which is the app store for Leap Motion. I logged in with my LeapMotion account. It comes with a few free apps. Cut the Rope didn’t run unfortunately. Didn’t bother to give an error message either. So I tried the others. Lotus looks like a weird psychedelic app. As far as I could understand you make music with your fingers. Here’s a little demo of how it looks:

Development

What’s more interesting than downloading and using apps is developing one on my own! In order to do this you have to sign up for a developer account at: http://developer.leapmotion.com After the sign up you download their SDK. So far I managed to install the drivers, tried a few apps and downloaded the SDK and I’ll post the development experience in another post.

Final words

I’m not so sure if this gizmo will take off but as long as it’s an interface for the OS, not some custom apps, I don’t see much use for it. Keeping hands in the air for long tasks is tiring. I think it only might make some games more fun but that’s a very small market.

Hardware, Mobile comments edit

If you have an Android phone that supports NFC, you can save and change some settings very easily based on your location. All you need is some blank NFC tags like shown below, which are very cheap and a free app called NFC Task Launcher.

NFC Tags

NFC for the uninitiated

NFC stands for Near Field Communication. It’s a set of standards build on RFID that allows wireless communication between devices in a close proximity. It is commonly used in contactless payment systems.

Manage tasks with the task launcher

It’s a very intuitive and easy to use app. First, you select an action group (like WiFi On/Off, BlueTooth On/Off).

NFC Task Launcher

Then you configure the action (like Enable/Disable) and finally you touch to your tag to associate the task with it.

Secure your phone outside the house

After playing around with tools like WiFi PineApple I’m now even more afraid of wireless networks than ever. Thinking about it, I don’t connect to open networks on purpose and if my phone connects to one of them that means something fishy is going on. So why should I have WiFi when I’m not using my own? But of course turning it on and off every time I enter/leave the house is cumbersome. My solution is: doing it with NFC. Although it’s not completely automated, I just touch my phone to the red tag I stuck to my door when I leave the house and the WiFi is turned off. When I enter the house, I touch on the green one it is turned back on. Simple as that!

NFC Task Launcher

Resources

Fitness, Gadget comments edit

Although that’s a great song from Iron Maiden, the topic of this post is different. The topic is my new running gadget Garmin ForeRunner 10:

Garmin Forerunner

It is a simple and basic device but has all the functionality I need: Measure distance and time. In addition to that it comes with a software that allows you to upload your run info to Garmin Connect and you can keep track of your progress.

Once you install the software all you need to do is connect the device to your computer with the provided USB adapter and click Upload on the UI. It scans the device and uploads the new activities.

Garmin

Then you can go to Analyze –> Activities where you can analyze individual runs or you can go to your Dashboard to view your current status like your records etc.

Garmin

I’m definitely delighted with this gadget. It currently costs £84 on Amazon. Not the cheapest gadget and also there are free alternatives like RunKeeper app but it is still well worth the money. The only problem was it cannot determine the position very easily. I had a warm up walk for about 5 minutes and during this time it couldn’t pinpoint my location. Before I could start running I had to stand still for about a minute but once it finds it I didn’t have any problems during my run. Granted that I used it only one time as it came yesterday but my initial impression is that it is a great tool and very motivating for running. So highly recommended.

Gadget comments edit

I know they are about to release XBox One and they have already released Kinect for Windows but I still only have a Kinect for XBox for 360. I’m hoping to create a quick and sample application to test my Kinect and discover what’s in Kinect SDK so that I can remove it for a while from my gadgets-pending-to-be-tested-and-played-with list.

Until I buy a better and more recent hardware I just decided to utilise my existing sensor. I followed Channel9’s video tutorials and here is the code to display a video output of the sensor on the screen:

Xbox-360-Kinect-Standalone

Source code:

using System.Windows;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using Microsoft.Kinect;

namespace KinectDemo
{
	public partial class MainWindow : Window
	{
		KinectSensor _sensor;

		public MainWindow()
		{
			InitializeComponent();
		}

		private void Button_Click(object sender, RoutedEventArgs e)
		{
			
		}

		private void Window_Loaded(object sender, RoutedEventArgs e)
		{
			if (KinectSensor.KinectSensors.Count > 0)
			{
				_sensor = KinectSensor.KinectSensors[0];

				if (_sensor.Status == KinectStatus.Connected)
				{
					_sensor.ColorStream.Enable();
					_sensor.DepthStream.Enable();
					_sensor.SkeletonStream.Enable();
					_sensor.AllFramesReady += _sensor_AllFramesReady;
					_sensor.Start();
				}
			}
		}

		void _sensor_AllFramesReady(object sender, AllFramesReadyEventArgs e)
		{
			using (ColorImageFrame colorFrame = e.OpenColorImageFrame())
			{
				if (colorFrame == null)
				{
					return;
				}

				byte[] pixels = new byte[colorFrame.PixelDataLength];
				colorFrame.CopyPixelDataTo(pixels);

				int stride = colorFrame.Width * 4;
				image1.Source = BitmapSource.Create(colorFrame.Width, colorFrame.Height, 96, 96, PixelFormats.Bgr32, null, pixels, stride);
			}
		}

		void StopKinect(KinectSensor sensor)
		{
			if (sensor != null)
			{
				sensor.Stop();
				sensor.AudioSource.Stop();
			}
		}

		private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)
		{
			StopKinect(_sensor);
		}
	}
}

I think for this amount of code and the simplicity it is a pretty good result:

Video:

Probably I will continue my conquest of Kinect after I get the latest one, but for now I’ll just call it.

Resources

Gadget comments edit

I have a number of electronics kits and boards like Netduino, Arduino and Gadgeteer. Among all the cheapest one I’ve ever seen is Texas Instrument’s MSP430 LaunchPad. Last year I bough one of these just because it was so cheap but couldn’t spend much time on it. I recently found out it has dropped to £8 and I decided to buy the latest version. Now that I have 2 of these babies I definitely need to spend some time to play with them.

MSP 430

Looks like there are a few differences between the two. The one on the left in the picture above is the old version. The new version comes with better controllers and male pins on the side.

Installation

First thing to do is go to its official site and download Code Composer Studio to develop programs to run on it. It is an Eclipse-based IDE so I’m hoping it won’t take to long to get acquainted with it.

First Program

Good thing there are plenty of tutorials about it. I decided to follow one of the simplest ones which just blinks one of the on-board LEDs. To be honest, the code looks hideous:

unsigned int i = 0; 
void main(void)
{
  WDTCTL = WDTPW + WDTHOLD;     
  P1DIR |= 0x01;

  for (;;)
  {
    P1OUT ^= 0x01;
    for(i=0; i&lt; 20000; i++);
  }
}

I deleted the comments to keep it short. But even with detailed comments it looks hard to develop complex applications with this. Anyway, I’ll see how far I can get with it. So far, for this amound of work, the results are not so bad:

Resources

Gadget comments edit

After Raspberry Pi opened the way for low-cost small computers, a number of them started to show up in the market. One of the most recent ones gaining ground is BeagleBoard.

Beagle Logo

I just bought the cheap version which is called BeagleBone black which looks something like this:

BeagleBone_Black_Board

Nice looking little fellow, right?

Getting Started

I followed the getting started guide which you can find on the home page. Also the link in the resources section. When I plugged it in to USB it is recognized as a removable device. Then downloaded and tried to install the drivers as suggested but no luck. Apparently the drivers are unsigned and Windows 8 didn’t allow me to install them. Googling a little bit I found a step-by-step guide to change the machine settings so that unsigned drivers can be installed. I followed the steps and it worked like a charm. The original URL is in the resources section also the steps are below.

Troubleshooting

To be able to install drivers on Windows 8 follow the steps below:

1. Press win+c on the keyboard to bring up the charms side bar (or move mouse to right top corner of the screen)
2. Click the Settings button.
3. Click the Change PC Settings at the bottom of the sidebar.
4. On screen that shows up, select the General option from the sidebar then scroll down the page that appears.
5. Click the Restart now button under the Advanced startup section.
6. You will momentarily see the restarting screen, then it will switch to a blue screen titled "Choose an option"
7. Click the Troubleshoot button.
8. Click Advanced options.
9. Click Startup Settings
10. Click Restart
11. You should then see a Startup Settings screen after your computer reboots.
12. Press 7 or F7 on your keyboard to Disable driver signature enforcement
13. Now Windows 8 will continue starting up.
14. Log-in as normal, and then run BONE_D64.exe again
15. Now you should see 4 warning dialogs about "unsigned driver installation", click OK for all of them.

Results

So far so good:

BeagleBone Black

I can connect to the web server running by default. I’ll try to explore what I can do with it further. Let’s see what it is capable of.

Resources

Security comments edit

EMET

EMET (Enhanced Mitigation Experience Toolkit) is a free utility that helps prevent memory corruption vulnerabilities and is designed to prevent hackers from gaining access to a system. I haven’t used it before but according to the documentation the new features are:

  • Certificate Trust: Allows to detect Man in the Middle attacks that leverage fraudulent SSL certificates
  • ROP mitigations: Block exploit that leverage the Return Oriented Programming exploitation technique.
  • Early Warning Program: Allows enterprise customers and Microsoft to analyze the details of an attack and respond effectively.
  • Audit Mode: Provides monitoring functionalities for testing purposes.
  • Redesigned User Interface: Streamlines the configuration operations and provides accessibility.

Under the hood this tool performs very low-level operations and honestly I don’t know what they all mean. For me the biggest benefit was learning about advanced attacks and exploits and how to avoid them. I strongly recommend reading the user’s guide. Apparently there are many interesting attack techniques when it comes to exploiting the memory. EMET provides mitigation methods against these attacks. For instance:

  • Structured Exception Handler Overwrite Protection (SEHOP): This mitigation was shipped with Windows Vista SP1 but with EMET it can be used in previous versions as well.
  • Data Execution Prevention (DEP): This mitigation exists since Windows XP but applications need to be compiled with a special flag to opt-in to DEP. With EMET allows applications without that flag to opt as well.
  • Heapspray allocation: “When an exploit runs, it often cannot be sure of the address where its shellcode resides and must guess when taking control of the instruction pointer. To increase the odds of success, most exploits now use heapspray techniques to place copies of their shellcode at as many memory locations as possible.”
  • Mandatory Address Space Layout Randomization (ASLR): ASLR randomizes the address space of the loaded modules so that attackers cannot predict their locations. Like in DEP, EMET allows applications without the special flag to use this feature.

EMET

Certificate Trust

I was wondering about this feature as it’s more “visible” than the others. Basically what you do is define protected websites and pinning rules. Pinning Rule is essentially importing an SSL certificate’s thumbprint. Once you import it you create a protected website which is simply entering the domain name of the site and you associate the certificate with the site.

EMET

From now on if the certificate changes EMET displays a message like below:

EMET

Actually, I didn’t want to spend a great deal to test it first so the easiest way to test is running Fiddler (allow it to install it’s own certificate) and visit login.live.com. EMET comes with that site already configured so as soon as it notices the certificate doesn’t match its rule it fires the alarm. Looks like a nice feature but it only works with IE which I almost never use other than testing my code. Also I was expecting more fuss about it actually. It just displays the window for a few seconds and you can keep visiting the site. If for instance you were looking at something else in that brief period while the site is loading you may completely miss the fact you are being “Man-in-the-Middle”d. Overall, it’s better to have it than not having it at all I think.

Resources

Certification, SQL Server comments edit

This the Part 2 of my Study notes for 70-461 exam. You can find the Part 1 here

04. Aggregate Functions

  1. GROUPING SETS Using GROUPING SETS operator returns the same result set generated by using a GROUP BY, ROLLUP and CUBE operator. For example, this query generates the following result set:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total
     FROM Sales.SalesOrderHeader o
     GROUP BY GROUPING SETS ((), o.TerritoryID, YEAR(OrderDate))
     ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC
    

    Output: Query Output

    Source: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

  2. RANK / DENSE_RANK / NTILE / ROW_NUMBER These functions may be quite helpful for reporting queries.

    RANK – DENSE_RANK: These functions help to order rows by assigning an incrementing number based on the criteria used in ORDER BY clause. The difference between them is rank skips the next value when there are two of the same one and dense_rank doesn’t.

    Example:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     RANK() OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
    

    Output:

    Query Output

  3. NTILE Works like rank but it takes an integer parameter (n) and partitions the results to n-pieces. For example, the query below with NTILE returns 40 rows. As n is 4 in this example, the first 10 rows got rank 1 and 11 – 20 got rank 2 and so forth. Example:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     NTILE(4) OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
    

    Output:

    Query Output

    ROW_NUMBER: Just increments the rank no matter what. Example:

     SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
     ROW_NUMBER() OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
     FROM Sales.SalesOrderHeader o
     GROUP BY o.TerritoryID, YEAR(OrderDate)
     ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
    

    Output:

    Query Output

Source: http://msdn.microsoft.com/en-us/library/ms189798.aspx

05. Analytic Functions

LAG / LEAD: When I first saw these I instantly loved them! They prevent an unnecessary costly self-join to access previous and next rows in a result set.

So for example check out the following query and its output:

SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
LAG(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate)) AS PrevYear,
LEAD(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate))  AS NextYear
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
GROUP BY o.TerritoryID, YEAR(OrderDate)
ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC

Output:

Query Output

Notice that every row has the previous row’s current value in its PrevYear column. Except the first one obviously as it doesn’t have a previous year. And the same logic applies to the NextYear value with the difference that LEAD function is used to obtain those values.

Source: http://msdn.microsoft.com/en-us/library/hh213234.aspx

06. TRANSACTION TIPS

One important topic to grasp is transaction isolation levels. I had a few questions related to this topic. Also it is crucial to create high-performance queries. The isolation levels you need to pay attention are:

READ UNCOMMITTED: This one is most loose one as it allows dirty reads. But much faster than the others as it doesn’t lock the rows.

READ COMMITTED: It doesn’t allow reading uncommitted data. This is the default level.

REPEATABLE READ: Prevents dirty reads. Concurrent operations cannot modify or delete data.

SERIALIZABLE: This level takes it one step further: In  addition to REPEATABLE READ it prevents concurrent operations from inserting data too.

SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Source: http://msdn.microsoft.com/en-us/library/ms173763.aspx

Certification, SQL Server comments edit

As I mentioned before I like certification because it gives me a chance to cover all topics related to a subject no matter how trivial. During my preparations for this exam I learned some useful stuff. I believe they can be very useful in real-life scenarios. A compiled a list of items that I learned while studying for this exam:

01. Common Table Expressions

Not a new feature in SQL 2012 actually. I have used it a few times but I didn’t know its name back then. A Common Table Expression (CTE) is basically a temporary result set that you can use in the scope statement. For example

WITH DepartmentCTE (Name, GroupName)
AS
(
    SELECT Name, GroupName
    FROM HumanResources.Department
    WHERE GroupName = 'Executive General and Administration'
)

SELECT NationalIDNumber
FROM DepartmentCTE cte
INNER JOIN HumanResources.Employee e ON cte.Name = e.JobTitle

This is not a very good example because a simple join could be used instead. But for the demonstration purposes it should be enough to layout the syntax and usage.

Source: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

02. Pivot / Unpivot

I had one question about Pivot and that was just the definition of it so it was easy. Basically, pivot converts row values to columns. For example, take this query:

SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
GROUP BY o.TerritoryID, YEAR(OrderDate)
ORDER BY SaleYear ASC, o.TerritoryID ASC

which yields this output:

Query Output

Say we want to get all years as columns and the totals as the values in the row. Using pivots we can rewrite the query as follows:

SELECT 'Total Sales' AS TotalSales, [2005], [2006], [2007], [2008]
FROM
(
    SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SubTotal
    FROM Sales.SalesOrderHeader o
    WHERE o.TerritoryID = 1
) AS SourceTable
PIVOT
(
    SUM(SubTotal)
    FOR SaleYear IN ([2005], [2006], [2007], [2008])
) AS PivotTable;

and we get this resultset:

Query Output

Source: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

03. FOR XML

I think XML lost a lot of ground after the rise of JSON over the last few years but still it is used very commonly. I never used FOR XML in a real-life project but I was aware the Microsoft cares about it and not surprisingly I got 4 questions. It’s is very simple and intuitive actually. The keywords you need to know are: RAW, AUTO, EXPLICIT, PATH and ELEMENTS

Raw: Returns a single element for row.

Query:

SELECT TOP 1 *
FROM HumanResources.Department
FOR XML RAW

Output:

<row DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="2002-06-01T00:00:00"/>

Auto: Generates the name of the elements from the query

Query:

SELECT TOP 1 *
FROM HumanResources.Department
FOR XML AUTO

Output:

<HumanResources.Department DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="2002-06-01T00:00:00">

Elements: Returns the values in elements rather than attributes.

Query:

SELECT TOP 1 *
FROM HumanResources.Department
FOR XML AUTO, ELEMENTS

Output:

<HumanResources.Department>
<DepartmentID>1</DepartmentID>
<Name>Engineering</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>2002-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>

Path: Gives more control over the path of the nodes

Query:

SELECT TOP 1 *
FROM HumanResources.Department
FOR XML PATH('Department'), ROOT('HumanResources')

Output:

<HumanResources>
<Department>
<DepartmentID>1</DepartmentID>
<Name>Engineering</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>2002-06-01T00:00:00</ModifiedDate>
</Department>
</HumanResources>

Source: http://msdn.microsoft.com/en-us/library/ms178107.aspx