PDA

View Full Version : mysql - duplicated rows


cyanogen
11/08/2014, 03:18 AM
I noticed some issues with my sql DB while I was browsing through the rows that players have created. Some (not all) have information that is completely identical. They usually occur consecutively as well. The differences between the rows is the username they registered, the ID, and the IP they registered with. Everything else is identical. Relevant code following:


#define dRegister 6287
#define dLogin 6288

//sql config
#define mysql_host "edited"
#define mysql_user "edited"
#define mysql_password "edited"
#define mysql_database "edited"


//sql connection handle
new mysql = -1;
new Name[MAX_PLAYERS][24], IP[MAX_PLAYERS][16];

enum PlayerInfo
{
Adminlevel, //User's admin level
ID,
Password[129],
Money, //User's money
Kills, //User's kills
Deaths, //User's deaths
Bounty, //User's bounty
isMuted, //Whether or not they are muted.
StartYear, //Day player started.
StartMonth,
StartDay,
StartIP[16], //IP of the player when they register
TogglePM,
ToggleG,
knifeK,
chainsawK,
colt45K,
silencedK,
deagleK,
shotgunK,
sawedoffK,
spazK,
uziK,
mp5K,
ak47K,
m4K,
tec9K,
rifleK,
sniperK,
dWins,
dLosses,
silencedUnk,
colt45Unk,
deagleUnk,
shotgunUnk,
spazUnk,
sawedoffUnk,
uziUnk,
mp5Unk,
ak47Unk,
m4Unk,
rifleUnk,
sniperUnk,
parachuteUnk,
tec9Unk,
armorUnk,
dildoUnk,
flowerUnk,
caneUnk,
brassUnk,
clubUnk,
nitestickUnk,
knifeUnk,
batUnk,
shovelUnk,
poolstickUnk,
katanaUnk,
chainsawUnk
}

new gData[MAX_PLAYERS][PlayerInfo];

public OnGameModeInit()
{
//other code
mysql_log(LOG_ERROR | LOG_WARNING | LOG_DEBUG);

mysql = mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);

if(mysql_errno(mysql) != 0) print ("Could not connect to the database!");
}
public OnPlayerConnect(playerid)
{
//other code
GetPlayerName(playerid, Name[playerid], 24);
GetPlayerIp(playerid, IP[playerid], 16);
mysql_format(mysql, query, sizeof(query), "SELECT `Password`, `ID` FROM `playerinfo` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
mysql_tquery(mysql, query, "OnAccountCheck", "d", playerid);
}
public OnPlayerDisconnect(playerid)
{
new query[300];

mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `Adminlevel`=%d, `Money`=%d, `Kills`=%d WHERE `ID`=%d", gData[playerid][Adminlevel], gData[playerid][Money], gData[playerid][Kills], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `Deaths`=%d, `Bounty`=%d, `isMuted`=%d WHERE `ID`=%d", gData[playerid][Deaths], gData[playerid][Bounty], gData[playerid][isMuted], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `TogglePM`=%d, `ToggleG`=%d, `knifeK`=%d WHERE `ID`=%d", gData[playerid][TogglePM], gData[playerid][ToggleG], gData[playerid][knifeK], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `chainsawK`=%d, `colt45K`=%d, `silencedK`=%d WHERE `ID`=%d", gData[playerid][chainsawK], gData[playerid][colt45K], gData[playerid][silencedK], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `deagleK`=%d, `shotgunK`=%d, `sawedoffK`=%d WHERE `ID`=%d", gData[playerid][deagleK], gData[playerid][shotgunK], gData[playerid][sawedoffK], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `spazK`=%d, `uziK`=%d, `mp5K`=%d WHERE `ID`=%d", gData[playerid][spazK],gData[playerid][uziK], gData[playerid][mp5K], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `ak47K`=%d, `m4K`=%d, `tec9K`=%d WHERE `ID`=%d", gData[playerid][ak47K], gData[playerid][m4K], gData[playerid][tec9K], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `rifleK`=%d, `sniperK`=%d, `dWins`=%d WHERE `ID`=%d", gData[playerid][rifleK], gData[playerid][sniperK], gData[playerid][dWins], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `dLosses`=%d WHERE `ID`=%d", gData[playerid][dLosses], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `silencedUnk`=%d, `colt45Unk`=%d, `deagleUnk`=%d WHERE `ID`=%d", gData[playerid][silencedUnk], gData[playerid][colt45Unk], gData[playerid][deagleUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `shotgunUnk`=%d, `spazUnk`=%d, `sawedoffUnk`=%d WHERE `ID`=%d", gData[playerid][shotgunUnk], gData[playerid][spazUnk], gData[playerid][sawedoffUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `uziUnk`=%d, `mp5Unk`=%d, `ak47Unk`=%d WHERE `ID`=%d", gData[playerid][uziUnk], gData[playerid][mp5Unk], gData[playerid][ak47Unk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `m4Unk`=%d, `rifleUnk`=%d, `sniperUnk`=%d WHERE `ID`=%d", gData[playerid][m4Unk], gData[playerid][rifleUnk], gData[playerid][sniperUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `parachuteUnk`=%d, `tec9Unk`=%d, `armorUnk`=%d WHERE `ID`=%d", gData[playerid][parachuteUnk], gData[playerid][tec9Unk], gData[playerid][armorUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `dildoUnk`=%d, `flowerUnk`=%d, `caneUnk`=%d WHERE `ID`=%d", gData[playerid][dildoUnk], gData[playerid][flowerUnk], gData[playerid][caneUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `brassUnk`=%d, `clubUnk`=%d, `nitestickUnk`=%d WHERE `ID`=%d", gData[playerid][brassUnk], gData[playerid][clubUnk], gData[playerid][nitestickUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `knifeUnk`=%d, `batUnk`=%d, `shovelUnk`=%d WHERE `ID`=%d", gData[playerid][knifeUnk], gData[playerid][batUnk], gData[playerid][shovelUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `poolstickUnk`=%d, `katanaUnk`=%d, `chainsawUnk`=%d WHERE `ID`=%d", gData[playerid][poolstickUnk], gData[playerid][katanaUnk], gData[playerid][chainsawUnk], gData[playerid][ID]);
mysql_tquery(mysql, query, "", "");
}
forward OnAccountCheck(playerid);
public OnAccountCheck(playerid)
{
new rows, fields;
cache_get_data(rows, fields, mysql);
if(rows)
{
cache_get_field_content(0, "Password", gData[playerid][Password], mysql, 129);
gData[playerid][ID] = cache_get_field_content_int(0, "ID");
ShowPlayerDialog(playerid, dLogin, DIALOG_STYLE_PASSWORD, "Login", "Type your password.", "Confirm", "Decline");
}
else
{
ShowPlayerDialog(playerid, dRegister, DIALOG_STYLE_PASSWORD, "Register", "Type in a password you can remember.", "Confirm", "Decline");
}
return 1;
}
forward OnAccountLoad(playerid);
public OnAccountLoad(playerid)
{
gData[playerid][ID] = cache_get_field_content_int(0, "ID");
gData[playerid][Adminlevel] = cache_get_field_content_int(0, "Adminlevel");
gData[playerid][Money] = cache_get_field_content_int(0, "Money");
gData[playerid][Kills] = cache_get_field_content_int(0, "Kills");
gData[playerid][Deaths] = cache_get_field_content_int(0, "Deaths");
gData[playerid][Bounty] = cache_get_field_content_int(0, "Bounty");
gData[playerid][isMuted] = cache_get_field_content_int(0, "isMuted");
gData[playerid][StartYear] = cache_get_field_content_int(0, "StartYear");
gData[playerid][StartMonth] = cache_get_field_content_int(0, "StartMonth");
gData[playerid][StartDay] = cache_get_field_content_int(0, "StartDay");
gData[playerid][TogglePM] = cache_get_field_content_int(0, "TogglePM");
gData[playerid][ToggleG] = cache_get_field_content_int(0, "ToggleG");
gData[playerid][knifeK] = cache_get_field_content_int(0, "knifeK");
gData[playerid][chainsawK] = cache_get_field_content_int(0, "chainsawK");
gData[playerid][colt45K] = cache_get_field_content_int(0, "colt45K");
gData[playerid][silencedK] = cache_get_field_content_int(0, "silencedK");
gData[playerid][deagleK] = cache_get_field_content_int(0, "deagleK");
gData[playerid][shotgunK] = cache_get_field_content_int(0, "shotgunK");
gData[playerid][sawedoffK] = cache_get_field_content_int(0, "sawedoffK");
gData[playerid][spazK] = cache_get_field_content_int(0, "spazK");
gData[playerid][uziK] = cache_get_field_content_int(0, "uziK");
gData[playerid][mp5K] = cache_get_field_content_int(0, "mp5K");
gData[playerid][ak47K] = cache_get_field_content_int(0, "ak47K");
gData[playerid][m4K] = cache_get_field_content_int(0, "m4K");
gData[playerid][tec9K] = cache_get_field_content_int(0, "tec9K");
gData[playerid][rifleK] = cache_get_field_content_int(0, "rifleK");
gData[playerid][sniperK] = cache_get_field_content_int(0, "sniperK");
gData[playerid][dWins] = cache_get_field_content_int(0, "dWins");
gData[playerid][dLosses] = cache_get_field_content_int(0, "dLosses");
gData[playerid][silencedUnk] = cache_get_field_content_int(0, "silencedUnk");
gData[playerid][colt45Unk] = cache_get_field_content_int(0, "colt45Unk");
gData[playerid][deagleUnk] = cache_get_field_content_int(0, "deagleUnk");
gData[playerid][shotgunUnk] = cache_get_field_content_int(0, "shotgunUnk");
gData[playerid][spazUnk] = cache_get_field_content_int(0, "spazUnk");
gData[playerid][sawedoffUnk] = cache_get_field_content_int(0, "sawedoffUnk");
gData[playerid][uziUnk] = cache_get_field_content_int(0, "uziUnk");
gData[playerid][mp5Unk] = cache_get_field_content_int(0, "mp5Unk");
gData[playerid][ak47Unk] = cache_get_field_content_int(0, "ak47Unk");
gData[playerid][m4Unk] = cache_get_field_content_int(0, "m4Unk");
gData[playerid][rifleUnk] = cache_get_field_content_int(0, "rifleUnk");
gData[playerid][sniperUnk] = cache_get_field_content_int(0, "sniperUnk");
gData[playerid][parachuteUnk] = cache_get_field_content_int(0, "parachuteUnk");
gData[playerid][tec9Unk] = cache_get_field_content_int(0, "tec9Unk");
gData[playerid][armorUnk] = cache_get_field_content_int(0, "armorUnk");
gData[playerid][dildoUnk] = cache_get_field_content_int(0, "dildoUnk");
gData[playerid][flowerUnk] = cache_get_field_content_int(0, "flowerUnk");
gData[playerid][caneUnk] = cache_get_field_content_int(0, "caneUnk");
gData[playerid][brassUnk] = cache_get_field_content_int(0, "brassUnk");
gData[playerid][clubUnk] = cache_get_field_content_int(0, "clubUnk");
gData[playerid][nitestickUnk] = cache_get_field_content_int(0, "nitestickUnk");
gData[playerid][knifeUnk] = cache_get_field_content_int(0, "knifeUnk");
gData[playerid][batUnk] = cache_get_field_content_int(0, "batUnk");
gData[playerid][shovelUnk] = cache_get_field_content_int(0, "shovelUnk");
gData[playerid][poolstickUnk] = cache_get_field_content_int(0, "poolstickUnk");
gData[playerid][katanaUnk] = cache_get_field_content_int(0, "katanaUnk");
gData[playerid][chainsawUnk] = cache_get_field_content_int(0, "chainsawUnk");
isLogged[playerid] = true;
}
forward OnAccountRegister(playerid);
public OnAccountRegister(playerid)
{
gData[playerid][ID] = cache_insert_id();
printf("New account registered. ID: %d", gData[playerid][ID]);
isLogged[playerid] = true;
return 1;
}
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
switch(dialogid)
{
case dLogin:
{
if(response)
{
new hpass[129], query[100];
WP_Hash(hpass, 129, inputtext);
if(!strcmp(hpass, gData[playerid][Password]))
{
mysql_format(mysql, query, sizeof(query), "SELECT * FROM `playerinfo` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
mysql_tquery(mysql, query, "OnAccountLoad", "i", playerid);
}
else
{
ShowPlayerDialog(playerid, dLogin, DIALOG_STYLE_INPUT, "Login", "Type your password.\nWrong password.", "Confirm", "Decline");
}
}
else Kick(playerid);
}
case dRegister:
{
if(response)
{
if(strlen(inputtext) < 6) return ShowPlayerDialog(playerid, dRegister, DIALOG_STYLE_INPUT, "Register", "Type a password you can remember. \nAt least 6 chars long.", "Confirm", "Decline");
new query[300];
WP_Hash(gData[playerid][Password], 129, inputtext);
new Year, Month, Day;
getdate(Year, Month, Day);
mysql_format(mysql, query, sizeof(query), "INSERT INTO `playerinfo` (`Username`, `Password`, `StartYear`, `StartMonth`, `StartDay`, `StartIP`) VALUES ('%e', '%s', %d, %d, %d, '%s')", Name[playerid], gData[playerid][Password], Year, Month, Day, IP[playerid]);
mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);
}
else Kick(playerid);
}
}

cyanogen
16/08/2014, 09:54 PM
Okay. Took me a while to realize the problem but it finally dawned on me. In onaccountregister I don't query the server to get the default values to set into the player enum. That means if a person registers they get the data that was in the slot on the server before them, so if it was an admin, then they get their alevel and other stuffs. this was a dangerous bug and I'm glad I caught it. hope this helps those who have similar problems. -

-Closed-