When you want to pass ad hoc or user supplied values to your SQL command at run time, it is important to use parameters to represent them in order to prevent the possibility of your application being exposed to SQL injection attacks.

SQL Injection is a technique that results in unauthorized SQL commands being executed against your database. SQL injection occurs usually as a result of taking user input and concatenating it with hard coded SQL statements that form part of an application's code base. The user input has been crafted specifically to alter the SQL that the program's designer intended to execute.

The canonical example used to illustrate the issue involves a login form. Typically, a login form is designed to accept a user name and a password that uniquely identifies an individual. If a valid combination is presented, the user is authorized to access protected parts of the application. A vulnerable SQL statement designed to check that submitted credentials against those stored in the database might look like this:

    var sql = "select * from tblusers where username = '" + username + "' and password = '" + password + "'";

The username and password variables represent the values submitted by the user. They are concatenated with the SQL to generate the command that is executed. If those values are "mike" and "pass1" respectively, the generated SQL will be:

1.     select * from tblusers where username = 'ravinder' and password = 'singh123'
2. 


The command, when executed should only return rows if a match for those values is found. However, what happens if the value submitted for the password is ' or ''='? The resulting SQL will become:

        select * from tblusers where username = 'ravinder' and password = '' or ''=''

This completely alters the effect of the SQL so that it returns all rows in the user table, because ''='' is always true. Additional SQL syntax has been injected into the statement to change its behavior. The single quotes are string delimiters as far as T-SQL is concerned, and if you allow users to enter these without managing them, you are asking for potential trouble.

Often, you will see well-meaning advice from people that you should escape single quotes, which converts them to literal values instead of SQL syntax:

1. username = username.Replace("'","''");
2. password = password.Replace("'","''");
3. var sql = "select * from tblusers where username = '" + username + "' and password = '" + password + "'";
4. 


Indeed, this has the desired effect. The generated SQL will no longer return all rows in the database. However, this does not mitigate against all forms of SQL injection. Consider the very common scenario where you are querying the database for an article, product or similar by ID. Typically, the ID is stored as a number - most of them are autogenerated by the database:

1. var sql = "select * from products where productid = " + productid;
2. 


The value for the productid variable could come from a posted form, or a query string value - perhaps from a hyperlink on a previous page. It's easy for a malicious user to amend a query string value.

 Imagine that the malicious user appends ;drop table AspNetUsers-- on the end of the query string before requesting the page. Now the generated SQL becomes

Any database that can manage batch commands (i.e. any worth using) will execute this and the AspNetUsers table will disappear.

Some people advise that you should validate all user input against a blacklist of SQL keywords and syntax, but the problem with this approach is that there are perfectly valid reasons for users to submit values that might include entries in the blacklist. In addition, you will have to go back and patch your blacklist every time your database provider adds new keywords to their product as they improve its feature set.

Thanks, for reading the blog, I hope it helps you. Please share this link on your social media accounts so that others can read our valuable content. Share your queries with our expert team and get Free Expert Advice for Your Business today.