2

I'm using asp.net MVC 4 to make a website where user can upload .xlsx file and save the data to MSSQL table. I want to make sure that there is no illegal characters such as SQL injection statements in the file before saving the datas. So far I tested out with $ sign it works fine but it'll only catch if a cell has only that character, not in between characters. Here is my code,

Controller

    public ActionResult BulkReadings()
    {
        string pathToExcelFile = System.IO.Path.Combine(Server.MapPath("~/ExcelFiles/"), "BulkReads.xlsx");
        string sheetName = "Sheet1";

        var excelFile = new ExcelQueryFactory(pathToExcelFile);
        var getSheet = from a in excelFile.Worksheet(sheetName) select a;
        string Subject = "";
        string Type = "";
        string Reading = "";

        foreach (var a in getSheet)
        {
            if (a["Subject"] == "$" || a["Type"] == "$" || a["Reading"] == "$")  // This is where it checks for the "$" sign
            {
                if (System.IO.File.Exists(pathToExcelFile))
                {
                    System.IO.File.Delete(pathToExcelFile);
                }
                TempData["meter_fail"] = "Error! Illegal Characters!";
                return RedirectToAction("MeterManager");
            }
            else
            {
                Subject = a["Subject"];
                Type = a["Type"];
                Reading = a["Reading"];
                try
                {
                    Reading newEntry = new Reading();
                    newEntry.title = Subject;
                    newEntry.type = Type;
                    newEntry.reading1 = Reading;
                    rentdb.Readings.Add(newEntry);
                }
                catch
                {
                    if (System.IO.File.Exists(pathToExcelFile))
                    {
                        System.IO.File.Delete(pathToExcelFile);
                    }
                    TempData["meter_fail"] = "Error! Upload Failed!";
                    return RedirectToAction("MeterManager");
                }
            }
        }
        rentdb.SaveChanges();
        if (System.IO.File.Exists(pathToExcelFile))
        {
            System.IO.File.Delete(pathToExcelFile);
        }
        TempData["meter_success"] = "Reading(s) uploaded successfully!";
        return RedirectToAction("MeterManager");
    }

How can I check for multiple illegal characters that can be present as single or with other characters in the cell?

halfer
  • 19,824
  • 17
  • 99
  • 186
Shihan Khan
  • 2,180
  • 4
  • 34
  • 67

1 Answers1

1

As @Sam Axe stated, the best way to avoid sql injection attacks is to parameterize your queries. Parameters are placeholders for values instead of using the user-input values.

For example:

using (SqlConnection conn = new SqlConnection(NorthwindConnectionString))
{
    string query = "SELECT * FROM Products WHERE ProductID = @Id";
    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.Parameters.AddWithValue("@Id", Request.QueryString["Id"]);
    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        DetailsView1.DataSource = rdr;
        DetailsView1.DataBind();
    }
}

Here is some further reading on it: https://msdn.microsoft.com/library/bb738521(v=vs.100).aspx

Ageonix
  • 1,748
  • 2
  • 19
  • 32
  • Thanks, but I'm using `EF 6`, any chance I can do that with EF? – Shihan Khan Feb 06 '16 at 06:17
  • I haven't personally done it, but it looks like it can be done there as well. Here's an article where it's being done: http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application – Ageonix Feb 06 '16 at 06:25