SQL : character ":" causes error

[raptor2x 10]

Hello,

So basically I have a dialog where the player can enter a text. Then I save this text in my database.

It's working good but if the player puts the character ":" inside his text, the query will understand as if it was a new input (I don't know if I'm expressing myself correctly, sorry).

Here is the log so you will understand what I'm saying :

[14:44:50:842 +02:00] [Thread 4856] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Input String addOpinion:76561198028528740:Raptor:this is a wonderful test :)
[14:44:50:842 +02:00] [Thread 4856] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Expected: 3 Got: 4
[14:32:36:478 +02:00] [Thread 4856] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Input String addOpinion:76561198028528740:Raptor:: 
[14:32:36:478 +02:00] [Thread 4856] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Expected: 3 Got: 4
[14:50:00:276 +02:00] [Thread 3028] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Input String addOpinion:76561198028528740:Raptor:test 2 : : : : : : : 
[14:50:00:276 +02:00] [Thread 3028] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Expected: 3 Got: 10

So what should I do to force the character not be considerated as a new input ?

private ["_answerAuthorID", "_answerAuthorName", "_answerText"];
_answerAuthorID = _this select 0;
_answerAuthorName = _this select 1;
_answerText = _this select 2;

[format ["addOpinion:%1:%2:%3", _answerAuthorID, _answerAuthorName, _answerText]] call extDB_Database_async;

Thank you ! 

[Ranwer135 308]

I may haven't checked out extDB yet, but use this code before your DB logs the input:

if (!((_answerText find ":") isEqualTo -1)) exitWith {hint "Input is wrong, try again!"};

[hoverguy 177]

The : character is used as a separator, so re-format your message or filter it.

 

You can make an array of forbidden characters and check against em whenever the user is about to send the message (click send button), also you can tell the player which characters can't be used.

[raptor2x 10]

Thanks for your help guys.

So there is no way to have this character in a normal text in the database ? We must prevent the player from writing it ?

Btw, is ":" the only character that can't be used ?

[hoverguy 177]

Try to store the message as an array of strings, should work.

 

_myArray = [];
_myArray pushBack _answerText;

[Ranwer135 308]

Thanks for your help guys.

So there is no way to have this character in a normal text in the database ? We must prevent the player from writing it ?

Btw, is ":" the only character that can't be used ?

I believe there is a command called 'onChar' which you can put into your dialog config. You can find more info here: https://community.bistudio.com/wiki/User_Interface_Event_Handlers

[raptor2x 10]

Try to store the message as an array of strings, should work.

 

_myArray = [];
_myArray pushBack _answerText;

 

Like that ?

private ["_answerAuthorID", "_answerAuthorName", "_answerText"];
_answerAuthorID = _this select 0;
_answerAuthorName = _this select 1;
_answerText = _this select 2;

_textArray = [];
_textArray pushBack _answerText;

[format ["addOpinion:%1:%2:%3", _answerAuthorID, _answerAuthorName, _textArray]] call extDB_Database_async;

It gives 

 

But the problem is the same with ":" 

[17:56:57:934 +02:00] [Thread 5564] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Input String addOpinion:76561198028528740:Raptor:[":"]
[17:56:57:934 +02:00] [Thread 5564] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Expected: 3 Got: 4
[17:57:37:105 +02:00] [Thread 5564] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Input String addOpinion:76561198028528740:Raptor:["qsdqsdqds qs :"]
[17:57:37:105 +02:00] [Thread 5564] extDB2: SQL_CUSTOM_V2: Incorrect Number of Inputs: Expected: 3 Got: 4

I believe there is a command called 'onChar' which you can put into your dialog config. You can find more info here: https://community.bistudio.com/wiki/User_Interface_Event_Handlers

 

What am I supposed to do with onChar ? 

 

Thanks guys !

[hoverguy 177]

With onChar dialog EVH you can do stuff (disable some chars for example...) whenever the player inputs something recognizable.

[SilentSpike 84]

I feel like whatever extension you're using should be handling input validation, so I'd report it to whoever made it. Doing it manually is your best workaround for now.

[raptor2x 10]

I feel like whatever extension you're using should be handling input validation, so I'd report it to whoever made it. Doing it manually is your best workaround for now.

 

Yep, the problem is that I'm a beginner so I don't know how to do that exactly. That's not a big big problem so I think that I will just try to warn the player if he writes ":".

[dreadedentity 278]

Definitely an issue with the extension, you should contact the writer of extDB for a fix.

So what should I do to force the character not be considerated as a new input ?

I doubt there's anything you can do in .sqf that will fix this. You will need to rewrite the extension, or get the original writer to rewrite it.

 

It seems you're going to have to scan input for any colon's and reject it, or scan and scrub all colon's from input.

[killzone_kid 1330]

One would expect that if extension wants 3 params it should treat anything that comes after 2nd param as 3rd param, including ":". You may want to mention this to the author of the extension.

[torndeco 128]

I feel like whatever extension you're using should be handling input validation, so I'd report it to whoever made it. Doing it manually is your best workaround for now.

 

The problem is Arma str command doesn't escape quotations properly.

For example if user inputted some text, it could close the current sqf array & insert whatever they like.

Since you are unable to parse input correctly (especially when you allow user input), there is no way to tell were the current value ends and the next one starts.

That is why extDB2 uses a seperator character

 

One would expect that if extension wants 3 params it should treat anything that comes after 2nd param as 3rd param, including ":". You may want to mention this to the author of the extension.

 

The problem is the seperator character could be in a user input string in the 2nd param.

Now your database query input values are screwed up

If this is an SQL Update there will never be any errors from MySQL/SQLite  once the query itself is valid.

And the chances of you noticing the database not updating is almost zero

------------------------------------------------

Unless Arma makes a new str command to escape quotations (its like 5 minutes work for them todo) there is no really no nice way todo in a generic fashion.

Arma Engine is already using Boost Library, so there is no excuse  already has a find/replace string function.

They just need to find & replace the quotations and escape them.

Call the new command strX so it doesn't break any existing code.

Then it would be possible to parse inputted values correctly with an extension & not have to worry about user inputted data.

 

--------------------------------------------------

The easiest solution is to check user input for : character, or if you want you can define a different seperator character.

 

[pedeathtrian 99]

Obviously, bug in extension. Imagine C did not give us \" for escaping of quotation marks in strings. torndeco, you should have provided some escaping to allow user input contain ":", like C-ctyle "\:" or smth like that.

 

raptor2x, workaround is quite simple.

Process user input before calling extension and what extension return on databse read:

// 8758 is for U+2236 RATIO
#define ESC_CHAR 8758

fnc_processStringOutputToDB = {
	// function replaces all occurencies of string "∶" with "∶0" and ":" with "∶1"
	// all function could be written as single line:
	// (((_this splitString toString [ESC_CHAR]) joinString toString [ESC_CHAR,48]) splitString ":") joinString toString [ESC_CHAR,49];
	private ["_spl", "_repl"];
	_spl = _this splitString toString [ESC_CHAR];
	_repl = _spl joinString toString [ESC_CHAR,48]; // 48 is char '0';
	_spl = _repl splitString ":";
	_repl = _spl joinString toString [ESC_CHAR,49] // 48 is char '1';
	_repl
};

fnc_processStringInputFromDB = {
	// function replaces all occurencies of "∶0" with "∶" and "∶1" with ":"
	private ["_spl", "_i", "_count", "_chars", "_str"];
	_spl = _this splitString toString [ESC_CHAR];
	_count = count _spl;
	for [{_i = 1}, {_i<_count}, {_i=_i+1}] do {
		switch ((_spl select _i) select [0,1]) do {
			case "0": {
				_spl set [_i, (toString [ESC_CHAR]) + (_spl select _i) select [1]];
			};
			case "1": {
				_spl set [_i, ":" + (_spl select _i) select [1]];
			};
		};
	};
	_spl joinString ""
}; 

∶ is Unicode U+2236 RATIO symbol, decimal is 8758. Looks almost like colon, but it's not. Actually could be any symbol user most likely not entered before.

Note, that you will need to convert all your affected database contents to replace ":"  (colon) with "∶1" (RATIO then 1) and "∶" (RATIO) with "∶0" (RATIO then 0).

[torndeco 128]

Seriously arma is the one that isn't escaping strings....
https://community.bistudio.com/wiki/str


-----------------------------

If i provide a function to escape quotations.
No matter which method you come up with, you will still need to parse the data in SQF, before you sent to the extension.

i.e what happens when the user string has  /:  in it ?? (you still need to parse for it & escape it correctly in SQF before you send it to the extension)

Since any user inputted could have manipulated the string to screw with the extension.

So until BIS fixes this issue, there is a single unicode char (default is : ) that you can't use, which you just need to check for on user input / playername (if stored in database).
Also there is builtin functional in extDB2 to change the seperator character to any unicode char you want to use instead.
You can even custom define it for a single database query if using SQL_CUSTOM_V2.

 

--------------------------------

Anyway this is an arma issue, all BIS needs todo is escape their strings correctly.
If arma did this, then all extensions could parse a stringified array, with zero worry about user inputted strings

test = "str"ing"; // Error
test2 = "str""ing"; // Correct

diag_log (str test2); // Output is str"ing the str/format commands doesn't escape quotations !!!!!!!!!!!!

For example
test3 = call compile format["%1",test2];   // This will fail since the quotation isn't escaped, ie it tried to compile  "str"ing", instead of "str""ing"

But if you think you can solve this issue correctly in a generic extension then by all means prove me wrong

[torndeco 128]

More info about this issue with strings is at 
Google Cache of arma3 feedback tracker
http://webcache.googleusercontent.com/search?q=cache:BiMtTjR6R0MJ:feedback.arma3.com/view.php%3Fid%3D21362%26nbn%3D6+&cd=1&hl=en&ct=clnk&gl=ie

[pedeathtrian 99]

If i provide a function to escape quotations.

No matter which method you come up with, you will still need to parse the data in SQF, before you sent to the extension.

i.e what happens when the user string has  /:  in it ?? (you still need to parse for it & escape it correctly in SQF before you send it to the extension)

But at least there will be the way to store any data using your extension. Well, nevermind, I already posted code which is required only on user side.

 

 

Also there is builtin functional in extDB2 to change the seperator character to any unicode char you want to use instead.

You can even custom define it for a single database query if using SQL_CUSTOM_V2.

Didn't know that. Failed to find it on your wiki page. Seriously, I tried.

This is actually (almost) enough to solve OP's problem without analyzing player's input on any side. So I take my words about obvious bug back. Sorry.

 

Anyway this is an arma issue, all BIS needs todo is escape their strings correctly.

We all know it can take forever. Unfortunately.