关于使用占位符防止SQL注入

在使用 Sequelize 处理 MySQL 的一些查询时,需要使用原生 SQL 语句,正好碰到了关于占位符(?) 的一些问题,在这里记录下来

为了防止SQL注入,通常会对 SQL 中传入的参数进行编码,而不是直接进行字符串拼接。
而在 Sequelize 中,可以使用其提供的 query 方法中的 replacements 参数和占位符 ? 实现同样的效果

例如对于以下查询语句

1
"SELECT * FROM users WHERE id = " + "123"

在 Sequelize 中,可以用下面的方法进行替换

1
2
3
4
5
6
const sql = 'SELECT * FROM users WHERE id = ?'
const optArrays = [123]
db.sequelize.query(sql, {
raw: true,
replacements: optArrays
})

当这条语句运行时,Sequelize 会将 optArrays 数组中的值按顺序填充到 sql 中对应的占位符 (?) 处, 这就要求 optArrays 的长度不能小于 sql 语句中的占位符 (?) 的数量,且要和 (?) 的顺序保持一致,否则在运行时将会报错

1
2
3
4
5
6
const sql = 'SELECT * FROM users WHERE name LIKE ? AND sex = ? AND age > ?'
const optArrays = ['%李%', '男', 18]
db.sequelize.query(sql, {
raw: true,
replacements: optArrays
})

上述语句运行后,实际执行的 sql 为

1
SELECT * FROM users WHERE name LIKE '%李%' AND sex = '男' AND age > 18

如果将 optArrays 中最后一个值18删掉,即

1
2
onst sql = 'SELECT * FROM users WHERE name LIKE ? AND sex = ? AND age > ?'
const optArrays = ['%李%', '男']

实际执行的语句将会变成

1
SELECT * FROM users WHERE name LIKE '%李%' AND sex = '男' AND age > ?

显然是一条错误的语句,所以最好保持占位符与参数数组的一致


更新

当传入的参数中包含占位符时,要对参数进行转义,最好对于所有传入的查询条件都进行转义,防止注入或其他类型的错误
如:

1
2
const sql = 'SELECT * FROM videos WHERE title LIKE ? AND ispublish = ?'
const optArrays = ['%如何防止SQL注入?%', 1]

按照占位符和参数数组的对应关系,当这条语句执行时,optArrays[0] 对应的是 LIKE 后面的 (?),而 optArrays[1] 对应的是 ispublish = 后面的 (?),而实际上运行的语句是

1
SELECT * FROM videos WHERE title LIKE '%如何防止SQL注入1%' AND ispublish = ?

可以看到由于optArrays[0]中存在占位符(?),所以在执行时,按照占位符的顺序,原本属于 ispublish = ? 的 optArrays[1] 被匹配到了如何防止SQL注入?的占位符处,导致查询语句报错

对于这种情况,最好的办法是对每个传入的查询参数进行转义处理,将一些特殊字符,如:%,&,?,_ 等进行转义,这样在查询时就不会出现类似上面的问题

1
2
3
const sql = 'SELECT * FROM videos WHERE title LIKE ? '
const opt1 = '%如何防止SQL注入?%'
opt1 = opt1.toString().replace(/(%|_|&|\?)/g, (s) => '\\' + s)

转义后上面的查询语句将会变成

1
SELECT * FROM videos WHERE title LIKE '%如何防止SQL注入\?%' AND ispublish = 1

这样就不会出现报错的问题了