Deleting duplicate rows in SQL Server

by Abe Miessler 27. July 2010 18:03

I came across this very slick method for deleting duplicate records while poking around on StackOverflow today.  I've used a variety of methods in the past for deleting dupes, but nothing quite as clean as this.

The SQL below would work for a table in the following format, you'll need to adjust as necessary.  Please note that this will only work if id is a Primary key.

 

MyTable: id, Col1, Col2, Col3

 

And the SQL:

 

DELETE MyTable 
FROM
MyTable
LEFT OUTER JOIN
(
   SELECT MIN
(id) as id, Col1, Col2, Col3
   FROM
MyTable
   GROUP BY
Col1, Col2, Col3
) as KeepRows ON
   
MyTable.id= KeepRows.id
WHERE
   
KeepRows.RowId IS NULL

Hope this helps someone!

Tags:

SQL | StackOverflow.com

Implementing a sequential pulsate effect using jQuery.

by Abe Miessler 22. June 2010 00:58

I was recently working on a webpage where I wanted to have a collection of tabs at the top of the page pulsate one after the other.  Had to do a bit of digging around (and get some help from a friend of mine who happens to be a jQuery expert) so I thought I would post it up here incase anyone else runs into this.  Once it was done it was remarkably little code, yet another example of why I'm falling in love with jQuery and jQuery UI.

<html>
    <head>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
        rel="stylesheet" type="text/css" />

    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>

    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
        <script>
              $(document).ready(function () {
                  $("#myTabs").tabs({ fx: { opacity: "toggle"} });
                  recurivePulsate($("#items").find("li")[0]);
              });

              function recurivePulsate(elem) {
                    $(elem).effect("pulsate", { times: 1 }, 500, function () {
                        recurivePulsate($(elem).next());
                    });
                }
        </script>
    </head>
    <body>
        <div id="myTabs">
            <ul id="items">
               <li><a href="#first">First</a></li>
               <li><a href="#second">Second</a></li>
               <li><a href="#third">Third</a></li>
               <li><a href="#fourth">Fourth</a></li>
            </ul>
            <div id="first">Data for First!</div>
            <div id="second">Data for Second!</div>
            <div id="third">Data for Third!</div>
            <div id="fourth">Data for Fourth!</div>
        </div>
    </body>
</html>

Tags:

jQuery

Proper input length validation.

by Abe Miessler 3. March 2010 09:07

When creating a web application that accepts input from users one important step to ensure that you application is secure, is assigning a maximum length to user input.  Keep in mind that input length validation is just the tip of a very large iceberg when it comes to web application security, but it is a good first step.  It's safe to say that if someone is trying to put in 1200 characters for their first name that something fishy is going on.

As much as I love ASP.NET I've found that it can easily lull some programmers into a false sense of security when it comes to input length validation.  To demonstrate what I am talking about I have put together a small application that will show how simple it is to pass longer than expected input to an application that would appear (to the uninformed programmer at least) to limit input length.

To begin I have created the basic aspx page below.  Notice the MaxLength attribute that is highlighted in red:

This is where our security troubles often begin....  It seems like this MaxLength is telling us that it won't let anything over 9 characters get passed to our beloved server.  Don't believe it!  IT'S LYING!!!  To understand why the MaxLength attribute dosn't do everything it claims it does lets go ahead and run out application and take a look at the rendered HTML:

As you can see, our seemingly invincible asp:TextBox control has been rendered as a standard html input with a maxlength of 9.  This is bad because this on the client's side, so the client can change whatever they would like and send it back to the server.  To show you what i'm talking about I am going to make use of a neat application called Burp Suite.  Burp Suite is a collection of tools that can be used for attacking web applications.  If you are at all interested in web security I recommend you download it and check out some of it's features.  For the porpouse of this post we will be using Burp Proxy, which is a HTTP Proxy server that allows you to intercept, inspect and modify the raw http traffic being passed in both directions. 

First I will go to the webpage with the vulnerability without intercepting anything.  I attempt to add more than 9 characters and, as advertised, I am not able to. 

Now I will begin capturing http traffic and submit the search text to the server.  This is where things get interesting...  Notice the red box in the image below:

So at this point burp suite has captured this HTTP request, showed me what params are in it (as well as letting me look at and mess with a whole lot of other info in the request).  It is basically in a holding state at this point until I have modified what I want and decide to forward it to the server.  So next I will change the value of tb_SearchBox and forward the request.  See below:

As you can see i've added what ever I wanted to the value of tb_SearchBox and this will now be passed to the server when I click the "forward" button.

Alright... so now that we have an idea of why this is a vulnerability how do we fix it?  The answer is by adding server side input validation.  I've found that the simplest way to do this is to compare the text length to the MaxLength of the TextBox control.  If the former is longer than the latter then your website is under attack.  Period.  What you do at that point is up to you but I recommend at a minimum doing what the comments in the IF statement from the image below suggest.

 

I hope this helps to make your website just a little more secure.  Remember, always be wary of built in ASP.NET validation and when in doubt, go ahead and check it on the server side.

 

 

 

 

 

Tags: ,

web application security

Adding reCAPTCHA to your BlogEngine.net site.

by Abe Miessler 3. March 2010 07:15

After being hit by a barage of spam comments I decided it was time to look at adding CAPTCHA to AbeMiester.com

NOTE TO GOOGLE: I am in no way affiliated with any organizations trying to buy gold, sell miniture bicycles(wtf?) or hawk a natural cure for acne.

I did some research and finally settled on reCAPTCHA for my anti-Bot needs.  reCAPTCHA is a free CAPTCHA service that actually helps to digitize aging books, newspapers and radio broadcasts for future generations.  The way they do this is actually quite facinting and I recommend you read more about it.

While the process of adding reCAPTCHA to your average website is quite simple, I found that adding it to BlogEngine.NET was fairly difficult.  Luckily for me Filip Stanek did an excellent post on integrating reCAPTCHA into your BlogEngine.net website.  He provides instructions on how to do it quick and dirty as well as details as to exactly what was changed.

If you've been having spam comment problems on your BlogEngine.net site I'd highly recommend you looking into adding reCAPTCHA!

Tags: , ,

spam | bots | CAPTCHA

Setting up your testing environment when performance tuning SQL Scripts.

by Abe Miessler 11. January 2010 04:22

When testing the performance of SQL Scripts it is important to make sure that every time your run something you are starting from scratch.  It is deceptively easy to make a change to a SQL Script and believe that it has dramatically improved performance, when in reality you are simply reusing the execution plan or data that is stored in cache.  For almost every situation it is best to test your scripts against the worst case scenario, which means no cached execution plans or data.  To ensure that any improvements I make are really because of improvements I made, I like to drop these lines before any test scripts I am running:

 

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

-- Your SQL begins here

 

Be sure that you use these carefully as it will affect the whole server.

CHECKPOINT writes all dirty pages to disk.

DBCC DROPCLEANBUFFERS will ensure that you are testing against a cold cache which is going to be your worst-case scenario when running a query.

DBCC FREEPROCCACHE will clear out your execution plan cache if you are testing against a stored procedure.  You can have a little more control over what is removed from the execution plan cache by passing the appropriate parameters to DBCC FREEPROCCACHE.  More information on that can be found at MSDN.

 

Tags:

Detecting database locks and viewing running processes using Adam Machanic's WhoIsActive script

by Abe Miessler 17. December 2009 06:37

Ran into an issue today where I was trying to track down a mysterious lock on one of the Databases I was working on.  After banging my head against the keyboard for a bit I was directed to Adam Machanic's WhoIsActive Script and the day was saved.  I'll do a quick demo to show you some of the more basic features but I'd recommend taking a look at it in detail.

 

Say we have an evil SQL Script like the one below:

Begin Transaction t1
while 1=1
BEGIN
    update Person.Address set AddressLine1 = '1970 Napa Ct.'
    WHERE AddressID = 1
END
Commit Tran t1

Once this bad boy starts executing it will lock that record to all updates until it completes (so forever).  Now lets suppose someone else comes along and tries to run the query below (notice the red boxes):

 

 

At this point you realize that something has gone horribly wrong...  But not to worry, once you've installed the WhoIsActive stored procedure you can quickly identify which processes are running and which ones are causing blocks.  Lets take a look at the results of EXEC sp_WhoIsActive below:

After a quick look at the results we can see that the blocking_session_id is 55.  Once we know that we can either kill the session or wait it out.  Luckily we can also take a look at the SQL that is executing because it is in the sql_text column, so you will know that waiting it out would be a lost cause.  I recommend downloading the WhoIsActive script and trying it out for yourself!

Tags: , , ,

Performance Tuning | SQL

.NET String vs StringBuilder

by Abe Miessler 8. December 2009 03:44

A common mistake made by new programmers is to not utilize the StringBuilder class in the .Net framework.  To begin understanding when we should use StringBuilder let's first look at some of the inner workings of strings.

The most important thing to know about strings is that they are immutable.  That is, once you have created a string you can never change it.  It will never get longer, shorter or have any of its characters changed.  This is a difficult concept for many people to understand.  Lets look at the code below:

string Name = "Abe";

Name = Name + "miester";

While it might appear that you are actually just adding "miester" to the end of "Abe" in order to update the existing string what is happening in the background is actually much different.  When you assign the new value to the Name variable two entirely new strings are created.  There are now actually three strings in memory: "Abe", "miester" and "Abemiester".  The strings "miester" and "Abe" are no longer needed and the garbage collector will reclaim their memory at the next collection.

Now that we have an idea of how strings work lets look at a situation that could be improved by the use of the StringBuilder class.  Lets look at the code below:

 

    string letsCount = "Let's count! ";

    for(int i=; i< 100; i++)

    {

         letsCount += i + ", ";

    }

    Console.WriteLine(letsCount);

 

The above code will create a string that counts from 0 to 100.  Unfortunately, since we are using the string class, a new string will be created on the heap every iteration.  To avoid this we can use the StringBuilder class.

The StringBuilder class is provided as a means to dynamically construct a string in an efficient manner.  A StringBuilder object represents a string as an arrary of Char structures, each Char can have it's value changed and the array can be shrunk.  If the string grows beyond the allocated array of characters, StringBuilder automatically creates a new, larger array, copies the contents over, and begins using the new array.  It should be noted that dynamically growing your StringBuilder contents hurts performance so you should do your best to set a good initial capacity.  When you are working with a String who's value changes frequently this is much more efficient.  Lets look at the code below:

    using System.Text;

    StringBuilder letsCount = new StringBuilder("Let's count! ", 150); //Initialize the string to "Let's count!" and set the initial length to 150

    for(int i=; i< 100; i++)

    {

         letsCount..AppendFormat("{0}, ",i);

    }

    Console.WriteLine(letsCount.ToString());

 

This method is much more efficient because we are not creating a new string on every iteration.  In situations where you are dynamically adding to a string you should try to use this method whenever possible.

 

Update:

After talking with some of the folks at StackOverflow.com I found out that the fastest way to do string appends is actually to use the String Join method.  While StringBuilder does offer an improvement, Join performs even better.  Take a look at this post for more details on why.

 

Tags:

ASP.NET | StringBuilder

ASP.NET MVC "Hello World" tutorial.

by Abe Miessler 6. December 2009 22:12

After listening to one of my coworkers rave about how wonderful MVC was for the last week I decided it was time to cut my teeth with the ASP.NET MVC 1.0 framework and find out for myself if it was really all it was cracked up to be.  Below you will find step by step instructions to creating your first basic ASP.NET MVC “Hello World” application.


To begin you must download the ASP.NET MVC framework which can be found here.


Once this has installed open Visual Studio and create a new ASP.NET MVC Web Application:



Next you will be asked if you would like to create Unit Tests for this application.  Since this is beyond the scope of this tutorial we will select “No” and continue.


We now have our base MVC Project and we will begin adding functionality.  Let’s start by adding a model to the project.  To do this right click on the “Models” folder and select Add->Class and name it “Person.CS”.



In our new class we will create a new string member called “Name”.



Next we will add a controller class called HelloWorldClass.cs.  To do this, right click on the “Controls” folder and click “Add New Item”.  In the Add New Item dialog box select “MVC Controller Class” and name it “HelloWorldController.cs”.  As you continue through this tutorial you will probably notice that this Controller contributes little to the project.  While we could get away without having a Controller for a project that is this simple, I wanted to introduce the concept to illustrate how Models, Views and Controllers interact with one another.



Now we will reference the Person model from the HelloWorldController and add a function called “SayHello”.  When you are done your HelloWorldController should look like this:



The “HelloWorldController” now has a function that will accept a string called “username” and return that to the View.  In order to complete this process we must now create a View that will display what is returned by the “SayHello” function.  To add our view you must first right click on the “Views” folder and add a new folder called “HelloWorld”.  Once created right click the folder and select Add->New Item.  In the “Add New Item” dialog select “MVC View Content Page” and name it “Greet.aspx”.



Next you will be prompted to select a master page.  The default one will work fine for our project so just click ok.



Now we will add some simple code to display a greeting to whatever name is in ViewData[“PersonsName”].  When you are finished your page should look like this:



The last step we need to take is also the step that I found the most confusing.  In the global.asax file we must update the RegisterRoutes method to map to our new page with the appropriate parameters.  For simplicitiy sake I will just provide the code to enter in but if you would like to see Routing explained I have a post that goes into more detail here.


To setup routing for this project we will add the following code BEFORE the default route that is already in the RegisterRoutes method.


            routes.MapRoute(

                "HelloWorld",

                "HelloWorld/{action}/{userName}",

                 new

                 {

                     controller = "HelloWorld",

                     action = "SayHello",

                     userName = "Mystery Man"

                 }

            );


When you done it should look like this.



Now it is time to see the fruits of our labor.  Run your project for the first time.  You should see the following page:



Now you must enter a URL that will be routed to our newly created page.  To do this enter “HelloWorld/Greet/Abe” after the URL above.  When you do you should see the page below:



Congratulations! You have created your first ASP.NET MVC page!

 

Conclusions:

While I had originally setout to find advantages of MVC vs Forms I feel that this tutorial really only served to introduce me to using the MVC framework.  Now that I am familiar with MVC I hope to do another tutorial that demonstrates (or dosn't) the advantages that MVC has over Forms.  Stay tuned...

Tags: , , ,

Blog | ASP.NET | MVC

First Post!

by Abe Miessler 2. December 2009 04:22

My name is Abe Miessler and this is my website.  I am a software engineer specializing in the .NET framework and SQL Server.  I've created this site as a motivation for me to learn more about the technologies that I am comfortable with, learn more about technologies that I know little or nothing about and maybe even help a person or two along the way.  Welcome.

Tags:

Powered by BlogEngine.NET 1.6.0.0

About the author

Abe lives with his beautiful wife Jessica and their cat Molly in Sacramento California.  He enjoys outdoor activities, anything that has to do with technology and playing chess.