From 6cc341355e736952329b328232c2560dc6f29060 Mon Sep 17 00:00:00 2001
From: lzx <2246256235@qq.com>
Date: 星期二, 05 十一月 2024 15:07:50 +0800
Subject: [PATCH] Merge branch 'master' of ssh://115.28.86.8:29418/~admin/昆仑_1025
---
Server/王琨元/code/MysqlConn.cpp | 310 +++++++++++++++++++++++----------------------------
1 files changed, 140 insertions(+), 170 deletions(-)
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp" "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp"
index 57ba1c6..5725af8 100644
--- "a/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp"
+++ "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp"
@@ -1,174 +1,144 @@
+#include "stdafx.h"
#include "MysqlConn.h"
+#include <regex>
-MysqlConn::MysqlConn()//初始化数据库
-{
- m_result = nullptr;
- m_mysqlRow = nullptr;
- // 传入nullptr空指针时，会自动分配一个MYSQL对象
- m_conn = mysql_init(nullptr);
-}
-MysqlConn::~MysqlConn()//释放数据库连接
-{
- freeRes(); // 释放结果集
- if (m_conn != nullptr) {
- mysql_close(m_conn);
- m_conn = nullptr;
- }
-}
-void MysqlConn::freeRes()//结果集释放
-{
- if (m_result) {
- mysql_free_result(m_result);
- m_result = nullptr;
- }
-}
-bool MysqlConn::connect(const string user, const string passwd, \
- const string dbName, string ip, \
- const unsigned short port)//连接数据库
-{
- MYSQL* res = mysql_real_connect(m_conn, ip.c_str(), user.c_str(), \
- passwd.c_str(), dbName.c_str(), port, nullptr, 0);
- // 修改编码
- mysql_set_character_set(m_conn, "gb2312");
- return res != nullptr;
-}
-bool MysqlConn::update(const string sql) const
-{
- // 执行成功返回0；
- int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
- if (res != 0) {
- return false; // 提示
- }
- return true;
-}
-bool MysqlConn::query(const string sql)
-{
- freeRes();
- int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
- if (res != 0) {
- return false; // 提示
- }
- m_result = mysql_store_result(m_conn);
- return true;
-}
-bool MysqlConn::getRes()
-{
- if (m_result != nullptr) {
- // char** 获取单行记录
- m_mysqlRow = mysql_fetch_row(m_result);
- if (m_mysqlRow != nullptr) {
- return true;
- }
- freeRes();
- }
- return false;
-}
-string MysqlConn::getValue(const int fieldIndex) const
-{
- int fieldCount = mysql_num_fields(m_result);
- if (fieldIndex >= fieldCount || fieldIndex < 0) {
- return string(); // 提示
- }
- char* value = m_mysqlRow[fieldIndex];
- // 得到一个保存各字段值长度的数组
- unsigned long* len = mysql_fetch_lengths(m_result);
- unsigned long length = len[fieldIndex];
- // 防止结果中存在\0导致数据丢失
- return string(value, length);
-}
-bool MysqlConn::selectDB(const string dbName) const
-{
- int res = mysql_select_db(m_conn, dbName.c_str());
- if (res != 0) {
- return false; // 提示
- }
- return true;
-}
-void MysqlConn::backupCurrentDB(const string path)
-{
- string sql = "show tables";
- int r = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned long>(sql.size()));
- if (r != 0) {
- return; // 提示
- }
- MYSQL_RES* tableRes = mysql_store_result(m_conn);
- for (int i = 0; i < mysql_num_rows(tableRes); ++i) {
- MYSQL_ROW tableName = mysql_fetch_row(tableRes);
- backupCurrentTable(path, tableName[0]);
- }
-}
-void MysqlConn::backupCurrentTable(const string path, const string tableName)
-{
- string file = path + tableName + ".sql";
- ofstream ofs(file);
- if (!ofs.is_open()) {
- return; // 提示
- }
- // 表结构写入
- string showCreate = "show create table " + tableName + ";";
- bool res = query(showCreate);
- if (!res) {
- return; // 提示
- }
- if (getRes()) {
- string writeSQL = getValue(1) + ";\n";
- ofs.write(writeSQL.c_str(), writeSQL.size());
- // cout << writeSQL << endl;
- }
- // 表数据写入
- string sql = "select * from " + tableName + ";";
- res = query(sql);
- if (!res) {
- return; // 提示
- }
- while (getRes()) {
- string writeSQL = "insert into `" + tableName + "` values(";
- for (int i = 0; !getValue(i).empty(); ++i) {
- if (i != 0) {
- writeSQL += ",";
- }
- MYSQL_FIELD* valueType = mysql_fetch_field_direct(m_result, i);
- if (valueType->type == MYSQL_TYPE_DECIMAL
- || valueType->type == MYSQL_TYPE_TINY
- || valueType->type == MYSQL_TYPE_SHORT
- || valueType->type == MYSQL_TYPE_LONG
- || valueType->type == MYSQL_TYPE_FLOAT
- || valueType->type == MYSQL_TYPE_DOUBLE
- || valueType->type == MYSQL_TYPE_TIMESTAMP
- || valueType->type == MYSQL_TYPE_LONGLONG
- || valueType->type == MYSQL_TYPE_INT24) {
- writeSQL += getValue(i);
- }
- else {
- writeSQL += "'" + getValue(i) + "'";
- }
- }
- writeSQL += ");\n";
- ofs.write(writeSQL.c_str(), writeSQL.size());
- }
- ofs.close();
-}
-bool MysqlConn::transaction() const
-{
- // 将事务提交设置为手动提交
- return mysql_autocommit(m_conn, false);
-}
-bool MysqlConn::commit() const
-{
- return mysql_commit(m_conn);
-}
-bool MysqlConn::rollback() const
-{
- return mysql_rollback(m_conn);
-}
-void MysqlConn::refreashAliveTime()
-{
- m_alivetime = steady_clock::now();
-}
-ll MysqlConn::getAliveTime()
-{
- // 毫秒 <<= 纳秒:精度降低
- milliseconds res = duration_cast<milliseconds>(steady_clock::now() - m_alivetime);
- return res.count();
+
+// 初始化连接
+MysqlConn::MysqlConn() {
+ mysql_ = mysql_init(mysql_);
+ // 设置字符集
+ if (mysql_) mysql_set_character_set(mysql_, "gbk");
}
+// 连接数据库
+bool MysqlConn::connect(std::string ip, std::string userName, std::string passwd, std::string db, int port) {
+ mysql_ = mysql_real_connect(mysql_, ip.c_str(), userName.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0);
+ if (!mysql_) {
+ return false;
+ }
+ return true;
+}
+
+// 释放资源
+MysqlConn::~MysqlConn() {
+ if (mysql_) {
+ mysql_close(mysql_);
+ mysql_ = nullptr;
+ }
+ freeRes();
+}
+
+// 更新数据
+bool MysqlConn::update(std::string sql) {
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ if (!stmt) {
+ return false;
+ }
+ if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length())) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ int paramCount = mysql_stmt_param_count(stmt);
+ if (paramCount > 0) {
+ // 如果有参数，需要进行参数绑定等操作，这里暂不实现
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ if (mysql_stmt_execute(stmt)) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ mysql_stmt_close(stmt);
+ return true;
+}
+
+// 查询数据库
+bool MysqlConn::query(std::string sql) {
+ freeRes();
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ if (!stmt) {
+ return false;
+ }
+ if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length())) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ int paramCount = mysql_stmt_param_count(stmt);
+ if (paramCount > 0) {
+ // 如果有参数，需要进行参数绑定等操作，这里暂不实现
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ if (mysql_stmt_execute(stmt)) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ res_ = mysql_stmt_result_metadata(stmt);
+ if (!res_) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ mysql_stmt_close(stmt);
+ return true;
+}
+
+// 得到结果集
+bool MysqlConn::getResult() {
+ if (res_) {
+ row_ = mysql_fetch_row(res_);
+ if (row_) return true;
+ }
+ return false;
+}
+
+// 获取结果集的字段
+std::string MysqlConn::getField(int index) {
+ int cols = mysql_num_fields(res_);
+ if (index >= cols || index < 0) return std::string("");
+
+ char* value = row_[index];
+ unsigned long len = mysql_fetch_lengths(res_)[index];
+ return std::string(value, len);
+}
+
+// 事务操作
+bool MysqlConn::transaction() {
+ return mysql_autocommit(mysql_, false);
+}
+
+// 提交事务
+bool MysqlConn::commit() {
+ return mysql_commit(mysql_);
+}
+
+// 事务回滚
+bool MysqlConn::rollback() {
+ return mysql_rollback(mysql_);
+}
+
+void MysqlConn::refreshActiveTime() {
+ activeTime_ = std::chrono::steady_clock::now();
+}
+
+long long MysqlConn::getActiveTime() {
+ // 纳米
+ std::chrono::nanoseconds nased = std::chrono::steady_clock::now() - activeTime_;
+ // 转换成毫米
+ std::chrono::microseconds millsed = std::chrono::duration_cast<std::chrono::microseconds>(nased);
+ return millsed.count(); // 多少毫秒
+}
+
+// 安全校验实现，这里简单使用正则表达式判断是否包含危险字符
+bool MysqlConn::isSqlSafe(const std::string& sql) {
+ std::regex dangerousPattern(".*(['\";\\-+=]).*");
+ return!std::regex_search(sql, dangerousPattern);
+}
+
+void MysqlConn::freeRes() {
+ if (res_) {
+ mysql_free_result(res_);
+ res_ = nullptr;
+ }
+}
\ No newline at end of file
--
Gitblit v1.8.0