Not all dashes are created equal. That's what I learned today. If you look at the ASCII Character Codes CheatSheet you will see 3 different dashes. The first is the normal one. The other two are considered extended ASCII. Extended ASCII is a polite way of saying it doesn't appear on your keyboard.

45  -
150 –
151 —

Over on DeepFitness.com, I try to create a friendly URL to each article. When you perform an HttpUtility.UrlEncode against a regular dash, it returns a dash. When you perform it against the 2nd dash it returns %e2%80%93. The 3rd dash will return %e2%80%94. Not exactly a search engine friendly URL.

I'm sure there are 10 ways to replace the bad dash with the good dash in C#. Here is the method I used.

string titleLink;
// assign titleLink a value - database perhaps
titleLink = HttpUtility.UrlEncode(titleLink);
// remove Extended ASCII dash with ASCII dash
titleLink = titleLink.Replace("%e2%80%93", "-");

A better way is to clean it up at the database level. Here is the SQL that will replace the extended dash with the normal dash.

UPDATE Article
SET title = REPLACE(title,CHAR(150),CHAR(45))

Labels: ASCII, Csharp, SQL

Below is a handy function for converting a hexadecimal value into an integer using a SQL Server user defined function.

IF OBJECT_ID('dbo.udfHex2Int') IS NOT NULL
        DROP FUNCTION dbo.udfHex2Int
GO
CREATE FUNCTION dbo.udfHex2Int
(
  @hexstr AS varchar(1000)
)
-- Function converts VARCHAR representation of HEX to INT
-- 'FF'  --> 255

RETURNS INT
AS
BEGIN

  IF @hexstr IS NULL RETURN NULL

  DECLARE
    @curbyte AS int,
    @varbin  AS varbinary(500)

  IF @hexstr LIKE '0x%' SET @hexstr = SUBSTRING(@hexstr, 3, 8000)

  SET @hexstr =
    CASE LEN(@hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @hexstr

  SET @varbin = 0x
  SET @curbyte = LEN(@hexstr) / 2

  WHILE @curbyte > 0
  BEGIN
    SET @varbin =
      CAST(
        CASE SUBSTRING(@hexstr, @curbyte * 2, 1)
          WHEN '0' THEN 0x00
          WHEN '1' THEN 0x01
          WHEN '2' THEN 0x02
          WHEN '3' THEN 0x03
          WHEN '4' THEN 0x04
          WHEN '5' THEN 0x05
          WHEN '6' THEN 0x06
          WHEN '7' THEN 0x07
          WHEN '8' THEN 0x08
          WHEN '9' THEN 0x09
          WHEN 'A' THEN 0x0A
          WHEN 'B' THEN 0x0B
          WHEN 'C' THEN 0x0C
          WHEN 'D' THEN 0x0D
          WHEN 'E' THEN 0x0E
          WHEN 'F' THEN 0x0F
        END |
        CAST(
          CASE SUBSTRING(@hexstr, @curbyte * 2 - 1, 1)
            WHEN '0' THEN 0x00
            WHEN '1' THEN 0x10
            WHEN '2' THEN 0x20
            WHEN '3' THEN 0x30
            WHEN '4' THEN 0x40
            WHEN '5' THEN 0x50
            WHEN '6' THEN 0x60
            WHEN '7' THEN 0x70
            WHEN '8' THEN 0x80
            WHEN '9' THEN 0x90
            WHEN 'A' THEN 0xA0
            WHEN 'B' THEN 0xB0
            WHEN 'C' THEN 0xC0
            WHEN 'D' THEN 0xD0
            WHEN 'E' THEN 0xE0
            WHEN 'F' THEN 0xF0
          END AS tinyint) AS binary(1))
      + @varbin
    SET @curbyte = @curbyte - 1
  END

  RETURN CAST(@varbin AS INT)

END 

Labels: Hexadecimal, SQL, UDF

When creating a report often the date column provides more detail than we need to see. Here is a handy little UDF that rounds a DATETIME column to either the MINUTE, HOUR, DAY, WEEK or MONTH.

DateFirst UDF

IF OBJECT_ID('dbo.udfDateFirst') IS NOT NULL
    DROP FUNCTION dbo.udfDateFirst
GO
CREATE FUNCTION dbo.udfDateFirst
(
    @interval    VARCHAR(10),
    @rawDate    DATETIME
)
RETURNS DATETIME
AS
BEGIN    
    -- Remove seconds for allintervals
    SET @rawDate = CAST(@rawDate AS SMALLDATETIME)

    IF @interval = 'MINUTE'        
        RETURN @rawDate
    ELSE -- strip off minutes
        SET @rawDate = DATEADD(minute,-1 * DATEPART(minute,@rawDate),@rawDate)

    IF @interval = 'HOUR'        
        RETURN @rawDate
    ELSE -- strip off hours
        SET @rawDate = DATEADD(hour, -1 * DATEPART(hour,@rawDate),@rawDate)

    IF @interval = 'DAY'
        RETURN @rawDate

    IF @interval = 'WEEK'
        RETURN DATEADD(day, -1 * DATEPART(weekday,@rawDate),@rawDate)

    -- move date to first of month
    SET @rawDate = DATEADD(day, -1 * DATEPART(day,@rawDate) + 1,@rawDate)

    IF @interval = 'MONTH'
        RETURN @rawDate
    
    -- move Date to first of year
    SET @rawDate = DATEADD(month, -1 * DATEPART(month,@rawDate)+1 , @rawDate)

    RETURN @rawDate
    
END

Test Query

DECLARE @now AS DATETIME
SET @now = getDate()

SELECT dbo.udfDateFirst('MINUTE',@now)
SELECT dbo.udfDateFirst('HOUR',@now)
SELECT dbo.udfDateFirst('DAY',@now)
SELECT dbo.udfDateFirst('WEEK',@now)
SELECT dbo.udfDateFirst('MONTH',@now)
SELECT dbo.udfDateFirst('YEAR',@now)

Labels: Dates, SQL, UDF

This article is written for SQL Server 2000, which supports user-defined functions and OUTER JOINS. All examples come from the Northwind database which is part of the default installation of SQL Server.

In SQL an OUTER JOIN is where you want to return all the elements in a table regardless of whether they exist in the second table. The most common case for using an OUTER JOIN is when you need a COUNT or SUM value even when the aggregate function returns a zero. Before we get to the heart of this article, let me use two queries to demonstrate the power of a basic OUTER JOIN.

Customers and Order COUNT

There are 2 ways to tackle this request. The first is with an INNER JOIN. It will only return Customers that have at least one order. The second way is with an OUTER JOIN. It will return all Customers, even those with no Orders.

-- A: INNER JOIN (returns 89 customers, all of which have at least 1 order) 
SELECT C.CompanyName, COUNT(R.OrderID) AS OrderCount 
FROM Customers C 
INNER JOIN Orders R ON C.customerID = R.customerID 
GROUP BY C.CompanyName 
ORDER BY COUNT(R.OrderID) DESC 

-- B: OUTER JOIN (returns all 91 customers, 2 of which have 0 orders) 
SELECT C.CompanyName, COUNT(R.OrderID) AS OrderCount 
FROM Customers C 
LEFT OUTER JOIN Orders R ON C.customerID = R.customerID 
GROUP BY C.CompanyName 
ORDER BY COUNT(R.OrderID) DESC 

Orders by Day of the Week for July 1996

The first OUTER JOIN was straight-forward. But what if the entity you need to perform an OUTER JOIN upon doesn't exist in the database? One such entity that comes to mind is date. Let's suppose the sales manager is trying to determine which day she is going to paint the office based upon the day of the week with historically the least amount of orders. The least amount could be zero, so we need to perform an OUTER JOIN. But how can you perform an OUTER JOIN against a range of dates? Three ways come to mind.

1. Create a TABLE of dates (or digits) using a SQL statement. This could be a temporary or permanent table.
2. Get verbose with your SQL and write long CASE statements.
3. Use the 2 user-defined functions I'm providing to you in this article.

The problem with #1 is now you're executing multiple statements to handle one query. You are creating a TABLE, perform the INSERT and ultimately dropping the TABLE. At this point you'll consider writing a stored procedure to accomplish the task. All for a single query. Too much work. The problem with #2 is your SQL can get very long and hard to read and the data is returned more like an Access PIVOT TABLE. The slick way to handle this task is using a user-defined function.

-- C: Using multiple CASE and SUM statement (long, ugly, and tabular) 
SELECT SUM(CASE DATEPART(dw,OrderDate) WHEN 0 THEN 1 ELSE 0 END) AS MonSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 1 THEN 1 ELSE 0 END) AS TueSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 2 THEN 1 ELSE 0 END) AS WedSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 3 THEN 1 ELSE 0 END) AS ThuSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 4 THEN 1 ELSE 0 END) AS FriSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 5 THEN 1 ELSE 0 END) AS SatSales, 
SUM(CASE DATEPART(dw,OrderDate) WHEN 6 THEN 1 ELSE 0 END) AS SunSales 
FROM Orders 
WHERE OrderDate BETWEEN '7/1/1996' AND '8/1/1996' 

-- D: Performing an OUTER JOIN against a user-defined function (dbo.udfNumbers) 
SELECT DATENAME(dw,N.number) AS DayName, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfNumbers(0,6) N 
LEFT OUTER JOIN Orders R ON N.number = DATEPART(dw,R.OrderDate) 
AND R.OrderDate BETWEEN '7/1/1996' AND '8/1/1996' 
GROUP BY DATENAME(dw,N.number), N.Number 
ORDER BY N.number

Using dbo.udfNumbers

The dbo.udfNumbers user-defined function returns a TABLE of numbers. It is available for download at the end of this article. The upper range limit is 999,999. If you need larger numbers, modifying the function should be fairly easy.

-- E: Usage dbo.udfNumbers(low range,high range) 
-- will return 6,7,8,9,10,11,12
SELECT number FROM dbo.udfNumbers(6,12)

Orders by Date for July 1996

I use dbo.udfNumbers for those quick queries where I just need a range of digits. For most date-based reporting I use the far more powerful dbo.udfDateTimes. This user-defined function uses the digit logic in dbo.udfNumbers along with SQL Server's date and time function to return ranges of datetime. Query C was based upon Day of the Week, so there were only 7 possible values. What if the sales manager asked for a day by day breakdown of orders for July? That would turn into one long and ugly SQL statement. The dbo.udfDateTimes function creates the date range need for that OUTER JOIN without the long and ugly SQL.

-- F: dbo.udfDateTimes day interval for July 1996 
SELECT dtime AS OrderDate, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfDateTimes ('7/1/1996', '7/31/1996',1,'day') DT 
LEFT OUTER JOIN Orders R ON DT.dtime = R.OrderDate 
GROUP BY dtime

Using dbo.udfDateTimes

Like dbo.udfNumbers, the user-defined function dbo.udfDateTimes returns a TABLE. The first parameter is start date. The second is end date. The third parameter is interval. In most cases this will be set to 1, but if you had a need to see one of the dateparts at an alternate interval, you would modify this parameter. The last parameter is datepart. Those supported are year, quarter, month, week, day, hour and minute. The upper range is 9,999,999, which (like udfNumbers) can easily be modified should need a function that returns more datetimes.

Download

dbo.udfNumbers - function that returns a table of digits.
dbo.udfDateTimes - function that returns a table of datetimes.

Last Words

One last piece of advice is to get your datetime range correct first before attempting to OUTER JOIN with it. Only once you know the dbo.udfDateTimes is returning the datetimes you need for the query should you proceed with your OUTER JOIN. Although the Northwind database deals with orders, another great use for these functions is when looking for missing data. In those cases where you need to seek out the datetimes where no data is present, add a HAVING clause set to zero.

-- G: Days in July 1996 with no orders 
SELECT dtime AS OrderDate, COUNT(R.orderID) AS OrderCount 
FROM dbo.udfDateTimes ('7/1/1996', '7/31/1996',1,'day') DT 
LEFT OUTER JOIN Orders R ON DT.dtime = R.OrderDate 
GROUP BY dtime 
HAVING COUNT(R.orderID) = 0

Labels: Dates, SQL, UDF

Here is a sample stored procedure that prevents duplicate inserts with the same value by first performing a test using SQL Server's EXISTS statement.

IF OBJECT_ID('prcTagNameInsert') IS NOT NULL
    DROP PROCEDURE prcTagNameInsert
GO
CREATE PROCEDURE prcTagNameInsert
    @tagName    VARCHAR(50)
AS

IF NOT EXISTS(SELECT 1 FROM Tag WHERE tagName = @tagName)
    INSERT INTO Tag (tagName) 
    VALUES (@tagname)

Labels: SQL

Well I got nailed today. My site INeedCoffee.com which is written using Classic ASP fell victim to a SQL Injection attack. The damage was limited to just one column in a table of nine rows.

Textbook SQL Injection Attack

Almost every example I've seen that explains SQL Injection shows how the WHERE clause is vulnerable to querystring manipulation.

sAuthorID = Request.QueryString("AuthorID")
sSQL = "SELECT firstName, lastName FROM Author WHERE authorID = " & sAuthorID

This query can be nailed with a single quote and an OR clause to dump the entire table to the screen. Adding 1' or '1'='1 to the querystring now yields this SQL.

SELECT firstName, lastName FROM Author WHERE authorID = 1 OR 1=1

Attacked Via a Column Sort

The attack I received came from appending additional SQL to ORDER BY clause. By clicking on the column header on the contributor page a column number is passed to the querystring. This was getting concatenated to a SQL statement.

EXAMPLE: http://ineedcoffee.com/by/michael_allen_smith/?s=1

The server-side ASP code looked like this:

sort = Request.QueryString("s")
If Len(sort) Then
    sSQL = sSQL & " ORDER BY " & sort
Else
    sSQL = sSQL & " ORDER BY C.Created DESC " 
End If

The hacker guessed the name of the table and one of the column names. From there it was an easy to hack together a querystring that looked like this:

http://ineedcoffee.com/by/michael_allen_smith/?s=1;UPDATE Section SET Name='hacked'

A simple semicolon followed by an UPDATE statement. This was just one column on a minor lookup table. I'm grateful that he/she didn't use the DROP TABLE command in the right sequence.

Locking It Down

Although ASP.NET has a nice feature to add parameters to dynamic SQL, Classic ASP is still best protected with stored procedures. Now the sort parameter goes into a stored procedure which can only perform SELECT. Prior to entering the stored procedure I'm now performing tests on length of the querystring (LEN) and determining if is an integer (IsNumeric).

Labels: ASP, Security, SQL