locked
Compiled "LINQ To Entities" Query And Sql parameterization RRS feed

  • Question

  • Why this code does not result in a parameterized query? Does the N prefix prevent sql injection?

    private static readonly Func<Context, string, IQueryable<int>> GetUserIdByEmail_Compiled =
                CompiledQuery.Compile<Context, string, IQueryable<int>>(
                    (ctx, userEmail) => ctx.UserSet.Where(u => u.Email == userEmail).Select(u => u.Id));

            public int GetUserIdByEmail(string email)
            {
                return GetUserIdByEmail_Compiled.Invoke(ctx, email).FirstOrDefault();
                //            "SELECT TOP (1)
                //[Extent1].[Id] AS [Id]
                //FROM [dbo].[UserSet] AS [Extent1]
                //WHERE N'abarref1@gmail.com' = [Extent1].[Email]"

            }

    On the other hand, this code generates a diferent (parameterized) query:

    ctx.UserSet.Where(u => u.Email == email).Select(u => u.Id).FirstOrDefault();
                //            "SELECT
                //[Limit1].[Id] AS [Id]
                //FROM ( SELECT TOP (1)
                //    [Extent1].[Id] AS [Id]
                //    FROM [dbo].[UserSet] AS [Extent1]
                //    WHERE [Extent1].[Email] = @p__linq__0
                //)  AS [Limit1]"

     

    Thanks in advance.

    Sunday, January 22, 2012 2:14 PM

Answers

    • Marked as answer by Alan_chen Wednesday, February 1, 2012 9:16 AM
    Sunday, January 22, 2012 2:30 PM
  • Hi Augusto Barreto,

    Welcome to MSDN Forum.

    SQL injection has been a significant risk for traditional SQL queries formed by concatenating user input. LINQ to SQL avoids such injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input.

    Best Regards.


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Alan_chen Wednesday, February 1, 2012 9:16 AM
    Tuesday, January 24, 2012 3:25 AM

All replies

    • Marked as answer by Alan_chen Wednesday, February 1, 2012 9:16 AM
    Sunday, January 22, 2012 2:30 PM
  • Hi Augusto Barreto,

    Welcome to MSDN Forum.

    SQL injection has been a significant risk for traditional SQL queries formed by concatenating user input. LINQ to SQL avoids such injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input.

    Best Regards.


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by Alan_chen Wednesday, February 1, 2012 9:16 AM
    Tuesday, January 24, 2012 3:25 AM