Scott Hanselman needs a favour

January 15, 2010 Leave a comment

The folks in the .NET Framework Setup team have a favor to ask, and it’s pretty cool info so I offered to help. Here’s the deal. When .NET 4 releases, the .NET 4 Client Profile will be released as a recommended update on Windows Update (WU) for Vista and Windows 7. It’ll be listed as optional on Windows XP. They need help testing the WU parts. (via)

Advertisements
Categories: Uncategorized

MySQL to SQLServer : Free Migration Assistant

January 13, 2010 Leave a comment

Microsoft announced the first CTP of a SQL Server Migration Assistant (SSMA) for MySQL v1.0 which automates aspects of the migration process to Microsoft SQL Server 2005 & 2008 including SQL Azure Database.

SQL Server Migration Assistant for MySQL is available for free download and preview at SSMA 2008 for MySQL v1.0 CTP1 and will be generally available in the summer of 2010. Customers and partners can also provide feedback via ssmateam@microsoft.com.  (via)

VS 2010 beta1 : Uninstallation problem

January 13, 2010 Leave a comment

Today I tried to uninstall visual studio 2010 beta1 so that I can get my hands wet with VS 2010 Beta 2. Visual Studio 2010 Beta 2 requires us to uninstall Beta 1.

But there was a problem in un installation of visual studio 2010 Beta 1. I got the following error:Setup is looking for file TFSObjectModel-x86.

The solution is very simple. When Visual Studio 2010 Beta 1 was installed, it installed Microsoft Team Foundation Server 2010 Beta 1 Object Model – ENU as a separate installation. So before uninstalling VS2010 Beta 1, one has to uninstall “Microsoft Team Foundation Server 2010 Beta 1 Object Model – ENU”. Below is the image that shows this in the control panel.

image

Hope this helps!

JQuery Error : $ is undefined

November 17, 2009 7 comments

When ever you get a JQuery ‘$ is undefined’ error, then the problem must be because reference to the jquery file is not correct and it is not loaded. Try to resolve the path of the jquery script file using Resolve URL.

<script type="text/javascript" src='<%= ResolveUrl("~/Scripts/jquery-1.3.2.js") %>’></script>

Lets say your jquery script file “jquery-1.3.2.js” is located in /Scripts under root directory, then you will have to resolve your url as said previously.

This is not a problem specific to JQuery files but is common to all master-content page. You have a master page and a content page. In the master page you have the reference to a javascript file (or css) using “~”. Unless all these files fall in the same folder you would not have any problem. The problem occurs only when the files are in different folders.

Converting ASP.NET Web Site to Web App

November 6, 2009 Leave a comment

There is a common mistake which almost most of us made when we  created a new web site with Visual Studio 2005. The earlier version of Visual Studio 2005 does not include a template for web application. In other words, it was only possible to create website.

So why do you have to convert from website to web app first of all? Well there are quite a few reasons to list out:

  Web Site Web App
Compilation of Markup Dynamic Dynamic
Compilation of code behind dynamic by default; can pre -compile the site using 2 modes – batch mode (one assembly per folder) and fixed-names (one assembly for each page or user control; this may result in increased memory usage) Precompiled; All code is compiled in to single assembly
Scope Only code in App_Code is available to all classes; namespaces are not created by default Since it is precompiled all page classes are in same assembly and are visible to all; namespace is created by default
File Structure Just aggregates files in a directory so based on file system; project file list and other assembly list are present in web.config (which violates SoC) has a neat project file which helps to have information of files and assemblies, helps in controlled build and deployment

After reading the above difference website option may look evil; but that’s not the case. There are cases when website may come in to rescue. In fact we have been using web site for our application for a long time. And deployment was not a problem, we used NANT to compile the application (batch-mode) and used INSTALLSHIELD to deploy the application.

So when to use website or webapp project? There is an excellent article from MSDN:

 

Task Web application projects Web site projects
Need to migrate large Visual Studio .NET 2003 applications X  
Prefer single-page code model to code-behind model   X
Prefer dynamic compilation and working on pages without building entire site on each page view (that is, save file and then simply refresh the page in the browser).   X
Need to control names of output assemblies X  
Need to generate one assembly for each page   X
Need stand-alone classes to reference page and user control classes X  
Need to build a Web application using multiple Web projects X  
Need to add pre-build and post-build steps during compilation X  
Want to open and edit any directory as a Web project without creating a project file   X

But after an over all comparison, Web Application looks more structured and gives us more control over the project.

How to convert a web site to web application?

ScotGu has given an excellent step-by-step approach to convert a web site to web application. (In fact the Microsoft consultant who came to audit our project also gave the same printout as guidance). This is an excellent resource to start with.

While I did the conversion I took notice of some points that are worth mentioning in addition to Scottgu’s list.

Pre-Requisites:

  1. Visual Studio 2005 does not have an option for web application project. The web application project template was later added to the visual studio in Visual Studio 2005 SP1.Please read here for What is new in Visual Studio 2005 SP1? Web application projects is one among them.
  2. If you are installing VS 2005 SP1 on Windows 2003 server you may get a weird error "The installation source for this product is not available". You must install a hotfix to fix this.
  3. So install the hot fix (point 2), restart the machine and then install VS 2005 SP1.
    Also see ScottGu’s suggestions on installing the service pack.

Steps in conversion:

  1. Create a web application project.
  2. Add reference to all of your dlls.
  3. Copy the files to the folder except web.config file
  4. Remember there is no App_Code in web application. So any classes in the app_code will not be visible to other classes in the project. This will break the build. To avoid this as a quick and effective fix, add a namespace to your classes in App_Code. And replace this class name with the fully qualified class name (with the newly added namespace) through out the project. (Of course not in the original class file in app_code). Renamed the app_code folder to something like Shared_Classes or something; this would avoid confusion.
  5. Change the Build Action to <compile> for all the classes in App_Code.
  6. Convert to Web Application.
  7. image
  8. When the convert to web application is being done the conversion may fail for some aspx files that use user controls that reside in the same directory and the same user control is also registered in the web.config file. Actually the creation of designer file may fail because of this error: tag has already been registered. To avoid this: register the user control in the problematic ascx page but choose a different TagPrefix (different from the one you have already registered in web.config file). For example:

These are few additional things I noticed while converting the project from web application to web site. Hope this helps!

How Fitzpatrick tricked AOL?

November 3, 2009 Leave a comment

Smart as hell!

Fitzpatrick: Yeah, I worked at Tektronix for a while. Before I had any official job, I got some hosting account. I got kicked off of AOL for writing bots, flooding their chat rooms, and just being annoying.I was scripting the AOL client from another Windows program. I also wrote a bot to flood their online form to send you a CD. I used a variation of my name, because I didn’t want their duplicate suppression to only send me one CD, because they had those 100 free hours, or 5000 free hours. I submitted this form a couple thousand times and for a week or so the postman would be coming with bundles of CDs wrapped up.

My mom was like,"Damn it, Brad, you’re going to get in trouble" I was like"Er-their fault right?". Then one day I get a phone call and I actually picked up the phone, which I normally didn’t, and it was someone from AOL. They were just screaming at me. "Stop sending us all these from submissions". I am not normally this quick and clever, but I just yelled back,"Why are you sending me all this crap? Every day the postman comes! He’s dropping off all these CDs" They are like "We are so sorry sir. It wont happen again"

– From Coders at Work
**

Categories: Uncategorized

Dynamic Queries, Stored Procedures and SQL Injections

October 28, 2009 Leave a comment

As every one knows that Ad hoc dynamic queries are prone to SQL Injection attacks, I am not going to touch that. But there is still some confusion hanging over usage of dynamic sql with in a stored procedure. This is what I thought of blogging about.

Point 1: Using dynamic SQL with in stored procedure are prone to SQL Injection attack.

Other disadvantages of using dynamic SQL includes:

  1. Not readable and there for un maintainable code.
  2. Execution path is not saved there fore every time a stored procedure is run execution path is calculated again and again.
    But there are cases when we might need to use dynamic queries inside a stored procedure. What have to be done in this case?
    To demonstrate the sql injection attacks and to give a sample how to avoid this, I created a table named test with just one column [name].

    Table Definition

    USE [ASPNETDEV]
    GO
    /****** Object:  Table [dbo].[test1]    Script Date: 10/28/2009 15:14:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test](
        [name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    Insert the below values in to the table.

    insert into test values (‘muthu’);

    insert into test values (‘muthu1’);

Case 1: Procedure using static query

create procedure testsi
(@name nvarchar(10))
as
select * from test where [name]=@name

when we execute the above procedure with normal parameters (‘muthu’) it brings just 1 row.

exec testsi ‘muthu’

Now I give a value that introduces SQL injection as below

exec testsi ‘muthu”;drop table test;–”select * from test;’

When you look in to the value passed to the parameter @name ; you can very well see the SQL injection in the form of ‘’;drop table test;—. As you see, this  just closes the single quote and drops the table test. Well this is sql injection.

But to our surprise executing this does not drop the table and promptly brings in one row.

muthu

Because what we passed is just a value for the column [name] and obviously we don’t have any row in the table [test] with the column [name] having value ‘muthu”;drop table test;–”select * from test;’

Case 2: Procedure with dynamic Query 

create procedure testsid
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
set @sql=’select * from test where [name]=”’ + @name + ””
print @sql
execute (@sql)

The line given in bold is the place where we use dynamic query.

Now lets execute this procedure using our SQL Injection value.

exec testsid ‘muthu”;drop table test;–”select * from test;’

Opps! Now the table is lost.

when you see the “Messages” tab in the Management Studio to your surprise it will be as follows:

select * from test where [name]=’muthu’;drop table test;–‘select * from test;’

(1 row(s) affected)

When you separate the statement using semicolon you will get 2 statements as follows; never mind the third one is commented.

  1. select * from test where [name]=’muthu’;
  2. drop table test;
  3. –‘select * from test;’

So this is SQL injection and this doesn’t just disappear if you use stored procedure.

Case 3: Procedure with dynamic query and avoiding SQL injection

However Microsoft has introduced a new way to run dynamic queries from the stored procedure using sp_executesql.

From MSDN:

To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.

 

Please refer to the SQL server 2008 books online to get more information about this sp_executesql.

alter procedure testside
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=@name’

exec sp_executesql @sql, @ParamDefinition,@name

print @sql

In the above procedure we create a dynamic parameterized  query and we pass the query, the parameter definition and the value for the parameter to sp_excutesql procedure.

exec testside ‘muthu”;drop table test;–”select * from test;’

Even though we run the procedure with SQL Injection values to our surprise the table test does not get dropped.

But remember just using sp_executesql will not avoid sql injection attacks. It must be used sensibly. For example an example as follows is still susceptible to sql injection.

How not to use sp_executesql?

create procedure testsides
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=”’ + @name + ””
exec sp_executesql @sql, @ParamDefinition,@name

print @sql

Even though the procedure uses “sp_executesql” it is still prone to SQL injection because it does not use parameterized query.

But there are times when one cannot use parameterized queries, in this case there is no way but to use dynamic query. But in this case one must take extra-ordinary steps to validate the data. This article may help further understanding.

Update:

A live example for sql injection attack: Barackobama.com!