Archive for the ‘Development’ Category

Does Design Matter in Web Development

Last week, I was asked to make a change to the sign up form on the “Spend Smart. Eat Smart.” web site. After making this change, I was asked why I took the red bar out of the footer. My response, the change I made  should not have effected the footer.

After digging into the problem, we discovered that the change that had cause the red bar to go away had actually happened 6 days prior. This means that the design was broken for almost a week before anyone noticed. If someone had noticed, it wasn’t important enough for them to contact us. And this is a site that gets fairly regular updates, at least a couple each week.

When people come to us to develop a new web site, the conversation is usually focused on how the home page will look. It seems to me like we should spend a lot more time on content, and less on design. I’d love to work on sites where we talked about the content pages first, and then how that content flows up to the home page, rather than the other way around.

Most of the articles I’ve found (search for web design vs content) say that design is important in that a site should look professional, and should not distract from the content. They also say that content is what usually drives people to your site, and the content on your site is what people want to see.

To be clear, I am not picking on the Spend Smart. Eat Smart. web site. I think they do a better job with content than do many of the sites we work with. I especially like their related blog, and that they are using blogs posts as content on their main site. We are trying to duplicate these concepts on a Managing Tough Times web site, which is in the middle of a soft launch, with the hard launch coming in a couple of weeks.

Do your web sites incorporate the right balance between design and content?

FireFox Add-on – Web Developer Toolbar

The Web Developer Toolbar is the reason I started using FireFox. A couple years ago, I was just getting started with cascading stylesheets (CSS) and web standards, then Matt Heerema showed me this tool. I was hooked.

Web Developer Toolbar inserts an additional toolbar in Firefox. One of the cool things it lets you do is change the CSS your browser uses to display a page. To do this, select the CSS menu in the toolbar, and select edit. You can then change any of the styles on the page. This is much easier than changing the stylesheet on your web server and reloading the page.

I use this feature almost daily when working on web pages. Once I get something working in my browser, I’m able to copy the CSS, and paste it into the real stylesheet on the server. Not until this step will other people see the changes you’re making.

It allows you to do other things as well, such as disable javascript, disable images, and disable stylesheets. By taking these three steps, you are able to get an idea of what GoogleBot will see when it accesses your page.

Another thing it will do is outline certain elements on the page. For example, you can have it outline tables, or depreciated html tags, or a host of other elements. This can be invaluable when helping others troubleshoot issues on their web pages.

The last feature I’ll mention is the tools menu, where you can do things like verify that your CSS is valid, that the HTML is valid, and that your page passes Section 508 validation. You can also get a speed report for your web page.

I’ve just scratched the surface of what the toolbar can do for you. If you work on web pages, either as a developer or designer, I think you’ll find the Web Developer Toolbar has many useful options for you.

Windows Client for Subversion – TortoiseSVN

The client was the biggest adjustment I needed to make when switching from Visual SourceSafe (VSS) to Subversion. A VSS client was integrated with Visual Studio, which meant you could do most things related to source control through Visual Studio.  So when I it started looking at Subversion, the first client I tried integrated into Visual Studio, but I couldn’t get it working properly.

I ended up installing TortoiseSVN, which adds the source control functionality to Windows Explorer. I was skeptical because I though it would be a pain to go to Windows Explorer whenever I wanted to interact with Subversion, but I was wrong. I quickly got used to working with Subversion in this manner.

Probably the biggest advantage of working with TortoiseSVN is that I can have multiple working copies of a project. For example, I can have a copy on my development machine, on our test server, and our production server. When I want to see what has changed between the repository and the working version, I simply use the “Check for modifications” command of TortoiseSVN. This tells you what files have changed, and has a pretty good diff tool to show how those files changed.

Because TortoiseSVN integrates with Windows Explorer, rather than Visual Studio, it can be used by people that don’t use Visual Studio. This is important to us because some design changes are made directly on a test server, not on a developer’s computer. TortoiseSVN will save a ton of time in keeping these changes in sync.

All of this works because by default, Subversion uses an edit-merge-commit model, rather than the checkout-edit-checkin model used by VSS. The differences are explained in Chapter 2: Checkins of Eric Sink’s series on source control. At first this change of models sounded strange to me, but I found that the edit-merge-commit model really does work better for some of the things we’ve been doing with projects.

I’ve been running Subversion and TortoiseSVN in a test environment for about 6 weeks. So far, I’ve been extremely pleased in how it’s working.

Installing Subversion on Windows

At first I was apprehensive about installing Subversion on Windows. I was concerned because the web site mentions Apache, and Apache isn’t on my Windows virtual machine. Did I really want another web server running on this machine?

Then, about a month ago, I decided to give it a try, so I installed the Tigris version for Windows. To my surprise, it was pretty easy to install, Apache was included in the install package.  The main problem I had was figuring out how to run Subversion as a service rather than a simple application.

Then, about a week ago, I decided to change the virtual machine Subversion was installed on. This time, I installed the CollabNet package of Subversion. Wow! This was even easier. It took care of installing everything I needed. Here are the steps I took, very loosely followed from this page on Setting up Subverion on Windows:

  1. Download and install the package from Collabnet
  2. Added a Environmental variable called SVN_EDITOR pointing to c:\windows\notepad.exe
  3. Modified the file C:\Program Files\CollabNet Subversion Server\httpd\conf\httpd.conf to so Apache would Listen to a port other than 80, as 80 interfered with Microsoft Content Management System and IIS already on the machine
  4. Created a Subverion Repository
  5. Because Subversion was already installed on another virtual server, and I wanted to keep that repository, I simply copied the entire folder from one server to the other.

Copying the repository from one server to the other almost trivial. Just copied it in, and it worked. I highly recommend the package from CollabNet.

The hardest part of changing servers was that I had to use the Relocate… command to point all my working directories to this new repository.  Not a hard thing to do, but it took a few minutes to do this every working directory.

So far, I’ve been very pleased with the way Subversion has been working for me. I’m very close to expanding it to include the other people on our development staff. Unfortunately, this will mean yet another server install, moving the repository, and relocating all the working directories. At least when this time comes, I’m confident that the change will be easy.

Looking at Source Code Control Solutions

Lately, I’ve been looking at options for doing source code control within our group. What really got me started rethinking source control was these articles titled Source Control HOWTO by Eric Sink.

This series of articles does great job of describing what source code control is all about. It covers a lot of different concepts within source control, and how different products handle these concepts. Best of all, the series is a pretty easy read. I learned a lot from these articles and appreciate that Eric made them available. I highly recommend them.

We have been using an older version of Microsoft Visual SourceSafe. Although we have not run into some of the corruption issues some people have experienced, I’d like to find a better way of handling source code. The choices I see include:

  1. Continue running the old version of Visual SourceSafe
  2. Upgrade to the newest version of it
  3. Use another commercial product like SourceGear Vault
  4. Use an open source product like Subversion

A few weeks ago, I decided to try out Subversion. It was easy to install on one of my test servers, and I really like the way it works. It has already solved a problem I was having keeping a project current between our production server, test server, and my development platform. I’m thinking it might be time to put Subversion on a real server, and increase our use of it.

It might seem a little strange that a developer working mostly in .NET would use an open source product for source control. As this results of this survey question from CodeProject shows, I’m not alone. When asked what source code control people use, Subversion got 33% while Visual SourceSafe got 27%.

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.

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.