Preventing SQL injection attacks using C#.NET

[CWA]

What is a SQL injection attack?

Have any of your websites ever been a victim of a SQL injection attack? Well, one of my sites recently was so unfortunate and a lot of data was deleted from the database (Hooray for regular backups ). So, I looked around for a couple of solutions on how to prevent this, and wrote the function below which you can call in C#.NET to remove harmful code from any value passed to the database.

Example:
Note how the single brackets are used on both sides of the SafeSqlLiteral function:
‘” & SafeSqlLiteral(txtInput.Text, 2) & ”’

strQuery = “SELECT * FROM tablename WHERE name = ‘” & SafeSqlLiteral(txtInput.Text, 2) & ”’”;

Namespaces imported

<%@ import Namespace="System" %>
<%@ import Namespace="System.Text.RegularExpressions" %>

And the function to call

public string SafeSqlLiteral(System.Object theValue, System.Object theLevel){

    // Written by user CWA, CoolWebAwards.com Forums. 2 February 2010
    // http://forum.coolwebawards.com/threads/12-Preventing-SQL-injection-attacks-using-C-NET
    
    // intLevel represent how thorough the value will be checked for dangerous code
    // intLevel (1) - Do just the basic. This level will already counter most of the SQL injection attacks
    // intLevel (2) -   (non breaking space) will be added to most words used in SQL queries to prevent unauthorized access to the database. Safe to be printed back into HTML code. Don't use for usernames or passwords
    
    string strValue = (string)theValue;
    int intLevel = (int)theLevel;
    
    if (strValue != null) {
        if (intLevel > 0) {
            strValue = strValue.Replace("'", "''"); // Most important one! This line alone can prevent most injection attacks
            strValue = strValue.Replace("--", "");
            strValue = strValue.Replace("[", "[[]");
            strValue = strValue.Replace("%", "[%]");
        }
        if (intLevel > 1) {
            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;
    }
    else {
        return strValue;
    }
}

[k4reem]

Many thanks CWA, very useful to use
i ve added:
strValue = strValue.Replace(" OR ", "");
strValue = strValue.Replace(" AND ", "");

to level 1, to prevent things like "OR 1=1"

[CWA]

Np! And thank you for the addition to this code

[luicegor]

Yes I have this issue. Our official website always having problem with SQL injection attack. Due the this concern major data was deleted and we need backup. I wish for couple of solution for it.

[nobita_deptrai]

Your article is very good, thank you for sharing tutorial about sql injection. SQL Injection is one of the ways to attack websites that I know now, in addition to the attack site again? I hope you can share your writing more posts for all to see how to attack against web site first result is data loss.

[jonsy]

Very nice tutorial.it is verymuch useful for me to learn c#.net.

[kummaq]

For example I found an blog post .. which I can not link because this posting forum works that way.

try to google: secret-sql-escape-characters

Anyway you should replace "\\\r\n" with "\\\r\n\r\n" which is slightly insane.