September 22, 2012

C# - Avoid SQL Injection By Validating Strings


What is SQL Injection:
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.
By MSDN

For Eg:
Assume the below dynamic query going to execute
String sql = "select * from Products where ProductID = '" + @ProductID + "'";

However, assume a user enters @ProductID as P213445'; drop table Products--

After assembled the above query will be
select * from Products where ProductID = 'P213445'; drop table Products--

This one syntactically correct, if this executes means we lost a table, Right..!!
So We should keep in mind SQL Injection while developing.
We can avoid SQL Injection by several methods, I given one of it.

Use the below one validate user input

//** Doubles up single quotes to stop breakouts from SQL strings **
public static string SQLSafe(string strRawText)
{
    string strCleanedText = "";
    int iCharPos = 0;
 
    while (iCharPos < strRawText.Length)
    {
        //** Double up single quotes, but only if they aren't already doubled **
        if (strRawText.Substring(iCharPos, 1) == "'")
        {
            strCleanedText = strCleanedText + "''";
            if (iCharPos != strRawText.Length)
            {
                if (strRawText.Substring(iCharPos + 1, 1) == "'")
                    iCharPos = iCharPos + 1;
            }
        }
        else
        {
            strCleanedText = strCleanedText + strRawText.Substring(iCharPos, 1);
        }
 
        iCharPos++;
    }
 
    return strCleanedText.Trim();
}

1 comment:

  1. 1. .NET has all these functionalities build in! Use them!
    2. Use the StringBuilder!

    ReplyDelete

Recommended Post Slide Out For Blogger