Pulling data from SQL table in script

Sarge said:
actually i think you dont even need the ?/[0]. Thats just the way to substitute in variables in the hive call.

you could also do a direct

_sqlstring ="CHILD:999: select id from building WHERE class_name= 'barrack' AND id > 0";

need to give that a try though.

cheers, Sarge
Click to expand...

Here's some food for thought. In PHP MySQL injection is a big problem, users pass their own query in as data and force your server to execute it. one way to work around this is by using parameters in your query instead of just replacing a string value in the query itself (? vs %1 in this example). The engine (mysqli in my case) strips the values passed as parameters (?) of bad data and cleans it the best it can. I'm guessing (not 100%) that the Hive.dll is also doing some sort of cleaning of input data if it is passed as a param.

How much of a threat is injection in dayz? probably minimal at best but who knows with all the access that hackers have to execute code they desire.

I'll try to get my query working using your sample as a template. I think I owe you a beer if I get it working!
 
how can i use this is a script like the "safezone's"? currently. I want to pull/store the user id in a database and place them in the script.

would it work like this? or do i need to then format it somehow to look like this "123141" , "345353" , "456353"
Code: 
// define query
    _key = format ["CHILD:999: select playerid from player WHERE instance_id= '%1' AND id > ?:[0]:", _playerid];    // format the sql call for the hiveext.dll
    _result = _key call server_hiveReadWrite;  // execute the hive call
 
    _status    = _result select 0;            // get the status of the result
 
    if (_status == "CustomStreamStart") then {    //check if the stream coming from the hive was opened
        _val = _result select 1;                  // get the number of entries that will be coming in the stream
        diag_log format["What pulled: Value of - _val : %1",_val];
    };
   
    _result = _key call server_hiveReadWrite;
    _tbl_plyr_id = _result select 0;            // will contain your actual value
   
   
    if ((getPlayerUID player) == ((_tbl_plyr_id)) exitWith {
titleText ["your id is in the database", "PLAIN DOWN", 3];
};
titleText ["your id is not the database", "PLAIN DOWN", 3];
 
machine said:
how can i use this is a script like the "safezone's"? currently. I want to pull/store the user id in a database and place them in the script.

would it work like this? or do i need to then format it somehow to look like this "123141" , "345353" , "456353"
Code: 
// define query
    _key = format ["CHILD:999: select playerid from player WHERE instance_id= '%1' AND id > ?:[0]:", _playerid];    // format the sql call for the hiveext.dll
    _result = _key call server_hiveReadWrite;  // execute the hive call
 
    _status    = _result select 0;            // get the status of the result
 
    if (_status == "CustomStreamStart") then {    //check if the stream coming from the hive was opened
        _val = _result select 1;                  // get the number of entries that will be coming in the stream
        diag_log format["What pulled: Value of - _val : %1",_val];
    };
 
    _result = _key call server_hiveReadWrite;
    _tbl_plyr_id = _result select 0;            // will contain your actual value
 
 
    if ((getPlayerUID player) == ((_tbl_plyr_id)) exitWith {
titleText ["your id is in the database", "PLAIN DOWN", 3];
};
titleText ["your id is not the database", "PLAIN DOWN", 3];
Click to expand...

Well for starters, your query assumes that there is a table called "player" here is what the query will look like with your _key string:

select playerid from player WHERE instance_id= '_playerid' AND id > 0

I think you want to format your _key statement like this:
Code: 
 _key = format ["CHILD:999: select playerid from player WHERE instance_id= ? AND id = '%1':[1]:", _playerid];    // format the sql call for the hiveext.dll

assuming that [1] is your instance number. I'm not at a spot where I can test this but it looks right...

assuming we wanted to go full param mode:
Code: 
 _key = format ["CHILD:999: select playerid from player WHERE instance_id= ? AND id = ?:[1,%1]:", _playerid];    // format the sql call for the hiveext.dll

not sure if you need single quotes around the param makers (?)
 
thanks will also try this out when I'm off work. thank you! I think i can use the instance id part for the different safe zones.

@OP sorry for highjacking :p
 
Sarge said:
actually i think you dont even need the ?/[0]. Thats just the way to substitute in variables in the hive call.

you could also do a direct

_sqlstring ="CHILD:999: select id from building WHERE class_name= 'barrack' AND id > 0";

need to give that a try though.

cheers, Sarge
Click to expand...


hey sarge, take a look at this code here. The query is valid, I have checked with a good playerid and it works fine.
Code: 
              _dotheDamnThing = 1;
 
if (_dotheDamnThing == 1) then {
 
// define query
//_key = format ["CHILD:999: select id from building WHERE class_name= '%1' AND id > ?:[0]:", _classname];    // format the sql call for the hiveext.dll
_key = format["CHILD:999:select replace(cl.`inventory`, '""', '""""') inventory, replace(cl.`backpack`, '""', '""""') backpack, replace(coalesce(cl.`model`, 'Survivor2_DZ'), '""', '""""') model from `cust_loadout` cl join `cust_loadout_profile` clp on clp.`cust_loadout_id` = cl.`id` where clp.`unique_id` = '?':[%1]:",_playerID];
_result = _key call server_hiveReadWrite;  // execute the hive call
_status    = _result select 0;            // get the status of the result
 
if (_status == "CustomStreamStart") then {    //check if the stream coming from the hive was opened
_val = _result select 1;                  // get the number of entries that will be coming in the stream
diag_log format["SARGE DEBUG: Value of - _val : %1",_val];
if(_val > 0) then {
_result = _key call server_hiveReadWrite;
_inventory = call compile (_result select 0);
_backpack = call compile (_result select 1);
_model = call compile (_result select 2);
};
};
};

returns this error:
Code: 
14:24:06 Error in expression <","_resultArray","_data"];
_key = _this select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error position: <select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error select: Type String, expected Array,Config entry
14:24:06 File z\addons\dayz_server\init\server_functions.sqf, line 120
14:24:06 Error in expression <","_resultArray","_data"];
_key = _this select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error position: <select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error Generic error in expression
14:24:06 File z\addons\dayz_server\init\server_functions.sqf, line 120


I am not seeing a "child:999" in the hiveExt.log file but I do see other "child:" calls there:
Code: 
2013-05-07 16:48:12 HiveExt: [Debug] Original params: |CHILD:302:1:|
2013-05-07 16:48:12 HiveExt: [Information] Method: 302 Params: 1:
 
Hi guys and Sarge,

Many thanks, but I'm a bit stuck with the parameters at the end of the string:
WHERE instance_id= '%1' AND id > ?:[0]:", _playerid];
Click to expand...

Are those particular conditions necessary? Does one have to include conditions of instance_id and id?

In context, I have created a custom table player_domes containing two fields, dome_id and allowed_players.
  • dome_id contains a name of an individual to whom a base belongs, for example "johndoe"
  • allowed_players contains a list of GUIDs allowed to enter that person's dome, for example "12345678, 12345679"
What I'm confused on I guess is:
  • Do I have to use parameters at all? Is it a best practice thing? Will not using them work? If I do have to use them, I'm not clear on how to format them or include them.
  • Do I have to include insance_id and id as conditions?
Would I be looking at something like this? Would this work without parameters? If not, what would the equivalent be if it were altered to use parameters? That might clear up the parameter thing for me actually, as I could see it in context...

_key = format ["CHILD:999: select allowed_players from player_domes WHERE dome_id= 'johndoe'"];
Click to expand...

If I wanted to alter that, say I wanted to use a variable in there to check dome_id with, and wanted to set that variable = johndoe before the script began, what would I be looking at then? Could I use a variable as a parameter? Or would I have to put "johndoe" as a parameter directly? Would this be it?

_owner = "johndoe"
_key = format ["CHILD:999: select allowed_players from player_domes WHERE dome_id= ?:[%1]:", _owner];
Click to expand...

Thanks for your help!
 
hambeast said:
hey sarge, take a look at this code here. The query is valid, I have checked with a good playerid and it works fine.
Code: 
              _dotheDamnThing = 1;
 
if (_dotheDamnThing == 1) then {
 
// define query
//_key = format ["CHILD:999: select id from building WHERE class_name= '%1' AND id > ?:[0]:", _classname];    // format the sql call for the hiveext.dll
_key = format["CHILD:999:select replace(cl.`inventory`, '""', '""""') inventory, replace(cl.`backpack`, '""', '""""') backpack, replace(coalesce(cl.`model`, 'Survivor2_DZ'), '""', '""""') model from `cust_loadout` cl join `cust_loadout_profile` clp on clp.`cust_loadout_id` = cl.`id` where clp.`unique_id` = '?':[%1]:",_playerID];
_result = _key call server_hiveReadWrite;  // execute the hive call
_status    = _result select 0;            // get the status of the result
 
if (_status == "CustomStreamStart") then {    //check if the stream coming from the hive was opened
_val = _result select 1;                  // get the number of entries that will be coming in the stream
diag_log format["SARGE DEBUG: Value of - _val : %1",_val];
if(_val > 0) then {
_result = _key call server_hiveReadWrite;
_inventory = call compile (_result select 0);
_backpack = call compile (_result select 1);
_model = call compile (_result select 2);
};
};
};

returns this error:
Code: 
14:24:06 Error in expression <","_resultArray","_data"];
_key = _this select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error position: <select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error select: Type String, expected Array,Config entry
14:24:06 File z\addons\dayz_server\init\server_functions.sqf, line 120
14:24:06 Error in expression <","_resultArray","_data"];
_key = _this select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error position: <select 0;
 
_data = "HiveEXT" callExtensi>
14:24:06  Error Generic error in expression
14:24:06 File z\addons\dayz_server\init\server_functions.sqf, line 120


I am not seeing a "child:999" in the hiveExt.log file but I do see other "child:" calls there:
Code: 
2013-05-07 16:48:12 HiveExt: [Debug] Original params: |CHILD:302:1:|
2013-05-07 16:48:12 HiveExt: [Information] Method: 302 Params: 1:
Click to expand...

How does your function in server_functions look like ? Mine looks like this:

Code: 
server_hiveReadWrite = {
    private["_key","_resultArray","_data"];
    _key = _this;
    //diag_log ("ATTEMPT READ/WRITE: " + _key);
    _data = "HiveExt" callExtension _key;
    //diag_log ("READ/WRITE: " +str(_data));
    _resultArray = call compile format ["%1",_data];
    _resultArray
};

Do a search through your server files and see how the hiveext is called, and mimic that one in your code.
 
Andy Murtagh said:
Hi guys and Sarge,

Many thanks, but I'm a bit stuck with the parameters at the end of the string:


Are those particular conditions necessary? Does one have to include conditions of instance_id and id?

In context, I have created a custom table player_domes containing two fields, dome_id and allowed_players.
  • dome_id contains a name of an individual to whom a base belongs, for example "johndoe"
  • allowed_players contains a list of GUIDs allowed to enter that person's dome, for example "12345678, 12345679"
What I'm confused on I guess is:

  • Do I have to use parameters at all? Is it a best practice thing? Will not using them work? If I do have to use them, I'm not clear on how to format them or include them.
  • Do I have to include insance_id and id as conditions?
Would I be looking at something like this? Would this work without parameters? If not, what would the equivalent be if it were altered to use parameters? That might clear up the parameter thing for me actually, as I could see it in context...




If I wanted to alter that, say I wanted to use a variable in there to check dome_id with, and wanted to set that variable = johndoe before the script began, what would I be looking at then? Could I use a variable as a parameter? Or would I have to put "johndoe" as a parameter directly? Would this be it?



Thanks for your help!
Click to expand...

I was struggeling with the syntax for the hiveext as well, had the same questions that you have. Documentation is not the strong side of the hiveext package .-)

a) you only have to include instance_id if your db holds different instances, or you are on the official hive. If you are running a dedicated private hive, you only have data for one instance in there and the usage shouldnt be needed. Especially in your case where you are working off a custom table.

The formatting / parameters are only needed IF you want to pass / alter parameters while calling the db read/write. Afaics your syntax is correct,

Code: 
_owner = "johndoe"
_key = format ["CHILD:999: select allowed_players from player_domes WHERE dome_id= %1", _owner];

should do the same thing.

On another note - rethink your db structure, to make it easier to maintain / retrieve / index data.

- dome_id (autoincrement)
- dome_owner (char), i would use the UID and not a name
- dome_allowed_users (char, write an array to it, and convert what you get back to an array to add/remove entries programatically)

Sarge
 
Sarge said:
How does your function in server_functions look like ? Mine looks like this:

Code: 
server_hiveReadWrite = {
    private["_key","_resultArray","_data"];
    _key = _this;
    //diag_log ("ATTEMPT READ/WRITE: " + _key);
    _data = "HiveExt" callExtension _key;
    //diag_log ("READ/WRITE: " +str(_data));
    _resultArray = call compile format ["%1",_data];
    _resultArray
};

Do a search through your server files and see how the hiveext is called, and mimic that one in your code.
Click to expand...

I blew that code out and started with a fresh player_login.sqf . I was able to get it to stop erroring but for some reason the code for _isNew was failing. Kept complaining about expecting an array so I left it as default which seems to be ok for just testing my db calls.

When I get to my code, I call the query I keep getting a status of any.


On the note of your previous post, I am seeing multiple ways they are passing child calls to the hive

ex 1:
Code: 
_key = format["CHILD:101:%1:%2:%3:",_playerID,dayZ_instance,_playerName];
_primary = [_key,false,dayZ_hivePipeAuth] call server_hiveReadWrite;
if (count _primary > 0) then {

ex 2:
Code: 
_key = format["CHILD:203:%1:%2:%3:",_charID,[_wpns,_mags],[_bcpk,[],[]]];
_key call server_hiveWrite;
diag_log format ["server_playerLpg = %1",_key];

server_functions hive functions
Code: 
server_hiveWrite = {
private["_data"];
 
_data = "HiveEXT" callExtension _this;
 
 
};
 
server_hiveReadWrite = {
private["_key","_resultArray","_data"];
_key = _this select 0;
 
_data = "HiveEXT" callExtension _key;
 
_resultArray = call compile format ["%1;",_data];
 
_resultArray;
};

I just have a sneaking feeling that they disabled the child:999 call in this hive.ini
 
I'm not having much luck with this at all =/

I think child:999 is fine for me because when I look in HiveExt.log I see:
2013-05-10 20:10:08 HiveExt: [Information] Method: 999 Params: select payload, loop_interval, start_delay from message where instance_id = ?:[xxxx]:
Click to expand...
So I suspect I'm ok on the using of 999, does that sound like a fair assumption based on the above?

The reason I'm not having a lot of luck here I think is that I have put several debug messages in the script to identify where it's running and what it's doing but I can find none of my debug messages in any of the other files in which I can see debug messages.

The problem is, even when I go back to using my old, working script which is fully functional but with no sql pull and the details hard coded into it and just put a debug message in the top:
diag_log format["QUAN DEBUG: start of script"];
Click to expand...
I STILL get no output... Am I just looking in entirely the wrong place? Where are these debug messages supposed to be recorded?

/pout

Thanks all!
 
Right, getting somewhere. I found my debug messages. \o/

I have the sql pull in my script now, and it's not erroring at all. I'm getting results from my debug messages, but they're not what I want =/ I'm using the following code for the pull:
Code: 
// Define owner of the dome to reference the database table for the whitelist
_dome_id = 1;
 
// define query
_key = format["CHILD:999: select allowed_player_guid from player_domes WHERE id=%1", _dome_id];    // format the sql call for the hiveext.dll
diag_log format["QUAN DEBUG: Value of _key: %1",_key];
_result = _key call server_hiveReadWrite;  // execute the hive call
diag_log format["QUAN DEBUG: Value of _result: %1",_result];
 
_status = _result select 0;            // get the status of the result
diag_log format["QUAN DEBUG: Value of _status: %1",_status];
 
if (_status == "CustomStreamStart") then {    //check if the stream coming from the hive was opened
        _val = _result select 1;                  // get the number of entries that will be coming in the stream
        diag_log format["QUAN DEBUG: Value of stream check: %1",_val];
};
 
_result = _key call server_hiveReadWrite;
diag_log format["QUAN DEBUG: Value of _result: %1",_result];
 
_whitelist = _result select 0;            // will contain your actual value
diag_log format["QUAN DEBUG: Value of whitelist: %1",_whitelist];

In my ArmA2OA.RPT file I get the following output from my messages:
"QUAN DEBUG: Value of _key: CHILD:999: select allowed_player_guid from player_domes WHERE id=1"
"QUAN DEBUG: Value of _result: any"
"QUAN DEBUG: Value of _status: any"
"QUAN DEBUG: Value of _result: any"
"QUAN DEBUG: Value of whitelist: any"
Click to expand...

From what I can see, everything in the first debug message is correct, if I take that same snippet and run it as an SQL query inside of my editor it correctly returns a cell containing my GUID, so I'm pretty sure we're right on the money there...

I have also tried it using CHILD:504 as hambeast suggests and I get the same issue:
"QUAN DEBUG: Value of _key: CHILD:504:select allowed_player_guid from player_domes WHERE id=1"
"QUAN DEBUG: Value of _result: any"
"QUAN DEBUG: Value of _status: any"
"QUAN DEBUG: Value of _result: any"
"QUAN DEBUG: Value of whitelist: any"
Click to expand...

The remaining debug messages however would seem to indicate that nothing is getting back to the script. Does anyone have any thoughts on what's causing that?

A.
 
Hi Sarge, Hambeast and all,

I've not managed to progress any further than this and can't figure out why the script is returning "any". Can anyone offer any assistance or suggestions on the script above?

Many thanks!
A.
 
This is where I'm stuck at too. I'll let you know if I work around it.

Sent from my Galaxy Nexus using Tapatalk 2
 
I haven't Sarge, I'm fairly new to this unfortunately and wasn't aware that extra debugging options were available. That would be very helpful in trying to find a resolution to it. Could you point me in the direction of tutorials to do so?
 
You must log in or register to reply here.
Back
Top