CODEWITHSUNDEEP
c#asp.netlinqsearchsql-injection
Shrivatsan
Shrivatsan

Reputation: 105

How to use Linq instead of SQL Injection query for custom search

I would like to use Linq instead of below hardcoded Sql Injection to search from SqlServer DATABASE TABLES. How to retrieve Dynamically generated web controls input texts in C# Linq and replace the entire Sql injection in Linq for searching.

My C# code:

protected void Search_Button_Click(object sender, EventArgs e) 
{
    try 
     {  
       Table maintable = Select.FindControl("dynamic_filter_table_id") as Table;
       int rc = maintable.Rows.Count;
            if (rc == 1)
            {

                DropDownList D1 = maintable.FindControl("MainDDL") as DropDownList;
                if (D1.SelectedValue.Contains("decimal"))
                {
                    TextBox T1 = maintable.FindControl("txtbox1") as TextBox;
                    TextBox T2 = maintable.FindControl("txtbox2") as TextBox;
                    SqlDataAdapter sql = new SqlDataAdapter("SELECT F.Col1,F.Col2,V.COL1, col2,col3, col4 , col5, cl6 FROM TABLE1 as V , TABL2 as F WHERE V.Col1 = F.Col1 AND " + DDL1.SelectedItem.Text + " >= " + T1.Text + " AND " + DDl1.SelectedItem.Text + " <= " + T2.Text, con);

                    DataSet data = new DataSet();
                    sql.Fill(data);
                    con.Close();
                    Session["DataforSearch_DDL"] = data.Tables[0];
                }
            }
       }

    catch
    {
      ImproperSearch();
    }
}

Upvotes: 0

Views: 472

Answers (2)

jgauffin
jgauffin

Reputation: 101150

Why don't you just rewrite your query to be SQL-injection safe? LINQ won't give you any benefit.

You can achieve that by doing two things.

The first is to secure the column names. That is accomplished by specifying which characters is allowed for column names (more secure than trying to figure out what characters is not allowed). In this case I remove everything but letters and digits. If you have column names which contains underscore, just add that to the check.

The next thing is to use parameterized queries. Each ADO.NET driver has built in support for that, so you just have to specify the value using cmd.Parameters.AddWithValue. By doing so the value isn't part of the query string and hence there is no potential SQL injection.

using (var con = yourConnectionFactory.Create())
{
    using (var cmd = new SqlCommand(con))
    {
        var safeKey1 = OnlyLettersAndDigits(DDL1.SelectedItem.Text);
        var safeKey2 = OnlyLettersAndDigits(DDL2.SelectedItem.Text);

        cmd.CommandText = "SELECT F.Col1,F.Col2,V.COL1, col2,col3, col4 , col5, cl6 " + 
                              " FROM TABLE1 as V , TABL2 as F WHERE V.Col1 = F.Col1 " +
                              " AND " + safeKey1 + " >= @text1 " + 
                              " AND " + safeKey2 + " <= @text2 ";
            cmd.Parameters.AddWithValue("text1", T1.Text);
            cmd.Parameters.AddWithValue("text2", T2.Text); 
            var adapter = new SqlDataAdapter(cmd);

            var data = new DataSet();
            sql.Fill(data);
            Session["DataforSearch_DDL"] = data.Tables[0];
    }
}

public string OnlyLettersAndDigits(string value)
{
    var stripped = "";
    foreach (var ch in value)
    {
        if (char.IsLetterOrDigit(ch))
            stripped += ch;
    }

    return stripped;
}

Upvotes: 4

cachet.net
cachet.net

Reputation: 320

You can use store procedure for customer search, It will work for both ADO.Net and LINQ approach. Just create a SP and add it to your DBML file very simple.

Here is the link how you can use SP in LINQ

http://www.mssqltips.com/sqlservertip/1542/using-stored-procedures-with-linq-to-sql/

Upvotes: 2

Related Questions