安全的SQL查询demo

safe_sql_query.md

目录

表明，字段名场景

JDBC样例

Spring-JDBC样例

Mybatis XML Mapper样例

Mybatis Criteria样例

表,字段名称

(Select, Order by, Group by 等)

// 插入数据用户可控时，应使用白名单处理
// example for order by

String orderBy = "{user input}";
String orderByField;
switch (orderBy) {
    case "name":
        orderByField = "name";break;
    case "age":
        orderByField = "age"; break;
    default:
        orderByField = "id";
}

JDBC

String name = "foo";

// 一般查询场景
String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement pre = conn.prepareStatement(sql);
pre.setString(1, name);
ResultSet rs = pre.executeQuery();

// like 模糊查询场景
String sql = "SELECT * FROM users WHERE name like ?";
PreparedStatement pre = conn.prepareStatement(sql);
pre.setString(1, "%"+name+"%");
ResultSet rs = pre.executeQuery();

// where in 查询场景
String sql = "select * from user where id in (";
Integer[] ids = new Integer[]{1,2,3};

StringBuilder placeholderSql = new StringBuilder(sql);
for(int i=0,size=ids.length;i<size;i++) {
    placeholderSql.append("?");
    if (i != size-1) {
        placeholderSql.append(",");
    }
}
placeholderSql.append(")");

PreparedStatement pre = conn.prepareStatement(placeholderSql.toString());
for(int i=0,size=ids.length;i<size;i++) {
    pre.setInt(i+1, ids[i]);
}
ResultSet rs = pre.executeQuery();

Spring-JDBC

JdbcTemplate jdbcTemplate = new JdbcTemplate(app.dataSource());

// 一般查询场景
String sql = "select * from user where id = ?";
Integer id = 1;
UserDO user = jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserDO.class), id);

// like 模糊查询场景
String sql = "select * from user where name like ?";
String like_name = "%" + "foo" + "%";
UserDO user = jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserDO.class), like_name);

// where in 查询场景
NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(app.dataSource());

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("names", Arrays.asList("foo", "bar"));

String sql = "select * from user where name in (:names)";
List<UserDO> users = namedJdbcTemplate.query(sql, parameters, BeanPropertyRowMapper.newInstance(UserDO.class));

Mybatis XML Mapper

<!-- 一般查询场景 -->
<select id="select" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT *
    FROM user
    WHERE name = #{name}
</select>

<!-- like 查询场景 -->
<select id="select" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT *
    FROM user
    WHERE name like CONCAT("%", #{name}, "%")
</select>

<!-- where in 查询场景 -->
<select id="select" parameterType="java.util.List" resultMap="BaseResultMap">
    SELECT *
    FROM user
    WHERE name IN
    <foreach collection="names" item="name" open="(" close=")" separator=",">
      #{name}
    </foreach>
</select>

Mybatis Criteria

public class UserDO {
    private Integer id;
    private String name;
    private Integer age;
}

public class UserDOExample {
    // auto generate by Mybatis
}

UserDOMapper userMapper = session.getMapper(UserDOMapper.class);
UserDOExample userExample = new UserDOExample();
UserDOExample.Criteria criteria = userExample.createCriteria();

// 一般查询场景
criteria.andNameEqualTo("foo");

// like 模糊查询场景
criteria.andNameLike("%foo%");

// where in 查询场景
criteria.andIdIn(Arrays.asList(1,2));

List<UserDO> users = userMapper.selectByExample(userExample);