Have you failed a job interview?
Send us your job interview questions and get correct answers.

BP14: Use parametrized sql queries

Using parametrized sql queries will help you to prevent sql injection attacks. Do not ever compose the sql command (T-SQL) using string concatenation of string interpolation.

Safe approach

SqlCommand command = ...;
command.CommandText = "SELECT * FROM Customes WHERE CustomerId = @CustomerId";
command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId; command.ExecuteNonQuery();

Security hole 1

SqlCommand command = ...;
command.CommandText = "SELECT * FROM Customes WHERE CustomerId = {customerId}";
command.ExecuteNonQuery();

You are may be thinking that the customerId is int, so what's the problem? It's just a matter of time until someone decides to change the data-type of he customerId value o string and then you have a problem.

Security hole 2

SqlCommand command = ...;
command.CommandText = "SELECT * FROM Customes WHERE CustomerId = " + customerId.ToString();
command.ExecuteNonQuery();

Same as at the previous security hole, it's just a matter of time until someone changes the customerId to some other type during refactoring and then you have a hidden problem just waiting to be exploited.

Comments

No Comments Yet.
Be the first to tell us what you think.

Download Better Coder application to your phone and get unlimited access to the collection of enterprise best practices.

Get it on Google Play