SQL Injection

SQL Injection is at once, one of the most common and most misunderstood vulnerabilities that we've come across in our testing. Part of the fault lies with the remediation steps given by Microsoft which state that one should use stored procedures, without delving into how one should use them.
Are stored procedures immune to SQL Injection attacks? Undoubtedly not! Let's have a look at the following line in a stored procedure

set @query = 'Select * From Users Where UserID Like %' + @userId + '%'
exec (@query)

This procedure is also vulnerable because the parameter is directly appended to the query and then executed. Now, why exactly does SQL Injection occur? It happens because the execution engine takes a string as input, without distinguishing between user supplied data and the query logic. This allows the user to format his input in a way to modify the structure of the query. When the data is passed via parameters, the execution engine is careful to distinguish between the code and data segments, and not allow user input such as ' or -- to break the structure of the query. However, when it is directly concatenated as a string and then executed, SQL injection is possible - even in stored procedures.