[Marttt]

Better read some stuff to understand what SQL Injection means

https://www.w3schools.com/sql/sql_injection.asp

The rest is self-explanatory

[JLmelenchon]

Yes but this was not my point...

I remember an exploit used by cheaters on disconnect where they entered a custom reason to crash server, so my question makes sense.

[azalty]

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

No, as the character used to stop a string is called a null character/terminator, and can't be escaped or entered in any way through chat, username or anything.
There's no need to store escaped names in an array, just store the normal names and escape them when you write a query.

However, escaping a username works by doubling each quote, ex:

if username is 'tes't', it will be escaped ''tes''t'' as doubling a single quote makes it be escaped in SQL

^ this will increase the size of the string, so always use a buffer with double the size of what you intend to store. I recommend creating a big SQL buffer, I personally always do char g_sSQLBuffer[2000] at the start of my plugin, and always use that, so I don't get any problem like this. Feel free to adapt the size of the buffer to your use case, but I recommend having at least 1000.

How to deal with this?
Just do everything as usual, and use Database.Format() to format queries (requires you to have the DB methodmap) or SQL_FormatQuery(), instead of Format().
Make sure to use big enough buffers when formatting queries, user input strings can take up to two times their original length when escaped.

[JLmelenchon]

So i did not touch anything and it suddenly broke, not sure why.

L 11/14/2022 - 19:50:16: [reputation.smx] SQL Error: Incorrect integer value: 'NULL' for column 'Id' at row 1

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, " `recipient_steamid` VARCHAR(22) 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, " PRIMARY KEY (`Id`) ) ENGINE = MyISAM;");

	for (int i; i < MaxClients; i++)
	{
		if (!IsValidClient(i))
			continue;
			
		OnClientPostAdminCheck(i);
	}

	SQL_TQuery(db, SQL_ErrorCheckCallback, query);
}

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

Should i replace "null" by "not null" in the single line of code? VALUES ('NULL'

[Marttt]

The error is self explanatory:

SQL Error: Incorrect integer value: 'NULL' for column 'Id' at row 1

Means that you are passing "null" to the Id column

When the table is created, the column is set like this:

`Id` INT(11) NOT NULL AUTO_INCREMENT

So "NOT NULL" means not null
Also if is "AUTO_INCREMENT" doesn't make sense passing it to the insert clause, cause it will automatically add a valid number (ID+1) to the column


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

remove the 'Id' and the 'NULL'