CODEWITHSUNDEEP
c#asp.net.netsql-serverentity-framework
Mikkel Refsgaard
Mikkel Refsgaard

Reputation: 111

How can I secure SQL parameters with entity framework SqlQuery?

I have this method that should take an unknown amount of id's. I got this method almost done but it isnt secure yet for obvious reasons, i know i could write my own method to strip the parameters but i would be more comfortable by using some build in method for this.

Here is the method

public static List<LocationModel> FetchCitiesByAreas(IEnumerable<string> areas)
    {
        using (var db = new BoligEnt())
        {
            var sqlQuery = new StringBuilder();
            var first = true;

            sqlQuery.Append("SELECT DISTINCT a.city AS City, a.zip AS Zip ");
            sqlQuery.Append("FROM zip_city AS a ");
            sqlQuery.Append("WHERE country = 1 ");

            foreach (var d in areas)
            {
                if (first)
                {
                    sqlQuery.Append("AND a.area_id = '" + d + "'");
                    first = false;
                }
                else
                {
                    sqlQuery.Append("OR a.area_id = '" + d + "'");
                }

            }

            return db.Database.SqlQuery<LocationModel>(sqlQuery.ToString()).ToList();
        }
    }

i know it have this function built in but as i stated earlier i dont know the exact amount of ids that will come in

db.Database.SqlQuery<LocationModel>("SELECT * FROM table WHERE id = @p0 ;", id).ToList();

Thanks

Upvotes: 2

Views: 351

Answers (2)

crthompson
crthompson

Reputation: 15865

Why dont you just use Linq?

var locations = (from zip in db.zip_city
                 where areas.Contains(zip.area_id) && zip.Country == 1
                 select new LocationModel{
                   City = zip.City,
                   Zip = zip.Zip
                  })
          .Distinct()
          .ToList();

If you still want to parameterize your query, you need to use EntityCommand

Also note that your query will fail because you havent put parenthesis around your OR statements.

I suggest structuring your sql like this:

string sqlQuery =
    @"SELECT DISTINCT a.city AS City, a.zip AS Zip
         FROM zip_city AS a 
         WHERE country = 1 AND (1=0 "

   for (int i = 0; i < areas.Count; i++)
   {
       sqlQuery.Append("OR a.area_id = @d" + i.ToString() + " ");
   }
   sqlQuery.Append(")");

Upvotes: 3

entropic
entropic

Reputation: 1683

While I completely agree with paqogomez, in that you should just use LINQ to do the query, the .SqlQuery has the ability to take a parameter array. You could change your statement to look like this:

var sqlQuery = new StringBuilder();

sqlQuery.Append("SELECT DISTINCT a.city AS City, a.zip AS Zip ");
sqlQuery.Append("FROM zip_city AS a ");
sqlQuery.Append("WHERE country = 1 ");

for (int i = 0; i < areas.Count; i++)
{
    if (i == 0)
    {
        sqlQuery.Append("AND (a.area_id = @p" + i.ToString());
    }
    else
    {
        sqlQuery.Append(" OR a.area_id = @p" + i.ToString());
    }
}
sqlQuery.Append(")");

var results = db.Database.SqlQuery<LocationModel>(sqlQuery.ToString(), areas.ToArray()).ToList();

I added the missing parenthesis needed to your query to correctly filter out the OR results as well. I've also taken the assumption that areas is something like a List, or at least something you can easily get the count from.

Upvotes: 3

Related Questions