Bug Pattern: SQL_INJECTION_TURBINE

    The input values included in SQL queries need to be passed in safely. Bind variables in prepared statements can be used to easily mitigate the risk of SQL injection. Turbine API provide a DSL to build query with Java code.

    Vulnerable Code:

    1. List<Record> BasePeer.executeQuery( "select * from Customer where id=" + inputId );

    Solution (using Criteria DSL):

    1. Criteria c = new Criteria();
    2. c.add( CustomerPeer.ID, inputId );
    4. List<Customer> customers = CustomerPeer.doSelect( c );

    Solution (using specialized method):

    1. Customer customer = CustomerPeer.retrieveByPK( new NumberKey( inputId ) );

    Solution (using OWASP Encoder):

    1. import org.owasp.esapi.Encoder;
    3. BasePeer.executeQuery("select * from Customer where id = '"+Encoder.encodeForSQL(inputId)+"'");

    References (Turbine)
    Turbine Documentation: Criteria Howto
    References (SQL injection)
    WASC-19: SQL Injection
    CAPEC-66: SQL Injection
    CWE-89: Improper Neutralization of Special Elements used in an SQL Command (‘SQL Injection’)
    OWASP: Top 10 2013-A1-Injection
    OWASP: SQL Injection Prevention Cheat Sheet
    OWASP: Query Parameterization Cheat Sheet