Spring learning -- sql parameter injection of JDBC template

Turn around: sql parameter injection of [spring] JDBC template

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>
<!-- NamedParameterJdbcTemplate There are two types of constructors for. One.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>

1, Parameters in array form

Advantages: for simple sql, part of memory space can be saved. Reduce the amount of code, easy to read.

Disadvantages:

  • 1. The same parameters cannot be reused, which makes array occupy more space. (of course, in today's hardware, this little space / memory and extra time to add array values are negligible.)

  • 2. If it is an in parameter, you need to dynamically splice (?) placeholders. (I think it's the most cumbersome and cumbersome, expansion: oracle supports 1000 in at most)

  • 3. If there are too many parameters in sql, it is not easy to read and modify.

/**
     * General? Occupation parameter; < br >
     * Question: < br >
     *  1,If the parameter is in, you need to add placeholders dynamically?. It's obviously troublesome. <br>
     *  2,If the parameter appears more than once, it will also appear repeatedly in the array. Obviously it's 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's an in parameter, splicing? Placeholders is cumbersome.
        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));
    }

I am used to using List to add parameters, and then converting List to Array.

The advantage is: if you use arrays, when sql has dynamic conditions, you cannot determine the array length. With List, you don't need to maintain it yourself.

2, Parameters in the form of map

Advantage:

1. The problem of in parameter is solved.

2. Parameter values can be reused.

/**
     * map Implement alias parameters. <br/>
     * Solution: < br / >
     *   1,Compared with array parameters, it solves the problems of complex parameter in and variable reuse. <br/>
     * Question: < br / >
     *   1,If it seems that in cannot be used as an array, it can be used as a list. <br/>
     *   2,The trouble is that NamedParameterJdbcTemplate and JdbcTemplate do not inherit / 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 for in is very friendly, and query conditions can also be reused. But I have a problem: the parameter is in. In map, it can't be in array form, and List is OK.

Special: NamedParameterJdbcTemplate has no inheritance / implementation relationship with JDBC template (Named can be generated through DataSource and jdbctemplate). So when you write the public parent dao, think about how to write it.

3, Parameters in the form of javaBean

/**
     * javaBean Parameters. <br></>
     * To introduce a secondary javaBean.
      * @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 source code, the feeling is to use reflection to find the attribute name. And then deal with it as a map.

Compared with map, this form only depends on whether to use map or JavaBean.

On the surface, the difference is that if the parameters are passed to the dao layer through JavaBeans, the bean s need not be converted into maps. In contrast, if it is passed to dao layer through map, it does not need to be converted to JavaBean in the form of map.

4, What is preventing sql injection? (very simple personal understanding)

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 dao layer:

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

Suppose that the expected IDs are all self increasing numeric strings such as 1, 2, 3, etc.

However, this sql may end up in any form. For example, in case of malicious attack, the final sql is: select * from child c where c.id='100';delete from child; --'

The red underlined part is the value of id (- - in sql, it is the comment, and the last quotation mark is commented out).

Then the data of the entire table child will be deleted, which is obviously unsafe.

I simply tested that the database is oracle. No matter jdbc template, pure jdbc or hibernate, there is no appeal problem (presumably handled in oracle driver jar). An exception will be thrown:

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(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 this problem does not exist in JDBC template
         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;// To create a precompiled Statement object, this is usually 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 precompiled statements
            result = pre.executeQuery();// Execute the query, note that there is no need to add parameters in brackets
            while (result.next()){
                // When result set is not empty
                System.out.println("Full 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 occupy resources
                // Pay attention to the order of closing. The last one is the first one
                if (result != null) result.close();
                if (pre != null) pre.close();
                if (con != null) con.close();
                System.out.println("Database connection closed!");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }

    /**
     * Test sql injection attacks. JDBC template will 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 will be thrown for attacking sql. I can run this sql in PL/SQL. So, my guess is that the driver jar of oracle has been processed.

(you can see how to prevent SQL injection attacks. There are many on the Internet. Although tested above, the example will not appear during the test. But you can learn more about the possible circumstances of SQL injection attacks.)

Tags: Programming SQL JDBC Java Oracle

Posted on Mon, 06 Apr 2020 04:11:29 -0700 by jungalist