Prevent SQL Injection by leaving a legacy

How to prevent SQL Injection attacks for generations to come:

Displaying the query SQL in your Reports

I was recently asked by a co-worker the following question:
"Is there a programmatic way to pull the dataset SQL and display it on a [Reporting Services 2005] report?"

With a little bit more background information I was able to help her out. Some important facts that needed to be pointed out:

• what flavor or DBMS are you using (hopefully SQL Server 2005)?


• Are you allowed to create and use stored procedures as the backend of your reports?

With those facts straight, we can approach this problem with the following solution (I am sure there are other, perhaps more elegant, ways to solve this, so add your comments).

UPDATED:
If Stored Procedures are not being used, then you can simply include a textbox in your report body with the following expression:
=Datasets!mydatasetname.CommandText

If you'd like, you can set the visibility of this textbox based on another textbox's toggle property, and set the text property for that 2nd textbox as "view report query" with a blue color so it looks like a link or action of some sort.

If you use stored procedures, then read on:

We must adopt a few "standards" regarding reporting and SQL, when approching the problem with this solution.

First, you will want to use Stored Procedures (sproc) for all your report datasets. This allows you to do some extra things with your sql code that you can't do in the report dataset designer.

Second, you will want to use dynamic sql, since you're wanting to output the sql statement as part of your query results (e.g., in a column of your dataset).

SQL Server contains a stored procedure called "sp_executesql" that allows you to execute sql statements much in the same way as using EXEC(@sql). Except, it adds a layer of protection against SQL Injection Attacks by introducing parameterized queries. So in fact what you'd have for your stored procedure is something like the sproc I am including at the bottom of this post.

Notice that we have 2 ways of getting the sql statement: as a rpt developer, you may choose to display the "DebugSql" column in the report, and as SQL developer, you can choose to execute your sproc with the @debug parameter set to 1, so it only prints the sql that would execute. This offers some flexibility for all.

Either way, in the report dataset designer, you can just specify query type of Text (not StoredProcedure, it gets kinda buggy when dealing with dynamic sql). Then for the query text you'll enter something like this:

EXEC dbo.GetMyReportData @param1, @param2

(no need to include the @debug parameter, since it has a default).

There's an excellent whitepaper titled "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog (http://www.sommarskog.se/dynamic_sql.html), where he discusses all the possible ways of doing dynamic sql, including some best practices (like the sp_executesql sproc). I would highly recommend taking a look at this if you need more information on the topic or would like to understand more about the sp_executesql procedure (it's also documented in the SQL Server Books Online at http://msdn2.microsoft.com/en-us/library/ms188001.aspx).

Here is the sample stored procedure SQL, enjoy!

 IF EXISTS (SELECT * FROM dbo.sysobjects
           WHERE id = OBJECT_ID('dbo.GetMyReportData')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    BEGIN
        DROP PROCEDURE dbo.GetMyReportData
    END
GO

CREATE PROCEDURE dbo.GetMyReportData
  @param1 NVARCHAR(25)
  , @param2 NVARCHAR(25)
  , @debug BIT = 0
AS
BEGIN

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT
  col1 AS Column1
  , col2 AS Column2
  , col3 AS Column3
  , @N_debugSql AS DebugSql
FROM
  myTable WITH(NOLOCK)
WHERE
  col1 = @N_param1
  AND col2 = @N_param2'

DECLARE @parameterList NVARCHAR(MAX)
SET @parameterList = N'
  @N_param1 NVARCHAR(25)
  , @N_param2 NVARCHAR(25)
  , @N_debugSql NVARCHAR(MAX)'

IF @debug = 0
  BEGIN
    EXECUTE sp_executesql @sql, @parameterList
      , @N_param1 = @param1
      , @N_param2 = @param2
      , @N_debugSql = @sql
  END
ELSE
  BEGIN
    PRINT (@sql)
  END

END

How do you organize emails and calendar?

After reading a blog by Scott Hanselman on the topic of email organization with Outlook, as well as one of my co-workers tips for Outlook 2007, I was curious to find out how other folks organize their life. This includes anything from emails, to tasks, calendaring, IMs, communication in general. Also, what tools and programs are you using to keep yourself organized? Do you use a plan like the GTD (Get Things Done) or Franklin Covey’s?

So to start off….here is my so called “system”:

Before
Well….it’s morphing a little since I’ve started using categories and search folders in Outlook 2007. But before, when I used Outlook 2003, I set up multiple subfolders under my Inbox (including one for distribution list emails, travel, and lodging, as well as Admin stuff). Then, using multiple rules, I would automatically have them moved to these folders as they arrived.

Inbox
I confess my Inbox is a dumping ground for old messages. I usually archive items that are about 4-6mos old…..but anything else that isn’t picked up by rules stays in my inbox. I use the search feature to find messages. However it looks like I am starting to go the “google” way to do emails…tagging them by using the categories feature, and combining indexed search with search folders.

Calendar
I try to color code appointments and things on my calendar by using the category selection, but to this day I don’t have a single view of my client calendar (since I have an exchange account at my client and use their Outlook) merged with my work calendar. That would be a nice thing to get working but I am not sure I can do that yet, like the blog post mentioned earlier. I am, however interested in syncing my personal Yahoo calendar, and my wife’s Google calendar up so I can see them using Outlook, and so I can overlay them. If you’ve done that, please share your story.

Mobile
I have a Windows Mobile phone, so I can get my emails and calendar synced, and I have only my Inbox and a couple of the distribution list folders synced up to my phone (no need to see other messages on the mobile).

Instant Messenger
Finally, I have several IM accounts (Yahoo, MSN, GTalk, and I think I even had an ICQ acct at some point…though haven’t used that in years), but honestly, I generally stay on MSN/Windows Live Messenger. When I am the client, since they block IMs, I use Meebo (http://www.meebo.com/), which uses the Internet protocol (HTTP) to transport your messages, so it doesn’t get blocked by firewalls. Also, they allow you to connect to multiple IM services using one ID (VERY NICE)…and it runs from the web browser (which is good and bad). I heard that the Google Talk client allowed you to connect to other IM services besides Google’s, but I’ve yet to try it.

I’d be interested in finding out what other ways are being used to stay organized with your communications, so post your comments, dear readers!