一个简单的数据库插件,帮助其他模组在 MySQL 或 SQLite 数据库中实现数据持久化。它通过一个简单的文本文件(DBLib.properties)进行配置,并提供一个 API 来协助生成安全的查询语句,利用 Java 的方法防范 SQL 注入攻击。一旦选择了 MySQL 或 SQLite,你的查询语句基本上与具体选择无关(除非你使用了针对 MySQL 或 SQLite 的特定调用)。
功能特性:
- 对于模组开发者而言,允许系统管理员根据自己的配置在 MySQL 和 SQLite 之间切换,而无需修改代码逻辑
- 管理数据库配置
- 在所有涉及数据库调用的脚本中,使用同一个插件来处理所有数据库操作
- 帮助你编写更安全的 SQL 查询,避免 SQL 注入攻击
被以下模组使用:
安装与配置:
将此插件放入 Bukkit 服务器的 plugins 目录。首次运行时,它将创建一个默认的 DBLib.properties 文件,默认选择 SQLite。DBLib.properties 文件会创建在插件文件夹内,路径为:“$CRAFTBUKKIT-HOME/plugins/<your plugin name>/DBLib.properties”。
配置示例:(当前机器上的 MySQL,使用默认端口和密码 1234,表名为 'playtime tracker')
MySQL=true
host=localhost
database=playtimetracker
username=root
password=1234
port=3306
DBLib.properties 文件中的属性说明:
- MySQL:如果为 true,则连接 MySQL;如果为 false,则使用 SQLite。
- database:在 MySQL 服务器内部创建的数据库名称,或者是 SQLite 的文件名。
仅适用于 MySQL:
- host:要连接到的主机。"localhost" 指的是服务器所在的主机。
- username:用于 MySQL 登录的用户名。请注意,对于 MySQL,"root" 并不是你机器上的 "root",而是数据库中的 "root"。
- password:以该用户名在主机上登录时使用的密码。
- port:连接到服务器主机以找到 MySQL 服务器的端口。即 MySQL 服务器监听的端口。默认情况下,如果省略此属性,则使用默认的 MySQL 端口 3306。
权限:
无 —— 由于本插件旨在让其他插件访问数据,因此它不包含任何权限。
配置:
会创建一个单独的 config.yml 文件。其唯一的选项是 "debug: false"。如果设置为 true,则会输出调试消息。
API 调用与用法:
初始化:
在你的 onEnable 函数中获取此插件:
PluginManager pm = this.getServer().getPluginManager();
this.dbplugin = (MOCDBLib) pm.getPlugin("MOCDBLib");
使用此插件创建数据库连接。这将读取/创建配置文件(见下文):
this.dbconnector = this.dbplugin.getMineCraftDB(<your plugin name>, <a Logger object>);
注意:插件名称将作为配置文件放置的位置……这可能会带来问题。
确保数据库中已存在我们将要使用的表。在此示例中,表的名称为 "table1",如果不存在,则将其初始化为包含 'id' 和 'txt' 属性。
注意:这只是一个辅助函数。你可以直接调用 SQL 来实现相同的功能。
this.dbconnector.ensureTable("table1","id integer, txt varchar(50)");
执行 SQL 查询:
这是一个简单的 SQL 查询,返回一个 ResultSet 对象。
ResultSet rs = this.dbconnector.sqlSafeQuery("SELECT * FROM table1 WHERE id=2");
这是一个与上述完全相同的 SQL 查询,但使用了 Java 的 PreparedStatement 来避免 SQL 注入攻击。
注意:PreparedStatement 的“set”方法中,起始索引为 1,而非 0。
注意:上述查询无论如何都是安全的,因为它是静态字符串,不依赖用户输入或可能注入 SQL 攻击的传递参数。
public void performACall(int _id) {
java.sql.PreparedStatement ps = this.dbconnector.prepareStatement("SELECT * FROM table1 WHERE id=?");
ps.setInt(1,_id);
ResultSet rs = this.dbconnector.sqlQuery(ps);
...
}
验证表及其列是否存在
当您更改插件版本之间的数据库架构,并希望尝试不破坏用户的现有数据时,这非常有用。这是一个很好的做法。:)
String[][] mycols= { {"id","integer"}, { "player", "varchar(50) not null"}, {"world", "varchar(50) not null"} };
...
this.dbconnector.updateSafeQuery( dbconnector.createTableIfNotExistsString("MyTable", mycols, "id"));
MySQLLib mysqldb = (MySQLLib) dbconnector;
if ( mysqldb.checkForColumnInTable(mycols[0][0],"MyTable") == false )
mysqldb.createColumnInTable(mycols[0][0],mycols[0][1],"MyTable");
完整 API:
以下是您应该使用的函数调用。
// 管理连接
Connection getConnection();
Boolean checkConnection();
Boolean closeConnection();
// 执行假定安全的查询
ResultSet sqlSafeQuery(String query);
int insertSafeQuery(String query);
int updateSafeQuery(String query);
int deleteSafeQuery(String query);
// 如果你希望返回受影响表的键
ResultSet insertSafeQueryAndKeys(String query);
ResultSet updateSafeQueryAndKeys(String query);
ResultSet deleteSafeQueryAndKeys(String query);
// 调用更安全的预编译语句
PreparedStatement prepareStatement(String _query);
ResultSet sqlQuery(PreparedStatement query);
int insertQuery(PreparedStatement query);
int updateQuery(PreparedStatement query);
int deleteQuery(PreparedStatement query);
PreparedStatement prepareStatementAndKeys(String _query);
// 例如: PreparedStatement ps = dbconnector.prepareStatementAndKeys( ... );
// ...
// ResultSet rss = ps.getGeneratedKeys();
// 管理表
Boolean checkTable(String table);
Boolean wipeTable(String table);
Boolean ensureTable(String _tblname, String _tblcolumns);
// 工具类
String createTableString(String _tablename, String[][] _cols, String _keys );
String createTableIfNotExistsString(String _tablename, String[][] _cols, String _keys );
// 仅限 MySQL
布尔值 checkForTable(字符串 _table);
布尔值 checkForColumnInTable(字符串 _column, 字符串 _table);
布尔值 createColumnInTable(字符串 _colname, 字符串 _coldesc, 字符串 _table)
注意:关于插入/更新/删除查询,以及为何将它们分开。我遵循了 SQLLight 插件的惯例,并且说实话,我喜欢这样拆分。然而,所有操作的底层 SQL 调用都是相同的。因此,你可以将插入 SQL 发送到删除 SQL 而不会出现问题。
一些提示
这是一种简单的方法,通过让编译器(或开发环境)捕获 SQL 错误来避免拼写错误。将数据库列存储为数组和枚举。注意:请告诉我下面手写的代码是否有效。
静态 枚举 TCOLS { col1, col2 }; // 使用枚举有助于保持所有字符串使用的一致性,因为否则没有编译时检查
静态 字符串 TABLENAME = "MyTable";
静态 字符串 TABLEKEYS = TCOLS.col1+","+TCOLS.col2;
静态 字符串[][] TABLECOLS= {
{ TCOLS.col1.toString, "integer DEFAULT 0" },
{ TCOLS.col2.toString, "text DEFAULT ''" } };
...
字符串 tableinitstring = DBConnector.createTableIfNotExistsString(TABLENAME,TABLECOLS,TABLEKEYS);
DBConnector dbc = MOCDBLib.getConnector().sqlSafeQuery(tableinitstring);
...
// 确保表列存在,如果表已经存在(即更新到具有不同列的新插件版本)
TCOLS[] tc = TCOLS.values();
for(int i=0;i<tc.length;i++) {
if ( mysqldb.checkForColumnInTable(tc[i].toString(),TABLENAME) == false )
{
// 不存在,所以正在创建。
mysqldb.createColumnInTable(TABLECOLS[i][0],TABLECOLS[i][1],TABLENAME);
}
}
MOCDBLib 与 SQLLite:
感谢 SQLLight 项目提供了许多原始想法。如果你熟悉该代码,你将轻松学习这个。
相较于 SQLLite 项目的优势:
- 这是一个插件,因此许多插件都可以使用它
- 更新更容易(不需要打开插件并将 SQLLite 插入到 jar 文件中)
- 更安全的查询,因为它有助于避免 SQL 注入攻击
- MySQL 和 SQLite API 已合并,因此作为程序员,你无法察觉到你使用的是哪种数据库解决方案
- 完全不同的代码组织方式
- 额外的 API 调用以帮助你
- 配置更简单
A simple database plugin to help plugins create persistence in either MySQL and SQLite databases. It is configurable via a simple text file (DBLib.properties) and helps with the creation of safe queries by an API that helps you use Java's method of getting around SQL injection attacks. Once you choose MySQL or SQLite, your queries are basically agnostic to that choice (unless you use SQL specific calls to MySQL or SQLite).
Features:
- for plugin devs, allow sys admins to toggle between MySQL and SQLite to suit their own configuration, without you chaining your code
- manages DB configuration
- use same plugin for all DB handling in all scripts that use DB calls
- helps you write safer SQL queries that avoid SQL injection attacks
Used By:
- MOC Playtime Tracker
- CodeBlocks - pending publication
- Fizziks - soon to be released
- ReginBlock - soon to be released
Installation and Configuration:
Drop this into the plugins directory of your bukkit server. It will create a default DBLib.properties file the first time it runs, selecting SQLite by default. The DBLib.properties file is created in the plugin's folder so : "$CRAFTBUKKIT-HOME/plugins/<your plugin name>/DBLib.properties".
example config: (MySQL on the current machine with default port and password 1234 in table 'playtime tracker')
MySQL=true
host=localhost
database=playtimetracker
username=root
password=1234
port=3306
attributes in the DBLib.properties file:
- MySQL: If true, connects to MySQL. If false, uses SQLite.
- database: The actual database's name that is created inside the MySQL server or SQLite file.
MySQL Only:
- host: The machine it looks to connect to. "localhost" refers to the server's machine.
- username: The name of the login used to MySQL. Remember that for MySQL, "root" is not "root" on your machine, but "root" for the database.
- password: The password used to login as the username on the host.
- port: The port to connect to on the server machine to find the MySQL server. I.e. where the MySQL server is listening. By default, and if this property is omitted, it uses the default MySQL port of 3306.
Permissions:
N/A - This plugin has no permissions since it is all about plugins accessing data.
Configuration:
A single config.yml file is created. It's sole option is "debug: false". If set to true, it spews debug messages.
API Calls and Usage:
Initialization:
In your onEnable function, get this plugin:
PluginManager pm = this.getServer().getPluginManager();
this.dbplugin = (MOCDBLib) pm.getPlugin("MOCDBLib");
Create a database connection with this plugin. This will read in/create the configuration file (see below):
this.dbconnector = this.dbplugin.getMineCraftDB(<your plugin name>, <a Logger object>);
NOTE: The plugin name is where this will place the config file... this might be problematic.
Ensure that a table we will use exists in the database. Here, the table's name is "table1" and it is initialized (if it does not exist) as having an 'id' and 'txt' attributes.
NOTE: This is just a helper function. You can call SQL directly to do the same thing.
this.dbconnector.ensureTable("table1","id integer, txt varchar(50)");
Perform an SQL query:
This is a simple SQL query that returns a ResultSet object.
ResultSet rs = this.dbconnector.sqlSafeQuery("SELECT * FROM table1 WHERE id=2");
This is an SQL query that does the exact same query as above but uses Java's PreparedStatement to avoid SQL injection attacks.
NOTE: PreparedStatements, in their "set" methods, have a starting index of 1, not 0.
NOTE: The above query is safe anyway, since it is a static string and not relying on user input or a passed parameter which could inject an SQL attack.
public void performACall(int _id) {
java.sql.PreparedStatement ps = this.dbconnector.prepareStatement("SELECT * FROM table1 WHERE id=?");
ps.setInt(1,_id);
ResultSet rs = this.dbconnector.sqlQuery(ps);
...
}
Verify a Table and its Columns Exist
This is useful for when you change your database schemas between versions of a plugin and want to try to not blow up existing data of users. This is a good thing to do. :)
String[][] mycols= { {"id","integer"}, { "player", "varchar(50) not null"}, {"world", "varchar(50) not null"} };
...
this.dbconnector.updateSafeQuery( dbconnector.createTableIfNotExistsString("MyTable", mycols, "id"));
MySQLLib mysqldb = (MySQLLib) dbconnector;
if ( mysqldb.checkForColumnInTable(mycols[0][0],"MyTable") == false )
mysqldb.createColumnInTable(mycols[0][0],mycols[0][1],"MyTable");
Full API:
Here are the function calls you should be using.
// Manage connections
Connection getConnection();
Boolean checkConnection();
Boolean closeConnection();
// Perform assumed to be safe queries
ResultSet sqlSafeQuery(String query);
int insertSafeQuery(String query);
int updateSafeQuery(String query);
int deleteSafeQuery(String query);
// If you want the keys of the affected tables returned
ResultSet insertSafeQueryAndKeys(String query);
ResultSet updateSafeQueryAndKeys(String query);
ResultSet deleteSafeQueryAndKeys(String query);
// Call the safer prepared statements
PreparedStatement prepareStatement(String _query);
ResultSet sqlQuery(PreparedStatement query);
int insertQuery(PreparedStatement query);
int updateQuery(PreparedStatement query);
int deleteQuery(PreparedStatement query);
PreparedStatement prepareStatementAndKeys(String _query);
// ex. PreparedStatement ps = dbconnector.prepareStatementAndKeys( ... );
// ...
// ResultSet rss = ps.getGeneratedKeys();
// Manage Tables
Boolean checkTable(String table);
Boolean wipeTable(String table);
Boolean ensureTable(String _tblname, String _tblcolumns);
// Utility
String createTableString(String _tablename, String[][] _cols, String _keys );
String createTableIfNotExistsString(String _tablename, String[][] _cols, String _keys );
// MySQL Only
Boolean checkForTable(String _table);
Boolean checkForColumnInTable(String _column, String _table);
Boolean createColumnInTable(String _colname, String _coldesc, String _table)
NOTE: Regarding insert/update/delete queries, and why they are separate. I was following the SQLLight plugin's convention, and honestly, I like splitting it up like this. However, the underlying SQL call for all is the same. So, you could send insert SQL to a delete SQL w/o incident.
Some Pointers
This is a simple method to avoid typos by letting your compiler (or development environment) catch SQL errors. Store your database columns as an array and enums. NOTE: Please let me know if my hand typed code below works.
static enum TCOLS { col1, col2 }; // using enums helps keep all the string usage consistent as there is no compile time checks otherwise
static String TABLENAME = "MyTable";
static String TABLEKEYS = TCOLS.col1+","+TCOLS.col2;
static String[][] TABLECOLS= {
{ TCOLS.col1.toString, "integer DEFAULT 0" },
{ TCOLS.col2.toString, "text DEFAULT ''" } };
...
String tableinitstring = DBConnector.createTableIfNotExistsString(TABLENAME,TABLECOLS,TABLEKEYS);
DBConnector dbc = MOCDBLib.getConnector().sqlSafeQuery(tableinitstring);
...
// ensure the table columns exist, if the table already exists (i.e. updating to a new plugin version with different columns)
TCOLS[] tc = TCOLS.values();
for(int i=0;i<tc.length;i++) {
if ( mysqldb.checkForColumnInTable(tc[i].toString(),TABLENAME) == false )
{
// does not exists, so creating.
mysqldb.createColumnInTable(TABLECOLS[i][0],TABLECOLS[i][1],TABLENAME);
}
}
MOCDBLib Vs SQLLite:
Thanks to the SQLLight project many of the original ideas. If you are used to that code, you will easily learn this.
Advantages over SQLLite project:
- this is a plugin so many plugins can use it
- updating is easier (it does not require cracking open plugins and inserting SQLLite into that jar file)
- safer queries as it helps you avoid SQL Injection attacks
- MySQL and SQLite APIs are merged so as a programmer, you don't notice the difference between which db solution you are using
- totally different code organization
- few extra API calls to help you out
- configuration is simpler
评论 0
登录后参与评分
还没有评论,来抢沙发吧