From 4b032971d376b063480e53842045561f7c9b399b Mon Sep 17 00:00:00 2001
From: wangky <m1561510467@163.com>
Date: 星期一, 04 十一月 2024 18:36:06 +0800
Subject: [PATCH] 1
---
Server/王琨元/code/MysqlConn.cpp | 261 ++++++++++++++++++++--------------------------------
1 files changed, 101 insertions(+), 160 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..cf3a431 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,115 @@
#include "MysqlConn.h"
+#include <regex>
+// 初始化连接
+MysqlConn::MysqlConn() {
+ mysql_ = mysql_init( mysql_ );
+ // 设置字符集
+ if( mysql_ ) mysql_set_character_set(mysql_ , "gbk");
+}
-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; // 提示
+// 连接数据库
+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;
}
-bool MysqlConn::query(const string sql)
-{
+
+// 释放资源
+MysqlConn::~MysqlConn() {
+ if (mysql_) {
+ mysql_close(mysql_);
+ mysql_ = nullptr;
+ }
freeRes();
- int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
- if (res != 0) {
- return false; // 提示
+}
+// 更新数据
+bool MysqlConn::update( std::string sql ) {
+ if (!isSqlSafe(sql))
+ {
+ return false;
}
- m_result = mysql_store_result(m_conn);
+ int ret = mysql_query( mysql_ , sql.c_str());
+ if( ret != 0 ){
+ return false;
+ }
return true;
}
-bool MysqlConn::getRes()
-{
- if (m_result != nullptr) {
- // char** 获取单行记录
- m_mysqlRow = mysql_fetch_row(m_result);
- if (m_mysqlRow != nullptr) {
- return true;
- }
- freeRes();
+
+// 查询数据库
+bool MysqlConn::query(std::string sql) {
+ freeRes( );
+ if (!isSqlSafe(sql))
+ {
+ return false;
+ }
+ int ret = mysql_query(mysql_, sql.c_str());
+ if (ret != 0) return false;
+
+ // 获取查询结果
+ res_ = mysql_store_result(mysql_);
+ if (!res_) return false;
+ return true;
+}
+
+// 得到结果集
+bool MysqlConn::getResult() {
+ if (res_) {
+ row_ = mysql_fetch_row(res_);
+ if(row_) return true;
}
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();
+
+// 获取结果集的字段
+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