From f647dd12ca604593774b7022c49f47ac98baf617 Mon Sep 17 00:00:00 2001
From: jhc <2991879965@qq.com>
Date: 星期四, 07 十一月 2024 15:02:13 +0800
Subject: [PATCH] 上传日志
---
Server/王琨元/code/MysqlConn.cpp | 355 +++++++++++++++++++++++++++++++----------------------------
1 files changed, 187 insertions(+), 168 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..071cf5f 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,193 @@
+#include "stdafx.h"
#include "MysqlConn.h"
-MysqlConn::MysqlConn()//初始化数据库
-{
- m_result = nullptr;
- m_mysqlRow = nullptr;
- // 传入nullptr空指针时，会自动分配一个MYSQL对象
- m_conn = mysql_init(nullptr);
+
+
+// 初始化连接
+// 初始化连接
+MysqlConn::MysqlConn() {
+ m_mysql = mysql_init(nullptr);
+ if (!m_mysql) {
+ cout << "Failed to initialize MySQL." << endl;
+ return;
+ }
+ // 设置字符集
+ if (m_mysql) mysql_set_character_set(m_mysql, "gbk");
+ // 使用写死的参数连接数据库
+ m_mysql = mysql_real_connect(m_mysql, "127.0.0.1", "root", "123456", "mayi_kunlun", 3306, nullptr, 0);
+ if (!m_mysql) {
+ cout << "MysqlConn connect to mysql is failed!" << endl;
+ // 确保在连接失败时正确清理资源
+ mysql_close(m_mysql);
+ m_mysql = nullptr;
+ }
+ // 初始化 row_ 和 res_
+ m_row = nullptr;
+ m_res = nullptr;
}
-MysqlConn::~MysqlConn()//释放数据库连接
-{
- freeRes(); // 释放结果集
- if (m_conn != nullptr) {
- mysql_close(m_conn);
- m_conn = nullptr;
- }
+bool MysqlConn::isConnected() {
+ return m_mysql != 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();
+// 连接数据库
+//bool MysqlConn::connect() {
+// string ip = "127.0.0.1";
+// string userName = "root";
+// string passwd = "123456";
+// string db = "mayi_kunlun";
+// int port = 3306;
+// m_mysql = mysql_real_connect(mysql_, ip.c_str(), userName.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0);
+// if (!m_mysql) {
+// return false;
+// }
+// return true;
+//}
+
+// 释放资源
+MysqlConn::~MysqlConn() {
+ if (m_mysql) {
+ mysql_close(m_mysql);
+ m_mysql = nullptr;
+ }
+ freeRes();
}
+// 更新数据
+bool MysqlConn::update(string sql) {
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(m_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(string sql, vector<vector<string>>& result)
+{
+ freeRes();
+ int ret = mysql_query(m_mysql, sql.c_str());
+ if (ret != 0) {
+ fprintf(stderr, "Query failed: %s\n", mysql_error(m_mysql));
+ return false;
+ }
+ // 获取查询结果
+ m_res = mysql_store_result(m_mysql);
+ if (!m_res) return false;
+ //将结果集转到二维数组result里面
+ //int col_cnt = m_mysql->field_count;
+ int col_cnt = mysql_field_count(m_mysql);
+ cout << col_cnt << endl;
+ while (m_row = mysql_fetch_row(m_res)) {
+ vector<string> tmp;
+ for (int i = 0; i < col_cnt; i++) {
+ tmp.push_back(m_row[i]);
+ }
+ result.push_back(tmp);
+ }
+ return true;
+}
+
+// 查询数据库
+bool MysqlConn::query(string sql) {
+ freeRes();
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(m_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;
+ }
+ m_res = mysql_stmt_result_metadata(stmt);
+ if (!m_row) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ mysql_stmt_close(stmt);
+ return true;
+}
+
+// 得到结果集
+bool MysqlConn::getResult() {
+ if (m_row) {
+ m_row = mysql_fetch_row(m_res);
+ if (m_row) return true;
+ }
+ return false;
+}
+
+// 获取结果集的字段
+string MysqlConn::getField(int index) {
+ int cols = mysql_num_fields(m_res);
+ if (!m_res || index >= cols || index < 0) return string("");
+
+ char* value = m_row[index];
+ // 使用显式类型转换
+ unsigned long len = static_cast<unsigned long>(mysql_fetch_lengths(m_res)[index]);
+ return string(value, len);
+}
+
+// 事务操作
+bool MysqlConn::transaction() {
+ return mysql_autocommit(m_mysql, false);
+}
+
+// 提交事务
+bool MysqlConn::commit() {
+ return mysql_commit(m_mysql);
+}
+
+// 事务回滚
+bool MysqlConn::rollback() {
+ return mysql_rollback(m_mysql);
+}
+
+void MysqlConn::refreshActiveTime() {
+ m_activeTime = chrono::steady_clock::now();
+}
+
+long long MysqlConn::getActiveTime() {
+ // 纳米
+ chrono::nanoseconds nased = chrono::steady_clock::now() - m_activeTime;
+ // 转换成毫米
+ chrono::microseconds millsed = chrono::duration_cast<chrono::microseconds>(nased);
+ return millsed.count(); // 多少毫秒
+}
+
+// 安全校验实现，这里简单使用正则表达式判断是否包含危险字符
+bool MysqlConn::isSqlSafe(const string& sql) {
+ regex dangerousPattern(".*(['\";\\-+=]).*");
+ return!regex_search(sql, dangerousPattern);
+}
+
+void MysqlConn::freeRes() {
+ if (m_res) {
+ mysql_free_result(m_res);
+ m_res = nullptr;
+ }
+}
\ No newline at end of file
--
Gitblit v1.8.0