Creating a custom item system. I need a pointer.

[Jiroxys7]

Okay, so i'm learning SQLite (great guide, chris. cant wait to see the rest of it!)
Thing is, I want my item system to be able to have varying stats, such as a semi-random durability value. Can SQLite support this? or would I be better off with another system?

Also, if i stick with SQLite, what are these security issues, and how should i go about preventing them?

edit: Lastly, if it make any difference, I'm looking to be able to incorporate a bag size limit too.

[cbk1994]

Any form is SQL is basically a table. The way you use it is what determines its capabilities (e.g. unique item properties).

The security issues with SQL (including other SQL variants like MySQL) are generally SQL injection.

For example, if you had a table which stored a player's kills and deaths:

PHP Code:

+-----------+------+-------+
|account    |kills |deaths |
+-----------+------+-------+
|Stefan     |100   |20     |
|unixmad    |0     |132    |
|cbk1994    |23    |92     |
+-----------+------+-------+ 


And had a player command like "/checkstats Stefan", your code might be like

PHP Code:

function onActionServerSide(cmd, acc) {
  if (cmd == "checkStats") {
    temp.req = requestSQL("SELECT * FROM points WHERE account = '" @ acc @ "'", true);
    temp.stats = {req.rows[0].kills, req.rows[0].deaths};
    player.chat = format("Stats for %s: %s (kills), %s (deaths)", acc, stats[0], stats[1]);
  }
}
// assume there is a clientside trigger 


This may seem totally benign but a tricky player might use the chat command

/checkpoints 'error lul

which would turn your SQL into

SELECT * FROM points WHERE account = ''error lul'

which would obviously cause a syntax error, and depending on the situation, could be a problem. It's not hard to see how you could cause a problem like this. Take a bank system, for example, where a player is changing their PIN

PHP Code:

UPDATE bank SET pin = '1234' WHERE account = 'cbk1994' 


If the PIN can be anything, the player could do this:

PHP Code:

UPDATE bank SET pin = '1234', balance = 100000, account = 'cbk1994' WHERE account = 'cbk1994' 


This is a perfectly valid SQL query but could be abused. The PIN they would use to cause this problem is

1234', balance = 100000, account = 'cbk1994

You see the problem?

Fortunately it's easy to fix.

Instead of:

PHP Code:

req("UPDATE bank SET pin = '" @ pin @ "' WHERE account = '" @ player.account @ "'"); 


you use:

PHP Code:

req("UPDATE bank SET pin = '" @ pin.escape() @ "' WHERE account = '" @ player.account.escape() @ "'"); 


Notice that I used str.escape(). This turns a string like "Chris' house" to "Chris'' house". The way it is escaped is different between different SQL variants. When SQLite reads "Chris'' house", it knows that the two apostrophes are for escaping and that there should really just be one, so the string is still inserted as "Chris' house".

Essentially,

Originally Posted by http://www.cgisecurity.com/questions/sql.shtml

SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it.

Sorry for the long reply, eventually that would have been part of the guide.

[fowlplay4]

While making an SQL Item System has it's advantages:

- Simple backup of server items
- Easy analysis your the server's items
- Transactions

If you don't have any experience with SQL you can end up doing very bad things particularly making inefficient tables and queries that will cause a significant performance loss compared to making a simple clientr flag based one.

If you design your item system properly you should be able to "plug-in" an SQL back-end to it with ease.

[adam]

Originally Posted by fowlplay4

.....
If you design your item system properly you should be able to "plug-in" an SQL back-end to it with ease.

I suggest this. Worry about a good design first. SQL later.

[WhiteDragon]

Originally Posted by adam

i suggest this. Worry about a good design first. Sql later.

+1

[Jiroxys7]

So best thing in my case would be to make a well-working clientr.-based system then worry about adding SQL to it later down the road? If that's the case then that works out perfectly. especially since I'm becoming increasingly more focused on having a server that's actually playable. I was just kind of concerned that I might not be able to do that whole plugging-in thing if i made it clientr.-based. It's great to know that I probably wont have to rescript the entire system =]

[cbk1994]

Originally Posted by Jiroxys7

So best thing in my case would be to make a well-working clientr.-based system then worry about adding SQL to it later down the road? If that's the case then that works out perfectly. especially since I'm becoming increasingly more focused on having a server that's actually playable. I was just kind of concerned that I might not be able to do that whole plugging-in thing if i made it clientr.-based. It's great to know that I probably wont have to rescript the entire system =]

The key to most modular systems is using functions instead of accessing variables directly.

In other words, if you're loading the values of an item, have a player function to handle that. If you ever switch to a SQL-based storage system, you can easily change the backend. This is what was done on Era Dev when SQL was announced with about 5 lines of extra code.

[Jiroxys7]

Originally Posted by cbk1994

The key to most modular systems is using functions instead of accessing variables directly.

Hm. could you give me an example on the difference between those? I just want to be perfectly clear on it. Are you saying to uh.. like...

crap, I cant think of anything xP

[fowlplay4]

Basic item system (nothing MUD-related though):

class: player_itemfunctions

PHP Code:

public function addItem(itemname, qty) {
  // Increment quantity of the item
  clientr.item.(@itemname) += qty;
}

public function takeItem(itemname, qty) {
  // Decrement quantity of the item
  clientr.item.(@itemname) -= qty;
  // Check for non-existent quantity value
  // I.e: No sense having an item with a quantity of -3
  if (clientr.item.(@itemname) <= 0) {
    // Destroy Item Flag 
    clientr.item.(@itemname) = "";
  }
} 


Then in your Control-NPC:

PHP Code:

function onActionPlayerOnline() {
  player.join("player_itemfunctions");
} 


Now in other scripts (on the serverside) you can add items like this:

player.addItem("Amazing Sword of Doom", 1);

or

findplayer("Jiroxys7").addItem("Broken Sword of Fail", 100);

Which would add/set a flag like this in your player flags:

clientr.item.Amazing Sword of Doom=1

[cbk1994]

To expand on what Jerret said, you will never access the data directly.

PHP Code:

if (player.chat == "buy sword") {
  if (player.getItemCount("super_sword") >= 0) {
    return player.chat = "You can only have one super sword!";
  }
  
  if (player.getItemCount("lesser_sword") >= 0) {
    player.removeItem("lesser_sword", player.getItemCount("lesser_sword"));
  }
  
  player.addItem("lesser_sword", 1);
} 


Even core scripts like the inventory should use functions like getAllItems to access the items. Then you could easily change code like this

PHP Code:

public function getItemCount(item) {
  return this.clientr.item.(@ item);
} 


to this

PHP Code:

public function getItemCount(item) {
  temp.req = requestSQL("SELECT * FROM items WHERE item = '" @ item.escape() @ "' AND account = '" @ this.account.escape() @ "'", true);
  return req.rows[0].quantity;
} 


without breaking any of your scripts.

[xAndrewx]

If you are using SQL like that, isn't it laggy? (With alot of players...?)

[cbk1994]

Originally Posted by xAndrewx

If you are using SQL like that, isn't it laggy? (With alot of players...?)

It depends a lot more on how you structure your system than the limits of SQL.

A quick example:

PHP Code:

function r(str) {
  echo("+" @ (timevar2 - this.start) @ ": " @ str);
}

function onCreated() {
  this.join("func_sql");
  
  req("DROP TABLE IF EXISTS test_2");
  
  // this only has to be done once
  sqliteEnableFileSynchronization("default", false);
  this.start = timevar2;
  
  // begin transaction
  
  r("Beginning transaction");
  req("BEGIN");
  
  r("Creating table");
  req("CREATE TABLE IF NOT EXISTS test_2 (account TEXT NOT NULL DEFAULT '', itemid TEXT NOT NULL DEFAULT '', quantity INT NOT NULL DEFAULT 0)");
  
  r("Creating index");
  req("CREATE INDEX IF NOT EXISTS idx_test ON test_2 (account, itemid)");
  
  temp.items = {"uzi", "handgun", "shotgun", "ak47", "medpack", "skateboard", "ammo"};
  
  r("Adding 70,000 rows to table");
  temp.c = 0;
  
  // add 70,000 rows to the table
  for (temp.item : items) {
    for (temp.i = 0; i < 10000; i ++) {
      req("INSERT INTO test_2 (account, itemid, quantity) VALUES ('" @ i @ "', '" @ item @ "', " @ int(random(1, 1000)) @ ")");
      
      if (c % this.maxlooplimit == 0) {
        sleep(0.1); // avoid max loop limit
      }
      
      c ++;
    }
  }
  
  r("Done adding items");
  req("COMMIT");
  r("Transaction closed");
  
  // reset time
  echo("--time reset--");
  this.start = timevar2;
  
  temp.num = req("SELECT sum(quantity) FROM test_2 WHERE itemid = 'ak47'", true)[0][0];
  r("Total number of AK47s: " @ num);
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num);
  
  req("UPDATE test_2 SET quantity = quantity + 200 WHERE account = '2653' AND itemid = 'ak47'");
  r("Added 200 AK47s to account '2653'");
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num); 


outputs:

+0: Beginning transaction
+0.000104904: Creating table [0.0001 seconds]
+0.000465869: Creating index [0.0003 seconds]
+0.000668048: Adding 70,000 rows to table [0.0002 seconds]
+4.176818847: Done adding items [4.18 seconds]
+4.183884859: Transaction closed [0.01 seconds]
--time reset--
+0.035701036: Total number of AK47s: 4971411 [0.04 seconds]
+0.035872936: How many AK47s account '2653' has: 380 [0.0001 seconds]
+0.036087036: Added 200 AK47s to account '2653' [0.001 seconds]
+0.03618288: How many AK47s account '2653' has: 580 [0.0001 seconds]

SQLite is no pushover .

[Jiroxys7]

Well i had got the basic clientr. thing working, but i've reached a problem where i cant seem to get the vars to set an existing param to a new param. Heres pretty much exactly what i'm currently stuck on. I dont think the formatting of this is right.

PHP Code:

function onActionServerSide(cmd, equipname){
  if(cmd == "equip"){
    clientr.equips[0]={equipname[0]};
    }; 


and even if i get this to work, (though probably a simple fix) that's just putting the name of the equip in the variable for the GUI's to read and tell the player. everything else is still convoluted and disorganized. Basically, I'm trying to set the requirements of the equip, the name of the equip, the set stats of the equip (defense mods), and the other stats (stat bonuses that may not be present in all equipment) and where all of it's params will be stored in clientr.var params.
(so for example, the helm goes in clientr.equips[0] so any given piece doesnt end up with multiple instances of itself in clientr.equips)

then once i get that done, i'm definitely going to run into the issue of figuring out how to load the extra stat bonuses without needing to set empty parameters for every equip.

Then finally, I also have the problem on where to even store the equipment data since i'm using variables instead of keeping them in readable docs or something. (currently i'm storing the equip data in inventory system itself. bad idea, i know. Though I was planning on storing it in a dbnpc. though something's telling me that's not the right thing to do either.)

I'm kind of stuck in a rut here. Should I keep trying to get all of this stuff working through clientr.vars? It seems like all i would need to know to use SQLite is knowledge of how to store the info in the database (arcs?) and how to access the data through scripts instead of trying to re-sort all of the data i have now, tell the script what's what, and where it goes.

Any suggestions?

[xAndrewx]

Oh woah- seems pretty fast. thank you

[Jiroxys7]

Originally Posted by cbk1994

A quick example:

snip

Where can I learn what all of that means? Or better yet, a place that will show and explain to me how to go about reading and searching the tables?

[fowlplay4]

Originally Posted by Jiroxys7

Where can I learn what all of that means? Or better yet, a place that will show and explain to me how to go about reading and searching the tables?

I'd recommend reading some of the articles here:

http://www.w3schools.com/sql/default.asp

and using a dedicated npc for SQL requests:

http://forums.graalonline.com/forums...ad.php?t=85256

and that's all there is to using GS2+SQL.