企业官网建设流程全解析

Unity数据库安全实践：构建防注入的MySQL管理器

在游戏开发中，数据库操作是常见需求，但直接将连接字符串和SQL命令硬编码在脚本中会带来严重的安全隐患。本文将带你从零开始构建一个安全、可维护的Unity数据库访问层，解决SQL注入风险和配置管理问题。

1. 为什么需要重构原始方案？

原始代码直接将数据库连接信息（如用户名、密码）和SQL语句以字符串拼接方式写在脚本中，这会导致两个核心问题：

1. SQL注入风险：攻击者可以通过精心构造的输入参数篡改SQL语句逻辑
2. 维护困难：数据库配置变更需要重新编译项目

// 不安全示例：字符串拼接SQL string sql = "insert into player(ID,LV,Name) values('" + id + "','" + level + "','" + name + "')";

更专业的做法是使用参数化查询和外部化配置：

// 安全示例：参数化查询 string sql = "INSERT INTO player(ID, LV, Name) VALUES(@id, @level, @name)"; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@level", level); cmd.Parameters.AddWithValue("@name", name);

2. 构建安全的数据库管理器

2.1 创建DatabaseManager单例

单例模式确保全局只有一个数据库连接实例：

using MySql.Data.MySqlClient; using UnityEngine; public class DatabaseManager : MonoBehaviour { private static DatabaseManager _instance; private MySqlConnection _connection; public static DatabaseManager Instance { get { if (_instance == null) { GameObject go = new GameObject("DatabaseManager"); _instance = go.AddComponent<DatabaseManager>(); DontDestroyOnLoad(go); } return _instance; } } private void Awake() { if (_instance != null && _instance != this) { Destroy(gameObject); return; } _instance = this; InitializeConnection(); } }

2.2 外部化配置管理

将敏感信息移出代码，使用ScriptableObject存储配置：

[CreateAssetMenu(fileName = "DatabaseConfig", menuName = "Configs/Database Config")] public class DatabaseConfig : ScriptableObject { public string host = "localhost"; public string port = "3306"; public string database = "gametest"; public string username = "root"; public string password = "123456"; }

在DatabaseManager中加载配置：

private void InitializeConnection() { DatabaseConfig config = Resources.Load<DatabaseConfig>("DatabaseConfig"); string connectionString = $"server={config.host};" + $"port={config.port};" + $"database={config.database};" + $"user={config.username};" + $"password={config.password};"; _connection = new MySqlConnection(connectionString); _connection.Open(); }

3. 实现安全的CRUD操作

3.1 参数化查询实践

安全查询示例：

public List<PlayerData> GetPlayersByLevel(int minLevel) { List<PlayerData> players = new List<PlayerData>(); string query = "SELECT * FROM player WHERE LV >= @minLevel"; MySqlCommand cmd = new MySqlCommand(query, _connection); cmd.Parameters.AddWithValue("@minLevel", minLevel); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { PlayerData player = new PlayerData { ID = reader.GetInt32("ID"), Name = reader.GetString("Name"), Level = reader.GetInt32("LV") }; players.Add(player); } } return players; }

3.2 安全的插入与更新

public int AddPlayer(PlayerData player) { string query = "INSERT INTO player(ID, LV, Name) VALUES(@id, @level, @name)"; MySqlCommand cmd = new MySqlCommand(query, _connection); cmd.Parameters.AddWithValue("@id", player.ID); cmd.Parameters.AddWithValue("@level", player.Level); cmd.Parameters.AddWithValue("@name", player.Name); return cmd.ExecuteNonQuery(); }

3.3 事务处理

对于需要原子性操作的情况：

public bool TransferItem(int fromPlayerId, int toPlayerId, int itemId) { MySqlTransaction transaction = null; try { transaction = _connection.BeginTransaction(); // 从源玩家移除物品 string removeQuery = "UPDATE inventory SET player_id = @toPlayerId " + "WHERE player_id = @fromPlayerId AND item_id = @itemId"; MySqlCommand removeCmd = new MySqlCommand(removeQuery, _connection, transaction); removeCmd.Parameters.AddWithValue("@fromPlayerId", fromPlayerId); removeCmd.Parameters.AddWithValue("@toPlayerId", toPlayerId); removeCmd.Parameters.AddWithValue("@itemId", itemId); int affectedRows = removeCmd.ExecuteNonQuery(); if (affectedRows == 0) { transaction.Rollback(); return false; } transaction.Commit(); return true; } catch (Exception e) { transaction?.Rollback(); Debug.LogError($"Transaction failed: {e.Message}"); return false; } }

4. 高级安全实践

4.1 连接池管理

优化数据库连接性能：

// 在连接字符串中添加池配置 string connectionString = $"server={config.host};" + $"port={config.port};" + $"database={config.database};" + $"user={config.username};" + $"password={config.password};" + $"Pooling=true;" + $"Min Pool Size=5;" + $"Max Pool Size=20;" + $"Connection Lifetime=300;";

4.2 防御性编程

添加连接状态检查：

public bool IsConnected { get { if (_connection == null) return false; try { using (var cmd = new MySqlCommand("SELECT 1", _connection)) { return cmd.ExecuteScalar() != null; } } catch { return false; } } }

4.3 日志与错误处理

实现详细的错误日志记录：

public T ExecuteQuery<T>(string query, Func<MySqlCommand, T> action) { try { using (MySqlCommand cmd = new MySqlCommand(query, _connection)) { return action(cmd); } } catch (MySqlException e) { Debug.LogError($"MySQL Error [{e.Number}]: {e.Message}"); throw; } catch (Exception e) { Debug.LogError($"Database Error: {e.Message}"); throw; } }

5. 性能优化技巧

5.1 批量插入优化

对于大量数据插入：

public void BulkInsertPlayers(List<PlayerData> players) { using (MySqlTransaction transaction = _connection.BeginTransaction()) { try { string query = "INSERT INTO player(ID, LV, Name) VALUES(@id, @level, @name)"; foreach (var player in players) { MySqlCommand cmd = new MySqlCommand(query, _connection, transaction); cmd.Parameters.AddWithValue("@id", player.ID); cmd.Parameters.AddWithValue("@level", player.Level); cmd.Parameters.AddWithValue("@name", player.Name); cmd.ExecuteNonQuery(); } transaction.Commit(); } catch { transaction.Rollback(); throw; } } }

5.2 使用预处理语句

对于频繁执行的查询：

private MySqlCommand _preparedGetPlayerCommand; public PlayerData GetPlayer(int playerId) { if (_preparedGetPlayerCommand == null) { string query = "SELECT * FROM player WHERE ID = @playerId"; _preparedGetPlayerCommand = new MySqlCommand(query, _connection); _preparedGetPlayerCommand.Parameters.Add("@playerId", MySqlDbType.Int32); _preparedGetPlayerCommand.Prepare(); } _preparedGetPlayerCommand.Parameters["@playerId"].Value = playerId; using (var reader = _preparedGetPlayerCommand.ExecuteReader()) { if (reader.Read()) { return new PlayerData { ID = reader.GetInt32("ID"), Name = reader.GetString("Name"), Level = reader.GetInt32("LV") }; } } return null; }

在实际项目中，我们还需要考虑数据库迁移、多环境配置（开发/测试/生产）以及更细粒度的权限控制。这套方案已经成功应用于多个商业游戏项目，显著降低了安全风险和维护成本。