Archive for the ‘SQL’ Category

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.

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.

CASE Statement in SQL Query

I’ve used SQL to varying degrees for a number of years. However, one of the features I’ve recently learned about is the CASE statement. For some reason, I missed this powerful feature when I studied SQL.

When would you use the CASE statement? I use it when writing stored procedures when I want to have optional input parameters. Consider this simple stored procedure:

ALTER PROCEDURE [dbo].[GetRooms]
(
@roomID int = 0,
@color varchar = ''
@type varchar =''
)
AS
BEGIN
SELECT roomID, roomName, roomColor, roomType, roomWidth, roomLength, roomheight
FROM tblRooms
WHERE
roomID = CASE @roomID WHEN 0 THEN roomID ELSE @roomID END
and roomColor = CASE @color WHEN '' THEN roomColor ELSE @color END
and roomType = CASE @type WHEN '' THEN roomType ELSE @type END
ORDER BY roomName
END

With this one Stored Procedure, we have a lot of options for what is returned, depending on what parameters we include in the call.

EXEC GetRooms – Returns all the rooms in the database.
EXEC GetRooms @roomID=3 – Returns the room with ID of 3
EXEC GetRooms @color=’blue’, @type=’kitchen’ – Returns all the kitchens that are blue

With the optional parameters, this one stored procedure can be used in a number of different scenarios. Using this technique, I once replaced about a dozen stored procedures in a database with just one. How cool is that!

Parameters in Stored Procedures

This tips comes from Doug Nelson, in his blog entry “Optional Parameters in Stored Procedures“. I’ve used this extensively in MS SQL. According to the documentation, it should also works in Oracle and mySQL.

[Update on 4/23/2008: I notice that the link to Doug’s post hasn’t been working lately, so I wrote an article “CASE Statements in SQL Query“, which covers some of the same concepts.]

To me, the most interesting thing is Doug’s posting is the clever use of the CASE statement. I follow his example in many of the stored procedures I write. By using this technique, I’ve saved a lot of time in trying to build where clauses for select statements, and it’s greatly cut down on the amount of SQL code I write.

Let’s say we have a table that contains a list of people, including firstname, lastname, address, city, state, zip. We want to search for people based on their firstname and/or lastname and/or the state they live in. We can do this with a single stored procedure by including parameters for the three possible search criteria . Simply determine the search criteria the user included (ie did they include a firstname in the search form), and pass that criteria to the stored procedure using the corresponding parameters. No more trying to dynamically build where clause based on what the use input into the search form!

Stored Procedures in SQL

Most of the projects I work on use MS SQL server as a data store. When setting up a database, I usually create stored procedures to interact with the database. There are a number of reasons I do this:

  1. Limit access to the database. By using stored procedures, we can give the web user account access to execute the stored procedures without giving them datareader or datawriter access. This means that the only way this account can interact with the database is through the stored procedure. Very much limits the possibility of getting hit by a SQL injection attack.
  2. All interactions with the database are parameterized. Again, this limits the possibility of getting attacked by SQL Injection.
  3. Keeps the database separate from your source code. Especially important if you have multiple applications that access the same database. This allows you to make changes to the stored procedure without making changes to the application(s). This works as long as you don’t change the parameters or structure of the return results of the procedure.
  4. Makes it easy to do multiple things with a single database call. You can even build transactions in the stored procedure when you need to ensure that all queries are successful once the first one runs. If one of the queries fail, then the entire transaction is rolled back.
  5. For projects with a Data Access Layer (DAL), the DAL is almost trivial to write. Simply call the stored procedures and pass back the results.

For some projects, using stored procedures can add too much complexity. However, I tend to error on this side rather than putting too much SQL into the application’s code.