Rule Definition
By using Stored Procedures the database engine is more able to optimize the access plan and to reuse them. It also limit the parsing phase of the SQL order. This generally result in better performance.
From a security point of view, it is generally safer to use SP rather than dynamic SQL as this limit the risk of having SQL-injection.
Remediation
Transform the SQL into a SP and use parameters. Then call the SP.
Do not transform the SQL in a SP that in turn uses dynamic SQL (e.g. @exec or EXECUTE_IMMEDIATE) as this deny all the benefits of the change.
Violation Code Sample
class MyClass
{
private String s = "select col1from MyTable where col2 =";
public void fct( String sVal )
{
s += "'" + sVal + "'";
exec s;
}
Fixed Code Sample
class MyClass
{
public void fct( String sVal )
{
exec "MySP +sVal"
}
Related Technologies
.Net
Technical Criterion
Architecture - Multi-Layers and Data Access
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.