Security Problem #1: SQL Injection Vulnerabilities
One security breach opens when SQL statements are dynamically created as software executes. If a hacker is able to pass fixed inputs into the SQL statement, the inputs can become part of the SQL statement. This technique can be used to gain access to privileged data, login to password-protected areas without a proper login, remove database tables, add new entries to the database, or even login to an application with administrator privileges. The traditional attempt to avoid this problem is to validate all user inputs, but there is a way to prevent these attacks altogether.
|SQL Injection Best Practice #1: Ensure that all SQL statements recognize user inputs as variables, and that statements are precompiled before the actual inputs are substituted for the variables.
Typically, this best practice implemented as a two-stage process. In the first stage, you build and parse your SQL statements with variables in place of the expected user inputs. Next, before the statement is passed to the database, replace your variables with the user inputs. Make sure that your user inputs are never parsed as the actual SQL statementthis renders even malicious user inputs ineffective.
For instance, in Java, a secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement. When PreparedStatement is used, most JDBC drivers will prepare a statement with the server, and then supply the parameters separately. In either case, after the initial parsing, there is a clear distinction between the SQL statement and the variable. The variables are encapsulated and special characters within them are automatically escaped in a manner suited to the target database. Consequently, it is impossible for a hacker to pass malicious input and have it treated as if it were the actual SQL statement— which is necessary if the hacker is going to succeed with SQL injection attacks.
| SQL Injection Best Practice #2: If you use PreparedStatement, ensure that all parameters should be inserted through appropriate JDBC calls.
Even if you use PreparedStatement, you still need to pay attention to the way in which you build arguments. All parameters should be inserted through the appropriate JDBC calls. If you concatenate the SQL sentence and omit the JDBC calls, then an attempted SQL injection could be parsed as SQL, and the hacker could succeed.
Implementing SQL Injection Best Practices
How do you ensure that code follows these best practices? Most SQL statements are created dynamically; consequently, you need to execute the application paths that create SQL statements and verify whether they are being constructed in a secure manner (i.e., that the statements are precompiled with variables before user input is added). In addition, you need to inspect the database-related code to verify that secure coding practices are being followed (for instance, the Java best practices of using PreparedStatement instead of Statement, and for using PreparedStatement correctly). The standard AEP best practices for Java, C/C++ and .NET applications that interact with a database cover both of these types of verification.
For instance, for Java applications, you would enforce the AEP coding standard and analyze the code that is responsible for forming the SQL statements, and then verify that if you build SQL for JDBC, you always use PreparedStatement. You could also verify whether all available PreparedStatements are built properly (with all parameters inserted through appropriate JDBC calls, rather than string concatenation). Moreover, to determine whether SQL statements are being built in the recommended two-step process, you could watch database calls as the application is being tested and determine if statements are always being constructed in a safe manner.