SQL Injection in Stored Procedures cos of Dynamic SQL

Dynamic SQL in Stored Procedures

In some scenarios, it becomes unavoidable to use dynamic SQL in stored procedures. It is not bad at all; it depends on how we form that Dynamic SQL query. As a rule of thumb, it is better to use SQL parameters as replaceable values in dynamic queries instead of concatenation

Let’s dive in a bit:

What is Dynamic SQL?

It is nothing but when you form SQL queries inside a proc and execute them using execute OR exec OR sp_executesql stored procedure.

Example:

CREATE PROCEDURE DynamicSelectProcedure

AS

BEGIN

declare @sql nvarchar(max)

set @sql = concat(N'SELECT * from Person where SSID = ', @testvalue)

execute sp_executesql @sql

END

GO

Above example is not a real-time example for dynamic SQL which could have been easily replaced with simple select with parameter passed to it.

More practical example would be a simple search based on user input. Consider procedure accepts xml or complete search string with all user selected search criteria. SQL query is formed by parsing search criteria xml or search string and executed in procedure. This is a typical example where user has a way to influence the query execution. His search criteria selection will change the dynamic SQL which procedure creates and executes. Everything looks and ola! search works as expected when user inputs like a good caring programmer.

That little room for Injection -SQL injection or SQLi

If you read through above paragraph again, we just gave a little space for a SQL injection attack. As all good books say, using Stored procedures with arguments unarguably avoids SQL injection attacks than executing SQL commands formed from UI. However, in above case we formed a SQL query inside a stored procedure is using user input. A little hacker might just want to play with your search and try to get more information or even do nasty things.

Back to little stored procedure we wrote to select a person based on SSID. We added a vulnerability by appending user input to the query we are forming inside stored procedure. This user input can be formed in a way to attack our database. Just try below user input for test value.

'''4980222'';select top 100 * from IncomeDetails;'

Attacker got for more information (must say sensitive information) than what procedure was supposed to return. Without proper access policies on stored procedure, attacker might be even able to do disastrous things to our database. See below input for stored procedure parameter,

'''4980222'';drop table IncomeDetails;'

Attacker could drop a table of high importance for the system and that’s not even the end!

What did we do wrong here?

“Forgetting the basics”

1. We total forgot parameters concept. By concatenating parameter value to SQL string, we are allowing actual query to be modified anytime dynamically. A parameter value with a query will simply get appended to existing query and hence becomes part of query execution.

When SQL uses parameters, it is considered safe.

Parameters values are not dumbly replaced wherever they’re used. Sql query optimizer will create a query execution plan with parameter as a replaceable value inside the existing query. Parameter value should not contain anything which will change “query estimation\execution plan” i.e. if value contains any dangerous SQL query optimizer identifies it. It cross-checks whether query optimized plan still sticks to original (pre-compiled with only replaced parameter value). This saves us from SQL injection through parameter values.

We can change vulnerable statement of our SP to use parameters like below:

declare @sql nvarchar(max)

   set @sql = 'SELECT * from Person where SSID = @ssid'

   execute sp_executesql @sql, N'@ssid varchar(100)', @ssid=@testValue

        or

Even predefined statement like below. In this case statement is already compiled with only parameter value as variable.

SELECT * from Person where SSID = @testvalue 

Lesson learnt:

Don’t concatenate user input with SQL. Use statements with parameters or pre-created sql statements.

2. Grant Permissions: Every procedure should be associated with correct permissions. In our above procedure, our intention was only to select the data from some table. We can easily avoid any dangerous query like drop, delete or update by controlling user or role based access. We can use or create a role which has only select permission on tables and grant that role with stored procedure execute permissions.

Permission can be granted to a role or user from SQL management studio by selecting Stored Procedures -> Properties -> Permissions. Better way would be to track it through SQL Grant permission command after Create SP code. This SQL script can be reused after dropping and recreating stored procedures with same permissions.

Example:

GRANT EXECUTE ON <<Stored Procedure>>

       TO DBUSER_Reader_Person;

GO

Reversing a Singly Linked List in C#

Brief explanation:

This is pretty straightforward question and most of interviewers would not be interested in asking this. However, this tests our basics of navigating a singly linked list.

Only point to remember here is, we need to keep track of "next", "previous" and "current" node at any point of navigation. We can use these nodes and alter links to change direction of list.

We will start current node set to start of the list, previous is set to NULL since there is nothing before first node. Note that previous node is not connected to "current" node before we start to alter the list.

For every node until it is not null:

1. Connect "current" node to previous (next node is nothing but Current->Next) instead of "next" node. This is the major step where we break link between current to next but connect current to previous i.e. make 
2. Move current to next node,
3. Move previous node to current node.

At the end of above loop, current node would have moved to end and "previous" node will be pointing to the first node of reversed list.

C# Code:

        
        static void ReverseSinglyLinkedLists()
        {
            Node list = new Node()
            {
                data = 'm',
                Next = new Node()
                {
                    data = 'a',
                    Next = new Node()
                    {
                        data = 'n',
                        Next = new Node() { data = 'u', Next = null }
                    }
                }
            };

            Node temp = list;
            //Below code to print initial  list
            StringBuilder initial = new StringBuilder();
            while (temp != null)
            {
                initial.Append(temp.data + " -> ");
                temp = temp.Next;
            }

            initial.Append("NULL");
            Console.WriteLine("Given Singly Linked List: " + Environment.NewLine + initial.ToString());


            temp = list;
            Node prev = null;
            Node cur, next;
            cur = list;
            while(cur!=null)
            {
                next = cur.Next;
                cur.Next = prev;
                
                prev = cur;
                cur = next;
            }


            //prev is now current first node on the revesed list

            //Below code to print reversed list
            StringBuilder reversedResult = new StringBuilder();
            while (prev !=null)
            {
                reversedResult.Append(prev.data + " -> ");
                prev = prev.Next;
            }
            reversedResult.Append("NULL");
            Console.WriteLine("Reversed Singly List: "+ Environment.NewLine+ reversedResult.ToString());
        }

C# XmlSerialization - Generating XSD from XML, Classes from XSD, Serializing Array of Custom Type and Enumerations etc

Generating XSD from XML:

XSD.exe tool can be used to generate schema file from an XML file. XSD.exe will be available with Visual Studio installation. 

• Open Visual Studio Command Prompt 
• Type in the command "xsd <xml file name(including path) to be used for schema generation>"
• Done. XSD gets generated in the same folder where you XSD command is executed.

Now you have the XSD, What next? 

Note: Generated XSD might not be exactly what you want. It is only according to the XML provided. Enumeration Types or Complex Types might not be created in XSD as you are expecting. Example: Printer Type below looks like an enumeration field, but it isn't obvious from the given XML file.

Example XML File:

<XmlSerializable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <Printers>       <Printer Name="Denali" Type="Mfp"/>       <Printer Name="Garnet" Type="NetworkScanner"/>    </Printers> </XmlSerializable>

Generate XSD File:

<?xml version="1.0" encoding="utf-8"?> <xs:schema id="XmlSerializable" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="XmlSerializable" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">     <xs:complexType>       <xs:choice minOccurs="0" maxOccurs="unbounded">         <xs:element name="Printers">           <xs:complexType>             <xs:sequence>               <xs:element name="Printer" minOccurs="0" maxOccurs="unbounded">                 <xs:complexType>                   <xs:attribute name="Name" type="xs:string" />                   <xs:attribute name="Type" type="xs:string" />                 </xs:complexType>               </xs:element>             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:choice>     </xs:complexType>   </xs:element> </xs:schema>

Generate Class from XSD(Schema file):

Again, XSD utility is our buddy here. Run XSD tool with /classes options and provided XSD generated in previous step as input. There you go, XSD generates classes for you.

Auto generated class will have properties and classes generated the way those were present in XSD. In above example, There would be three classes generated, one "XmlSerializable" element, another for element Printers and another for element Printer. Each of these types will "contain" other type as defined by complex types in XSD. 

That said, most of the day to day development activities involve writing classes by serializable by ourselves instead of defining XSDs.  Let's see in brief about how to write xml serializable class in C#.

Coding a Serializable class:

[Everything pertaining to xml serialization is available under the namespace " System.Xml.Serialization"]

Create a class with properties for data that you want to contain in that class. Each of these property can again be a type that is serializable. Attributes on the top of properties control the way the data gets serialized or organized in the XML file. You can find the complete list of attributes available here. Each type which is part of another serializable type can be thought of as Complex type in XSD.

Note: Adding wrong attributes may result in a different xml than what you expect. Read through use the attributes list and their usage for creating serializable class

Example: Marking a property as XmlArray indicates that the property will hold array of items,where you need also to mark the item type by specifying XmlArrayItem.

Enumeration values can be marked by attribute "XmlEnum". Serializer will then place the Enumeration Type in place of Enumeration value in the Serialized Xml.

Steps involved in serialization:

1. Create a XML Serializer isntance by proving the type which you are intending to serialize.
2. Open a stream to the ouput xml file.
3. Provide this stream and instance to the serialize method of serializer instance created.
4. Close the stream and serialized data will be in output file mentioned in step 2.

Serializing Code :

XmlSerializable x = new XmlSerializable();
            x.Printers = new Printer[2];

            x.Printers[0] = new Printer();
            x.Printers[0].Type = PrinterType.Mfp;
            x.Printers[0].Name = "Denali";

            x.Printers[1] = new Printer();
            x.Printers[1].Type = PrinterType.NetworkScanner;
            x.Printers[1].Name = "Garnet";

            XmlSerializer serializer = new XmlSerializer(typeof(XmlSerializable));
            XmlWriter writer = XmlWriter.Create("D:\\output.xml");
            serializer.Serialize(writer,x);
            writer.Close();

Serializable Class example which covers serializing a class which contains array of other class type and also enumerations:

    [Serializable]
    [XmlRoot()]
    public class XmlSerializable
    {
        Printer[] printers;

        [XmlArray("Printers")]
        [XmlArrayItem("Printer")] 
        public Printer[] Printers
        {
            get
            {
                return printers;
            }
            set
            {
                printers = value;
            }
        }
    }
   
    [XmlType("Printer")]         
    public class Printer
    {
        PrinterType type;

        [XmlAttribute()]

        public PrinterType Type
        {
            get
            {
                return type;
            }
            set
            {
                type = value;
            }
        }

        [XmlAttribute()]
        public string Name;


    }
 
    public enum PrinterType
    {
        [XmlEnum]
        None =0,
        [XmlEnum]
        Mfp,
        [XmlEnum]
        Sfp,
        [XmlEnum]
        NetworkScanner
    }

Catching Exceptions from a different thread, Exception Handling in multithreaded environment - C#

Note: Examples are on .NET 3.5 and does not consider .NET 4.0 features like Parallel Task Library.

If there is any exception in a method that is being executed on a different thread, those exception details will not shared with the main thread. It is necessary to catch any exception on a worker\different thread to debug issues especially in multithreaded environment.

// Wrong example: Attempting to catch exception from a thread in caller method
try
{
    Thread t = new Thread(new ThreadStart(LocalMethod));


t.Start();
}
catch (Exception ex)
{
    //do something with ex
}

Few points from MSDN about "there is no such thing called *Unhandled exception* in Managed code" after .NET version 2.0:

/** Straight from MSDN

The most significant change pertains to managed threads. In the .NET Framework versions 1.0 and 1.1, the common language runtime provides a backstop for unhandled exceptions in the following situations:

• There is no such thing as an unhandled exception on a thread pool thread. When a task throws an exception that it does not handle, the runtime prints the exception stack trace to the console and then returns the thread to the thread pool.
• There is no such thing as an unhandled exception on a thread created with the Start method of the Thread class. When code running on such a thread throws an exception that it does not handle, the runtime prints the exception stack trace to the console and then gracefully terminates the thread.
• There is no such thing as an unhandled exception on the finalizer thread. When a finalizer throws an exception that it does not handle, the runtime prints the exception stack trace to the console and then allows the finalizer thread to resume running finalizers.

The foreground or background status of a managed thread does not affect this behavior.

For unhandled exceptions on threads originating in unmanaged code, the difference is more subtle. The runtime JIT-attach dialog preempts the operating system dialog for managed exceptions or native exceptions on threads that have passed through native code. The process terminates in all cases.

**/

Let us take a look at possible ways of handling exceptions from different threads:

a. Adding a method which in turn invokes the method that executes on a different thread and which might be process heavy will allow to catch the exceptions and execute some code to handle this exception. By handling exceptions this way, application can made sure of running even when there are exceptions on different threads.

This pattern is used widely across different multithreaded applications. This method which allows calling thread method and handling exceptions will receive "method to execute" and "exception handler method" as arguments. It invokes "method to invoke" in try block and call the exception handler in catch block. Below code explains this approach, though it is not real-time example.

static void SafeThreadCaller(Action method, Action handler)
        {
            try
            {
                if (method != null)
                {
                    method();
                }
            }
            catch (Exception e)
            {
                handler(e);
            }
        }

        static void ExceptionHandler(Exception e)
        {            
            Console.WriteLine("Handled at ExceptionHandler in SafeHandler thread: "+ e.Message+". Thread ID: "+ Thread.CurrentThread.ManagedThreadId);
        }

        static void ThreadMethod(values v)
        {
            if (v.a > v.b)
                throw new ArgumentException("Arguments Invalid");
        }

Invoking above SafeThreadCaller method is easy using lamda expressions. It expects two Action delegates. If the thread method expects argument, it is easy to use lamda expressions as substitute for delegate as below:

//Caller method
public static void ThreadExceptions()
        {
            Console.WriteLine("In Main Method Thread ID: " + Thread.CurrentThread.ManagedThreadId);
            Thread t = new Thread(() => SafeThreadCaller(() => ThreadMethod(new values() { a = 5, b = 3 }), ExceptionHandler));
            t.Start();
        }

Note: "values" is a simple class created to hold two values a and b.

b. There is another way where information can be passed from thread method to caller through "out" parameters. I'm not big fan of this approach, since this might raise the need for the caller to wait for other thread to join.

public static void ThreadExcetionsAtCaller()
        {
            Exception ex = null;
            Thread t = new Thread(() => ThreadMethodWithOutParameter(new values() { a = 5, b = 3 }, out ex));
            t.Start();
            Console.WriteLine("In Main Method Thread ID: " + Thread.CurrentThread.ManagedThreadId);
            t.Join();
            if (ex != null)
            {
                Console.WriteLine("Handled at Caller: " + ex.Message + ". Thread ID: " + Thread.CurrentThread.ManagedThreadId);
                File.AppendAllText(@"D:\temp\1.txt","Handled at Caller: " + ex.Message + ". Thread ID: " + Thread.CurrentThread.ManagedThreadId);
            }
        }
static void ThreadMethodWithOutParameter(values v,out Exception ex)
        {
            ex = null;
            try
            {
                ThreadMethod(v);
            }
            catch(Exception e)
            {
                ex = e;
            }
        }
static void ThreadMethod(values v)
        {
            if (v.a > v.b)
                throw new ArgumentException("Arguments Invalid");
        }

c. If you like to handle all thread exceptions at once, AppDomain has support for AppDomain.CurrentDomain.UnhandledException and also ThreadException events. Your application can add a event handler to these events to print necessary information for any exception.

This method might work allows us to graceful exit the application on unhandled exception instead of crashing with "Unhandled exception occurred" message to the user. Little of house keeping pertaining to the current application domain can also be taken care before exiting the application.

//Not a perfect example :)
 AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler
                                                (CurrentDomain_UnhandledException);
     void CurrentDomain_UnhandledException(object o, UnhandledExceptionEventArgs u)
     {
          Console.WriteLine("UNHANDLED EXCEPTION IN THE APPLICATION!");        
     }

Note: UnhandledExceptionEventArgs will not have information about StackTrace. We can get information about whether CLR instance is terminating because of the current unhandled exception.

Opening Google Chrome in InCognito mode by default (Private Browsing by default on chrome)

If you like to open chrome always in incognito mode, follow below simple steps:

1. Right click on Google Chrome Shortcut (either on Desktop or Start Menu or wherever the shortcut is located.)
2. Click on Properties.
3. In the Shortcut tab, you will see something like below as Target for the shortcut: C:\..\..\AppData\Local\Google\Chrome\Application\chrome.exe 
4. Add a parameter "incognito" to the Target so that Chrome would always pick this parameter while getting launched through shortcut i.e. change to target to include "-incognito" like below: C:\..\..\AppData\Local\Google\Chrome\Application\chrome.exe  -incognito

Whenever you launch chrome next time using shortcut, by default it gets launched in incognito mode(your browsing history is safe by default.)

Happy Browsing!

Strings in depth C# - Immutability, string interning, String.Intern, Intern Pool and StringBuilder

a. String are immutatble

Immutability means value of a string object can't be modified once its assigned. If a string object is modified to create a different value a new string object with that new value will be created.

Because of this immutable property of strings, any operation like Addition or Deletion  will result in new string creation and it will be performance penalty. 

In cases where we repeatedly add or delete from a string, StringBuilder is suggested. Unlike string any operation on StringBuilder object will work on single instance of string object and hence will be efficient in cases where repeated string modification operations are involved.

b. String is a reference type

I know it's a simple concept yet good to note it.

c. What is string interning?

String interning is a method of storing only one copy of each distinct string value, which must be immutable. Interning strings makes some string processing tasks more time- or space-efficient at the cost of requiring more time when the string is created or interned. The distinct values are stored in a string intern pool.

C# also uses string interning. It interns string based on the literal value. However any operation that results in same literal value as a literal in intern pool does not get to use the existing intern reference.  

Intern Pool:

The common language runtime(CLR) automatically maintains a table, called the "intern pool", which contains a single instance of each unique literal string constant declared in a program, as well as any unique instance of String you add programmatically.

The intern pool conserves string storage. If you assign a literal string constant to several variables, each variable is set to reference the same constant in the intern pool instead of referencing several different instances of String that have identical values.

There are two methods where you can get the intern of a string i.e. get the reference for a string if it's already in the Intern pool.

a. IsInterned

Returns reference to a string if the literal string is already in the intern pool otherwise it returns null.

string s1 = "hello";

string s2 = String.IsInterned("Hello") ;

IsInterned will check intern pool for string with value "hello" and returns reference to that string.

Hence s1 will have reference as s2.

Note: IsInterned does not return bool but returns "null" when value is not present in intern pool.

b. Intern

Returns reference to a string if the literal string is already in the intern pool otherwise create a string with given literal value.

Below block is straight from MSDN about performance considerations when working with intern pool.

Performance Considerations with intern pool:

If you are trying to reduce the total amount of memory your application allocates, keep in mind that interning a string has two unwanted side effects. First, the memory allocated for interned String objects is not likely be released until the common language runtime (CLR) terminates. The reason is that the CLR's reference to the interned String object can persist after your application, or even your application domain, terminates. Second, to intern a string, you must first create the string. The memory used by the String object must still be allocated, even though the memory will eventually be garbage collected.

The .NET Framework version 2.0 introduces CompilationRelaxations.NoStringInterning enumeration member. The NoStringInterning member marks an assembly as not requiring string-literal interning. You can apply NoStringInterning to an assembly using the CompilationRelaxationsAttribute attribute. Also, when you use the Native Image Generator (Ngen.exe) to compile an assembly in advance of run time, strings are not interned across modules.

Longest Common Substring and Longest Common Subsequence (LCS) - Code using C# (Part 1)

Before starting with algorithms, let us understand difference between a substring of a string and a susbsequence of a string. Substring and subsequence are confusing terms. 

Sub-string is the part of the input string itself. It has characters in the same order they appear in the main string. Whereas subsequence is the string which has sequence of characters of main string, which can be formed by deleting one or many characters randomly from the input string.

Example:

Input string    : "HELLO WELCOME EVERYONE"

Sub-string     : "EVERY" or "COME"

Subsequence : "LWCME"

Longest Common substring

Longest Common substring of two given strings is the string which is common substring of both given strings and longest of all such common substrings.

Example:
String 1   : "HELLO WELCOME EVERYONE"
String 2   : "NO TWEET NO NO WELEVERYONEWELHELL"
LCString : "EVERYONE"

One approach of solving this would be using Dynamic Programming.

a. Allocate a 2D array of size [str1.Length, str2.Length]
b. Each value of this matrix saves the information about length of longest substring that's ending at that position. e.g. L[3,4] gives length of longest common substring of first three characters of first input string and first four characters of second input string.
c. If we start with 1st character of string 1 and 1st character of string 2, if they match we can say L[0, 0] = 1
d. We then can build this array by seeing if LCS value at any postion is greater than cached LCS value.
e. We will also save the ending index of the longest common substring till every point when character do not match. This can be used to backtrack and get the actual LCS from given input string.
f. L[str1.Length, str2.Length] will have the final LCS length saved in it.
g. Each step uses LCS value calculated in previous steps and adds one to it if characters match at new indexes in both given strings.

In below C# code I'm assuming array of characters are passed as input instead of string.

public static void PrintLongestCommonSubstring(char[] str1, char[] str2)         {             int[,] l = new int[str1.Length, str2.Length];             int lcs = -1;             string substr = string.Empty;             int end = -1;                         for (int i = 0; i < str1.Length; i++)             {                 for (int j = 0; j < str2.Length; j++)                 {                     //if character at 'i' in string 1 and character at 'j' in string 2                     matches then increment the length of lcs value for that position                     if (str1[i] == str2[j])                     {                         //taking care of array indexes                         if (i == 0 || j == 0)                         {                             l[i, j] = 1;                         }                         else                         {                             //characters match , lets update new lcs value                             l[i, j] = l[i - 1, j - 1] + 1;                         }                         //This is to get the longest common substring                         // use end index in the first string for longest common substring                         if (l[i, j] > lcs)                         {                             lcs = l[i, j];                             end = i;                         }                     }                     else                     {                         //characters do not match use set length of LCS at that position                         as 0                         l[i, j] = 0;                     }                 }             }             for (int i = end - lcs + 1; i <= end; i++)             {                 substr += str1[i];             }                         Console.WriteLine("Longest Common SubString Length = {0}, Longest Common                  Substring = {1}", lcs, substr);         }

NOTE: I hope comments in the above code helps in understanding the code block. 

I will continue this article and explain about finding Longest Common Subsequence in next article.

Algorithm and References: wikipedia (link provided above)