Sql Injection Attacks and Subsonic

Question

If I use SubSonic to create DAL for my web project do I need to worry about preventing SQL Injection Attacks?

Answer 1

No, SubSonic uses parameters to pass data into the database, it takes care of this.

Answer 2

This depends on how you construct your queries. It is totally possible to write unsafe queries with subsonic if you don't use parameters.

// Bad example:

string sql = "delete from Products where ProductName = " + rawUserInput;
QueryCommand qry = new QueryCommand(sql, Product.Schema.Provider.Name);
DataService.ExecuteQuery(qry);

// Should be:

string sql = "delete from Products where ProductName = @TargetName";
QueryCommand qry = new QueryCommand(sql, Product.Schema.Provider.Name);
qry.AddParamter("@TargetName", rawUserInput, DbType.String);
DataService.ExecuteQuery(qry);

Answer 3

The Short answer is no. If you use the Subsonic Generated classes or the Subsonic.Select class to generate your queries/update/insert statements then you do not need to worry in that SubSonic uses Parameters correctly.

Paul did point out however that if you go out of your way to write unsafe SQL that SubSonic will allow you to. SubSonic isn't your mother it won't stop you it is more like your best friend, it will tell you not to but if you decide to do it that is your decision.

Answer 4

Just to re-present the example of Paul (if you FK constraints )

     string rawUserInput = "Queso Cabrales1";
     #region  BadExample
     //string sql = "delete from Products where ProductName = " + rawUserInput;
     ////QueryCommand objQueryCommand = new QueryCommand(sql, Product.Schema.Provider.Name);
     ////DataService.ExecuteQuery(objQueryCommand);
     #endregion BadExample

     #region BetterExample
     // Should be:

     string sql = "update Products set ProductName =  @ProductName where ProductName='Queso Cabrales'";
     QueryCommand objQueryCommand = new QueryCommand(sql, Northwind.Product.Schema.Provider.Name);
     objQueryCommand.AddParameter("@ProductName" , rawUserInput, DbType.String);
     DataService.ExecuteQuery(objQueryCommand);


     panGvHolder.Controls.Clear();

     Query qry = Northwind.Product.CreateQuery();
     qry.Columns.AddRange(Northwind.Product.Schema.Columns);
     qry.WHERE("UnitPrice > 15").AND("UnitsInStock < 20 ");
     //WHERE("UnitPrice > 15").AND("UnitsInStock < 30 ");
     #endregion BetterExample

     #region PresentResultsReplaceResponseWriteWithConsole.WriteLineForConsoleApp
     using (IDataReader rdr = qry.ExecuteReader())
     {
      Response.Write("<table>");
      while (rdr.Read())
      {
       Response.Write("<tr>");
       for (int i = 0; i < rdr.FieldCount; i++)
       {
        Response.Write("<td>");
        Response.Write(rdr[i].ToString() + " ");
        Response.Write("<td>");
       } //eof for 
       Response.Write("</br>");
       Response.Write("</tr>");
      }
      Response.Write("<table>");
     }
     #endregion PresentResultsReplaceResponseWriteWithConsole.WriteLineForConsoleApp

    } //eof method