How To Fix SQL Injection: Queries

How To Fix SQL Injection:

How to Fix SQL Injection Using Microsoft .Net Parameterized Queries


Paramaterized Query

The purpose of a parameterized query is to allow the data source to be able to distinguish executable statements from untrusted data.

Secure Usage
 
// Build the query statement using parameterized query.
string sql = "SELECT UserId FROM User WHERE " +
                "UserName = @UserName AND Password = @Password";
 
using (SqlCommand cmd = new SqlCommand(sql))
 
{
    // Create the parameter objects as specific as possible.
    cmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50);
    cmd.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 25);
 
    // Add the parameter values.  Validation should have already happened.
    cmd.Parameters["@UserName"].Value = UserName;
    cmd.Parameters["@Password"].Value = Password;
    cmd.Connection = connnection;
 
    try
    {
        cmd.Connection.Open();
        var userId = cmd.ExecuteScalar();
    }
    catch (SqlException sx)
    {
        // Handle exceptions before moving on.
    }
}

The above code is not vulnerable to SQL Injection because it properly uses parameterized queries. ** This is very similar to how you could properly call a stored procedure, but it is very important to note that if the stored procedure has dynamic SQL and uses the EXEC or EXECUTE statements it could still be vulnerable.

Vulnerable Usage
 
using (SqlConnection connnection = new SqlConnection(ConnString))
{
    // Build the query statement using dynamic data.
    string sql = "SELECT UserId FROM User WHERE " +
                 "UserName = '" + UserName + "' AND " +
                 "Password = '" + Password + "'";
    using (SqlCommand cmd = new SqlCommand(sql))
    {
         cmd.Connection = connnection;
         try
         {
             cmd.Connection.Open();
             var userId = cmd.ExecuteScalar();
         }
         catch (SqlException sx)
         {
             // Handle exceptions before moving on.
         }
     }
}

The above example is vulnerable to SQL Injection if the data input is not properly validated and sanitized. If a user could inject a single quote (') character, it would be possible to modify the SQL statement from what the developer originally intended.

Vulnerable Usage
 
string sql = "EXEC usp_login '" + UserName + "','" + Password + "'";
using (SqlCommand cmd = new SqlCommand(sql))
{
    cmd.Connection = connnection;
    try
    {
        cmd.Connection.Open();
        var userId = cmd.ExecuteScalar();
    }
    catch (SqlException sx)
    {
        // Handle exceptions before moving on.
    }
}

The above example is also vulnerable, in just the same way the previous example was. Even if the stored procedure is not susceptible to SQL Injection, this statement is. If the user could inject a single quote (') character, it would be possible to append on to the EXEC statement and execute additional commands.


Authors

James Jardine