Safe SQL Literals

Handle SQL injection

Manage the input data from UI  to be safe  for SQL execution has been problem in many sites which has caused lot of damage to different sites called as SQL injection. Hackers are always there looking into your website to find loop holes.

Still people don't take care to handle these small issues which cause financial loss, as well as companies loose their clients. I am laying out a sample code which would help you to handle inputs for SQL injection.

There are different samples available and the below is the one which I see to be most safest.

public string SafeSqlLiteral(string strValue)
    {
        strValue = strValue.Replace("'", "''"); // Most important one! This line alone can prevent most injection attacks
        strValue = strValue.Replace("--", "").Replace("[", "[[]").Replace("%", "[%]").Replace(" OR ", "").Replace(" or ", "");
        strValue = strValue.Replace(" AND ", "").Replace(" and ", "").Replace("\\\r\n", "").Replace("\\\r\n\r\n", "");

        string[] myArray = new string[] { "xp_ ", "update ", "insert ", "select ", "drop ", "alter ", "create ", "rename ", "delete ", "replace " };
        int i = 0;
        int i2 = 0;
        int intLenghtLeft = 0;
        for (i = 0; i < myArray.Length; i++)
        {
            string strWord = myArray[i];
            Regex rx = new Regex(strWord, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            MatchCollection matches = rx.Matches(strValue);
            i2 = 0;
            foreach (Match match in matches)
            {
                GroupCollection groups = match.Groups;
                intLenghtLeft = groups[0].Index + myArray[i].Length + i2;
                strValue = strValue.Substring(0, intLenghtLeft - 1) + "&nbsp;" + strValue.Substring(strValue.Length - (strValue.Length - intLenghtLeft), strValue.Length - intLenghtLeft);
                i2 += 5;
            }
        }
        return strValue;
    }