In this section, we will address the issue of SQL injections, which is a longstanding that demands utmost caution due to its severe consequences. Let’s examine the problem of SQL injection in detail.
Suppose we have a query that retrieves the “city” value from an HTML Form submitted, and we make the decision to directly incorporate this input into our query statement using the following code:
var city = Request.Form("city");
var sql = "SELECT * FROM Orders WHERE City = '" + city + "'";Dynamic SQL queries like this should be avoided, particularly when the input originates from the user. What can go wrong in this scenario? Well, if the user enters a valid city name, the resulting query will be fine, such as “SELECT * FROM Orders WHERE City = ‘Dubai'”. However, if the user enters somthing like “Dubai; DROP TABLE Order —“, with the additional statement causing potential harm. The double dashes indicate that everything following them is treated as a comment, and if not careful, one can risk losing all the Orders data.
It is crucial not to rely solely on data provided by the client side. What can be done to mitigate this issue? Let’s explore a few solutions.
One possible solution is to utilize stored procedures:
var myCommand = new SqlDataAdapter("MyStoredProcedure", connection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@some_id", SqlDbType.VarChar, 11);
parm.Value = "some input";
In this example, I have used a SQL data adapter, but it is not necessary. Generally, stored procedures offer a relatively secure approach, provided that dynamic SQL is not used internally. At the very least, this helps safeguard against user input.
Another approach is to utilize parameter, as shown in the following code:
var myCommand = new SqlDataAdapter("SELECT * FROM Items WHERE id = @id", connection);
var parm = myCommand.SelectCommand.Parameters.Add("@id", SqlDbType.VarChar, 11);Here, we employ ADO.NET parameters to address the issue of SQL injection. This method proves helpful in preventing such vulnerability.
Lastly, using an ORM (Object-Relational Mapping) framework like Entity Framework or NHibernate offers built-in protection against SQL injections. However, it is still important to exercise caution and validate user inputs. Always validate potential dangerous operations.
By employing these strategies, we can fortify our defences against SQL injections and maintain the integrity and security of our applications.
