Mysql读写分离+防止sql注入攻击
Reference
[1] https://zhuanlan.zhihu.com/p/111682902
一、读写分离和防止sql注入的必要性(foreword)
1、 读写分离:
- 一句话定义:读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
图1-1 主从读写分离示意图
- 读写分离的好处:
(1) 增加冗余
(2) 增加了机器的处理能力
(3) 对于读操作为主的应用,使用读写分离是最好的场景,因为可以确保写的服务器压力更小,而读又可以接受点时间上的延迟。
2、 防止sql注入攻击:
一句话定义:SQL注入攻击(SQL Injection),简称注入攻击,是Web开发中最常见的一种安全漏洞。可以用它来从数据库获取敏感信息,或者利用数据库的特性执行添加用户,导出文件等一系列恶意操作,甚至有可能获取数据库乃至系统用户最高权限。
二、最佳解决方案(what)
主库用读写账户,从库用只读账户,建立mysql链接时使用参数interpolateParams=true
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
//主库用读写账户 建立mysql链接时使用参数interpolateParams=true
db_master, err := sql.Open("mysql", "master:@tcp(127.0.0.1:3306)/db_test?charset=utf8&interpolateParams=true")
if err != nil {
panic(err.Error()) //只是举例,真实使用中需要对错误进行处理和返回
}
defer db_master.Close()
//从库用只读账户 建立mysql链接时使用参数interpolateParams=true
db_slave, err := sql.Open("mysql", "slave:@tcp(127.0.0.1:3306)/db_test?charset=utf8&interpolateParams=true")
if err != nil {
panic(err.Error())
}
defer db_slave.Close()
rows, err := db_slave.Query("SELECT * FROM tbl_user WHERE user_id = ?", 1)
if err != nil {
panic(err.Error())
}
}
三、 几种方案对比(why)
1、主从都使用读写账户,sql语句采用字符串拼接方式(废弃)
既不能实现主从读写分离,也不能防止sql注入攻击。
2、主从都使用读写账户,sql采用prepare+execute的方式(存在风险)
可以防止sql注入攻击,但不能实现主从读写分离,当并发量上来后主库压力会很大,存在风险。
3、主采用读写账户,从采用只读账户,sql采用prepare+execute的方式(会报错)
(1)首先抛出报错:
(2)报错原因分析:
图1-2 报错原因分析
错误原因:如果prepare的时候sql发给了从库1,但是execute的时候因为从库1延时较大,sql命令发给了从库2,就会报上面这个错误(除此之外,默认proxy会隔断时间切换备机,保证备机都能用到)。
4、主采用读写账户,从采用只读账户,建立mysql链接时使用参数interpolateParams=true
针对方案4,我们需要来看下底层源码:
这里我们需要关注两部分源码:database/sql和go-sql-driver/mysql,其中database/sql是golang针对数据库抽象出来的一个标准库,go-sql-driver/mysql是实现database/sql驱动接口的mysql驱动。
(1)我们一般写查询语句是这样的(举个栗子)
rows, err := db_slave.Query("SELECT * FROM tbl_user WHERE user_id = ?", 1)
if err != nil {
panic(err.Error()) //只是举例,真实使用中需要对错误进行处理和返回
}
我们知道prepared statement可以防止sql注入攻击,上图这样的写法看上去是使用的prepared statement方式,但到底是不是呢?我们继续往底层走。
(2)database/sql中查询接口是下面两个方法(即Query和QueryRow)
// Query executes a query that returns rows, typically a SELECT.
// The args are for any placeholder parameters in the query.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
return db.QueryContext(context.Background(), query, args...)
}
注:Query执行查询并返回多个数据行,这个查询通常是一个select,方法中args参数用于填写查询语句中包含的占位符的实际参数。
// QueryRow executes a query that is expected to return at most one row.
// QueryRow always returns a non-nil value. Errors are deferred until
// Row's Scan method is called.
// If the query selects no rows, the *Row's Scan will return ErrNoRows.
// Otherwise, the *Row's Scan scans the first selected row and discards
// the rest.
func (db *DB) QueryRow(query string, args ...interface{}) *Row {
return db.QueryRowContext(context.Background(), query, args...)
}
注:QueryRow与Query方法不同点是,执行一条查询最多只会返回一个数据行。
(3)发现宝藏:Query底层执行查询的策略
从queryDC方法来看Query默认是不使用prepared statement方式的,只有在查询时发生driver.ErrSkip错误才会启用prepared statement继续查询。
注:从Query到queryDC方法经过的连接获取和错误处理等逻辑不影响我们分析问题,可以忽略。
// queryDC executes a query on the given connection.
// The connection gets released by the releaseConn function.
// The ctx context is from a query method and the txctx context is from an
// optional transaction context.
func (db *DB) queryDC(ctx, txctx context.Context, dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
queryerCtx, ok := dc.ci.(driver.QueryerContext)
var queryer driver.Queryer
if !ok {
queryer, ok = dc.ci.(driver.Queryer)
}
if ok {
var nvdargs []driver.NamedValue
var rowsi driver.Rows
var err error
withLock(dc, func() {
nvdargs, err = driverArgsConnLocked(dc.ci, nil, args)
if err != nil {
return
}
//核心查询 不使用‘prepared Statement‘来执行
rowsi, err = ctxDriverQuery(ctx, queryerCtx, queryer, query, nvdargs)
})
if err != driver.ErrSkip {//发生错误driver.ErrSkip才使用‘prepared Statement‘方式去查询
if err != nil {//其他错误,关闭链接并报错返回
releaseConn(err)
return nil, err
}
// Note: ownership of dc passes to the *Rows, to be freed
// with releaseConn.
rows := &Rows{
dc: dc,
releaseConn: releaseConn,
rowsi: rowsi,
}
rows.initContextClose(ctx, txctx)
return rows, nil
}
}
//发生错误driver.ErrSkip时,才会使用‘Prepared Statement‘方式再次执行查询
var si driver.Stmt
var err error
withLock(dc, func() {
si, err = ctxDriverPrepare(ctx, dc.ci, query)//prepare
})
if err != nil {
releaseConn(err)
return nil, err
}
ds := &driverStmt{Locker: dc, si: si}
rowsi, err := rowsiFromStatement(ctx, dc.ci, ds, args...)//Statement
if err != nil {
ds.Close()
releaseConn(err)
return nil, err
}
// Note: ownership of ci passes to the *Rows, to be freed
// with releaseConn.
rows := &Rows{
dc: dc,
releaseConn: releaseConn,
rowsi: rowsi,
closeStmt: ds,
}
rows.initContextClose(ctx, txctx)
return rows, nil
}
(4)什么时候才会报driver.ErrSkip错误呢
答案就在go-sql-driver/mysql下的connection.go中真实Query方法中,请看大屏幕:
//mysql query底层实现
func (mc *mysqlConn) query(query string, args []driver.Value) (*textRows, error) {
if mc.closed.IsSet() {
errLog.Print(ErrInvalidConn)
return nil, driver.ErrBadConn
}
if len(args) != 0 {
if !mc.cfg.InterpolateParams {//能不能不使用Prepared Statement,就看interpolateParams是不是true了
return nil, driver.ErrSkip
}
// try client-side prepare to reduce roundtrip
prepared, err := mc.interpolateParams(query, args)//在mysql客户端侧使用插值法实现防止sql注入攻击
if err != nil {
return nil, err
}
query = prepared
}
// Send command
err := mc.writeCommandPacketStr(comQuery, query)
if err == nil {
// Read Result
var resLen int
resLen, err = mc.readResultSetHeaderPacket()
if err == nil {
rows := new(textRows)
rows.mc = mc
if resLen == 0 {
rows.rs.done = true
switch err := rows.NextResultSet(); err {
case nil, io.EOF:
return rows, nil
default:
return nil, err
}
}
// Columns
rows.rs.columns, err = mc.readColumns(resLen)
return rows, err
}
}
return nil, mc.markBadConn(err)
}
(5) 驱动程序是如何使用插值法防止sql注入攻击的呢?
我们到interpolateParams方法中一探究竟(我们只需要关注参数为string的情况):
func (mc *mysqlConn) interpolateParams(query string, args []driver.Value) (string, error) {
// Number of ? should be same to len(args)
if strings.Count(query, "?") != len(args) {
return "", driver.ErrSkip
}
buf, err := mc.buf.takeCompleteBuffer()
if err != nil {
// can not take the buffer. Something must be wrong with the connection
errLog.Print(err)
return "", ErrInvalidConn
}
buf = buf[:0]
argPos := 0
for i := 0; i < len(query); i++ {
q := strings.IndexByte(query[i:], '?')
if q == -1 {
buf = append(buf, query[i:]...)
break
}
buf = append(buf, query[i:i+q]...)
i += q
arg := args[argPos]
argPos++
if arg == nil {
buf = append(buf, "NULL"...)
continue
}
switch v := arg.(type) {
case int64:
buf = strconv.AppendInt(buf, v, 10)
case uint64:
// Handle uint64 explicitly because our custom ConvertValue emits unsigned values
buf = strconv.AppendUint(buf, v, 10)
case float64:
buf = strconv.AppendFloat(buf, v, 'g', -1, 64)
case bool:
if v {
buf = append(buf, '1')
} else {
buf = append(buf, '0')
}
case time.Time:
if v.IsZero() {
buf = append(buf, "'0000-00-00'"...)
} else {
v := v.In(mc.cfg.Loc)
v = v.Add(time.Nanosecond * 500) // To round under microsecond
year := v.Year()
year100 := year / 100
year1 := year % 100
month := v.Month()
day := v.Day()
hour := v.Hour()
minute := v.Minute()
second := v.Second()
micro := v.Nanosecond() / 1000
buf = append(buf, []byte{
'\'',
digits10[year100], digits01[year100],
digits10[year1], digits01[year1],
'-',
digits10[month], digits01[month],
'-',
digits10[day], digits01[day],
' ',
digits10[hour], digits01[hour],
':',
digits10[minute], digits01[minute],
':',
digits10[second], digits01[second],
}...)
if micro != 0 {
micro10000 := micro / 10000
micro100 := micro / 100 % 100
micro1 := micro % 100
buf = append(buf, []byte{
'.',
digits10[micro10000], digits01[micro10000],
digits10[micro100], digits01[micro100],
digits10[micro1], digits01[micro1],
}...)
}
buf = append(buf, '\'')
}
case json.RawMessage:
buf = append(buf, '\'')
if mc.status&statusNoBackslashEscapes == 0 {
buf = escapeBytesBackslash(buf, v)
} else {
buf = escapeBytesQuotes(buf, v)
}
buf = append(buf, '\'')
case []byte:
if v == nil {
buf = append(buf, "NULL"...)
} else {
buf = append(buf, "_binary'"...)
if mc.status&statusNoBackslashEscapes == 0 {
buf = escapeBytesBackslash(buf, v)
} else {
buf = escapeBytesQuotes(buf, v)
}
buf = append(buf, '\'')
}
case string:
//我们只需要关注参数为string的情况
buf = append(buf, '\'')
if mc.status&statusNoBackslashEscapes == 0 {
buf = escapeStringBackslash(buf, v)
} else {
buf = escapeStringQuotes(buf, v)
}
buf = append(buf, '\'')
default:
return "", driver.ErrSkip
}
if len(buf)+4 > mc.maxAllowedPacket {
return "", driver.ErrSkip
}
}
if argPos != len(args) {
return "", driver.ErrSkip
}
return string(buf), nil
}
源码中escapeStringBackslash方法的字面意思是对字符串参数转义,我们知道转义特殊字符就是防止sql注入攻击的有效方法之一,本着刨根问底的科学探索精神让我们再进到该方法中一探究竟~
//非常清晰,这里就是在做字符串中特殊字符的转义编码
// escapeStringBackslash is similar to escapeBytesBackslash but for string.
func escapeStringBackslash(buf []byte, v string) []byte {
pos := len(buf)
buf = reserveBuffer(buf, len(v)*2)
for i := 0; i < len(v); i++ {
c := v[i]
switch c {
case '\x00':
buf[pos] = '\\'
buf[pos+1] = '0'
pos += 2
case '\n':
buf[pos] = '\\'
buf[pos+1] = 'n'
pos += 2
case '\r':
buf[pos] = '\\'
buf[pos+1] = 'r'
pos += 2
case '\x1a':
buf[pos] = '\\'
buf[pos+1] = 'Z'
pos += 2
case '\'':
buf[pos] = '\\'
buf[pos+1] = '\''
pos += 2
case '"':
buf[pos] = '\\'
buf[pos+1] = '"'
pos += 2
case '\\':
buf[pos] = '\\'
buf[pos+1] = '\\'
pos += 2
default:
buf[pos] = c
pos++
}
}
return buf[:pos]
}
这里就是在做字符串中特殊字符的转义编码,因此使用参数interpolateParams=true可以防止sql注入攻击,并且因为没有使用prepared statement方式,当采用主从读写分离方式时,也不会再报出方案3中的错误,既实现了主从读写分离也可以防止sql注入攻击,是最佳解决方案。
(6)interpolateParams=true使用注意事项
可以看到官方文档最后给出一个补充说明,interpolateParams=true不可以与以下多字节编码共同使用(multibyte encodings BIG5, CP932, GB2312, GBK or SJIS),因为他们会引起sql注入脆弱性。
· 一个超经典 WinForm,WPF 卡死问题的终极反思
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
· .NET Core 对象分配(Alloc)底层原理浅谈
· 聊一聊 C#异步 任务延续的三种底层玩法
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(一):从.NET IoT入
· .NET 开发的分流抢票软件,不做广告、不收集隐私
· 一个超经典 WinForm,WPF 卡死问题的终极反思
· 开箱你的 AI 语音女友「GitHub 热点速览」
· 前端实现 HTML 网页转 PDF 并导出