SDLC - SQL Injection

SQL Injection

SQL Injection occurs when user input is embedded as-is inside a pre-built SQL query. The hacker inserts an SQL query into posted information that is executed by the database. These kinds of attacks can be prevented by use of Prepared Statements and Stored Procedures.

Parameterized queries separate the compiling of the query from the execution and the data. This separation means that data from the user is never compiled as part of the query preventing SQL Injection attacks. In addition to the security benefits, Prepared Statements give similar performance to Stored Procedures. An example of the proper use of Prepared Statement is below.

Vulnerability Example: standard statement

Statements = conn.createStatement();

String query ="SELECT customerId, firstName FROM customer where customerId="+customerId+";

ResultSet rs = s.executeQuery(query);

Vulnerability Example: non-parameterized prepared statement

String query ="SELECT customerId, firstName FROM customer where customerId="+customerId+";

PreparedStatement ps = conn.prepareStatement(query);

ResultSet rs = ps.executeQuery();

Secure Example: parameterized prepared statement

String query ="SELECT customerId, firstName FROM customer where customerId=?"

PreparedStatement ps = conn.prepareStatement(query);

ps.setString(1, customerId);

ResultSet rs = ps.executeQuery();

Stored Procedures can be used in place of Prepared Statements if needed; however, the use of Stored Procedures, as with any other query, should be reviewed by the Database Management Office (DBMO). Careful consideration should be given when using Stored Procedures as they tie the application to a specific database platform. It should also be noted that Stored Procedures that contain business logic are not allowed by the FSA Reference Architecture .