MySQL的SQL预编译及防SQL注入

1 SQL语句的执行处理

SQL的执行可大致分为下面两种模式：

“Immediate Statements” VS “Prepared Staements” ：

1.1 即时SQL

动态的根据传入的参数拼接SQL语句并执行，一条语句经过MySQL server层分析器、优化器、执行器组件，分别进行词法、语义解析、优化SQL语句、选择索引、制定执行计划、执行并返回结果。

对SQL语句进行词法语义分析、优化SQL语句、选择索引、制定执行计划等一系列操作，称为 “对SQL语句的编译”。

如上，一条SQL语句按照此流程处理，一次编译，单次运行，此类普通语句被称作 “Immediate Statements”（即时SQL）。

例如：

bool CUserModel::getUser(uint32_t nUserId, DBUserInfo_t &cUser) 
{
    CDBConn* pDBConn = CDBManager::getInstance()->GetDBConn("teamtalk_slave");
    if(pDBConn) 
    {
    	//根据函数外部传入的参数 nUserId，动态构造 select查询语句并执行：
		string strSql = "select * from IMUser where id = " + int2string(nUserId);
		CResultSet* pResultSet = pDBConn->ExcuteQuery(strSql.c_str());
		if(pResultSet) 
		{
			while(pResultSet->Next()) 
			{
				//...
			}
		}
	} 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

但是，绝大多数情况下，一般会需要一条SQL语句反复调用执行（例如上面的查找IMUser表中的用户信息，每次客户端向服务器请求登录验证时都需要执行一次），或者每次执行的时候只有个别的值不同（比如select的where子句值不同，update的set子句值不同，insert的values子句值不同）。

如果每次都需要经过上面的SQL编译过程（词法语义分析、语句优化、制定执行计划等），则效率明细会受到影响。

1.2 预处理SQL

所谓 “预编译SQL语句”，就是将此类SQL语句中的某些值使用 “占位符” 替代，可以视为将SQL语句 “模板化” 或者说 “参数化”。一般称这类语句为 “Prepared Statements”。

预编译SQL语句的优势在于：一次编译、多次运行，省去了解析、优化等过程。此外使用预编译SQL语句还能防止SQL注入，下文展开。

1.2.1 预编译SQL的实现步骤

（1）先与MySQL数据库取得连接，获得 “连接句柄” MYSQL* ：

MYSQl* mysql_init();
mysql_options();
mysql_real_connect(MYSQL*, ip, user_name, passed, db_name, port);
1
2
3

（2）基于这个 MYSQL* 连接句柄，初始化一个“预编译句柄”MYSQL_STMT*：

MYSQL_STMT* mysql_stmt_init(MYSQL*);
1

（3）传入准备好的带有“占位符”的SQL语句，进行编译：

mysql_stmt_prepare(MYSQL_STMT*, sql.c_str(), sizeof(sql));
1

（4）在后面要使用这个预编译的SQL语句时，需要向其中传入实参填补“占位符”，所以我们必须要先将占位符的个数统计出来，并预先初始化一个 MYSQL_BIND类型的结构体数组（MYSQL_BIND[]数组的元素个数是SQL语句中占位符的个数，数组中每个元素是MYSQL_BIND结构体，用于指定某个占位符上的数据类型(如int) 及 数据值），等待使用时向其中填充参数：

uint32_t m_param_cnt = mysql_stmt_param_count(MYSQL_STMT*);
MYSQL_BIND* m_param_bind = new MYSQL_BIND[m_param_cnt];	//新建一个数组
1
2

（5）在使用时，先给 MYSQL_BIND[] 数组填充值：

for(int index = 0; index < m_param_cnt; index++) 
{
	//如果value是int型：
	MYSQL_BIND[index].buffer_type = MYSQL_TYPE_LONG; 
	MYSQL_BIND[index].buffer = &value;
	/*
	//如果value是string型：
	MYSQL_BIND[index].buffer_type = MYSQL_TYPE_LONG; 
	MYSQL_BIND[index].buffer = (char*)value.c_str();
	MYSQL_BIND[index].buffer_length = value.size();
	*/
}
1
2
3
4
5
6
7
8
9
10
11
12

（6）向填充好实参的MYSQL_BIND数组传入MYSQL_STMT句柄，随后执行这条SQL语句，并检查执行结果：

msyql_stmt_bind_param(m_stmt, m_param_bind );
mysql_stmt_excute(m_stmt);		//如果有错误发生，函数返回非0，使用 mysql_stmt_error(m_stmt);可检查错误原因
mysql_stmt_affected_rows(m_stmt) == 0;
1
2
3

1.2.2 预编译SQL的C++使用举例

实现一个 CPrepareStatement 类，封装 MYSQL_STMT* 和 MYSQL_BIND* 对象，即相应的SQL预编译方法：

//cpreparestatement.h

class CPrepareStatement {
public:
    CPrepareStatement() {}
    ~CPrepareStatement() {}

    bool Init(MYSQL* mysql, string& sql);

    void SetParam(uint32_t index, int& value);
    void SetParam(uint32_t index, uint32_t& value);
    void SetParam(uint32_t index, string& value);
    void SetParam(uint32_t index, const string& value);

    bool ExecuteUpdate();

    uint32_t GetInsertId();

private:
    MYSQL_STMT*   m_stmt;
    MYSQL_BNID*   m_param_bind;
    uint32_t      m_param_cnt;
};


//cpreparement.cpp

bool CPrepareStatement::Init(MYSQL* mysql, string& sql) {
    mysql_ping(mysql);

    m_stmt = mysql_stmt_init(mysql);
    if(!m_stmt) {
        return false;
    }

    if(mysql_stmt_prepare(m_stmt, sql.c_str(), sql.size())) {
        printf("%s\n", mysql_stmt_error(m_stmt));
        return false;
    }

    m_param_cnt = mysql_stmt_papram_count(m_stmt);
    if(m_param_cnt > 0) {
        m_param_bind = new MYSQL_BIND[m_param_cnt];
        if(!m_param_bind) {
            return false;
        }
    }

    memset(m_param_bind, 0, sizeof(MYSQL_BIND) * m_param_cnt);
    return true;
}

//注意：给int型和string型赋值的方式是不同的：
void CPrepareStatement::SetParam(uint32_t index, int& value) {
    if(index >= m_param_cnt)
        return;

    m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;
    m_param_bind[index].buffer = &value;
}

void CPrepareStatement::SetParam(uint32_t index, uint32_t& value) {
    if(index >= m_param_cnt)
        return;

    m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;
    m_param_bind[index].buffer = &value;
}

void CPrepareStatement::SetParam(uint32_t index, string& value) {
    if(index >= m_param_cnt)
        return;

    m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;
    m_param_bind[index].buffer = (char*)value.c_str();
    m_param_bind[index].buffer_length = value.size();
}

void CPrepareStatement::SetParam(uint32_t index, const string& value) {
    if(index >= m_param_cnt)
        return;

    m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;
    m_param_bind[index].buffer = (char*)value.c_str();
    m_param_bind[index].buffer_length = value.size();
}

bool CPrepareStatement::ExecuteUpdate() {
    if(!m_stmt)
        return false;

    if(mysql_stmt_bind_param(m_stmt, m_param_bind)) {
        printf("%s\n", mysql_stmt_error(m_stmt));
        return false;
    }

    if(mysql_stmt_execute(m_stmt)) {
        printf("%s\n", mysql_stmt_error(m_stmt));
        return false;
    }

    if(msyql_affected_rows(m_stmt) == 0) {
        printf("no affect\n");
        return false; 
    }

    return true;
}

uint32_t CPrepareStatement::GetInsertId() {
    return mysql_stmt_insert_id(m_stmt);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

使用 class CPrepareStatement 类执行insert into插入操作：

bool CMessageModel::sendMessage(uint32_t nRelateId, uint32_t nFromId, uint32_t nToId, IM::BaseDefine::MsgType nMsgType, uint32_t nCreateTime, uint32_t nMsgId, string& strMsgContent) {

    CDBConn* pDBConn = CDBManager::getInstance()->GetDBConn("teamtalk_slave");
    if(pDBConn) {
        string strTableName = "IMMessage_" + int2string(nRelateId % 8);
        string strSql = "insert into " + strTableName + " ('relateId', 'fromId', 'toId', 'msgId', 'content', 'status', 
                                        'type', 'created', 'updated') values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        shared_ptr<CPrepareStatement> pStmt = make_shared<CPrepareStatement>();
        if(pStmt->Init(pDBConn->GetMysql(), strSql)) {
            uint32_t nStatus = 0;   //表示查询未被删除的记录
			uint32_t index = 0;
            pStmt->SetParam(index++, nRelateId);
            pStmt->SetParam(index++, nFromId);
            pStmt->SetParam(index++, nToId);
            pStmt->SetParam(index++, nMsgId);
            pStmt->SetParam(index++, strMsgContent);
            pStmt->SetParam(index++, nStatus);
            pStmt->SetParam(index++, nMsgType);
            pStmt->SetParam(index++, nCreateTime);
            pStmt->SetParam(index++, nCreateTime);
            
            pStmt->ExecuteUpdate();
        }
        //delete pStmt; 使用shared_ptr智能指针，不必delete删除
        pDBManager->RelDBConn(pDBConn); //这里同样可以使用RAII的方法实现自动释放，在 CDBConn类对象析构的时候释放连接
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

1.2.3 MYSQL_BIND()函数中的参数类型：

MYSQL_BIND() 函数中的参数类型如下表所示，可见 MYSQL_TYPE_LONG 表示的是 4字节的int型。

2 SQL注入

2.1 什么是SQL注入

所谓SQL注入，就是通过把SQL命令插入到Web表单提交或页面请求url的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意）的SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

实战举例

有个登陆框如下：

可以看到除了账号密码之外，还有一个公司名的输入框，根据输入框的形式不难推出SQL的写法如下：

SELECT * From table_name WHERE name=‘XX’ and password=‘YY’ and corporate=‘ZZ’

怎么做呢？

因为没有校验，因此，我们账号密码，都不填写，直接在最后，添加 or 1=1 –

看看与上面SQL组合，成了如下：

SELECT * From table_name WHERE name=’’ and password=’’ and corporate=’’ or 1=1-’

从代码可以看出，前一半单引号被闭合，后一半单引号被 “–”给注释掉，中间多了一个永远成立的条件“1=1”，这就造成任何字符都能成功登录的结果。

重要提醒
不要以为在输入框做个检查就够了，不要忘记了，我们web提交表单，是可以模拟url直接访问过去，绕开前段检查。因此，必须是后端，或是数据来检查才能有效防止。

（1）检查用户输入的合法性；

（2）将用户的登录名、密码等数据加密保存。

（3）预处理SQL。

（4）使用存储过程实现查询，虽然不推荐，但也是一个方法。

2.2 如何防止SQL注入

其实是因为SQL语句在程序运行前已经进行了预编译，在程序运行时第一次操作数据库之前，SQL语句已经被数据库分析，编译和优化，对应的执行计划也会缓存下来并允许数据库已参数化的形式进行查询，当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1’也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令，如此，就起到了SQL注入的作用了！

具体像这样。例如刚刚那条SQL：

SELECT * From table_name WHERE name=’’ and password=’’ and corporate=’’ or 1=1-’

开启预编译执行SQL的时候，则不会这么处理。会当成一个属性值。什么意思。随便你怎么加，都是一个值。也就是说，如果中间有产生歧义的，都将被处理掉，最后执行相当于是这样：

SELECT * From table_name WHERE name=’’ and password=’’ and corporate="'or 1=1–"

输入的一串，都被揉在一起，作一个参数，而不是SQL指令。