1

I have a need to run raw SQL in the context of EF for performance reasons. I cannot use Stored Procedures as I don't have control over the DB. I have the following query that works fine. However it is generating the insert values using string manipulation which would lead to SQL Injection. How do I safe guard from SQL Injection but still achieve the same result?

  public IEnumerable<CustomInfo> GetCustomInfo(List<string> inputs)
  {
         ...
         ...
         string baseQuery = @"
               DECLARE @tempTable TABLE ([tempCol] VARCHAR(1000));
               INSERT INTO @tempTable ([tempCol])
               VALUES {0};
                SELECT
                   a.[Attribute1],
                   b.[Attribute2] 
                FROM  Apple AS a
                INNER JOIN Badminton AS b ON a.[Id] = b.[AId]
                INNER JOIN @tempTable tt on tt.tempCol = a.[Attribute2]";

         var tmpTableValues = $"('{string.Join("'), ('", inputs)}')";
         var query = string.Format(baseQuery, tmpTableValues);

         return context.MyData.SqlQuery<CustomInfo>(query).ToList();
   }
softwarematter
  • 28,015
  • 64
  • 169
  • 263

0 Answers0