Insecure Use of SQL Queries
Why is this important?โ
SQL injections are dangerous because they can be easily identified by attackers. Hackers can use SQL injections to read from and sometimes even write to your database. SQL injections are very common and have been the cause of many high-profile breaches.
Check out this video for a high-level explanation:
References:
- WASC-19: SQL Injection
- OWASP: Query Parameterization Cheat Sheet
- SQL Injection Prevention Cheat Sheet
- CAPEC-66: SQL Injection
- Common Weakness Enumeration (CWE-89)
This is a biggie, read below to find out how to fix it.
Fixing Insecure Use of SQL Queriesโ
To help protect against SQL statement exploits, never create SQL queries using string concatenation. Instead, use a parameterized query and assign user input to parameter objects.
Option A: LINQ based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
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();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();Test it, ship it ๐ข and relax ๐ด
Option B: WebControls based SQL Injectionโ
References:
- MSDN: Using Parameters with the SqlDataSource Control
- MSDN: Script Exploits Overview
- MSDN: Filtering Event
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
"Select * From Customers where CustomerName = " & txtCustomerName.Value
By default, the
SqlDataSource
control uses theSystem.Data.SqlClient
data provider to work with SQL Server as the data source. TheSystem.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 [email protected], [email protected]
WHERE [email protected]"
DeleteCommand="DELETE Employees WHERE [email protected]"
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
orSystem.Data.Odbc
provider to work with your data source, respectively. TheSystem.Data.OleDb
andSystem.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=?"Test it, ship it ๐ข and relax ๐ด
Option C: OLE DB based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
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();
}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();
}Test it, ship it ๐ข and relax ๐ด
Option D: ODBC based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
var command = new OdbcCommand("SELECT * FROM [user] WHERE id = " + userInput, connection);
OdbcDataReader reader = command.ExecuteReader();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();Test it, ship it ๐ข and relax ๐ด
Option E: MsSQL Data Provider based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
var cmd = new SqlCommand("SELECT * FROM Users WHERE username = '" + username + "' and role='user'");
Replace it with the following:
var cmd = new SqlCommand("SELECT * FROM Users WHERE username = @username and role='user'");
cmd.Parameters.AddWithValue("username", username);Test it, ship it ๐ข and relax ๐ด
Option F: Entity Framework based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
var cmd = "SELECT * FROM Users WHERE username = '" + input + "' and role='user'";
ctx.Database.ExecuteSqlCommand(cmd);Replace it with the following:
var cmd = "SELECT * FROM Users WHERE username = @username and role='user'";
ctx.Database.ExecuteSqlCommand(
cmd,
new SqlParameter("@username", input));Test it, ship it ๐ข and relax ๐ด
Option G: EnterpriseLibrary.Data based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
db.ExecuteDataSet(CommandType.Text, "SELECT * FROM Users WHERE username = '" + input + "' and role='user'");
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);Test it, ship it ๐ข and relax ๐ด
Option H: nHibernate based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
session.CreateSQLQuery("SELECT * FROM users WHERE username = '" + username + "';");
Replace it with the following:
session.CreateSQLQuery("SELECT * FROM users WHERE username = :username;").SetParameter("username", username);
Test it, ship it ๐ข and relax ๐ด
Option H: Cassandra based CQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
session.Execute("SELECT * FROM users WHERE username = '" + username + "';");
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);Test it, ship it ๐ข and relax ๐ด
Option I: Npgsql based SQL Injectionโ
References:
Detailed Instructionsโ
Go through the issues that GuardRails identified in the PR.
Look for insecure patterns like this:
var cmd = new NpgsqlCommand("SELECT * FROM users WHERE username = '" + username + "';");
Replace it with the following:
var cmd = new NpgsqlCommand("SELECT * FROM users WHERE username = :username;");
cmd.Parameters.AddWithValue("username", username);Test it, ship it ๐ข and relax ๐ด
More information:โ
- OWASP Top 10 - A03 Injection
- Holistic Info-Sec for Web Developers: Injectiion risks, countermeasures
- SQL Injection Prevention Cheat Sheet
- CAPEC-66: SQL Injection
- Common Weakness Enumeration (CWE-89)
- Fixing SQL Injection Vulnerabilities