public static async Task <uint> GetUserId(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(0);
}
const string banuser = "******";
var m = new MySqlCommand(banuser, conn.Connection);
m.Parameters.AddWithValue("name", username);
var result = await m.ExecuteReaderAsync();
uint i = 0;
if (result.Read())
{
i = uint.Parse(result["user_id"].ToString());
}
await conn.Close();
return(i);
}
public static async Task <string> GetDirectGift(Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(null);
}
const string command = "SELECT gifts FROM directgift WHERE user_id = @id;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@id", client.UserId);
var result = await m.ExecuteReaderAsync();
string s = null;
while (await result.ReadAsync())
{
s = (string)result["gifts"];
}
result.Close();
if (s != null)
{
const string delcomm = "DELETE FROM directgift WHERE user_id = @id";
var n = new MySqlCommand(delcomm, conn.Connection);
n.Parameters.AddWithValue("@id", client.UserId);
await n.ExecuteNonQueryAsync();
}
await conn.Close();
return(s);
}
public static async Task <List <uint> > GetFriends(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(new List <uint>());
}
const string comm = "SELECT friends FROM friend_list WHERE user_id = @id";
var m = new MySqlCommand(comm, conn.Connection);
m.Parameters.AddWithValue("@id", userId);
var l = new List <uint>();
var result = await m.ExecuteReaderAsync();
if (!result.HasRows)
{
l = new List <uint>();
}
if (await result.ReadAsync())
{
var s = result["friends"].ToString();
l = s.Split(',').Select(uint.Parse).ToList();
}
await conn.Close();
return(l);
}
public static async Task GetBase(string username, Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "SELECT friendsafari.base, friendsafari.message, users.banned " +
"FROM users " +
"INNER JOIN friendsafari " +
"ON friendsafari.user_id = users.user_id " +
"WHERE users.username = @param_val_1;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
var result = await m.ExecuteReaderAsync();
if (!result.HasRows)
{
//We just send the login message so we don't need a special client handler just for this. User doesn't notice anyway
await client.SendMessage("<LOG result=0>");
await conn.Close();
return;
}
while (await result.ReadAsync())
{
if ((bool)result["banned"])
{
await client.SendMessage($"<TRA user={username} result=1>");
await conn.Close();
return;
}
if (result["base"] is DBNull)
{
await client.SendMessage($"<VBASE user={username} result=1 base=nil>");
await conn.Close();
return;
}
var Base = result["base"];
var messageDb = result["message"];
var message = messageDb is DBNull ? "nil" : messageDb.ToString();
await client.SendMessage($"<VBASE user={username} result=2 base={Base} message={Utilities.Encoding.Base64Encode(message)}>");
break;
}
await conn.Close();
}
public static void CreateTables()
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
conn.Close();
return;
}
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `CounterMetrics` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,`value` int(11) DEFAULT NULL, PRIMARY KEY(`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `friend_list` ( `user_id` unsigned int(11) NOT NULL, `friends` varchar(255) DEFAULT NULL, PRIMARY KEY (`user_id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `GTS` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Offer` json DEFAULT NULL, `Request` json DEFAULT NULL, `Accepted` tinyint(1) NOT NULL DEFAULT '0'," +
" `Result` json DEFAULT NULL, `user_id` int(11) unsigned DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `OfferLevel` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `users` ( `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(32) COLLATE utf8_unicode_ci NOT NULL," +
" `password` varchar(11) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `usergroup` int(10) NOT NULL DEFAULT '0'," +
" `banned` tinyint(1) NOT NULL DEFAULT '0', `uniquecode` char(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `base` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL," +
" `sprite` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `admin` tinyint(1) DEFAULT '0', PRIMARY KEY (`user_id`), UNIQUE KEY `username` (`username`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `ips` ( `user_id` int(10) unsigned NOT NULL, `ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `ipban` tinyint(1) DEFAULT NULL," +
" PRIMARY KEY (`user_id`,`ip`), CONSTRAINT `ips_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE)",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `user_data` ( `user_id` int(10) unsigned NOT NULL, `lastlogin` datetime NOT NULL, `guild_id` int(10) unsigned DEFAULT NULL," +
" PRIMARY KEY (`user_id`), CONSTRAINT `user_data_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE)",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `tradelog` ( `user1` char(255) DEFAULT NULL, `user2` char(255) DEFAULT NULL, `pokemon1` json DEFAULT NULL, `pokemon2` json DEFAULT NULL," +
" `time` datetime DEFAULT NULL, `i` smallint(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `newtradelog` ( `user1` char(255) DEFAULT NULL, `user2` char(255) DEFAULT NULL, `pokemon1` json DEFAULT NULL, `pokemon2` json DEFAULT NULL," +
" `time` datetime DEFAULT NULL, `i` smallint(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `wondertradelog`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL, `pokemon` varchar(255) DEFAULT NULL," +
" `time` datetime DEFAULT NULL, PRIMARY KEY (`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `directgift` (`gifts` json DEFAULT NULL,`user_id` int(11) NOT NULL, PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
conn.Connection).ExecuteNonQuery();
conn.Close();
}
public static async Task <bool> TradeIsAccepted(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(false);
}
const string s = "SELECT Accepted FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
var r = await c.ExecuteReaderAsync();
var ret = false;
while (await r.ReadAsync())
{
var accepted = (bool)r["Accepted"];
if (accepted)
{
ret = true;
}
}
await conn.Close();
return(ret);
}
public static async Task <string> CollectTrade(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(null);
}
const string s = "SELECT Result FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
var ret = "";
var r = await c.ExecuteReaderAsync();
while (await r.ReadAsync())
{
ret = (string)r["Result"];
}
r.Close();
const string delete = "DELETE FROM GTS WHERE id = @id";
var deletecom = new MySqlCommand(delete, conn.Connection);
deletecom.Parameters.AddWithValue("@id", tradeid);
await deletecom.ExecuteNonQueryAsync();
await conn.Close();
return(ret);
}
public static async Task <bool> UserOwnsTrade(uint tradeId, uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(false);
}
const string s = "SELECT user_id FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeId);
var r = await c.ExecuteReaderAsync();
var ret = false;
while (await r.ReadAsync())
{
var user = (uint)r["user_id"];
if (user == userId)
{
ret = true;
}
}
await conn.Close();
return(ret);
}
public static async Task <List <GTS.RequestGtsHolder> > GetUserTrades(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(null);
}
const string s = "SELECT id, Offer, Request, Accepted FROM GTS WHERE user_id = @id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", userId);
var ls = new List <GTS.RequestGtsHolder>();
var r = await c.ExecuteReaderAsync();
while (await r.ReadAsync())
{
var req = new GTS.RequestGtsHolder
{
Index = (int)r["id"],
Offer = JsonConvert.DeserializeObject <GTS.GamePokemon>((string)r["Offer"]),
Request = JsonConvert.DeserializeObject <GTS.RequestData>((string)r["Request"]),
Accepted = (bool)r["Accepted"]
};
ls.Add(req);
}
await conn.Close();
return(ls);
}
public static void Connect()
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string query = "SHOW TABLES;";
var cmd = new MySqlCommand(query, conn.Connection);
var reader = cmd.ExecuteReader();
var rows = new List <string>();
while (reader.Read())
{
var row = "";
for (var i = 0; i < reader.FieldCount; i++)
{
row += reader.GetValue(i).ToString();
}
rows.Add(row);
}
reader.Close();
foreach (var row in rows)
{
Console.WriteLine("Optimizing table: " + row);
var optimizequery = $"OPTIMIZE TABLE {row};";
var optimizeCommand = new MySqlCommand(optimizequery, conn.Connection);
optimizeCommand.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine("Optimizing complete!");
}
else
{
throw new Exception("Database not initialized, cannot connect. State: " + conn.Connection.State);
}
}
public static async Task MetricCountOne(int key)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command =
"INSERT INTO CounterMetrics (id, value) VALUES (@keyname, 1) ON DUPLICATE KEY UPDATE value = value + 1";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@keyname", key);
await mcom.ExecuteScalarAsync();
await conn.Close();
}
public static async Task <bool> UserExists(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(false);
}
const string command = "SELECT COUNT(*) FROM users WHERE username = @param_val_1;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
var result = int.Parse((await m.ExecuteScalarAsync()).ToString());
await conn.Close();
return(result > 0);
}
public static async Task LogWonderTrade(string username, string pokemon)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "INSERT INTO wondertradelog (username, pokemon, time) VALUES (@username, @pokemon, @time)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@username", username);
m.Parameters.AddWithValue("@pokemon", pokemon);
m.Parameters.AddWithValue("@time", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task LogWarning(uint userId, string reason)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
Logger.Logger.Log($"Warned user with id {userId} for reason '{reason}'");
const string command = "INSERT INTO warnings (user_id, reason, time) VALUES (@param_val_1, @param_val_2, @param_val_3)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", userId);
m.Parameters.AddWithValue("@param_val_2", reason);
m.Parameters.AddWithValue("@param_val_3", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task RemoveMessage(uint userId)
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "UPDATE friendsafari SET message = NULL WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static async Task LogTrade(string u1, string u2, string p1, string p2)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "INSERT INTO newtradelog (user1, user2, pokemon1, pokemon2, time) VALUES (@param_val_1, @param_val_2, @param_val_3, @param_val_4, @param_val_5)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", u1);
m.Parameters.AddWithValue("@param_val_2", u2);
m.Parameters.AddWithValue("@param_val_3", p1);
m.Parameters.AddWithValue("@param_val_4", p2);
m.Parameters.AddWithValue("@param_val_5", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task UpdateFriends(uint userId, List <uint> friendlist)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string comm = "INSERT INTO friend_list (user_id,friends)" +
"VALUES(@uid, @friendlist)" +
"ON DUPLICATE KEY UPDATE" +
"friends = VALUES(friends)";
var m = new MySqlCommand(comm, conn.Connection);
m.Parameters.AddWithValue("@uid", userId);
m.Parameters.AddWithValue("@friendlist", string.Join(",", friendlist.ToArray()));
#pragma warning disable 4014
m.ExecuteNonQueryAsync();
#pragma warning restore 4014
await conn.Close();
}
public static async Task MetricCountMultiple(IEnumerable <int> ls)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
var dic = new Dictionary <int, int>();
foreach (var i in ls)
{
if (dic.ContainsKey(i))
{
dic[i]++;
}
else
{
dic.Add(i, 1);
}
}
var sCommand = new StringBuilder("INSERT INTO CounterMetrics (id, value) VALUES ");
var rows = dic.Select(kp => $"('{kp.Key}','{kp.Value}')").ToList();
sCommand.Append(string.Join(",", rows));
sCommand.Append(";");
var m = new MySqlCommand(sCommand.ToString(), conn.Connection)
{
CommandType = CommandType.Text
};
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task <int> GetMetricValue(int key)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(0);
}
const string command = "SELECT value FROM CounterMetrics WHERE id=@key LIMIT 1";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@key", key);
var val = await mcom.ExecuteScalarAsync();
await conn.Close();
if (val == null || val is DBNull)
{
return(0);
}
return((int)val);
}
public static async Task GetRandomBase(Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "SELECT friendsafari.base, friendsafari.message, users.username, users.banned " +
"FROM friendsafari " +
"INNER JOIN users " +
"ON friendsafari.user_id = users.user_id " +
"WHERE friendsafari.base IS NOT NULL " +
"AND friendsafari.base <> '' " +
"AND users.banned <> 1 " +
"ORDER BY RAND() " +
"LIMIT 1";
var m = new MySqlCommand(command, conn.Connection);
var result = await m.ExecuteReaderAsync();
while (await result.ReadAsync())
{
var Base = result["base"];
var messageDb = result["message"];
var username = result["username"];
var message = messageDb is DBNull ? "nil" : messageDb.ToString();
await client.SendMessage(
$"<VBASE user={username} result=2 base={Base} message={Utilities.Encoding.Base64Encode(message)}>");
break;
}
await conn.Close();
}
public static async Task Ban(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string banuser = "******";
var m = new MySqlCommand(banuser, conn.Connection);
m.Parameters.AddWithValue("user", userId);
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task SetMessage(uint userId, string message)
{
message = Utilities.Encoding.RemoveSpecialCharacters(message);
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "UPDATE friendsafari SET message = @param_val_1 WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", message);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static async Task CancelTrade(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string s = "DELETE FROM GTS WHERE id = @id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
await c.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task SetAccepted(uint index, string pokemon)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string s = "UPDATE GTS SET Accepted=1, Result=@poke WHERE id = @index";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@poke", pokemon);
c.Parameters.AddWithValue("@index", index);
await c.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task <int> GetNumberOfTrades(uint userid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(0);
}
const string com = "SELECT COUNT(*) FROM GTS WHERE user_id=@id";
var mcom = new MySqlCommand(com, conn.Connection);
mcom.Parameters.AddWithValue("@id", userid);
var i = int.Parse((await mcom.ExecuteScalarAsync()).ToString());
await conn.Close();
return(i);
}
public static async Task SetTrainer(Client client, string trainer)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string command = "UPDATE friendsafari SET trainer = @param_val_1 WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", trainer);
m.Parameters.AddWithValue("@param_val_2", client.UserId);
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task UploadBase(uint userId, string Base)
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "INSERT INTO friendsafari (user_id, base) " +
"VALUES (@param_val_2, @param_val_1) " +
"ON DUPLICATE KEY " +
"UPDATE " +
"base = @param_val_1";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", Base);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static async Task Add(uint userid, string offer, string request, int level, string ownername)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string com = "INSERT INTO GTS (user_id, Offer, Request, OfferLevel, ownername) VALUES (@id, @offer, @request, @level, @ownername)";
var mcom = new MySqlCommand(com, conn.Connection);
mcom.Parameters.AddWithValue("@id", userid);
mcom.Parameters.AddWithValue("@offer", offer);
mcom.Parameters.AddWithValue("@request", request);
mcom.Parameters.AddWithValue("@level", level);
mcom.Parameters.AddWithValue("@ownername", ownername);
await mcom.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task Register(Client client, string username, string password, string email)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
username = username.RemoveSpecialCharacters();
var check = new MySqlCommand("SELECT " +
"(SELECT COUNT(*) username FROM users WHERE username = @val) usernames, " +
"(SELECT COUNT(*) FROM ips WHERE ip = @ip AND ipban = 1) ipbans", conn.Connection);
check.Parameters.AddWithValue("val", username);
check.Parameters.AddWithValue("ip", client.Ip.ToString());
var checkres = await check.ExecuteReaderAsync();
var canContinue = true;
while (await checkres.ReadAsync())
{
if ((long)checkres["usernames"] > 0)
{
await client.SendMessage("<REG result=0>");
canContinue = false;
}
else if ((long)checkres["ipbans"] > 0)
{
await client.SendMessage("<LOG result=2>");
canContinue = false;
Logger.Logger.Log($"User was blocked from registering because of IP ban: {username}");
}
}
if (!canContinue)
{
await conn.Close();
return;
}
checkres.Close();
var create = new MySqlCommand("INSERT INTO users (username, password, email, usergroup, base, sprite) " +
"VALUES (@name, @pass, @email, @usergroup, @base, @sprite)",
conn.Connection);
create.Parameters.AddWithValue("name", username);
create.Parameters.AddWithValue("pass", password);
create.Parameters.AddWithValue("email", email);
create.Parameters.AddWithValue("usergroup", 0);
create.Parameters.AddWithValue("base", "");
create.Parameters.AddWithValue("sprite", "");
await create.ExecuteNonQueryAsync();
var getUserid = new MySqlCommand("SELECT user_id FROM users WHERE username = @user", conn.Connection);
getUserid.Parameters.AddWithValue("user", username);
var idreader = await getUserid.ExecuteReaderAsync();
while (await idreader.ReadAsync())
{
client.UserId = (uint)idreader["user_id"];
}
idreader.Close();
var logonline = new MySqlCommand("INSERT INTO user_data (user_id, lastlogin) VALUES (@uid, @time)", conn.Connection);
logonline.Parameters.AddWithValue("uid", client.UserId);
logonline.Parameters.AddWithValue("time", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
#pragma warning disable 4014
logonline.ExecuteNonQueryAsync();
#pragma warning restore 4014
const string ipsetcommand = "INSERT INTO ips VALUES (@param_val_1, @param_val_2, @param_val_3)";
var o = new MySqlCommand(ipsetcommand, conn.Connection);
o.Parameters.AddWithValue("@param_val_1", client.UserId);
o.Parameters.AddWithValue("@param_val_2", client.Ip);
o.Parameters.AddWithValue("@param_val_3", 0);
#pragma warning disable 4014
o.ExecuteNonQueryAsync();
#pragma warning restore 4014
await client.SendMessage("<REG result=2>");
await conn.Close();
Logger.Logger.Log($"User registered: {username}");
}
public static async Task <LoginResult> Login(string username, string password, Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(LoginResult.WrongUsername);
}
username = username.RemoveSpecialCharacters();
const string logincommand = "SELECT users.user_id, usergroup, banned, password, admin, " +
"GROUP_CONCAT(ip separator ',') as 'IPs', " +
"(SELECT COUNT(*) FROM ips WHERE ip=@param_val_2 AND ipban=1) ipbans " +
"FROM users " +
"INNER JOIN ips " +
"ON users.user_id=ips.user_id " +
"WHERE username = @param_val_1 ";
var m = new MySqlCommand(logincommand, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
m.Parameters.AddWithValue("@param_val_2", client.Ip.ToString());
var result = await m.ExecuteReaderAsync();
var ret = LoginResult.Unset;
if (!result.HasRows)
{
await conn.Close();
return(LoginResult.WrongUsername);
}
var ips = new List <string>();
while (await result.ReadAsync())
{
if (result["password"].ToString() != password)
{
await conn.Close();
return(LoginResult.WrongPassword);
}
if (result["admin"] is DBNull)
{
client.Admin = false;
}
else if ((bool)result["admin"])
{
client.Admin = true;
}
if (client.Admin)
{
Console.WriteLine("Admin logged in, username: "******"banned"] && !client.Admin)
{
await conn.Close();
return(LoginResult.Banned);
}
if (((long)result["ipbans"] > 0))
{
ret = LoginResult.IpBanned;
}
ips = result["IPs"].ToString().Split(',').ToList();
var id = (uint)result["user_id"];
client.UserId = id;
}
result.Close();
if (!ips.Contains(client.Ip.ToString()))
{
const string ipsetcommand = "INSERT INTO ips VALUES (@param_val_1, @param_val_2, @param_val_3)";
var o = new MySqlCommand(ipsetcommand, conn.Connection);
o.Parameters.AddWithValue("@param_val_1", client.UserId);
o.Parameters.AddWithValue("@param_val_2", client.Ip);
o.Parameters.AddWithValue("@param_val_3", 0);
try
{
await o.ExecuteNonQueryAsync();
}
catch
{
// ignore
}
}
if (ret == LoginResult.Unset)
{
ret = LoginResult.Okay;
}
if (ret == LoginResult.Banned || ret == LoginResult.IpBanned)
{
#pragma warning disable 4014
DbUserManagement.Ban(client.UserId, ips);
DBWarnLog.LogWarning(client.UserId, "Automatic ban: IP ban");
#pragma warning restore 4014
Logger.Logger.Log($"User was banned automatically: {username}");
}
const string loggedInRegisterCom = "UPDATE user_data SET lastlogin = @param_val_2 WHERE user_id = @param_val_1";
var logregcommand = new MySqlCommand(loggedInRegisterCom, conn.Connection);
logregcommand.Parameters.AddWithValue("param_val_1", client.UserId);
logregcommand.Parameters.AddWithValue("param_val_2", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await logregcommand.ExecuteNonQueryAsync();
if (ret == LoginResult.Okay)
{
Logger.Logger.Log($"User logged in: {username}");
}
await conn.Close();
return(ret);
}