Sunday, February 19, 2012

What is SQL Injection in ASP.NET?

SQL injection can occur when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database.

Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:
    * Weak input validation.
    * Dynamic construction of SQL statements without the use of type-safe parameters.
    * Use of over-privileged database logins.

SQL Injection Example

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn. Otherwise, a user put the same string in query string in url that is expecting a user account.

' ; DROP DATABASE pubs  --
Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

// Use dynamic SQL
SqlDataAdapter myCommand = new SqlDataAdapter(
          "SELECT au_lname, au_fname FROM authors WHERE au_id = '" +
          SSN.Text + "'", myConnection);

// Use stored procedures
SqlDataAdapter myCommand = new SqlDataAdapter(
                                "LoginStoredProcedure '" +
                                 SSN.Text + "'", myConnection);
The developer's intention was that when the code runs, it inserts the user's input and generates a SQL the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

However, the code inserts the user's malicious input and generates the following query.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs --'

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. It closes the current statement only if the following parsed token does not make sense as a continuation of the current statement but does make sense as the start of a new statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''
The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

 Note : The semicolon is not necessarily required to separate SQL statements. This is dependent on vendor or implementation, but Microsoft SQL Server does not require them. For example, SQL Server parses the following as two separate statements:


Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.


How's easy to be a hacker. Just kidding! Please keep in mind that you need to validate all untrusted input to your application and you should assume that any input from users is malicious including form fields, query strings, client-side cookies, and browser environment values such as user agent strings and IP addresses.

Reference :

Sunday, February 12, 2012

How to encrypt and decrypt Query String Parameters in ASP.NET

Query strings are very easy way of passing data from one page to another page. The problem with query strings is that the information is visible in the url. However, many developers have been using this way carelessly including me and we are vulnerable to use the query string in some circumstances. In my experience, one friend of mine web site had attacked by hacker using SQL Injection method since he used the Inline SQL Statemnet and Query String in his programming. Hence query strings are able to see, we should encrypt the parameters where it is confidential or not so that we prevent our application from malicious access. In the following sample, I show you using RC2 encryption for the query strings when you click "Details" link on Gridview and it go to other page with encrypted query strings.

Create BasePage.cs as below.
using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.IO;
using System.Collections.Specialized;
using System.Security.Cryptography;
public class BasePage : System.Web.UI.Page
    // Key management for scrambling support
    public byte[] ScrambleKey
            byte[] key = value;
            if (null == key)
                // Use existing key if non provided
                key = ScrambleKey;
            Session["ScrambleKey"] = key;
            byte[] key = (byte[])Session["ScrambleKey"];
            if (null == key)
                RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
                key = rc2.Key;
                Session["ScrambleKey"] = key;
            return key;
    // Initialization vector management for scrambling support
    public byte[] ScrambleIV
            byte[] key = value;
            if (null == key)
                key = ScrambleIV;
            Session["ScrambleIV"] = key;
            byte[] key = (byte[])Session["ScrambleIV"];
            if (null == key)
                RC2CryptoServiceProvider rc2 = new RC2CryptoServiceProvider();
                key = rc2.IV;
                Session["ScrambleIV"] = key;
            return key;
    public string Encrypt(string message)
        UTF8Encoding textConverter = new UTF8Encoding();
        RC2CryptoServiceProvider rc2CSP = new RC2CryptoServiceProvider();

        //Convert the data to a byte array.
        byte[] toEncrypt = textConverter.GetBytes(message);

        //Get an encryptor.
        ICryptoTransform encryptor = rc2CSP.CreateEncryptor(ScrambleKey, ScrambleIV);

        //Encrypt the data.
        MemoryStream msEncrypt = new MemoryStream();
        CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write);

        //Write all data to the crypto stream and flush it.
        // Encode length as first 4 bytes
        byte[] length = new byte[4];
        length[0] = (byte)(message.Length & 0xFF);
        length[1] = (byte)((message.Length >> 8) & 0xFF);
        length[2] = (byte)((message.Length >> 16) & 0xFF);
        length[3] = (byte)((message.Length >> 24) & 0xFF);
        csEncrypt.Write(length, 0, 4);
        csEncrypt.Write(toEncrypt, 0, toEncrypt.Length);

        //Get encrypted array of bytes.
        byte[] encrypted = msEncrypt.ToArray();

        // Convert to Base64 string
        string b64 = Convert.ToBase64String(encrypted);

        // Protect against URLEncode/Decode problem
        string b64mod = b64.Replace('+', '@');

        // Return a URL encoded string
        return HttpUtility.UrlEncode(b64mod);
    public string Decrypt(string scrambledMessage)
        UTF8Encoding textConverter = new UTF8Encoding();
        RC2CryptoServiceProvider rc2CSP = new RC2CryptoServiceProvider();
        // URL decode , replace and convert from Base64
        string b64mod = HttpUtility.UrlDecode(scrambledMessage);
        // Replace '@' back to '+' (avoid URLDecode problem)
        string b64 = b64mod.Replace('@', '+');
        // Base64 decode
        byte[] encrypted = Convert.FromBase64String(b64);

        //Get a decryptor that uses the same key and IV as the encryptor.
        ICryptoTransform decryptor = rc2CSP.CreateDecryptor(ScrambleKey, ScrambleIV);

        //Now decrypt the previously encrypted message using the decryptor
        // obtained in the above step.
        MemoryStream msDecrypt = new MemoryStream(encrypted);
        CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read);

        byte[] fromEncrypt = new byte[encrypted.Length - 4];

        //Read the data out of the crypto stream.
        byte[] length = new byte[4];
        csDecrypt.Read(length, 0, 4);
        csDecrypt.Read(fromEncrypt, 0, fromEncrypt.Length);
        int len = (int)length[0] | (length[1] << 8) | (length[2] << 16) | (length[3] << 24);

        //Convert the byte array back into a string.
        return textConverter.GetString(fromEncrypt).Substring(0, len);
    public NameValueCollection DecryptQueryString(string scrambledMessage)
        // Decode the query string
        string queryString = Decrypt(scrambledMessage);

        NameValueCollection result = new NameValueCollection();
        char[] splitChar = new char[] { '&' };
        char[] equalChar = new char[] { '=' };
        // Split query string to components
        foreach (string s in queryString.Split(splitChar))
            // split each component to key and value
            string[] keyVal = s.Split(equalChar, 2);
            string key = keyVal[0];
            string val = String.Empty;
            if (keyVal.Length > 1) val = keyVal[1];
            // Add to the hashtable
            result.Add(key, val);
        // return the resulting hashtable
        return result;

Create a .aspx page and extend BasePage.cs we created before.
Put the following gridview that use ObjectDataSource and "Northwind" DB in <form> tag(for creating the below gridview and data access, you can see in the my previous post).
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" CellPadding="4"
    AllowPaging="True" AllowSorting="True" AlternatingRowStyle-Wrap="True"
    DataSourceID="ObjectDataSource2" EmptyDataText="There is no data.">
    <RowStyle BackColor="#EFF3FB" />
        <asp:BoundField HeaderText="No." DataField="No" />
        <asp:BoundField HeaderText="CustomerID" DataField="CustomerID" SortExpression="CustomerID"/>
        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" SortExpression="CompanyName" />
        <asp:BoundField HeaderText="ContactName" DataField="ContactName" SortExpression="ContactName" />
        <asp:TemplateField HeaderText="Action" >
                 <asp:HyperLink ID="hlkdetail" runat="server"
                 NavigateUrl='<%# querystringencodedecode.aspx?query=" + Encrypt("ID=" + Eval("CustomerID"))%>'
    <AlternatingRowStyle BackColor="White" />
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" SelectMethod="GridDataPage"
    TypeName="DAL" SelectCountMethod="DataRowCount"
    SortParameterName="SortExpression" EnablePaging="True">
        <asp:Parameter Name="maximumRows" Type="Int32" />
        <asp:Parameter Name="startRowIndex" Type="Int32" />
        <asp:Parameter Name="SortExpression" Type="String" />

Create querystringencodedecode.aspx and put the below label control to <form> tag.
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

Add the following code to Page_Load.
protected void Page_Load(object sender, EventArgs e)
    if (!string.IsNullOrEmpty(Request.QueryString["query"]))
        System.Collections.Specialized.NameValueCollection querystring = DecryptQueryString(Request.QueryString["query"]);
        string id = querystring["ID"];
        //Handle id here.
        Label1.Text = id.ToString();

Here are two snapshots to show how this example works.
Gridview page
When you click on Detail link, it go to below page with encrypted query string and then decrypt the query string again and show the parameter value on page.

You should avoid the query string method for passing data between pages when your data is important.

Original post :

Saturday, February 4, 2012

How to show PDF in new browser without showing Download File dialog

Sometimes, we need to show user guideline or user manual as PDF format in browser and we want to generate the PDF file and show it in browser according to their user info often.This example illustrates the simple way to show the PDF in new/tab browser without showing "Download File dialog" as below.

PDF in browser
Create a .aspx page and add the following control to <form> tag. Its navigateUrl will redirect the "AppGenerator.ashx" page we created later (navigateUrl has querystring also that it just show how to pass the parameter and we don't use).

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/AppGenerator.ashx?id=1" Target="_blank">PDF Generate</asp:HyperLink>

Creaet a Generic Handler AppGenerato.ashx here as below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
public class AppGenerator : IHttpHandler
    public void ProcessRequest(HttpContext context)
            int id;
            string Qs = context.Request.QueryString["id"];

            if (int.TryParse(Qs, out id))
                //go here to handle the querystring parameter
                //you can get the byte array data from your DB depend on parameter.

                //I use the static file here for sample and added pdf file in "Files" folder.
                string fileName = "Tutorial.pdf";
                string fullPath = context.Request.MapPath("~/Files/" + fileName);//here is relative file path in your project.
                //get byte array from file.
                System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
                int byteLen = (int)fs.Length;
                byte[] file = new byte[byteLen];
                fs.Read(file, 0, byteLen);

                //context.Response.ContentType = "image/jpeg"; // for images
                //context.Response.ContentType = "image/gif";
                //context.Response.ContentType = "image/png";
                //context.Response.ContentType = "text/plain";  for .txt
                //context.Response.ContentType = "text/HTML";   for .html, .htm

                context.Response.ContentType = "application/pdf";
        catch (Exception ex)
        { }
    public bool IsReusable
            return false;
That's it. When you click on the  "PDF Generate" hyperlink, it show the PDF on new browser. I haven't found yet for showing MS Word or Excel in browser. If you've got, pls drop a line. :)