[JLmelenchon]

I need your help, how can i bypass that? If i could just ignore that the timestamp is the same it will be good or is it important?

SQL Error: Duplicate entry '1667064627' for key 'timestamp'

Multiple players are receiving points at the same time that go to the database and the timestamp is the same so it does not register...

[JLmelenchon]

len += Format(query[len], sizeof(query)-len, " UNIQUE (`timestamp`) ) ENGINE = MyISAM;");

[Marttt]

if is a column is unique and you are registering at the same time with the same value is kinda obvious.
usually you will have a column with auto increment so you don't need to care about unique fields.
if is a 1:1 relationship so your unique key should be the steamid, but is hard to tell with the given context.

[azalty]

Your primary key should always be something that isn't duplicate. In a SM plugin, most of the time you can either not specify any, and in most cases it'll make your DB auto choose one (a rowid), or you choose a steamid.

With this few info we can't really help you. You'll have to give us more code, your DB schema...

Based on your username, you seem to be french. I am too, so if you don't want to share things publicly here, feel free to add me on discord: azalty#9721 so I can help you.
I'll also be able to help you in the future since there aren't a lot of french SP devs around.

[JLmelenchon]

The steam id is reused multiple times in my plugin, this can't be unique. How can i add properly an id auto increment without having to restart my database?

public void SQL_CreateTables()
{
	int len = 0;
	char query[1256]; 
	len += Format(query[len], sizeof(query)-len, "CREATE TABLE IF NOT EXISTS `score` (");
	len += Format(query[len], sizeof(query)-len, " `timestamp` INT(11) NOT NULL DEFAULT '0' ,");
	len += Format(query[len], sizeof(query)-len, " `giver_steamid` VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `giver_name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `recipient_steamid` VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `recipient_name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `amount` INT(12) NOT NULL DEFAULT '100' ,");
	len += Format(query[len], sizeof(query)-len, " `reason` VARCHAR(120) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ");
	len += Format(query[len], sizeof(query)-len, " UNIQUE (`timestamp`) ) ENGINE = MyISAM;");

And it register like that:

Format(sQuery, sizeof(sQuery), "INSERT INTO `score` (`timestamp`, `giver_steamid`, `giver_name`, `recipient_steamid`, `recipient_name`, `amount`, `reason`) VALUES ('%i', '%s', '%s', '%s', '%s', '%i', '%s')", GetTime(), client_steamID, escapedClientName, target_steamID, escapedTargetName, amount, escapedReason);

But i guess i could change to that and remove the timestamp and use the steamid as primary key? But it only works if the player is already registered i guess.

Format(sQuery, sizeof(sQuery), "UPDATE `score` SET `amount`= %i, `reason`=\"%s\" WHERE `giver_steamid` = \"%s\" AND `recipient_steamid` = \"%s\"", amount, escapedReason, client_steamID, target_steamID);

[JLmelenchon]

I feel like i will not have the choice to restart the database from the begining (or does there is a way to add a column?). This is what i did, can you confirm me if it is ok? It registered me properly but you never know what can happen after, don't know much about sql.

public void SQL_CreateTables()
{
	int len = 0;
	char query[1256];
	len += Format(query[len], sizeof(query)-len, "CREATE TABLE IF NOT EXISTS `score` (");
	len += Format(query[len], sizeof(query)-len, " `Id` INT(11) NOT NULL AUTO_INCREMENT ,");
	len += Format(query[len], sizeof(query)-len, " `timestamp` INT(11) NOT NULL DEFAULT '0' ,");
	len += Format(query[len], sizeof(query)-len, " `giver_steamid` VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `giver_name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `recipient_steamid` VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `recipient_name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,");
	len += Format(query[len], sizeof(query)-len, " `amount` INT(12) NOT NULL DEFAULT '100' ,");
	len += Format(query[len], sizeof(query)-len, " `reason` VARCHAR(120) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ");
	len += Format(query[len], sizeof(query)-len, " UNIQUE (`Id`) ) ENGINE = MyISAM;");

Format(sQuery, sizeof(sQuery), "INSERT INTO `reputation` (`Id`, `timestamp`, `giver_steamid`, `giver_name`, `recipient_steamid`, `recipient_name`, `amount`, `reason`) VALUES ('NULL', '%i', '%s', '%s', '%s', '%s', '%i', '%s')", GetTime(), steamid, sName, steamid, sName, amount, sReason);

and picture: https://ibb.co/xs4mNg3

It seems to work as it added me again in the database but this time with id "2" but only tested alone.

[azalty]

Why use the UNIQUE constraint when you can use a PRIMARY KEY, which is way better?

Please also note that using auto increment breaks the code for SQLite as it's not written the same.

Search for ALTER TABLE to modify a table after it's been created. I think you can also do that in phpMyAdmin.

I'm also afraid that you don't escape user-input strings when inserting data in your database, which exposes you to SQL injection exploits

[JLmelenchon]

Originally Posted by azalty Why use the UNIQUE constraint when you can use a PRIMARY KEY, which is way better? Please also note that using auto increment breaks the code for SQLite as it's not written the same. Search for ALTER TABLE to modify a table after it's been created. I think you can also do that in phpMyAdmin. I'm also afraid that you don't escape user-input strings when inserting data in your database, which exposes you to SQL injection exploits

I tried "PRIMARY" and it says that the syntax is not right, but i will retry with PRIMARY KEY. (yes works now)

Not sure what do you mean by user-input string, how could they interfere with the database? There is no user command (regconsolecmd), only the server write to the database on round end.

But i heard of exploit with specific nicknames in the past, is it still a thing?

edit: i removed the names, had no utility anyway.

[azalty]

Originally Posted by JLmelenchon Not sure what do you mean by user-input string, how could they interfere with the database? There is no user command (regconsolecmd), only the server write to the database on round end. But i heard of exploit with specific nicknames in the past, is it still a thing? edit: i removed the names, had no utility anyway.

Yes. Players can break your query by writing a single quote (') in their username (or in their chat messages for example).
If the reason can be manually written by players, they can do SQL injections or break your queries.

let's take this:

INSERT INTO `reputation` (`Id`, `timestamp`, `giver_steamid`, `giver_name`, `recipient_steamid`, `recipient_name`, `amount`, `reason`) VALUES ('NULL', '%i', '%s', '%s', '%s', '%s', '%i', '%s')

here, if the name is:

', 'STEAM_1:1:293283', '', 9999, '');

They can put their own values in your query. Not sure if that example would work, or if it would make the whole query fail, but either way they can have big consequences.
This would do:

INSERT INTO `reputation` (`Id`, `timestamp`, `giver_steamid`, `giver_name`, `recipient_steamid`, `recipient_name`, `amount`, `reason`) VALUES ('NULL', '%i', '%s', '', 'STEAM_1:1:293283', '', 9999, '');', '%s', '%s', '%i', '%s')

[JLmelenchon]

Can these escaped names and reasons can also be used as en exploit on a sourcemod plugin when it goes into an array ?