New tool for SQL Load Testing

Check out the new SQL Load Test project on CodePlex!

Rob Jarratt, the VSTS ranger who wrote the WCF script generator for load testing WCF apps has done it again, this time for SQL! Once again he teamed up with the Services Test Lab to deliver this tool, this time working mainly with Geoff Gray on several client gigs to vet the tool.

If you are doing SQL load testing, where you want to test the database tier directly, this tool will be useful for you. The most common case for this is if you want to load test a "traditional" 2-tier app, where the client talks directly to the database. But certainly there are more scenarios where this tool will be useful.

This tool will take in a SQL trace and generate the ADO.NET code to replay the trace, which you can then plug into a load test.

Thanks Rob Jarratt and Geoff Gray for your efforts on this, and to Bijan Javidi for leading the project.

 

Read More

Shrinking SQL Server Databases

Shrinking SQL Server 2005 Databases to remove unnecessary transactions and empty space from the log file.

Note that you will need your database name **and** the SQL Server name of your log file (different to the NTFS name) for this look at  sys.database_files. In this script my database name is "MyDatabaseName" and the SQL Server name of my log file is "MyDatabaseName_log" :

          USE MyDatabaseName

          DBCC SHRINKFILE(MyDatabaseName_log, 1);

          BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

          DBCC SHRINKFILE(MyDatabaseName_log, 1);

          DBCC SHRINKDATABASE(MyDatabaseName) -- Optional - not sure how much this helps

With this done I got back 50% of the entire space on my disk - guess I needed it :-) !! 

More

 

Read More

SQL Server Programming Hacks

NULLS

     Testing for Null Values 

     How To Check If Any, ALL Or No Parameters Have A NULL Value 

     Return The First Non Null Value 

     Return Null If A Value Is A Certain Value 

     Concatenation with NULL values 

     NOT IN and NULLs

Dates

     How to find the first and last days in years, months etc 

     Epoch Date 

     Get Datetime Without Time 

     Date Ranges Without Loops 

     Calculate Birthday In Years 

     Formatting Dates 

     Calling Stored Procedures With Datetime Parameters 

     Trouble With ISDATE And Converting To SMALLDATETIME

     How To Calculate How Many Minutes Have Passed Since Midnight With Datetime In SQL

Sorting, Limiting Ranking, Transposing and Pivoting

     Return Top N Rows 

     Dynamic TOP 

     Sorting Numbers Stored In A Varchar Column 

     Row To Column (PIVOT)

     Column To Row (UNPIVOT)

     Split A String By Using A Number Table 

     Concatenate Values From Multiple Rows Into One Column

     Concatenate Values From Multiple Rows Into One Column Ordered

     How To Use ROW_NUMBER() In A WHERE Clause

     Rank 

     Dense Rank 

     Row Number 

     Sort certain values last 

     Returning The Maximum Value For A Row

Handy tricks

     5 ways to return rows from one table not in another table 

     Order IP Addresses 

     Data formatting dates 

     Data formatting SSN 

     6 Different Ways To Get The Current Identity Value 

     Use XACT_ABORT to roll back non trapable error transactions 

     Random Sorting 

     Sort Values Ascending But NULLS Last 

     Adding Leading Zeros To Integer Values 

     How do I format money/decimal data with commas? 

     Find Out How Many Occurrences Of A Substring Are In A String 

     Ten SQL Server Functions That You Have Ignored Until Now 

     Use the *1 trick to do math with two varchar values 

     Store The Output Of A Stored Procedure In A Table Without Creating A Table 

     Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both

     Three Ways To Display Two Counts From a Table Side By Side

     Finding duplicates across columns

     Use REPLACE To eliminate unwanted characters

     How To Check For A Certain Time With SQL

     CSharp IsNullOrEmpty Function In SQL Server

Pitfalls

     Update triggers 

     Integer math 

     Identity Values And Triggers 

     Use XACT_STATE() To Check For Doomed Transactions 

     Three differences between COALESCE and ISNULL 

     Non deterministic functions and nullif 

     Subquery typo with using in 

     ISNUMERIC Trouble 

     Case Without Else

     Syntax Error Converting The Varchar Value To A Column Of Data Type Int

Query Optimization

     Case Sensitive Search 

     No Functions on Left Side of Operator 

     Query Optimizations With Dates 

     Optimization: Set Nocount On

     No Math In Where Clause 

     Don't Use (select *), but List Columns

Undocumented but handy

     xp_getnetname 

     xp_fileexist 

     xp_dirtree 

     xp_subdirs 

     xp_getfiledetails 

     xp_fixeddrives 

     Sp_tempdbspace 

     xp_enumdsn 

     xp_enumerrorlogs 

     Some Undocumented DBCC Commands 

     sp_MSForEachtable

     sp_MSforeachDB

Usefull Admin stuff For The Developer

     sys.dm_exec_sessions

     Find all tables that contain a certain column

     Find All Tables Without Triggers In SQL Server

     Find all Primary and Foreign Keys In A Database

     Find Out If A Table Has An Identity Column

     Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

 

Read More

SQL Injection information for IIS admins and developers

The sql injection that has came up is affecting several ASP and ASP.NET applications.  Although the only way to prevent an attack is validate the code, hopefully these posts will provide some direction.  I included some links that discuss this more. 

• http://forums.iis.net/p/1149068/1868206.aspx  (Post by Bill Staples)
• http://forums.iis.net/t/1148917.aspx?PageIndex=1  (almost a million views, definitely worth reading)
• http://forums.iis.net/p/1150026/1872364.aspx
• http://forums.iis.net/p/1150023/1872371.aspx
• http://forums.iis.net/p/987743/1278151.aspx 

Here's a list of additional reading:

Building Secure ASP.NET Applications - Authentication, Authorization, and Secure Communication.
http://www.microsoft.com/downloads/details.aspx?FamilyID=055ff772-97fe-41b8-a58c-bf9c6593f25e&DisplayLang=en

Improving Web Application Security - Threats and Countermeasures
http://www.microsoft.com/downloads/details.aspx?FamilyId=E9C4BFAA-AF88-4AA5-88D4-0DEA898C31B9&displaylang=en

This link talks about the issue in ASP/NET perspective:SQL Injection Attacks:
http://msdn2.microsoft.com/en-us/library/aa302392.aspx#secnetch12_sqlinjectionattacks

Sample code provided by Microsoft to validate SQL statements.
http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx

Log parser examples
http://weblogs.asp.net/steveschofield/archive/2008/04/26/clarification-on-iis-reported-sql-injection-exploits.aspx

Youtube
http://youtube.com (search for sql injections)  This will show several videos posted on how people are doing this.

To do a quick find type from a command prompt

findstr "CAST(" ex080622.log > ss.txt   (change the log file date)

Note the 'CAST' is case senstative

More

Read More

SQL Injection

Specifically I wanted to write about means of checking your code for possible SQL Injection problems.

The first means of checking, if you are using Visual Studio 2008, is to use the Team System environment.  Specifically, there is a code analysis rule in there that will check for some SQL issues.

Note: This set of rules is not a complete set of possible security rules, nor are the existing rules 100% guaranteed to find all the problems in their area of analysis. No code analysis tool can replace a formal security audit. Nevertheless, we have found inside Microsoft that this can be of great help finding potential security issues.

This is a very useful first pass that you can use to check for some issues.  It will find code that you have that looks like:

someCommand.CommandText = 

   "SELECT AccountNumber FROM Users " +

   "WHERE Username='" + name + 

   "' AND Password='" + password + "'";

This is based on FxCop which is a tool that you can download for free here.  There is also a blog about this tool that has a lot of useful information, including updates, here.

If you aren't using Visual Studio 2008, which you should use as it is our best development environment yet, there are some other alternatives that you can use to check for SQL Injection vulnerabilities.

I haven't tested any of these, so feel free to comment on them if you have used them or add additional suggestions.

Acunetix WVS
Cenzic Hailstorm
Wapiti
Microsoft® Visual Studio Team System 2008 Database Edition Power Tools – this includes a T-SQL Static Code Analysis feature.

SQL Injection and how to avoid it

It isn't as big of a deal at the moment, but it is always good to make sure everyone is aware of this and how dangerous it can be.  There is some very good information on it located on MSDN here.  The important part is to remember that anytime you take input from an external source (someone typing on a web page), they don't always have to put in what you expect.

The safest way to keep yourself safe from SQL Injection is to always use stored procedures to accept input from user-input variables.  It is really simple to do this, for example, this is how you don't want to code things:

var Shipcity;

ShipCity = Request.form ("ShipCity");

var sql = "select * from OrdersTable where ShipCity = '" + 

          ShipCity + "'";

This allows someone to use SQL Injection to gain access to your database.  For example, imagine if someone put in the following for the "ShipCity":

Redmond'; drop table OrdersTable-- 

This would delete the entire table!  If you have seen much on SQL Injection, they have figured out all kinds of ways to get information about your database or server, so don't think they can't find the names of tables, etc.

The correct way to do this would be using a stored procedure as follows:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",

     SqlDbType.VarChar, 11);

parm.Value = Login.Text;

Then you will be protected.  Be sure to use parameterized stored procedures to keep the stored procedure from having the same problem as before.

-- Update --

The above code would call a stored procedure that would be something like:

CREATE PROCEDURE AuthorLogin @au_id varchar(11)

AS

SET NOCOUNT ON

SELECT Author from AuthorTable WHERE au_id = @au_id

GO

Note: the "SET NOCOUNT ON" will prevent SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure which will improve performance especially for large stored procedures.

-- End Update --

There are other hints and advice on the MSDN article that you can check out, but this is the major piece of advice to know.

There is also some additional information that you can find here.  You can find more information and a video at Explained – SQL Injection and another video about it here.  There are tons of links on the web so feel free to research this more to be sure you are safe from this problem.

Here are a few other links to help on the subject:

SQL Injection Attack from the SWI team at Microsoft
Preventing SQL Injections in ASP
Filtering SQL Injection From Classic ASP
Classic ASP which is still alive and parameterized queries
ISAPI filter to protect against SQL Injection
Michael Sutton's Blog on SQL Injection

 

Read More

Easy SQL "If Record Exists, Update It. If Not, Insert It."

A very common scenario is the one where we want to update the information in a record if it already exists in the table, and if it doesn’t exist, we want to create a new record with the information.

The most common solution to this problem is using IF EXISTS (subquery). This comes to mind first because it matches how we think about the problem (as you can see by the title of this article!). We say “If the criterion exists in this initial subquery, then I’ll do this. If not, I’ll do this other thing.” This results in a three-step process:

1. Do the subquery (SELECT whatever FROM wherever WHERE something).
2. Evaluate the EXISTS statement (is it there or not?).
3. Execute either the UPDATE or INSERT statement.

Now, let’s try an “unnatural” shortcut. I say unnatural because it doesn’t follow that “natural” logic occurring in our brain that I mentioned above. Instead, let’s just do the update, and if it fails, then we’ll do the insert. When the update fails, that just means that no rows were affected, not that an error was thrown. Now we are down to a one-step (if the update succeeds) or two-step process (if we have to insert instead). This is much more efficient!

Example:

This is not necessarily a practical example, but let’s say that we have a table called “Users” which has three fields: “UserID”, “FirstName”, and “LastName”. If a record already exists with the specified UserID, simply update it with the new @FirstName and @LastName values. If it does not exist, create a new record with those values.

CREATE PROCEDURE dbo.spAddUserName
(
@UserID AS int,
@FirstName AS varchar(50),
@LastName AS varchar(50)
)
AS
BEGIN
DECLARE @rc int
UPDATE [Users]
SET FirstName = @FirstName, LastName = @LastName
WHERE UserID = @UserID
/* how many rows were affected? */
SELECT @rc = @@ROWCOUNT
IF @rc = 0
BEGIN
INSERT INTO [Users]
(FirstName, LastName)
VALUES (@FirstName, LastName)
END
END

Read More