Skip to content

SubQuery安全子查询

Tuuz edited this page Aug 9, 2023 · 7 revisions

使用方法: 1.创建语句,使用buildsql创建语句 2.使用db.SubQuery(sql语句,语句alias,绑定参数)完成子查询绑定 3.继续使用连续方法做其他查询 4.get或find方法取出数据

这里的需求是按照某种方法排行家长的情况,但是如果不使用子查询,则需要将rank方法写入原语句,这会导致rank按照id或者实际column进行排序,如果按照虚拟字段(命名字段)排列,那么rank_index字段(rank)排序出来就会是混乱无序的,这样就无法完成需求了

因此需要在正常的sql查询语句外再包一层,做成子查询的形式,这样就能让rank使用实际顺序进行排列

安全问题: 查看了TP的子查询方法,因为没有执行stmt,有被注入的可能,所以在设计sq方法的时候,特别考量了安全问题,因此你的每一次subQuery的生成和执行,均需要使用BuildSql方法生成语句和查询绑定条件才能保证安全

复杂的例子,这里需要计算每所学校每个学生的完成量,并且在数据库中直接排行

func Api_select_sum_year(school_id, year, start_date, end_date interface{}, group_by, order_by string) []gorose.Data {
	db := tuuz.Db().Table(Table)
	db.Fields(`
	school_id,
	year,
	class_id,
	parent_num,
	student_num,

	count( 0 ) AS count,
	ROUND( ( SUM( daily_student_num ) + SUM( weekly_student_num )* 7 + SUM( monthy_student_num ) * 30 ) / SUM( student_num ), 3 ) AS student_ratio,
	ROUND( ( SUM( daily_parent_num ) + SUM( weekly_parent_num )* 7 + SUM( monthy_parent_num ) * 30 ) / SUM( parent_num ), 3 ) AS parent_ratio,
	ROUND( ( SUM( daily_parent_num ) + SUM( weekly_parent_num )* 7 + SUM( monthy_parent_num ) * 30 ) / SUM( student_num ), 3 ) AS parent_student_ratio,
	ROUND( SUM( daily_parent_num ) / SUM( student_num ), 3 ) AS daily_parent_ratio,
	ROUND( SUM( weekly_parent_num ) / SUM( student_num )* 7, 3 ) AS weekly_parent_ratio,
	ROUND( SUM( monthy_parent_num ) / SUM( student_num )* 30, 3 ) AS monthy_parent_ratio,
	SUM( complete_student ) AS complete_student,
	SUM( complete_parent ) AS complete_parent,
	SUM( complete_family ) AS complete_family,
	SUM( daily_student_num ) AS daily_student_num,
	SUM( weekly_student_num ) AS weekly_student_num,
	SUM( monthy_student_num ) AS monthy_student_num,
	SUM( daily_parent_num ) AS daily_parent_num,
	SUM( weekly_parent_num ) AS weekly_parent_num,
	SUM( monthy_parent_num ) AS monthy_parent_num 
`)
	if school_id != nil {
		db.Where("school_id", school_id)
	}
	if year != nil {
		db.Where("year", year)
	}
	db.Where("date", ">=", start_date)
	db.Where("date", "<", end_date)
	db.Group(group_by)
	db.Order(order_by)
	sql, args, _ := db.BuildSql("select")
	dd := tuuz.Db().SubQuery(sql, "a ,(SELECT @rank := 0) b", args)
	dd.Fields(`*,@rank :=@rank+1 as rank_index`)
	ret, err := dd.Get()
	if err != nil {
		Log.Dbrr(err, tuuz.FUNCTION_ALL())
		return nil
	} else {
		return ret
	}
}

简单的例子:我希望通过SchoolId查到对应学校,并在这些学校中再次进行筛选

这将会生成一条语句:

SELECT * FROM (SELECT area_id FROM `ps_school` WHERE `id` = ? and `domain` = ?) b WHERE `area_id` = ?

因为使用了参数化查询,所以可以在此基础上构建安全的查询方式

func Api_select_area_bySchoolId(id any) []gorose.Data {
	db := tuuz.Db().Table(Table)
	db.Fields("area_id")
	db.Where("id", id)
	db.Where("domain", "minshen")
	sql, args, err := db.BuildSql("select")
	if err != nil {
		Log.DBrrsql(err, db, tuuz.FUNCTION_ALL())
		return nil
	}
	db2 := tuuz.Db().Table(Table)
	db2.SubQuery(sql, "b", args)
	//db2.SubWhere("area_id", "=", sql, args)
	//db2.WhereIn("rate_up", []interface{}{70})
	ret, err := db2.Get()
	fmt.Println(db2.LastSql())
	if err != nil {
		Log.DBrrsql(err, db, tuuz.FUNCTION_ALL())
		return nil
	} else {
		return ret
	}
}

performance

如非必要,请不要滥用子查询,这将大大降低数据库在高并发中的性能