SQL parameter injection of "Spring" jdbctemplate

Demo

@Repository ("jdbcDao")
public class JdbcTemplateDao {
     @Autowired
     private JdbcTemplate jdbcTemplate;
     @Autowired
     private NamedParameterJdbcTemplate namedTemplate;
     private final static List <String> names = new ArrayList <String> ();
     private final String childAge = "5";
     private final String parentId = "2";
     static {
         names.add ("Wu San");
         names.add ("Wu Er");
     }
}

< Bean id="DataSource" ...> </bean >


<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" abstract="false" lazy-init="false" autowire="default">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- There are two kinds of constructors for NamedParameterJdbcTemplate. 1.DataSource; 2.JdbcOperations -->
<bean id="namedTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" abstract="false" lazy-init="false" autowire="default">
<constructor-arg type="javax.sql.DataSource" ref="dataSource" />
<!--constructor-arg type="org.springframework.jdbc.core.JdbcOperations" ref="jdbcTemplate" / -->
</bean>

Parameters in the form of an array

Advantage: For simple SQL, you can save some memory space. Reduce the amount of code, easy to read.

Disadvantages:

1, the same parameters can not be reused, so that the array occupies more space. (Of course, this space/memory, the extra time to add an array value, is completely trivial for today's hardware)

2, if it is in the parameters, to dynamic splicing (?) Placeholder. (personally considered the most troublesome, cumbersome, extended: Oracle to in Max support 1000)

3, if there are too many parameters in SQL, it is not good to read the changes.

/ **
     * General? Placeholder parameters; <br>
     * Question: <br>
     * 1. If the parameter is in, then you need to add a placeholder dynamically ?. Obviously this is troublesome. <br>
     * 2. If the parameter appears multiple times, the array must also appear repeatedly. Obviously this is a waste of space. <br>
     * /
    public List <Child> arrayParam () {
        List <Object> params = new ArrayList <Object> ();
        String sql = "select c.child_id childId, c.child_name, c.child_age childAge, c.parent_id parentId from child c";
        sql + = "where c.child_age =? and c.parent_id =?";
        params.add (childAge);
        params.add (parentId);
        // If it is an in parameter, the concatenation? Placeholder is troublesome.
        sql + = "and c.child_name in (";
        for (Iterator <String> iterator = names.iterator (); iterator.hasNext ();) {
            iterator.next ();
            sql + = "?";
            if (iterator.hasNext ()) sql + = ",";
        }
        sql + = ")";
        params.addAll (names);
        return this.jdbcTemplate.query (sql, params.toArray (), new BeanPropertyRowMapper <Child> (Child.class));
    }

Personal habits Use the list to add parameters, and then convert the list to an array.

The advantage is that if an array is used, the array length cannot be determined when the SQL has dynamic conditions. The list does not need to be maintained by itself.

Ii. parameters in the form of a map

Advantages:

1. Solve the in Parameter problem.

2, the parameter value can be reused.

/ **
      * map implements alias parameters. <br/>
      * Solved: <br/>
      * 1. Relative to the array parameter, the problems of complex parameter in and variable reuse are solved. <br/>
      * Question: <br/>
      * 1. If it seems that in cannot use an array, you can use a list. <br/>
      * 2. The more troublesome is that NamedParameterJdbcTemplate and JdbcTemplate have no inheritance / interface relationship. And Named depends on Jdbc, so pay attention when writing public dao.
       * @return
      * /
     public List <Child> mapParam () {
         Map <String, Object> params = new HashMap <String, Object> ();
         String sql = "select c.child_id childId, c.child_name, c.child_age childAge, c.parent_id parentId from child c";
         sql + = "where c.child_age =: age and c.parent_id =: id and c.child_name in (: names)";
         params.put ("age", childAge);
         params.put ("id", parentId);
         params.put ("names", names);
         return namedTemplate.query (sql, params, new BeanPropertyRowMapper <Child> (Child.class));
     }

It can be seen that the Parameter form support is friendly and the query condition can be reused. But I encountered a problem: The parameter is in, the map can not be used in the form of arrays, with list is possible.

In particular: Namedparameterjdbctemplate and jdbctemplate do not have any inheritance/implementation relationships (named can be generated by DataSource, JdbcTemplate). So when you write the common parent DAO, think about how to write it.

Iii. parameters of the JavaBean form

/ **
      * javaBean parameters. <br> </>
      * To introduce auxiliary javaBeans.
       * @return
      * /
     public List <Child> beanParam () {
         String sql = "select c.child_id childId, c.child_name, c.child_age childAge, c.parent_id parentId from child c";
         sql + = "where c.child_age =: childAge and c.parent_id =: parentId";
         sql + = "and c.child_name in (: names)";
         ParamBean bean = new ParamBean ();
         bean.setChildAge (childAge);
         bean.setParentId (parentId);
         bean.setNames (names);
         SqlParameterSource param = new BeanPropertySqlParameterSource (bean);

         return namedTemplate.query (sql, param, new BeanPropertyRowMapper <Child> (Child.class));
     } 

Simply browse the next source, the feeling is to use reflection to find the property name. Then deal with the same as the map form.

This form is relative to the map, only the map or the JavaBean.

The surface difference is that if the parameter is passed javabean to the DAO layer, then the bean is not converted to a map. If you pass a map to the DAO layer, you also need to convert the map form to JavaBean.

Iv. what is preventing SQL injection? (The individual is very simple to understand)

Suppose Sql:select * from child c where c.id =?;

If the DAO layer is for convenience, or there is no concept of preventing SQL injection (that is, security issues). Code in the DAO Layer:

String sql = "SELECT * from child C where c.id= '" +id+ "";

Suppose the desired ID is a string of numbers, such as the number of self-increment.

However, this SQL may end up in any form. For example, when a malicious attack, the final sql:select * from the child C where c.id= ' ";d elete from the child;--'

The red underline is the value of the ID (--a comment in sql, and the last quote is commented out).

Then it will delete the entire table child's data, which is obviously unsafe.

I tested it briefly and the database is Oracle. There is no appeal problem with jdbctemplate, pure jdbc, hibernate (conjecture is handled in the Oracle drive Jar). Will throw an exception:

java.sql.SQLException: ORA-00911: invalid character
at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError (T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive (T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC (T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL (T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8 (T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe (T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe (OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout (OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal (OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery (OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery (OraclePreparedStatementWrapper.java:1203)
at com.vergilyn.test.sh.dao.JdbcTemplateDao.injectionAttack (JdbcTemplateDao.java:49)
at com.lyn.Junit.TestJdbc.testInjectionAttack (TestJdbc.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke (NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke (Method.java:606)
at org.junit.runners.model.FrameworkMethod $ 1.runReflectiveCall (FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run (ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively (FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate (InvokeMethod.java:17)

Test code:

    @Test    @Rollback (@Test
    @Rollback (true)
    public void testInjectionAttack () {// Conclusion
         String id = "1";
        id = "1‘; delete from child where child_id = ‘1‘;-";
        jdbcDao.injectionAttack (id);
    }

    @Test
    @Rollback (true)
    public void testSqlInjectionAttack () {// Conclusion jdbcTemplate does not have this problem
         String id = "1";
        id = "1‘; delete from child where child_id = ‘1‘;-";
        Child rs = jdbcDao.sqlInjectionAttack (id);
        System.out.println (JSON.toJSONString (rs));
    }
public void injectionAttack (String id) {
       Connection con = null; // create a database connection
        PreparedStatement pre = null; // Create a prepared statement object, generally this is used instead of Statement
       ResultSet result = null; // create a result set object
        try {
            Class.forName ("oracle.jdbc.driver.OracleDriver"); // Load Oracle driver
            String url = "jdbc: oracle: thin: @ 127.0.0.1: 1521: orcl";
            String user = "vergilyn";
            String password = "409839163";
            con = DriverManager.getConnection (url, user, password); // Get connection

            String sql = "select c.child_id childId, c.child_name, c.child_age childAge, c.parent_id parentId from child c";
          sql + = "where c.child_id =‘ "+ id +" ‘";
          pre = con.prepareStatement (sql); // instantiate a prepared statement
            result = pre.executeQuery (); // Execute the query, note that no additional parameters are required in parentheses
            while (result.next ()) {
                // when the result set is not empty
                System.out.println ("Name:" + result.getString ("child_Name"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            try
            {
                // Close the above objects one by one, because not closing will affect performance and consume resources
                // Pay attention to the closing order, the last used first is closed
                if (result! = null) result.close ();
                if (pre! = null) pre.close ();
                if (con! = null) con.close ();
                System.out.println ("Database connection is closed!");
            }
            catch (Exception e)
            {
                e.printStackTrace ();
            }
        }
    }

    / **
     * Test SQL injection attacks. jdbcTemplate does not have this security issue.
     * @param id
     * @return
     * /
    public Child sqlInjectionAttack (String id) {
        String sql = "select c.child_id childId, c.child_name, c.child_age childAge, c.parent_id parentId from child c";
        sql + = "where c.child_id =‘ "+ id +" ‘";
        return this.jdbcTemplate.queryForObject (sql, new BeanPropertyRowMapper <Child> (Child.class));
    } 

Correct sql:

Select c.child_id childid,c.child_name,c.child_age childage,c.parent_id parentid from child c where c.child_id= ' 1 '

Attack sql:

Select c.child_id childid,c.child_name,c.child_age childage,c.parent_id parentid from child c where c.child_id= ' 1 '; Delete from child where child_id= ' 1 ';--'

The above exception is thrown against the attack SQL, which I can run in PL/SQL. Then, the personal conjecture is handled in the drive jar of Oracle.

(You can see how to prevent SQL injection attacks, many online.) Although the above test, for example, the situation will not appear in the test. However, you can learn more about the circumstances under which a SQL injection attack may occur.

Baidu Baike:sql Injection attack

SQL parameter injection of "Spring" jdbctemplate

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch