Formatted SQL queries can be difficult to maintain, debug and can increase the risk of SQL injection when concatenating untrusted values into the
query. However, this rule doesn’t detect SQL injections (unlike rule S3649), the goal is only to highlight complex/formatted queries.
Ask Yourself Whether
- Some parts of the query come from untrusted values (like user inputs).
- The query is repeated/duplicated in other parts of the code.
- The application must support different types of relational databases.
There is a risk if you answered yes to any of those questions.
Recommended Secure Coding Practices
Sensitive Code Example
Public Sub SqlCommands(ByVal connection As SqlConnection, ByVal query As String, ByVal param As String)
Dim sensitiveQuery As String = String.Concat(query, param)
command = New SqlCommand(sensitiveQuery) ' Sensitive
command.CommandText = sensitiveQuery ' Sensitive
Dim adapter As SqlDataAdapter
adapter = New SqlDataAdapter(sensitiveQuery, connection) ' Sensitive
End Sub
Public Sub Foo(ByVal context As DbContext, ByVal query As String, ByVal param As String)
Dim sensitiveQuery As String = String.Concat(query, param)
context.Database.ExecuteSqlCommand(sensitiveQuery) ' Sensitive
context.Query(Of User)().FromSql(sensitiveQuery) ' Sensitive
End Sub
Compliant Solution
Public Sub Foo(ByVal context As DbContext, ByVal value As String)
context.Database.ExecuteSqlCommand("SELECT * FROM mytable WHERE mycol=@p0", value) ' Compliant, the query is parameterized
End Sub
See
