Skip to content

Commit 58ad947

Browse files
BenauBenau
committed
Add IPV6 support to stats database
1 parent f7da3c9 commit 58ad947

File tree

2 files changed

+81
-30
lines changed

2 files changed

+81
-30
lines changed

‎NETWORKING.md‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -224,9 +224,9 @@ You have the best gaming experience when choosing server having all players less
224224

225225
Currently STK uses sqlite (if building with sqlite3 on) for server management with the following functions at the moment:
226226
1. Server statistics
227-
2. IP / online ID ban list
227+
2. IPV4 / online ID ban list
228228
3. Player reports
229-
4. IP geolocation
229+
4. IPV4 geolocation
230230

231231
You need to create a database in sqlite first, run `sqlite3 stkservers.db` in the folder where (all) your server_config.xml(s) located.
232232

@@ -236,6 +236,7 @@ CREATE TABLE IF NOT EXISTS (table name above)
236236
(
237237
host_id INTEGER UNSIGNED NOT NULL PRIMARY KEY, -- Unique host id in STKHost of each connection session for a STKPeer
238238
ip INTEGER UNSIGNED NOT NULL, -- IP decimal of host
239+
ipv6 TEXT NOT NULL DEFAULT '', -- IPV6 (if exists) in string of host (only created if ipv6 server)
239240
port INTEGER UNSIGNED NOT NULL, -- Port of host
240241
online_id INTEGER UNSIGNED NOT NULL, -- Online if of the host (0 for offline account)
241242
username TEXT NOT NULL, -- First player name in the host (if the host has splitscreen player)
@@ -272,7 +273,7 @@ CREATE TABLE IF NOT EXISTS (table name above)
272273

273274
If you want to see flags and readable names of countries in the above views, you need to initialize `v(server database version)_countries` table, check [this script](tools/generate-countries-table.py).
274275

275-
For IP and online ID ban list, player reports or IP mapping, you need to create one yourself:
276+
For IPV4 and online ID ban list, player reports or IP mapping, you need to create one yourself:
276277
```sql
277278
CREATE TABLE ip_ban
278279
(
@@ -301,11 +302,13 @@ CREATE TABLE player_reports
301302
(
302303
server_uid TEXT NOT NULL, -- Report from which server unique id (config filename)
303304
reporter_ip INTEGER UNSIGNED NOT NULL, -- IP decimal of player who reports
305+
reporter_ipv6 TEXT NOT NULL DEFAULT '', -- IPV6 (if exists) in string of player who reports (only needed for ipv6 server)
304306
reporter_online_id INTEGER UNSIGNED NOT NULL, -- Online id of player who reports, 0 for offline player
305307
reporter_username TEXT NOT NULL, -- Player name who reports
306308
reported_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Time of reporting
307309
info TEXT NOT NULL, -- Report info by reporter
308310
reporting_ip INTEGER UNSIGNED NOT NULL, -- IP decimal of player being reported
311+
reporting_ipv6 TEXT NOT NULL DEFAULT '', -- IPV6 (if exists) in string of player who reports (only needed for ipv6 server)
309312
reporting_online_id INTEGER UNSIGNED NOT NULL, -- Online id of player being reported, 0 for offline player
310313
reporting_username TEXT NOT NULL -- Player name being reported
311314
);

‎src/network/protocols/server_lobby.cpp‎

Lines changed: 75 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -222,11 +222,13 @@ void ServerLobby::initServerStatsTable()
222222
StringUtils::toString(ServerConfig::m_server_db_version) + "_" +
223223
ServerConfig::m_server_uid + "_stats";
224224

225-
std::string query = StringUtils::insertValues(
226-
"CREATE TABLE IF NOT EXISTS %s (\n"
225+
std::ostringstream oss;
226+
oss << "CREATE TABLE IF NOT EXISTS " << table_name << " (\n"
227227
" host_id INTEGER UNSIGNED NOT NULL PRIMARY KEY, -- Unique host id in STKHost of each connection session for a STKPeer\n"
228-
" ip INTEGER UNSIGNED NOT NULL, -- IP decimal of host\n"
229-
" port INTEGER UNSIGNED NOT NULL, -- Port of host\n"
228+
" ip INTEGER UNSIGNED NOT NULL, -- IP decimal of host\n";
229+
if (ServerConfig::m_ipv6_server)
230+
oss << " ipv6 TEXT NOT NULL DEFAULT '', -- IPV6 (if exists) in string of host\n";
231+
oss << " port INTEGER UNSIGNED NOT NULL, -- Port of host\n"
230232
" online_id INTEGER UNSIGNED NOT NULL, -- Online if of the host (0 for offline account)\n"
231233
" username TEXT NOT NULL, -- First player name in the host (if the host has splitscreen player)\n"
232234
" player_num INTEGER UNSIGNED NOT NULL, -- Number of player(s) from the host, more than 1 if it has splitscreen player\n"
@@ -235,7 +237,8 @@ void ServerLobby::initServerStatsTable()
235237
" connected_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Time when connected\n"
236238
" disconnected_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Time when disconnected (saved when disconnected)\n"
237239
" ping INTEGER UNSIGNED NOT NULL DEFAULT 0 -- Ping of the host\n"
238-
") WITHOUT ROWID;", table_name.c_str());
240+
") WITHOUT ROWID;";
241+
std::string query = oss.str();
239242
sqlite3_stmt* stmt = NULL;
240243
int ret = sqlite3_prepare_v2(m_db, query.c_str(), -1, &stmt, 0);
241244
if (ret == SQLITE_OK)
@@ -278,11 +281,13 @@ void ServerLobby::initServerStatsTable()
278281
std::string full_stats_view_name = std::string("v") +
279282
StringUtils::toString(ServerConfig::m_server_db_version) + "_" +
280283
ServerConfig::m_server_uid + "_full_stats";
281-
std::ostringstream oss;
284+
oss.str("");
282285
oss << "CREATE VIEW IF NOT EXISTS " << full_stats_view_name << " AS\n"
283286
<< " SELECT host_id, ip,\n"
284-
<< " ((ip >> 24) & 255) ||'.'|| ((ip >> 16) & 255) ||'.'|| ((ip >> 8) & 255) ||'.'|| ((ip ) & 255) AS ip_readable,\n"
285-
<< " port, online_id, username, player_num,\n"
287+
<< " ((ip >> 24) & 255) ||'.'|| ((ip >> 16) & 255) ||'.'|| ((ip >> 8) & 255) ||'.'|| ((ip ) & 255) AS ip_readable,\n";
288+
if (ServerConfig::m_ipv6_server)
289+
oss << " ipv6,";
290+
oss << " port, online_id, username, player_num,\n"
286291
<< " " << m_server_stats_table << ".country_code AS country_code, country_flag, country_name, version,\n"
287292
<< " ROUND((STRFTIME(\"%s\", disconnected_time) - STRFTIME(\"%s\", connected_time)) / 60.0, 2) AS time_played,\n"
288293
<< " connected_time, disconnected_time, ping FROM " << m_server_stats_table << "\n"
@@ -302,8 +307,10 @@ void ServerLobby::initServerStatsTable()
302307
oss.clear();
303308
oss << "CREATE VIEW IF NOT EXISTS " << current_players_view_name << " AS\n"
304309
<< " SELECT host_id, ip,\n"
305-
<< " ((ip >> 24) & 255) ||'.'|| ((ip >> 16) & 255) ||'.'|| ((ip >> 8) & 255) ||'.'|| ((ip ) & 255) AS ip_readable,\n"
306-
<< " port, online_id, username, player_num,\n"
310+
<< " ((ip >> 24) & 255) ||'.'|| ((ip >> 16) & 255) ||'.'|| ((ip >> 8) & 255) ||'.'|| ((ip ) & 255) AS ip_readable,\n";
311+
if (ServerConfig::m_ipv6_server)
312+
oss << " ipv6,";
313+
oss << " port, online_id, username, player_num,\n"
307314
<< " " << m_server_stats_table << ".country_code AS country_code, country_flag, country_name, version,\n"
308315
<< " ROUND((STRFTIME(\"%s\", 'now') - STRFTIME(\"%s\", connected_time)) / 60.0, 2) AS time_played,\n"
309316
<< " connected_time, ping FROM " << m_server_stats_table << "\n"
@@ -338,7 +345,10 @@ void ServerLobby::initServerStatsTable()
338345
else
339346
{
340347
oss << "CREATE VIEW IF NOT EXISTS " << player_stats_view_name << " AS\n"
341-
<< " SELECT a.online_id, a.username, a.ip, a.ip_readable, a.port, a.player_num,\n"
348+
<< " SELECT a.online_id, a.username, a.ip, a.ip_readable,\n";
349+
if (ServerConfig::m_ipv6_server)
350+
oss << " a.ipv6,";
351+
oss << " a.port, a.player_num,\n"
342352
<< " a.country_code, a.country_flag, a.country_name, a.version, a.ping,\n"
343353
<< " b.num_connections, b.first_connected_time, b.first_disconnected_time,\n"
344354
<< " a.connected_time AS last_connected_time, a.disconnected_time AS last_disconnected_time,\n"
@@ -923,14 +933,32 @@ void ServerLobby::writePlayerReport(Event* event)
923933
return;
924934
auto reporting_npp = reporting_peer->getPlayerProfiles()[0];
925935

926-
std::string query = StringUtils::insertValues(
927-
"INSERT INTO %s "
928-
"(server_uid, reporter_ip, reporter_online_id, reporter_username, "
929-
"info, reporting_ip, reporting_online_id, reporting_username) "
930-
"VALUES (?, %u, %u, ?, ?, %u, %u, ?);",
931-
ServerConfig::m_player_reports_table.c_str(),
932-
reporter->getAddress().getIP(), reporter_npp->getOnlineId(),
933-
reporting_peer->getAddress().getIP(), reporting_npp->getOnlineId());
936+
std::string query;
937+
if (ServerConfig::m_ipv6_server)
938+
{
939+
// We don't save the internally mapped ipv4 (0.x.x.x)
940+
query = StringUtils::insertValues(
941+
"INSERT INTO %s "
942+
"(server_uid, reporter_ip, reporter_ipv6, reporter_online_id, reporter_username, "
943+
"info, reporting_ip, reporting_ipv6, reporting_online_id, reporting_username) "
944+
"VALUES (?, %u, \"%s\", %u, ?, ?, %u, \"%s\", %u, ?);",
945+
ServerConfig::m_player_reports_table.c_str(),
946+
reporter->getIPV6Address().empty() ? reporter->getAddress().getIP() : 0,
947+
reporter->getIPV6Address(), reporter_npp->getOnlineId(),
948+
reporting_peer->getIPV6Address().empty() ? reporting_peer->getAddress().getIP() : 0,
949+
reporting_peer->getIPV6Address(), reporting_npp->getOnlineId());
950+
}
951+
else
952+
{
953+
query = StringUtils::insertValues(
954+
"INSERT INTO %s "
955+
"(server_uid, reporter_ip, reporter_online_id, reporter_username, "
956+
"info, reporting_ip, reporting_online_id, reporting_username) "
957+
"VALUES (?, %u, %u, ?, ?, %u, %u, ?);",
958+
ServerConfig::m_player_reports_table.c_str(),
959+
reporter->getAddress().getIP(), reporter_npp->getOnlineId(),
960+
reporting_peer->getAddress().getIP(), reporting_npp->getOnlineId());
961+
}
934962
bool written = easySQLQuery(query,
935963
[reporter_npp, reporting_npp, info](sqlite3_stmt* stmt)
936964
{
@@ -3026,14 +3054,30 @@ void ServerLobby::handleUnencryptedConnection(std::shared_ptr<STKPeer> peer,
30263054
#ifdef ENABLE_SQLITE3
30273055
if (m_server_stats_table.empty())
30283056
return;
3029-
std::string query = StringUtils::insertValues(
3030-
"INSERT INTO %s "
3031-
"(host_id, ip, port, online_id, username, player_num, "
3032-
"country_code, version, ping) "
3033-
"VALUES (%u, %u, %u, %u, ?, %u, ?, ?, %u);",
3034-
m_server_stats_table.c_str(), peer->getHostId(),
3035-
peer->getAddress().getIP(), peer->getAddress().getPort(), online_id,
3036-
player_count, peer->getAveragePing());
3057+
std::string query;
3058+
if (ServerConfig::m_ipv6_server && !peer->getIPV6Address().empty())
3059+
{
3060+
// We don't save the internally mapped ipv4 (0.x.x.x)
3061+
query = StringUtils::insertValues(
3062+
"INSERT INTO %s "
3063+
"(host_id, ip, ipv6 ,port, online_id, username, player_num, "
3064+
"country_code, version, ping) "
3065+
"VALUES (%u, 0, \"%s\" ,%u, %u, ?, %u, ?, ?, %u);",
3066+
m_server_stats_table.c_str(), peer->getHostId(),
3067+
peer->getIPV6Address(), peer->getAddress().getPort(), online_id,
3068+
player_count, peer->getAveragePing());
3069+
}
3070+
else
3071+
{
3072+
query = StringUtils::insertValues(
3073+
"INSERT INTO %s "
3074+
"(host_id, ip, port, online_id, username, player_num, "
3075+
"country_code, version, ping) "
3076+
"VALUES (%u, %u, %u, %u, ?, %u, ?, ?, %u);",
3077+
m_server_stats_table.c_str(), peer->getHostId(),
3078+
peer->getAddress().getIP(), peer->getAddress().getPort(),
3079+
online_id, player_count, peer->getAveragePing());
3080+
}
30373081
easySQLQuery(query, [peer, country_code](sqlite3_stmt* stmt)
30383082
{
30393083
if (sqlite3_bind_text(stmt, 1, StringUtils::wideToUtf8(
@@ -3841,6 +3885,10 @@ void ServerLobby::testBannedForIP(STKPeer* peer) const
38413885
if (!m_db || !m_ip_ban_table_exists)
38423886
return;
38433887

3888+
// We only test for ipv4 atm
3889+
if (!peer->getIPV6Address().empty())
3890+
return;
3891+
38443892
int row_id = -1;
38453893
unsigned ip_start = 0;
38463894
unsigned ip_end = 0;

0 commit comments

Comments
 (0)