CODEWITHSUNDEEP
c#subsonicsql-injection
TheVillageIdiot
TheVillageIdiot

Reputation: 40497

Sql Injection Attacks and Subsonic

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

Upvotes: 2

Views: 1644

Answers (4)

Yordan Georgiev
Yordan Georgiev

Reputation: 5420

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

Upvotes: 1

runxc1 Bret Ferrier
runxc1 Bret Ferrier

Reputation: 8233

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.

Upvotes: 3

P a u l
P a u l

Reputation: 7911

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);

Upvotes: 6

Pawel Krakowiak
Pawel Krakowiak

Reputation: 10090

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

Upvotes: 6

Related Questions