Improved Google Ranking

Last fall, @aafromaa posted a tweet that said she couldn’t find my blog when she searched for Brain Webster. I responded that it wouldn’t have helped much if she had searched for Brian Webster because my blog was on the 6th page of Google Results. @gparmer took this as a personal challenge to raise my ranking.

By January, my ranking was up to number 19. Next time I checked was last week, and I was on the first page. I had a couple of other people check, and the results ranged from 4 to 6.

Here’s things that helped raise the ranking when searching for Brian Webster:

You’ll notice that very few changes were to the blog itself. Almost all came from other people and/or other sources.

Here are some take aways from this little experiment:

  1. It takes time to improve your Google ranking. It’s almost a year since @aafromaa‘s initial tweet.
  2. You can’t do it alone, much of the help I received came from other people.
  3. Use appropriate page titles. I changed the name of my blog because people are more likely to search for Brian Webster than Brian Webster’s. This is likely the biggest change that helped improve the ranking. (When you search Google without the ‘s, I’m currently number 6, with the ‘s I’m at 32.) I made this change because people are more likely to search for me without putting in the ‘s.
  4. Get quality links to your page/site. From the above list, the quality links to my blog are probably my staff page, and Greg’s Bookmarks. The reason I say this is because these .edu sites probably have a higher quality rating from Google, therefore they are higher quality links. Of all the links, the one from my staff page is probably the best because it’s coming from another page about Brian Webster (Sorry Greg)
  5. Don’t fall into the trap of “link farms“, you can do it with relevant links.
  6. Very few of the changes where to the blog itself, most were from other sites and other people.

I had a slight advantage because it was obvious what search term I was trying to improve on, my name. It would have been a lot harder to get to the first page for terms like “child development” or “soybean rust”.

[Update – 8/21/2008:  Here are some ways to Find Links To Your Site]

Advertisements

Resetting the Identity Counter in SQL Server

Sometimes we want to reset the autoincrement identity field in an SQL database table. This might be done when deleting test data from a database, and moving the database to a production server. When doing this, it is nice to have the identity field start at 1 (one) again.

The SQL Server command to do this on a table called “tblUsers” would be:

exec DBCC CHECKIDENT (tblUsers, RESEED, 1)

This works when there are no rows in the table. If there are existing rows in the table, the reseed value has to be equal to of greater than the maximum existing identity value in the table. In this case, the next identity value would be the reseed value + 1.

More information can be found at the DBCC CHECKIDENT page from MSDN.

SQL Calls with Returned Value

In my last post, SQL Calls from ASP.NET, I talked about how to make a call to a stored procedure to query a database. But what do you do when the stored procedure uses an output parameters, such as SQL Stored Procedure: Save Data. Here is a sample method for making this database call:

using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

const string DBConn = "MyConnectionString";
public static int User_SaveProfile(int userID, string userFirstName, string userLastName)
{
Database db = DatabaseFactory.CreateDatabase(DBConn);
DbCommand cmd = db.GetStoredProcCommand("UserSave");

db.AddOutParameter(cmd, "@NewID", DbType.Int32, sizeof(Int32));
db.AddInParameter(cmd, "@UserID", DbType.Int32, userID);
db.AddInParameter(cmd, "@UserFirstName", DbType.String, userFirstName);
db.AddInParameter(cmd, "@UserLastName", DbType.String, userLastName);

db.ExecuteNonQuery(cmd);

return (Convert.ToInt32(db.GetParameterValue(cmd, "@NewID")));

}

Again, this is fairly straight forward code. It’s creating a Database object using the Database Factory, then building the a Database command with the appropriate parameters.

Once this is done, it makes the call to the database. This time however, it uses the method ExecuteNonQuery, which doesn’t return anything from the database, other than the output parameter of course. Lastly, it converts the output parameter into an integer, and returns it.

With this, the calling method can handle the returned value. If the stored procedure does error checking, it would be reflected in the @NewID that was returned. The calling method would then handle the error. Also, if zero (0) was passed in as the userID, and the returned @NewID is a positive number, a new record was added to the database. And if the @newID is equal to the userID, then an existing record was modified.

SQL Calls from ASP.NET

When I started working in .NET, one of the things that confused me was the process to make SQL calls from ASP.NET. The basic process was:

  1. Create a SqlConnection object
  2. Give the SqlConnection a connection string
  3. Create a SqlCommand
  4. Give the SqlCommand the actual SQL code
  5. Bind the SqlConnection to the SqlCommand
  6. Open the SqlConnection
  7. Execute the SqlCommand
  8. Close the SqlConnection

An example of how to do this can be found at ASP.NET Tutorials. While this works, I thought it seemed like a lot of work just to make a call to a database.

Then I heard about the Database Factory that is part of the Microsoft Enterprise Library 2.0. (yes, I know that there are newer versions, but this is the version I’ve used.) I’m only using a very small part of this library, but it sure makes database calls easy.

Here is some sample code that would call the stored procedure from Case Statement in SQL Query

using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

const string DBConn = "MyConnectionString";

public static DataTable GetRooms(int roomID)
{
Database db = DatabaseFactory.CreateDatabase(DBConn);
DbCommand cmd = db.GetStoredProcCommand("GetRooms");
db.AddInParameter(cmd, "@RoomID", DbType.Int32, roomID);

DataSet ds = db.ExecuteDataSet(cmd);
return (ds.Tables[0]);
}

Much simpler code. What’s happing here is that we have a connection string called MyConnectionString, and we use it create a Database object. We then give the database object the name of the stored procedure. We give it the parameters we want to pass, then we execute it. This returns a DataSet to us. From this DataSet, we grab the first table (ds.Table[0]), and return this table.

To me, this is a much easier way to make the database call, much easier than building the SqlConnection and SqlCommand, and doing all that in the correct order.

Also note that because we’re using an integer parameter to pass the roomID into the stored procedure, we’ve pretty much eliminated the possibility of this sql command being successfully targeted by a SQL injection attack.  See SQL Injection Prevention: Stored Procedures

SQL Stored Procedure: Save Data

I’ve been a fairly regular attendee of the Iowa Dot Net User Group. One of the first speakers I saw there was Doug Nelson, a developer out of Minneapolis. This tip comes from one of the side conversations of his talk that night. Although it was not part of his main topic, this has proved to be a very useful tip.

He said that from an application’s standpoint, there isn’t a lot of difference between inserting a new record in a database, or updating an existing record. In either case, you are saving a bunch of data to a database, therefore you can accomplish both tasks in one “save” stored procedure. Here is some example code:

CREATE PROCEDURE [dbo].[UserSave]
(
  @newID int output,
  @userID int = 0,
  @userFirstName varchar(50),
  @userLastName varchar(50)
)
AS
BEGIN
  IF NOT EXISTS ( SELECT null FROM tblUsers where UserID = @userID)
  BEGIN
    INSERT tblUsers
    (userFirstName, userLastName, InsertedDate )
    VALUES (@userFirstName, @userLastName, getdate())

    SET @newID = scope_identity()
  END
  ELSE
  BEGIN
    UPDATE tblUsers
    SET userFirstName = @userFirstName,
    userLastName = @userLastName,
    UpdatedDate = getdate()
    WHERE userID = @userID

    SET @newID = @userID
  END
END

Basically, the first thing this stored procedure does is check if there is a record with with the userID that is passed in. If not, it inserts a new record. If the userID does exist, it updates the information for that userID. It then returns the userID of the inserted/changed record through an output parameter.

Of course, this is a stripped down version of the stored procedure. In production, I would put in at least one additional check. If you passed in a userID, and that userID didn’t exist, I’d set @newID to an error code (such as -1). I’d then let the calling application handle this error situation.

The advantage of writing a stored procedure using this method is that all the database save logic is in this one place. If for some reason, the database structure changes, you change both the insert and update query in this one location.

SQL Injection Prevention: Stored Procedures

There are lots of things that can be done in a web application to help minimize the threats of SQL Injection attacks. There are also some techniques that I use on the database server to reduce the threat.

First, I try to minimize the amount of sensitive data we store. For example, we are not allowed to store credit card numbers. If we do e-commerce on our servers, we only store the transaction number that we get back from the campus’s e-commerce system. Of course, social security numbers are never allowed either.

Next, I use a different user account for each database on the server, and each account only has access to a single database. This way, if for some reason, someone gains access with that account, they can only mess with one database.

The next thing I try to do is have all database interactions go through stored procedures on the database server. When doing this, we do not give the database user any read/write privileges to the database tables. We simply give it rights to execute the relevant stored procedures.

Another advantage of the stored procedures is that all data is passed into them through parameters. Because the parameters are encoded when they are passed in, it greatly reduces any chance that any attack can be successful. The data still needs to be verified that it looks like valid input, this just adds another level of protection.

A side benefit of using stored procedures is that I can modify the stored procedure without having to change the application that uses that. This works as long as the structure of the input/output doesn’t change. As an example, there have been a couple times when I’ve had to change the order of things on web pages on our productions server. I’ve been able to do that by changing the “order by” clause in the select statement of the stored procedure without changing the web application at all.

A down side of using stored procedures is that it may take a little extra time to set them up, and code the application to use them. However, we use a tool in .NET that makes writing against the stored procedures pretty easy. More about this in an upcoming post.

Although they may take a little extra time to implement, I feel the added security offered by communicating with the database through stored procedures is well worth the effort.

See CASE Statements in SQL Query for a method to write flexible stored procedures.

I’ve Been Tagged

My friend, John Dorner, tagged me last week week. Thanks John. Usually I don’t follow through with things like this, but this time I thought I’d make an exception. After all, I enjoyed reading posts from others that have gotten tagged:

Five snacks I enjoy:

  1. White Chocolate
  2. Peanut Butter Cookies
  3. Other Types of Cookies
  4. Rhubarb Desserts
  5. Salted Peanuts

Five Things on My To-Do list today:

  1. System to handle audio/video/podcasting
  2. Revamp the county CMS application
  3. Experience a Jimmy Buffett Concert, too late to see the Greatful Dead
  4. Get into shape, run/Jog
  5. Build a tall case clock and tv stand

Five Things I would do if I were a billionaire:

  1. Large donation to church/synod/Lutheran Schools of America
  2. Other charity opportunities
  3. Pay off debts for friends/family
  4. Travel more, just try to keep me away from beaches/mountains
  5. Cry (I won’t want the troubles, don’t even play the lottery)

Five jobs I have had:

  1. Farm laborer
  2. Electrician’s assistant
  3. Network/System Administrator
  4. Rigger/Spotlight operator (I like telling stories about this)
  5. Web Developer, interests in usability, accessibility, and SEO

Five random things:

  1. I was raised by my aunt and uncle. But don’t even try to tell me that they aren’t my Mom and Dad. As for my cousins, they are my brothers and sisters. “Family” is much more important than biology.
  2. I’ve easily seen over 200 concerts/shows, and have over 100 concert t-shirts, and close to 150 back stage passes. I’ve paid to see less than 5 of these shows. (see jobs I’ve had)
  3. I attended a one room school. K-5th grade, I was the only kid in my grade. A new family moved to the neighborhood, and my class size doubled for grades 6-8. Going to high school with about 180 in my class was a big shock.
  4. My niece and nephew (ages 13 and 11) race go-carts. Whenever possible, I’m a member of their “pit crew”. It’s more work then you’d think.
  5. I’ve always loved music, and wish I could play. Anyone give piano, guitar, or drumming lessons?

Five of my bad habits:

  1. Caffeine in the form of diet soda
  2. Use inbox/deleted mail as file cabinets
  3. Work too much
  4. Jump from task to task, then back again
  5. Shyness

Five places I have lived:

  1. Born in Ames
  2. Farm by Saude, IA (is that on any maps anymore)
  3. Ames, IA

Five people I would like to get to know better (yes, this means you are tagged!):
Now go and post your lists of 5.

  1. Floyd
  2. Deb
  3. Lynette
  4. Kevin
  5. Matt

Honorable mention goes to some people that have already been tagged:

  • Greg (also tagged by John)
  • Vince -(also tagged by John)
  • Becky (Also tagged by Anne)

In addition, I’ve encouraged a number of people to start blogs. I really wanted to list names, but I won’t. This time:-)

SQL Injection Protection: Verifying Data

Whenever we take input on a web page, we should verify the data to help prevent SQL injection attacks. Remember that query strings in URLs  input data, as do fields on a web form. Either of these input methods can be exploited in an attack.

There are a number of things that we can check depending on how the data will be used. Following is a list of some of the things that we may wish to check.

  • For inputting numbers, convert the input string to a number before using it, even if it has to be converted back to a string to use it. More on this at SQL Injection Prevention: ID’s in the Query String
  • The same thing applies to other data types, such as Dates, Times, etc.
  • For inputs with a maximum number on characters, truncate the input string to that number of characters. For example, if the maximum length of a valid data string is 50 characters, truncate the string at 50 characters.
  • For inputs that will later be used as html output, strip unwanted tags. Things I’ve heard recently say you should have a whitelist of safe (allowable) tags, and you should strip all other tags.
  • May want to check for imbedded SQL type commands such as INSERT, DELETE, UPDATE, and CAST
  • If you use a string to build a SQL statement, you should encode that string. An easy way to do this is to use parameters in the SQL statement, as the parameters encode it for you.
  • When making database calls, don’t continue if you get results you don’t expect. For example, a query to check a username/password combination should return no more than one record. If it returns more, something is not right.

Another thing to remember is that you should not rely on client side data verification, you should also do server side verification. One reason for this is that the user may not have scripting turned on, in which case, the client side verification will never run. This YouTube video demonstrates another reason why only using client side verification is a bad idea.

SQL Injection Prevention: ID’s in the Query String

In the past year or so, I’ve helped solve three or four problems with data on a web site site being inserted/changed through SQL injection. Not a fun thing to have happen.

I used to think of SQL injection attacks coming through web forms, where people try to enter things into forms that cause the attacks. This can be a very dangerous type of attack, because in some cases, it will allow the attacker to log in as an administrator of the web app, therefore being able to see/do whatever they want in the application.

However, the attacks I’ve seen have all come through query strings, on pages that have URL’s like webapp.myserver.com/showpage.cfm?ID=8 What they do is simply add a semicolon ( ; ) to the end of the URL, then append SQL commands to it. If the application simply uses the query string variable “ID” to build a SQL command, the application is vulnerable.

The cases I’ve worked on have all been old, legacy code, and have been in two or three different programming languages. Bad code can be written in any language. These applications were written back when we didn’t hear much about SQL injection, therefore application developers didn’t always practice good programming practices. In fact, one of the applications I helped fix recently was originally written by a commercial firm that did some programming for one of our departments.

In each case, the way I fixed the problem was to simply convert the ID to an integer, then used this integer to build the where clause of the SQL command. By doing so, real requests for the web page work just fine, and one of three things happen the bogus hits come in:

  1. The server throws an error because the ID can no longer be converted to an integer.
  2. The conversion returns a default integer (such as zero), in which case you probably don’t have a ID=0 in your database, giving some kind of page not found message.
  3. The first part of the string is converted, up until it finds a character that is not a digit. In the example above, it would return the page where ID=8.

In all these cases, converting the query string variable ID to an integer, prevents the SQL injection attacks from being successful because it doesn’t pass the whole ID string along to the database.

Server Side vs Client Side Comments in ASP.NET

Last week, I was asked to make a change to the name of a channel (folder) in an application using our Content Management Server (CMS). After I did this, I went to their home page and received the dreaded yellow error screen of ASP.net.

After making this change, the application was throwing a null reference exception from a user control that was within comments in the .aspx file. This user control was trying to do something with the old channel name, but was no longer able to find it.

But why was this an issue at all? After all, the code was commented. Yes it was, however it was using client side (HTML) comment “<!– HTML comment –>“, instead of an ASP.NET comment “<%– ASP.NET comment –%>“.

Because “<!– –>” is a client side comment, the browser does not display whatever is in the comment, however the server still handles this code and sends it to the browser. If you have things like server controls, users controls, etc in this type of comment, the server still runs these controls.

As an example, consider this simple aspx file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Test Page</title>
</head>
<body>
  <!-- HTML Comment -->
  <%-- ASP.NET Comment --%>
  <p>Test</p>
</body>
</html>

When you do a view source in your browser on this page, you get:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Test Page</title>
</head>
<body>
  <!-- HTML Comment -->
  <p>Test</p>
</body>
</html>

Notice that the HTML comment does get sent to the browser, however the ASP.NET comment does not. The server ignores everything embedded in the the ASP.NET comment.

On the page I was looking at last week, this caused two major problems. First, we were sending code to the client that wasn’t really necessary. What was commented out was HTML code that the browser would not display, therefore it added a little to the download time for the page.

A bigger problem was this section of code had a couple of user controls in it, and these user controls took a few seconds to run each time this home page was hit. Therefore this was a very slow page to load, and the slowness was caused by the server generating an HTML comment which the user never saw. When I changed this, the load time for the page was greatly improved.

The moral of the story is simple. If you are writing a comment that should be seen in the HTML code, then go ahead and use the client side HTML comment code. However, in many cases, you don’t need/want the user to be able to see the comments. For these cases, you should use the ASP.NET comment code “<%– –%>”, which will cause the server to skip this code. In certain cases, this small change can have a dramatic effect on the performance of your web page.

For more information on this, see Scott Guthrie’s post: Tip/Trick: User Server Side Comments with ASP.NET 2.0