Spring boot JdbcTemplate sql注入测试

1.首先创建项目

通过JdbcTemplate来访问数据库，Spring boot提供了如下的starter来支撑

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

　　再引入Junit测试Starter:

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-test</artifactId>
       <scope>test</scope>
</dependency>

　　创建如下结构

user实体

public class User {
    private String name;
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
}

service

public interface UserService {
    public List<User> findUser(String name);
}

serviceimpl

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;
    @Override
    public List<User> findUser(String name) {
        return userDao.findUser(name);
    }
}

dao

public interface UserDao {
    public List<User> findUser(String name);
}

daoimpl

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Override
    public List<User> findUser(String name) {
        List<User> myUserList= new ArrayList<>();
        String sql="select * from tbuser where username ='"+name+"'";
        Map<String, Object> param = new HashMap<>();
        List<Map<String, Object>> mapList=new ArrayList<>();
        mapList=jdbcTemplate.queryForList(sql,param);
        for(int i=0;i<mapList.size();i++){
            Map<String,Object> testmap= mapList.get(i);
            User myuser=new User();
            myuser.setName((String) testmap.get("username"));
            myUserList.add(myuser);
        }
        return myUserList;
    }
}

可以看到的是明显的在通过字符串拼接sql语句

controller

@RestController
public class UserController {
    @Autowired
    private UserService userService;
    @RequestMapping("/user")
    public List<User> findUser(@RequestParam String name){
        return userService.findUser(name);
    }
}

执行：

正确的做法应该是预编译参数，参考代码

    @Override
    public List<User> findUserSec(String name) {
        List<User> myUserList= new ArrayList<>();
        String sql="select * from tbuser where username =:name";
        Map<String, Object> param = new HashMap<>();
        param.put("name",name);
        List<Map<String, Object>> mapList=new ArrayList<>();
        mapList=jdbcTemplate.queryForList(sql,param);
        for(int i=0;i<mapList.size();i++){
            Map<String,Object> testmap= mapList.get(i);
            User myuser=new User();
            myuser.setName((String) testmap.get("username"));
            myUserList.add(myuser);
        }
        return myUserList;
    }
}