Insecure Use of SQL Queries

Fixing Insecure Use of SQL Queries

About SQL Injection (SQLi)

What is SQL injection (SQLi)?

SQL injection (SQLi) is a type of security vulnerability that allows attackers to exploit an application's interaction with a SQL database.

This occurs when an application fails to properly sanitize user input and includes untrusted data in an SQL query, allowing attackers to inject malicious code or modify the intended behavior of the query.

SQL injection can affect various types of applications that interact with SQL databases, such as web applications, content management systems, and e-commerce platforms.

Attackers can use various techniques, such as crafted queries or filter values, to inject malicious code or entities into the application.

Check out this video for a high-level explanation:

What is the impact of SQL injections?

SQL injection can lead to various security threats and risks, such as:

• Information disclosure: SQL injection can expose sensitive information, such as user data, login credentials, or other types of confidential information, to unauthorized parties.
• Unauthorized access: SQL injection can allow attackers to gain unauthorized access to databases, perform unauthorized actions, or modify data.
• Denial-of-service: SQL injection can cause denial-of-service (DoS) attacks by injecting malicious queries or values that consume system resources, such as CPU or memory, leading to system crashes or slowdowns.
• Arbitrary code execution: SQL injection can execute arbitrary code on the database server by injecting malicious queries or values that exploit vulnerabilities in the application or the underlying operating system.

SQL injections are very common and have been the cause of many high-profile breaches.

How to prevent SQL injections?

To prevent SQL injection, it is important to follow security best practices and implement appropriate security measures, such as:

• Use parameterized queries: Use parameterized queries and other secure coding practices to ensure that user input is properly sanitized and not used to construct SQL queries directly.
• Use input validation: Validate user input to ensure that it is safe and does not contain malicious code or characters that can be used for injection attacks.
• Limit user access: Limit user access to databases to authorized users and restrict access to sensitive information and actions.
• Use secure coding practices: Follow secure coding practices, such as code reviews, vulnerability scanning and testing, and threat modeling, to ensure that the source code is free of vulnerabilities and that SQL injection vulnerabilities are addressed.
• Use a web application firewall: Use a web application firewall (WAF) that can help prevent SQL injection by filtering and blocking malicious traffic.

References

Taxonomies

• OWASP Top 10 - A03 Injection
• SANS TOP 25 - #3
• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

Explanation & Prevention

• OWASP: SQL Injection Attacks
• OWASP: SQL Injection Prevention Cheat Sheet
• OWASP: Query Parameterization Cheat Sheet
• CAPEC-66: SQL Injection
• NoSQL Injection explanation video
• Holistic Info-Sec: Injection risks
• Holistic Info-Sec: Injection countermeasures

Related CVEs

• CVE Details: CWE-89

Training

• Secure Code Warrior: SQL Injection

Option A: LINQ based SQL Injection

Solution-specific references:

• LINQ: How to Query for Information

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   db.ExecuteQuery(@"SELECT name FROM dbo.Users WHERE UserId = " + inputId + " AND group = 5");
   
   // or:
   var query = "SELECT name FROM dbo.Users WHERE UserId = " + userId + " AND group = 5";
   var id = context.ExecuteQuery<IEnumerable<string>>(query).SingleOrDefault();

3. Replace it with the following:

   var query = from user in db.Users
   where user.UserId == inputId
   select user.name;
   
   // or:
   var query = "SELECT name FROM dbo.Users WHERE UserId = {0} AND group = 5";
   var id = context.ExecuteQuery<IEnumerable<string>>(query, userId).SingleOrDefault();

4. Test it, ship it 🚢 and relax 🌴

Option B: WebControls based SQL Injection

Solution-specific references:

• MSDN: Using Parameters with the SqlDataSource Control
• MSDN: Script Exploits Overview
• MSDN: Filtering Event

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   "Select * From Customers where CustomerName = " & txtCustomerName.Value

3. By default, the SqlDataSource control uses the System.Data.SqlClient data provider to work with SQL Server as the data source. The System.Data.SqlClient provider supports named parameters as placeholders, as shown in the following example:

   <asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
     SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"
   
     InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                   SELECT @EmpID = SCOPE_IDENTITY()"
     UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
     DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"
   
     ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
     OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
     RunAt="server">
   
     <SelectParameters>
       <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
     </SelectParameters>
   
     <InsertParameters>
       <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
     </InsertParameters>
   </asp:sqlDataSource>

   Or if you are connecting to an OLE DB or ODBC data source, you can configure the SqlDataSource control to use the System.Data.OleDb or System.Data.Odbc provider to work with your data source, respectively. The System.Data.OleDb and System.Data.Odbc providers support only positional parameters identified by the “?” character, as shown in the following example:

   <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" 
     SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
                   FROM Employees WHERE EmployeeID = ?"
   
     InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
                   VALUES (?, ?, ?, ?, ?, ?); 
                   SELECT @EmpID = SCOPE_IDENTITY()"
   
     UpdateCommand="UPDATE Employees SET LastName=?, FirstName=?, Address=?,
                   City=?, Region=?, PostalCode=?
                   WHERE EmployeeID=?"

4. Test it, ship it 🚢 and relax 🌴

Option C: OLE DB based SQL Injection

Solution-specific references:

• OleDbCommand Documentation

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   string queryString = "SELECT OrderID, CustomerID FROM Orders WHERE OrderId = " + userInput;
   
   using (var connection = new OleDbConnection(connectionString))
   {
       OleDbCommand command = new OleDbCommand(queryString, connection);
       connection.Open();
       OleDbDataReader reader = command.ExecuteReader();
   }

3. Replace it with the following:

   string queryString = "SELECT OrderID, CustomerID FROM Orders WHERE OrderId = ?";
   
   using (var connection = new OleDbConnection(connectionString))
   {
       OleDbCommand command = new OleDbCommand(queryString, connection);
       command.Parameters.Add("@p1", OleDbType.Integer).Value = userInput;
       connection.Open();
       OleDbDataReader reader = command.ExecuteReader();
   }

4. Test it, ship it 🚢 and relax 🌴

Option D: ODBC based SQL Injection

Solution-specific references:

• OdbcCommand Documentation

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   var command = new OdbcCommand("SELECT * FROM [user] WHERE id = " + userInput, connection);
   OdbcDataReader reader = command.ExecuteReader();

3. Replace it with the following:

   var command = new OdbcCommand("SELECT * FROM [user] WHERE id = ?", connection);
   command.Parameters.Add("@id", OdbcType.Int).Value = 4;
   OdbcDataReader reader = command.ExecuteReader();

4. Test it, ship it 🚢 and relax 🌴

Option E: MsSQL Data Provider based SQL Injection

Solution-specific references:

• SQLCommand Class Documentation

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   var cmd = new SqlCommand("SELECT * FROM Users WHERE username = '" + username + "' and role='user'");

3. Replace it with the following:

   var cmd = new SqlCommand("SELECT * FROM Users WHERE username = @username and role='user'");
   cmd.Parameters.AddWithValue("username", username);

4. Test it, ship it 🚢 and relax 🌴

Option F: Entity Framework based SQL Injection

Solution-specific references:

• Entity Framework Documentation

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   var cmd = "SELECT * FROM Users WHERE username = '" + input + "' and role='user'";
   ctx.Database.ExecuteSqlCommand(cmd);

3. Replace it with the following:

   var cmd = "SELECT * FROM Users WHERE username = @username and role='user'";
   ctx.Database.ExecuteSqlCommand(
       cmd,
       new SqlParameter("@username", input));

4. Test it, ship it 🚢 and relax 🌴

Option G: EnterpriseLibrary.Data based SQL Injection

Solution-specific references:

• Microsoft.Practices.EnterpriseLibrary.Data.Sql Namespace

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   db.ExecuteDataSet(CommandType.Text, "SELECT * FROM Users WHERE username = '" + input + "' and role='user'");

3. Replace it with the following:

   DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM Users WHERE username = @username and role='user'");
   db.AddInParameter(cmd, "@username", DbType.String, input);
   db.ExecuteDataSet(cmd);

4. Test it, ship it 🚢 and relax 🌴

Option H: nHibernate based SQL Injection

Solution-specific references:

• nHibernate Manual - Querying Data

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   session.CreateSQLQuery("SELECT * FROM users WHERE username = '" + username + "';");

3. Replace it with the following:

   session.CreateSQLQuery("SELECT * FROM users WHERE username = :username;").SetParameter("username", username);

4. Test it, ship it 🚢 and relax 🌴

Option I: Cassandra based CQL Injection

Solution-specific references:

• Datastax Documentation - Named parameters

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   session.Execute("SELECT * FROM users WHERE username = '" + username + "';");

3. Replace it with the following:

   var preparedStatement = session.Prepare("SELECT * FROM users WHERE username = :username");
   
   var boundStatement = preparedStatement.Bind(new
   {
       username = username
   });
   
   session.Execute(boundStatement);

4. Test it, ship it 🚢 and relax 🌴

Option J: Npgsql based SQL Injection

Solution-specific references:

• Npgsql Documentation - Npgsql Basic Usage

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for insecure patterns like this:

   var cmd = new NpgsqlCommand("SELECT * FROM users WHERE username = '" +  username + "';");

3. Replace it with the following:

   var cmd = new NpgsqlCommand("SELECT * FROM users WHERE username = :username;");
   cmd.Parameters.AddWithValue("username", username);

4. Test it, ship it 🚢 and relax 🌴