SQL Injection, and Good Practices on Avoiding the Disaster
Injecting malicious query? No, thanks.
SQL Injection is defined as any vulnerability in which SQL statements are open to exploit and can be used to do anything bad honestly. This raises an issue to developers out there — How can I safely enter user information into the Database?
First of all, always imagine that every single user out there wants to steal your data, I know, this may sound overreacting, but it is better to take precautions than to get your application as the candidate for the biggest data heist of the century.
There are many easy ways in which you can avoid this Injection easily:
- Using ORM
ORM (Object-Relational Mapping) is a technique that allows you to query and manipulate data from database using Object-Oriented Paradigm. There are lots of libraries out there which already implements this technique, such as Entity Framework for C#. ORM allows you to sanitize input using the in-built methods, and ORM handles everything about queries to ease your development.
- Always use Parameterized Query
Parameterized query substitutes argument you entered prior before running the SQL Query, this eliminates “dirty” SQL Statements and will not be executed.
Here is an example of how to use Parameterized Query in C#:
string id = "12345";var query = "SELECT * FROM your_table WHERE id = ? ";SqlCommand command = new SqlCommand(query, connection){CommandType = System.Data.CommandType.Text};command.Parameters.Add("id", OdbcType.VarChar, 255);
command.Parameters["id"].Value = id;
- Sanitizing Inputs
Always sanitize any input from the user, there will always be someone making a mistake and inputting their name on the Date of Birth field. For example, you can use HtmlSanitizer library to sanitize the input.