Archive for October, 2009

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

    /****** Object:  Table [dbo].[test1]    Script Date: 10/28/2009 15:14:07 ******/
    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))
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.


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))
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))
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))
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.


A live example for sql injection attack:!

Moving ASP.NET web application from 32 bit to 64 bit

October 26, 2009 1 comment

Before we jump in to the details, we need to know few things to understand some basics.

Managed module is a standard 32 bit Microsoft Windows Portable Executable (PE32) file or standard 64 bit Microsoft Windows Portable Executable file (PE32+) that requires CLR to execute.

Parts of managed module:

PE32 or PE32+ header

Contains information about
1.    format of the file (PE32 or PE32+)
2.    type of the file (DLL, CUI or GUI)
3.    a timestamp: the time when the file is built.
4.    Information about native CPU code (if the module contain native CPU code)
For modules that has only IL this information is not used

CLR header

This includes information about
1.    Version of the CLR required
2.    MethodDef metadata token of the managed module’s entry point
3.    Location of Metadata and size of metadata
4.    resources
5.    strong name


Usually this part contains 2 tables
1.    information about the types and members defined in the module’s source
2.    information about the types and members referred by the module’s source.

Language code
Code produced by compiler during compilation. At run time CLR compiles the IL in to native CPU instructions.


Always keep in mind that all CLR complaint compilers produce IL code. At times this IL code is referred as managed code because it is managed by CLR.

Assembly Assembly is a logical group of one or mode modules
Manifest Manifest is a set of metadata tables that have information about list of files in the assembly, the dependencies of the files in the assembly and resource or data files associated with the files


Before we understand how CLR loads the managed code or assembly we need to study the difference between 32 bit and 64 bit versions of windows.

If your assembly files contain only type-safe managed code, you are writing code that should work on both 32-bit and 64-bit versions of Windows. No source code changes are required for your code to run on either version of Windows. In fact, the resulting EXE/DLL file produced by the compiler will run on 32-bit Windows as well as the x64 and IA64 versions of 64-bit Windows! In other words, the one file will run on any machine that has a version of the .NET Framework installed on it.

On extremely rare occasions, developers want to write code that works only on a specific version of Windows. Developers might do this when using unsafe code or when interoperating with unmanaged code that is targeted to a specific CPU architecture.

What is Unsafe Code?

By default Microsoft’s C# compiler produces safe code. Safe code is code that is verifiably safe. However it is possible to write unsafe code. Unsafe code is allowed to work directly with memory addresses and can manipulate bytes at these addresses. This is a powerful feature and is useful when interoperating with unmanaged code.

How to check if an assembly is type safe or not?
Microsoft supplies a utility called PEVerify.exe that analyses and reports the error if any unsafe code is used in the assembly.



To aid these developers,the C# compiler offers a /platform command-line switch. This switch allows you to specify whether the resulting assembly can run on x86 machines running 32-bit Windows versions only, x64 machines running 64-bit Windows only, or Intel Itanium machines running 64-bit Windows only. If you don’t specify a platform, the default is anycpu, which indicates that the resulting assembly can run on any version of Windows.

Depending on the platform switch, the C# compiler will emit an assembly that contains either a PE32 or PE32+ header, and the compiler will also emit the desired CPU architecture (or agnostic) into the header as well. Microsoft ships two command-line utilities, DumpBin.exe and CorFlags.exe, which you can use to examine the header information emitted in a managed module by the compiler.

How to find Platform dependency?


CLR Header

2.0 = .NET 1.0 or 1.1
2.5 = .NET 2.0


PE header type
PE32 = 32-bit
PE32+ = 64-bit

CorFlags Different flags
ILONLY Since assembly also allowed to contain native code, to be “AnyCPU” the assembly shall contain only IL.

1 = x86 target
0 = Any CPU target


Signed    1 = Assembly signed
0 = Assembly not signed

In our example we will have to look at three properties to find if the assembly is platform dependent or not.
Even the assembly has only ILCode still it can be platform dependent. PE and 32 Bit properties help to get more information.
PE & 32 Bit –> PE32 & 0

The combination of PE & 32 bit for different platforms are as follows:

  PE 32Bit
AnyCPU PE32 0
X86 PE32 1
x64 PE32(+) 0

So from the information displayed by CorFlags, our test assembly is truly “AnyCPU”

Also CorFlags can be used to forcefully change the PE headers. But personally I don’t like this because if it is compiled like that then it is done for a reason.


What happens during running of the executable?
1.    Windows examines the EXE file’s header to determine whether the application requires a 32 bit or 64 bit address space
2.    A file with PE32 header can run with a 32 bit or 64 bit address space
3.    A file with PE32+ header requires a 64 bit address space
4.    Windows also checks the CPU architecture information embedded inside the header to ensure that it matches the CPU type in computer.

64 bit versions of windows offer a technology that allows 32 bit windows applications to run. This technology is called WOW64 (for windows on Windows 64).It would be more appropriate if it was named as ‘Windows 32 on Windows 64”. To make it simple, WOW64 acts as a layer and let 32 bit process to run as if they are running in 32 bit system even though in real they are running in 64 bit OS. As you see there is an extra cost here. There is no free lunch.

The even allows 32 bit applications with x86 native code in them to run on an Itanium machine, because WOW technology can emulate X86 instruction set, but with a performance cost.

How do we know if the application is running under WOW64?
In the task manager if you see *32 near to your image name then your application is running under 32 bit emulation mode using WOW64. If you want to determine it from out of process use IsWow64Process.

Also you could use Module.GetPEKind to determine the platform targeted by the module.


Resulting managed module X86 Windows X64 Windows IA64 Windows
anycpu PE32/platform agnostic Runs as a 32 bit application Runs as 64 bit application Runs as a 64 bit applications
X86 PE32/X86 Runs as a 32 bit application Runs as a WoW64 application Runs as a WoW64 application
X64 PE32+/X64 Doesn’t run Runs as a 64 bit application Doesn’t run
Itanium PE32+/Itanium Doesn’t run Doesn’t run Runs as a 64 bit application

Where to set the /platform switch in VS


Process of creating a Process

After Windows has examined the EXE file’s header to determine whether to create a 32-bit process, a 64-bit process, or a WoW64 process, Windows loads the x86, x64, or IA64 version of MSCorEE.dll into the process’s address space. On an x86 version of Windows, the x86 version of MSCorEE.dll can be found in the C:\Windows\System32 directory. On an x64 or IA64 version of Windows, the x86 version of MSCorEE.dll can be found in the C:\Windows\SysWow64 directory, whereas the 64-bit version (x64 or IA64) can be found in the C:\Windows\System32 directory (for backward compatibility reasons). Then, the process’ primary thread calls a method defined inside MSCorEE.dll. This method initializes the CLR, loads the EXE assembly, and then calls its entry point method (Main). At this point, the managed application is up and running.


1.    If your application has only managed code then you don’t hesitate to use “Any CPU”. Best option if you are just using pure c#.

2.    If your application (or any of the third party dlls) has direct native code or have an assembly that is targeted to x86 (32) then you cannot run your application in X64 environment because when the dependent assembly (targeted to x86) could not be loaded in x64 environment (will throw bad format exception). In this case, use CorFlags tool to change the target to x64, if you are so sure that can be done!. Otherwise there is a hack.

3.    If any of your code targets specifically x86 platform then you must compile targeting the platform x86. This will run in x64 in emulated mode (WoW64) but bear in mind that you have a performance hit in WoW64.

4.    If any of your code targets specifically x64 platform (this wont occur normally until you have specific pointer sizes) then you got no choice except to compile your application targeting X64.

First Look : ASP.NET 4 Beta 2 and Ajax Preview 4.0

October 23, 2009 Leave a comment

I have just finished reading this white paper. I haven’t tried most of the things; but I wish to list some features which attracted me.

I can see extensive changes in AJAX but apart from that there are some remarkable changes that would give us more control by letting us extend them. As Dino Esposito says changes in ASP.NET 4.0 can be tagged as “More Control”.

The features I like in ASP.NET 4.0 Beta 2:

Core Services

Auto-Start web application:

A new scalability feature named auto-start that directly addresses this scenario is available when ASP.NET 4 runs on IIS 7.5 on Windows Server 2008 R2. The auto-start feature provides a controlled approach for starting up an application pool, initializing an ASP.NET application, and then accepting HTTP requests.

Permanently redirecting a page:

ASP.NET 4 adds a new RedirectPermanent helper method that makes it easy to issue HTTP 301 Moved Permanently responses rather than issuing HTTP 302 Found (Temporarily redirected) response which requires another round trip to the server.

Expanding the range of allowable URLs:

length of URL is no more limited to 260 and can customize the set of valid characters using the new requestPathInvalidChars attribute of the httpRuntime configuration element

Compress Session State:

When the compressionEnabled configuration option shown in the following example is set to true, ASP.NET will compress (and decompress) serialized session state by using the .NET Framework System.IO.Compression.GZipStream class. This is available only when out-of-process session is used.

Do you know it is possible to compress for in-proc session storage scenario also – even in ASP.NET 2.0?

Object Caching and Extensibility:

To make caching available for all applications, the .NET Framework 4 introduces a new assembly, a new namespace, some base types, and a concrete caching implementation.  The new System.Runtime.Caching.dll assembly contains a new caching API in the System.Runtime.Caching namespace.  The namespace contains two core sets of classes:
•    Abstract types that provide the foundation for building any type of custom cache implementation.
•    A concrete in-memory object cache implementation (the System.Runtime.Caching.MemoryCache class).

Extensible request validation:

The request validation feature has been made extensible so that we can use custom request-validation logic.(ASP.NET request validation searches incoming HTTP request data for strings that are commonly used in cross-site scripting (XSS) attacks)


Client Template rendering:

Templates offer the most manageable way of creating UI from data.

The following example shows a typical client template that we can create using ASP.NET AJAX 4.
<ul id="myTemplate" class="sys-template"
        <h3>{{ Name }}</h3>
        <div>{{ Description }}</div>


Data binding

One way live binding is what we saw in previous example {{ Description }}.

Two way live binding is useful when a text box is provided that enables users to modify the value of underlying data, as in the following example:
<input type="text" value="{binding Name}"/>

In two-way live binding, the binding works in both directions. If the target value is changed (in this case, the value in the UI), the source value is automatically updated (in this case, the underlying data item). Similarly, if the source value is changed (in this case, if the underlying data value is updated externally), the target value (the value in the UI) is updated in response. As a result, target and source are always in sync.

Observer Pattern

The observer pattern enables an object to be notified about changes that occur in another object. (The term observer pattern is often misused in JavaScript frameworks to describe event handling based on the addHandler method and similar techniques.) ASP.NET AJAX 4 implements the pattern completely. It adds observer functionality to ordinary JavaScript objects or arrays so that they raise change notifications when they are modified through the Sys.Observer interface

Client side controls

The DataView control can bind to any JavaScript object or array, or to any ASP.NET AJAX component

An example:

<ul sys:attach="dataview" class="sys-template"
    dataview:data="{{ imagesArray }}"
        <h3>{{ Name }}</h3>
        <div>{{ Description }}</div>

End-to-end Ajax-based data scenarios:

The AdoNetServiceProxy class enables read-write interaction with ADO.NET Data Services from JavaScript. The class enables access from JavaScript to a broad range of features of ADO.NET Data Services.

The AdoNetServiceProxy class is used by the AdoNetDataContext class in read-write scenarios that provides full support for change tracking in the browser. This enables complete end-to-end AJAX-based data scenarios.

The following example shows how to use the DataContext class.

<script type="text/javascript">
    var dataContext = new Sys.Data.DataContext();

<button onclick="dataContext.saveChanges()" class="right">Save Changes</button>

<ul sys:attach="dataview" class="sys-template"
    dataview:dataprovider="{{ dataContext }}"
        <input type="text" value="{binding Name}"/><br/>
        <input type="text" value="{binding Description}"/>

Web Form

View state enable/disable for individual controls:

All server controls have EnableViewState property including page. But one must remember that EnableViewState property is ignored for child controls. For example if Page has EnableViewState set to True,  its child control TextBox control will always read and write to the session state no matter what we set for enableviewstate property for that control.

But ASP.NET 4.0 will change this behavior by using a special property named ViewStateMode that accepts 3 values: enabled|disabled|inherit.

This is a good improvement.

Browser capabilities:

ASP.NET 4 includes a feature referred to as browser capabilities providers. As the name suggests, this lets us build a provider that in turn lets us to use our own code to determine browser capabilities.


ASP.NET 4 adds built-in support for using routing with Web Forms. Routing lets us configure an application to accept request URLs that do not map to physical files. Instead, we can use routing to define URLs that are meaningful to users and that can help with search-engine optimization (SEO) for our application. For example, the URL for a page that displays product categories in an existing application might look like the following example:
By using routing, we can configure the application to accept the following URL to render the same information:

Client ID creation

The new ClientIDMode property addresses a long-standing issue in ASP.NET, namely how controls create the id attribute for elements that they render.

The new ClientIDMode property lets you specify more precisely how the client ID is generated for controls. You can set the ClientIDMode property for any control, including for the page. Possible settings are the following:
•    AutoID – This is equivalent to the algorithm for generating ClientID property values that was used in earlier versions of ASP.NET.
•    Static – This specifies that the ClientID value will be the same as the ID without concatenating the IDs of parent naming containers. This can be useful in Web user controls. Because a Web user control can be located on different pages and in different container controls, it can be difficult to write client script for controls that use the AutoID algorithm because you cannot predict what the ID values will be.
•    Predictable – This option is primarily for use in data controls that use repeating templates. It concatenates the ID properties of the control’s naming containers, but generated ClientID values do not contain strings like "ctlxxx". This setting works in conjunction with the ClientIDRowSuffix property of the control. You set the ClientIDRowSuffix property to the name of a data field, and the value of that field is used as the suffix for the generated ClientID value. Typically you would use the primary key of a data record as the ClientIDRowSuffix value.
•   Inherit – This setting is the default behavior for controls; it specifies that a control’s ID generation is the same as its parent.

Query Extender – Filtering

To make data source filtering easier, a new QueryExtender control has been added in ASP.NET 4. This control can be added to EntityDataSource or LinqDataSource controls in order to filter the data returned by these controls. Because the QueryExtender control relies on LINQ, the filter is applied on the database server before the data is sent to the page, which results in very efficient operations.

The QueryExtender control supports a variety of filter options: Search,Range,Property Expression and CustomExpression.

More updates when I really work on them. Notify me incase if I had overlooked an important feature.

Happy learning!

VS2008 Tips : Organize Usings

October 22, 2009 Leave a comment

This was helpful for me manytimes and yet I have seen many developers who are not aware of this feature.


Select the using statements and right click and select Organize usings. There are 3 options available: Remove Unused Usings, Sort Usings and Remove and Sort usings.

After selecting “Remove and Sort Usings” my using statements got reduced to just 7.



Simple and nice feature, right?

“Google”lable and “Bing”able Twitter

October 22, 2009 Leave a comment

Google has reached an agreement with Twitter to include its updates in their search results. And Bing has also done the same kind of arrangements. This is a boost for Twitter and a very good news for us.

Only yesterday I wanted to search a blog post that was recommended by ScottGu. I didn’t remember the URL for the recommended blogpost. I know the words “Top 10 features 4”. The company that I work for, has banned Twitter. I thought of a search service for Twitter provided by other search giants will be good. So now it is available.

I still don’t know how to use Google to search Twitter messages but Bing has already provided an interface to search the Tweets.


But searching for this tweet didn’t bring up any results.

I tried to search by last tweet: Singapore getting diluted. But no luck. Bing didn’t bring up any results either.


But a search on @ScottGu gave few results.


As I can see, these are the results on tweets about scottgu.

Finally I searched using Twitter search itself. And also my tweet on Singapore getting diluted. Aha! The results are just fine. Hope the twitter search from Bing and Google will be OK very soon.

Categories: 2.0 Tags: , , ,

API for Wolfram Alpha

October 16, 2009 Leave a comment

now what? Wolfram Alpha is releasing its API today. The API documentation will be available at

I am particularly interested in this because an API for an engine that searches and computes data might be very helpful at times. The possibilities for applications could be endless. We just need imagination.

I cannot wait to mess up with the API.

A simple search with term “uncertainity principle” gave me nice results, rather than giving me a link to a page that has the data.


I told you, you just need imagination!

Critical October Security patches

October 14, 2009 Leave a comment

Microsoft today released a huge security patch. This includes 13 fixes out of which 6 updates are given as critical and they have the highest priority.


As we see in the priority list given by Microsoft, half of the security patches has something to do with day to day activities of the users or at worst at the server level.

As a web developer I am really concerned about patches for Internet Explorer, .Net CLR and Silver light. As a normal user the patch for Media Runtime and Media Player interests me, because I download loads of media content.

Below are the critical patches as recommended by Microsoft.


Vulnerabilities in Windows Media Runtime Could Allow Remote Code Execution (975682)
This security update resolves two privately reported vulnerabilities in Windows Media Runtime. The vulnerabilities could allow remote code execution if a user opened a specially crafted media file or received specially crafted streaming content from a Web site or any application that delivers Web content. An attacker who successfully exploited these vulnerabilities could gain the same user rights as the local user. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.


Vulnerabilities in SMBv2 Could Allow Remote Code Execution (975517)
This security update resolves one publicly disclosed and two privately reported vulnerabilities in Server Message Block Version 2 (SMBv2). The most severe of the vulnerabilities could allow remote code execution if an attacker sent a specially crafted SMB packet to a computer running the Server service. Firewall best practices and standard default firewall configurations can help protect networks from attacks that originate from outside the enterprise perimeter. Best practices recommend that systems that are connected to the Internet have a minimal number of ports exposed.


Cumulative Security Update for Internet Explorer (974455)
This security update resolves three privately reported vulnerabilities and one publicly disclosed vulnerability in Internet Explorer. The vulnerabilities could allow remote code execution if a user views a specially crafted Web page using Internet Explorer. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.


Vulnerabilities in the Microsoft .NET Common Language Runtime Could Allow Remote Code Execution (974378)
This security update resolves three privately reported vulnerabilities in Microsoft .NET Framework and Microsoft Silverlight. The vulnerabilities could allow remote code execution on a client system if a user views a specially crafted Web page using a Web browser that can run XAML Browser Applications (XBAPs) or Silverlight applications, or if an attacker succeeds in persuading a user to run a specially crafted Microsoft .NET application. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights. The vulnerabilities could also allow remote code execution on a server system running IIS, if that server allows processing ASP.NET pages and an attacker succeeds in uploading a specially crafted ASP.NET page to that server and executing it, as could be the case in a Web hosting scenario. Microsoft .NET applications, Silverlight applications, XBAPs and ASP.NET pages that are not malicious are not at risk of being compromised because of this vulnerability.


Vulnerabilities in GDI+ Could Allow Remote Code Execution (957488)
This security update resolves several privately reported vulnerabilities in Microsoft Windows GDI+. These vulnerabilities could allow remote code execution if a user viewed a specially crafted image file using affected software or browsed a Web site that contains specially crafted content. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.


Vulnerability in Windows Media Player Could Allow Remote Code Execution (974112)
This security update resolves a privately reported vulnerability in Windows Media Player. The vulnerability could allow remote code execution if a specially crafted ASF file is played using Windows Media Player 6.4. An attacker who successfully exploited this vulnerability could gain the same user rights as the local user. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.

Also to check for the missing security updates and misconfigurations we can use Microsoft Baseline Security Analyzer.

For MS09-061 the current vulnerabilities are explained here as follows:

  1. The first issue results from the manner in which Microsoft .NET verifiable code is verified, which could allow attackers to obtain a managed pointer to stack memory that is no longer used, leading to arbitrary code execution.
  2. The second vulnerability results from the manner in which Microsoft .NET verifiable code is verified, which could allow attackers to bypass a type equality check and execute arbitrary code.
  3. The third issue results from the manner in which the Microsoft .NET Common Language Runtime (CLR) handles interfaces, which could allow attackers to execute arbitrary code via a malicious application.