From 4e09764be3e77fc866399230982086cb606b7e11 Mon Sep 17 00:00:00 2001
From: wangky <m1561510467@163.com>
Date: 星期二, 29 十月 2024 12:06:17 +0800
Subject: [PATCH] 修改
---
Server/王琨元/document/单例模式封装.txt | 89 ++++++--------
Server/王琨元/document/数据库模块需求分析.docx | 0
Server/王琨元/document/备份导出.txt | 29 ++++
Server/王琨元/document/数据库连接池类.txt | 91 +++++++++++++++
Server/王琨元/document/防注入.txt | 94 +++++++++++----
5 files changed, 225 insertions(+), 78 deletions(-)
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/document/\345\215\225\344\276\213\346\250\241\345\274\217\345\260\201\350\243\205.txt" "b/Server/\347\216\213\347\220\250\345\205\203/document/\345\215\225\344\276\213\346\250\241\345\274\217\345\260\201\350\243\205.txt"
index 2532b6c..c07f1df 100644
--- "a/Server/\347\216\213\347\220\250\345\205\203/document/\345\215\225\344\276\213\346\250\241\345\274\217\345\260\201\350\243\205.txt"
+++ "b/Server/\347\216\213\347\220\250\345\205\203/document/\345\215\225\344\276\213\346\250\241\345\274\217\345\260\201\350\243\205.txt"
@@ -1,62 +1,51 @@
#include <iostream>
+#include <memory>
#include <mutex>
-#include <fstream>
#include <string>
-#include <ctime>
-using namespace std;
-class A {
+#include <mysql_driver.h>
+#include <mysql_connection.h>
+#include <cppconn/statement.h>
+#include <cppconn/resultset.h>
+
+class DatabaseOperator {
private:
- ofstream logFile;
- A() {
- logFile.open("123.txt", ios::app);
+ // 绉佹湁鏋勯�犲嚱鏁�
+ DatabaseOperator() {
+ try {
+ driver = sql::mysql::get_mysql_driver_instance();
+ connection = driver->connect("tcp://127.0.0.1:3306", "username", "password");
+ connection->setSchema("your_database");
+ } catch (sql::SQLException &e) {
+ std::cerr << "鏁版嵁搴撹繛鎺ラ敊璇�: " << e.what() << std::endl;
+ }
}
- ~A() {
- logFile.close();
- }
- A(const A &t){}
- A& operator=(const A &t){}
- static A* volatile s_obj;
- static mutex g_mutex;
+
+ static DatabaseOperator* instance;
+ static std::mutex mutex;
+ sql::Driver* driver;
+ std::unique_ptr<sql::Connection> connection;
+
public:
- static A* getInstance() {
- if (s_obj == nullptr) {
- lock_guard<mutex> guard(g_mutex);
- if (s_obj == nullptr) {
- s_obj = new A;
- }
+ // 鑾峰彇鍗曚緥瀹炰緥
+ static DatabaseOperator* getInstance() {
+ std::lock_guard<std::mutex> lock(mutex);
+ if (instance == nullptr) {
+ instance = new DatabaseOperator();
}
- return s_obj;
+ return instance;
}
- void write(const string& level, const string& description, const string& time) {
- logFile << "[" << level << "] " << "[" << time << "] " << description << endl;
- }
- void release() {
- if (s_obj) {
- delete s_obj;
- s_obj = nullptr;
+
+ // 鎵ц鏌ヨ鎿嶄綔锛堢ず渚嬶級
+ sql::ResultSet* query(const std::string& sql) {
+ try {
+ std::unique_ptr<sql::Statement> stmt(connection->createStatement());
+ return stmt->executeQuery(sql);
+ } catch (sql::SQLException &e) {
+ std::cerr << "鏌ヨ閿欒: " << e.what() << std::endl;
}
+ return nullptr;
}
};
-A* volatile A::s_obj = nullptr;
-mutex A::g_mutex;
-
-int main() {
- A* a1 = A::getInstance();
- A* a2 = A::getInstance();
- if (a1 == a2) {
- cout << "鍗曚緥鎴愬姛" << endl;
- }
- else {
- cout << "鍗曚緥澶辫触" << endl;
- }
- time_t now = time(nullptr);
- char buffer[80];
- struct tm timeinfo;
- localtime_s(&timeinfo, &now);
- strftime(buffer, 80, "%Y - %m - %d %H:%M:%S", &timeinfo);
- string timeStr(buffer);
- a1->write("1", "鏃ュ織", timeStr);
- a1->release();
- return 0;
-}
\ No newline at end of file
+std::mutex DatabaseOperator::mutex;
+DatabaseOperator* DatabaseOperator::instance = nullptr;
\ No newline at end of file
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/document/\345\244\207\344\273\275\345\257\274\345\207\272.txt" "b/Server/\347\216\213\347\220\250\345\205\203/document/\345\244\207\344\273\275\345\257\274\345\207\272.txt"
new file mode 100644
index 0000000..79c8216
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/document/\345\244\207\344\273\275\345\257\274\345\207\272.txt"
@@ -0,0 +1,29 @@
+#!/bin/bash
+
+# 鏁版嵁搴撹繛鎺ュ弬鏁�
+DB_USER="your_username"
+DB_PASS="your_password"
+DB_NAME="your_database_name"
+BACKUP_DIR="/path/to/backup/directory"
+
+# 鑾峰彇褰撳墠鏃ユ湡锛岀敤浜庢枃浠跺悕
+DATE=$(date +%Y%m%d%H%M%S)
+
+# 澶囦唤鏂囦欢鍚�
+BACKUP_FILE="${BACKUP_DIR}/backup_${DATE}.sql"
+
+# 鍒涘缓澶囦唤鐩綍锛堝鏋滀笉瀛樺湪锛�
+mkdir -p $BACKUP_DIR
+
+# 浣跨敤mysqldump杩涜鏁版嵁搴撳浠�
+mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE
+
+if [ $? -eq 0 ]; then
+ echo "鏁版嵁搴撳浠芥垚鍔燂細$BACKUP_FILE"
+else
+ echo "鏁版嵁搴撳浠藉け璐�"
+fi
+
+# 娣诲姞瀹氭椂浠诲姟锛堜娇鐢╟rontab -e鏉ョ紪杈戝畾鏃朵换鍔★級
+# 渚嬪锛屾瘡澶╁噷鏅�2鐐规墽琛屽浠�
+# 0 2 * * * /path/to/this/script.sh
\ No newline at end of file
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\346\250\241\345\235\227\351\234\200\346\261\202\345\210\206\346\236\220.docx" "b/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\346\250\241\345\235\227\351\234\200\346\261\202\345\210\206\346\236\220.docx"
new file mode 100644
index 0000000..0a608f6
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\346\250\241\345\235\227\351\234\200\346\261\202\345\210\206\346\236\220.docx"
Binary files differ
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\350\277\236\346\216\245\346\261\240\347\261\273.txt" "b/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\350\277\236\346\216\245\346\261\240\347\261\273.txt"
new file mode 100644
index 0000000..8c82c1a
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/document/\346\225\260\346\215\256\345\272\223\350\277\236\346\216\245\346\261\240\347\261\273.txt"
@@ -0,0 +1,91 @@
+#include <iostream>
+#include <list>
+#include <mutex>
+#include <condition_variable>
+#include <mysql_driver.h>
+#include <mysql_connection.h>
+#include <cppconn/statement.h>
+#include <cppconn/resultset.h>
+
+class SQLConnectionPool {
+private:
+ SQLConnectionPool(int minConns, int maxConns) :
+ minConnections(minConns), maxConnections(maxConns), currentConnections(0) {}
+
+ static SQLConnectionPool* instance;
+ static std::mutex mutex;
+ std::condition_variable cv;
+ int minConnections;
+ int maxConnections;
+ int currentConnections;
+ std::list<std::unique_ptr<sql::Connection>> connections;
+
+ // 鍒涘缓涓�涓柊鐨勬暟鎹簱杩炴帴
+ std::unique_ptr<sql::Connection> createConnection() {
+ try {
+ sql::Driver* driver = sql::mysql::get_mysql_driver_instance();
+ std::unique_ptr<sql::Connection> conn(driver->connect("tcp://127.0.0.1:3306", "username", "password"));
+ conn->setSchema("your_database");
+ return conn;
+ } catch (sql::SQLException &e) {
+ std::cerr << "鍒涘缓杩炴帴閿欒: " << e.what() << std::endl;
+ return nullptr;
+ }
+ }
+
+public:
+ // 鑾峰彇鍗曚緥瀹炰緥
+ static SQLConnectionPool* getInstance(int minConns, int maxConns) {
+ std::lock_guard<std::mutex> lock(mutex);
+ if (instance == nullptr) {
+ instance = new SQLConnectionPool(minConns, maxConns);
+ instance->initializePool();
+ }
+ return instance;
+ }
+
+ // 鍒濆鍖栬繛鎺ユ睜
+ void initializePool() {
+ for (int i = 0; i < minConnections; ++i) {
+ std::unique_ptr<sql::Connection> conn = createConnection();
+ if (conn) {
+ connections.push_back(std::move(conn));
+ currentConnections++;
+ }
+ }
+ }
+
+ // 鑾峰彇鏁版嵁搴撹繛鎺�
+ std::unique_ptr<sql::Connection> getConnection() {
+ std::unique_lock<std::mutex> lock(mutex);
+ while (connections.empty() && currentConnections >= maxConnections) {
+ cv.wait(lock);
+ }
+
+ std::unique_ptr<sql::Connection> conn;
+ if (!connections.empty()) {
+ conn = std::move(connections.front());
+ connections.pop_front();
+ } else if (currentConnections < maxConnections) {
+ conn = createConnection();
+ if (conn) {
+ currentConnections++;
+ }
+ }
+ return conn;
+ }
+
+ // 褰掕繕鏁版嵁搴撹繛鎺�
+ void releaseConnection(std::unique_ptr<sql::Connection>& conn) {
+ std::lock_guard<std::mutex> lock(mutex);
+ if (currentConnections > minConnections) {
+ currentConnections--;
+ } else {
+ connections.push_back(std::move(conn));
+ }
+ cv.notify_one();
+ }
+};
+
+std::mutex SQLConnectionPool::mutex;
+SQLConnectionPool* SQLConnectionPool::instance = nullptr;
\ No newline at end of file
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/document/\351\230\262\346\263\250\345\205\245.txt" "b/Server/\347\216\213\347\220\250\345\205\203/document/\351\230\262\346\263\250\345\205\245.txt"
index 09b9217..19a2f6c 100644
--- "a/Server/\347\216\213\347\220\250\345\205\203/document/\351\230\262\346\263\250\345\205\245.txt"
+++ "b/Server/\347\216\213\347\220\250\345\205\203/document/\351\230\262\346\263\250\345\205\245.txt"
@@ -1,29 +1,67 @@
-https://blog.csdn.net/qq_28245087/article/details/131453274
-1 .浣跨敤鍙傛暟鍖栨煡璇�
-浣跨敤鍙傛暟鍖栨煡璇㈠彲浠ラ槻姝QL娉ㄥ叆鏀诲嚮锛屽苟鎻愰珮浠ｇ爜鐨勫彲璇绘�у拰鍙淮鎶ゆ�с�傚湪Java涓紝鍙互浣跨敤PreparedStatement鏉ュ疄鐜板弬鏁板寲鏌ヨ銆�
-2. 杈撳叆楠岃瘉鍜岃繃婊�
-杈撳叆楠岃瘉鍜岃繃婊ゆ槸涓�绉嶇敤浜庣‘淇濈敤鎴疯緭鍏ユ暟鎹殑瀹夊叏鎬у拰鏈夋晥鎬х殑鎶�鏈�傚畠鍙互闃叉鎭舵剰杈撳叆鍜岄敊璇暟鎹鑷寸殑瀹夊叏婕忔礊鍜屽簲鐢ㄧ▼搴忛敊璇��
-3. 浣跨敤瀛樺偍杩囩▼
-瀛樺偍杩囩▼鏄竴缁勯瀹氫箟鐨凷QL璇彞闆嗗悎锛屽彲浠ュ湪鏁版嵁搴撲腑杩涜閲嶅鎬у拰澶嶆潅鎬х殑鎿嶄綔銆傚畠浠彲浠ユ帴鍙楀弬鏁帮紝骞朵笖鍙互鍦ㄦ暟鎹簱涓繘琛岄噸澶嶄娇鐢ㄣ��
-4.鏈�灏忔潈闄愬師鍒�
-鏈�灏忔潈闄愬師鍒欐槸涓�绉嶅畨鍏ㄦ�у師鍒欙紝鎸囩殑鏄负浜嗕繚鎶ゆ晱鎰熸暟鎹拰绯荤粺璧勬簮锛岀敤鎴峰簲璇ヨ鎺堜簣鏈�灏忓繀闇�鐨勬潈闄愩�傝繖鎰忓懗鐫�鐢ㄦ埛鍙兘璁块棶鍜屾墽琛屼粬浠伐浣滄墍闇�鐨勬暟鎹簱瀵硅薄鍜屾搷浣滐紝鑰屼笉鏄嫢鏈夊鏁翠釜鏁版嵁搴撶殑瀹屽叏璁块棶鏉冮檺銆�
-浣跨敤鏈�灏忔潈闄愬師鍒欏彲浠ュ噺灏戞綔鍦ㄧ殑瀹夊叏椋庨櫓鍜屾暟鎹硠闇茬殑鍙兘鎬с�傞�氳繃闄愬埗鐢ㄦ埛鐨勬潈闄愶紝鍙互闃叉浠栦滑瀵规暟鎹簱涓殑鏁忔劅鏁版嵁杩涜鏈粡鎺堟潈鐨勮闂�佷慨鏀规垨鍒犻櫎銆�
-5. 浣跨敤ORM妗嗘灦
-ORM锛堝璞″叧绯绘槧灏勶級妗嗘灦鏄竴绉嶅皢瀵硅薄妯″瀷鍜屽叧绯绘暟鎹簱涔嬮棿杩涜鏄犲皠鐨勬妧鏈�傚畠鍏佽寮�鍙戜汉鍛樹娇鐢ㄩ潰鍚戝璞＄殑鏂瑰紡鎿嶄綔鏁版嵁搴擄紝鑰屼笉闇�瑕佺紪鍐欑箒鐞愮殑SQL璇彞銆侽RM妗嗘灦灏嗘暟鎹簱琛ㄦ槧灏勪负瀵硅薄锛屽皢琛ㄧ殑琛屾槧灏勪负瀵硅薄鐨勫睘鎬э紝灏嗚〃涔嬮棿鐨勫叧绯绘槧灏勪负瀵硅薄涔嬮棿鐨勫叧鑱斻��
-ORM妗嗘灦鐨勪紭鐐瑰寘鎷彁楂樺紑鍙戞晥鐜囥�佸噺灏戜唬鐮侀噺銆佺畝鍖栨暟鎹簱鎿嶄綔銆佹彁渚涘璞＄骇鍒殑鏌ヨ鍜屾寔涔呭寲绛夈��
-6. 浣跨敤鍑嗗璇彞
-鍑嗗璇彞锛圥repared Statement锛夋槸涓�绉嶉缂栬瘧鐨凷QL璇彞锛屽畠鍏佽寮�鍙戜汉鍛樺皢鍙傛暟鍖栨煡璇㈠彂閫佸埌鏁版嵁搴擄紝骞跺湪鎵ц鏃舵彁渚涘弬鏁板�笺�傚噯澶囪鍙ュ彲浠ユ彁楂樻暟鎹簱鎿嶄綔鐨勬�ц兘鍜屽畨鍏ㄦ�э紝鍚屾椂杩樿兘闃叉SQL娉ㄥ叆鏀诲嚮銆�
-7.浣跨敤瀹夊叏鐨勬暟鎹簱杩炴帴
-浣跨敤瀹夊叏鐨勬暟鎹簱杩炴帴鏄潪甯搁噸瑕佺殑锛屽彲浠ヤ繚鎶ゆ暟鎹簱鍏嶅彈鎭舵剰鏀诲嚮鍜屾暟鎹硠闇层��
-浣跨敤SSL/TLS鍔犲瘑锛氶�氳繃浣跨敤SSL/TLS鍔犲瘑锛屽彲浠ョ‘淇濇暟鎹簱杩炴帴鍦ㄤ紶杈撹繃绋嬩腑鐨勬暟鎹畨鍏ㄣ��
-8.閬垮厤鍔ㄦ�佹嫾鎺QL璇彞
-閬垮厤鍔ㄦ�佹嫾鎺QL璇彞鏄负浜嗛槻姝QL娉ㄥ叆鏀诲嚮鍜屾彁楂樹唬鐮佺殑鍙鎬у拰鍙淮鎶ゆ�с��
-9.浣跨敤闃茬伀澧欏拰鍏ヤ镜妫�娴嬬郴缁�
-浣跨敤闃茬伀澧欏拰鍏ヤ镜妫�娴嬬郴缁熸槸涓轰簡淇濇姢璁＄畻鏈虹綉缁滃厤鍙楁湭缁忔巿鏉冪殑璁块棶鍜屾伓鎰忔敾鍑汇��
-10.瀹氭湡鏇存柊鍜岀淮鎶ゆ暟鎹簱杞欢
-瀹氭湡鏇存柊鍜岀淮鎶ゆ暟鎹簱杞欢鏄潪甯搁噸瑕佺殑锛屼互纭繚鏁版嵁搴撶殑瀹夊叏鎬с�佹�ц兘鍜屽姛鑳界殑绋冲畾鎬с�備互涓嬫槸涓�浜涜鏄庡拰瑙ｉ噴锛屼互鍙婁娇鐢↗ava浠ｇ爜绀轰緥鏉ュ疄鐜版暟鎹簱杞欢鐨勫畾鏈熸洿鏂板拰缁存姢锛�
+#include <iostream>
+#include <mysql_driver.h>
+#include <mysql_connection.h>
+#include <cppconn/statement.h>
+#include <cppconn/prepared_statement.h>
+#include <cppconn/resultset.h>
+#include <string>
+#include <regex>
-瀹氭湡鏇存柊锛�
-瀹氭湡鏇存柊鏁版嵁搴撹蒋浠舵槸涓轰簡鑾峰彇鏈�鏂扮殑瀹夊叏琛ヤ竵銆佸姛鑳芥敼杩涘拰鎬ц兘浼樺寲銆傛暟鎹簱渚涘簲鍟嗛�氬父浼氬彂甯冩洿鏂扮増鏈紝浠ヤ慨澶嶅凡鐭ョ殑婕忔礊鍜岄棶棰樸�傛洿鏂版暟鎹簱杞欢鍙互鎻愰珮鏁版嵁搴撶殑瀹夊叏鎬э紝骞剁‘淇濇暟鎹簱涓庢渶鏂扮殑鎶�鏈拰鏍囧噯淇濇寔涓�鑷淬��
-缁存姢浠诲姟锛�
-鏁版嵁搴撹蒋浠剁殑缁存姢浠诲姟鍖呮嫭澶囦唤鍜屾仮澶嶃�佺储寮曚紭鍖栥�佺粺璁′俊鎭洿鏂般�佺┖闂寸鐞嗐�佹棩蹇楃鐞嗙瓑銆傝繖浜涗换鍔℃湁鍔╀簬鎻愰珮鏁版嵁搴撶殑鎬ц兘銆佸彲鐢ㄦ�у拰鍙潬鎬с��
+class DatabaseUtils {
+public:
+ // 杩炴帴鏁版嵁搴�
+ static sql::Connection* connect() {
+ try {
+ sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
+ sql::Connection* con = driver->connect("tcp://127.0.0.1:3306", "mayi", "123456");
+ con->setSchema("your_database");
+ return con;
+ } catch (sql::SQLException& e) {
+ std::cerr << "鏁版嵁搴撹繛鎺ラ敊璇�: " << e.what() << std::endl;
+ return nullptr;
+ }
+ }
+
+ // 妫�鏌QL璇彞鏄惁瀛樺湪娼滃湪娉ㄥ叆椋庨櫓锛堢畝鍗曟鍒欐牎楠岋級
+ static bool isSafeSQL(const std::string& sql) {
+ // 绠�鍗曠殑姝ｅ垯琛ㄨ揪寮忥紝闃叉甯歌鐨勬敞鍏ュ叧閿瘝
+ std::regex injectionRegex("(drop|delete|update|insert|select\\s+\\*\\s+from)", std::regex_constants::icase);
+ return!std::regex_search(sql, injectionRegex);
+ }
+
+ // 浣跨敤鍙傛暟鍖栨煡璇㈡墽琛孲QL璇彞
+ static sql::ResultSet* executeSafeQuery(sql::Connection* con, const std::string& sql, const std::vector<std::string>& params) {
+ try {
+ sql::PreparedStatement* pstmt = con->prepareStatement(sql);
+ for (size_t i = 0; i < params.size(); ++i) {
+ pstmt->setString(i + 1, params[i]);
+ }
+ return pstmt->executeQuery();
+ } catch (sql::SQLException& e) {
+ std::cerr << "鏌ヨ鎵ц閿欒: " << e.what() << std::endl;
+ return nullptr;
+ }
+ }
+};
+
+int main() {
+ sql::Connection* con = DatabaseUtils::connect();
+ if (con) {
+ std::string sql = "SELECT * FROM your_table WHERE column_name =?";
+ std::vector<std::string> params = {"test_value"};
+ if (DatabaseUtils::isSafeSQL(sql)) {
+ sql::ResultSet* res = DatabaseUtils::executeSafeQuery(con, sql, params);
+ if (res) {
+ while (res->next()) {
+ // 澶勭悊缁撴灉
+ std::cout << res->getString(1) << std::endl;
+ }
+ delete res;
+ }
+ } else {
+ std::cerr << "娼滃湪鐨凷QL娉ㄥ叆椋庨櫓" << std::endl;
+ }
+ delete con;
+ }
+ return 0;
+}
\ No newline at end of file
--
Gitblit v1.8.0