-->

security emet

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

dev certification, sql_server

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

  1. 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

dev certification, sql_server

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