C# .NET Programming Blog

SQL injection is one of the most common hacking methods for websites where authentication is required for accessing the website content. In this article we try to verify if linq based data access layers are truly immune to SQL injection.

SQL Injection

To give a brief about SQL injection to those who are unaware of it. Consider a form where we accept user’s login id and his password and verify whether a user exists in database matching with those credentials. For validation we write a validation method with an inline query passing the login id and password, something similar to the code shown below.

Show Code

public bool Validate(string loginId, string password)
{
    string query =
        String.Format(@"SELECT * FROM USER_PROFILE
                        WHERE LOGIN_ID = '{0}' AND PASSWORD = '{1}'",
                        loginId, password);

    SqlDataAdapter da = new SqlDataAdapter(query, connection);
    DataTable dt = new DataTable();
    da.Fill(dt);

    if (dt.Rows.Count > 0) return true;
    else return false;
}

For a valid login id ‘admin’ with password ’123′ the query formed will be as shown below

SELECT * FROM USER_PROFILE
WHERE LOGIN_ID = 'admin' AND PASSWORD = '123'

To perform SQL injection on this website all I have to do is enter login id as ” OR 1=1 –’ and password anything lets say just blank ”. This time the query formed will be as shown below.

SELECT * FROM USER_PROFILE
WHERE LOGIN_ID = '' OR 1=1 --' AND PASSWORD = ''

If you look at the above query carefully. You will see that this query will always return a result set. The where condition LOGIN_ID=” will be false but the second condition OR 1=1 will always be true. The password part is commented out using –.

So using this simple trick we don’t need to know the user id or password to the website to log in. The effets of SQL injection can be far more worse if someone decides to run a truncate query or a drop table query separated by a semicolon.

Validation Using Linq

For the same example above we use Linq instead of ADO.net classes for data access. The same validation method is written as shown below.

Show Code

public bool Validate(string loginId, string password)
{
    DataClassesDataContext db = new DataClassesDataContext();

    var validUsers = from user in db.USER_PROFILEs
                     where user.LOGIN_ID == loginId
                     && user.PASSWORD == password
                     select user;

    if (validUsers.Count() > 0) return true;
    else return false;
}

You can try doing SQL injection on the above code. It will not work since linq writes parametrized, precompiled queries. This is the reason why SQL injection is not effective on linq generated queries. The linq generated query is shown below.

SELECT [t0].[LOGIN_ID], [t0].[PASSWORD]
FROM [dbo].[USER_PROFILE] AS [t0]
WHERE ([t0].[LOGIN_ID] = @p0) AND ([t0].[PASSWORD] = @p1)

The difference in the two queries is that in the inline query the parameters are substituted first and then the query gets compiled. Whereas in the linq query the query gets compiled first and then the parameters @p0 and @p1 are substituted. This ensures that the login id is substituted as the login id itself and does not alter the query in any way.

Use of Lambda Expressions

An even more efficient way of performing the validation is using lambda expressions as shown below.

Show Code

USER_PROFILE validUser = db.USER_PROFILEs.Single(
    user => user.LOGIN_ID == loginId
    && user.PASSWORD == password);

The above expression will return the single user who matches the credentials. If there are more than one user or no user that matches the provided credentials then it will raise an exception.

Conclusion

Linq based data access layers are perfectly safe against SQL injection attacks.