SQL Example

[Mitchell]

Ive been searching for a while now, and this is the only thing bothering me wondering how to work it, SQL im kindly asking some one to post a little plugin the saves and gets a value from a local SQL database so users could learn from it. Im a better learner when i have an actual code snippet to work off of, and right now i cant find a simple plugin to do that with out getting a lot of values saved etc. Im not asking for the sourcemod link to SQL tutorial, i have already looked at it a dozen times and still seems foreign for me.

[Impact123]

Actually it's not that hard, but there are many ways to do it depending on what you want to do.
How many rows you have, how many fields you have, is it numerical, should the database created or does it exist, et cetera.
If you have an example which is more near to what you want to achieve it should be better huh?
Also i mostly use my DBhandle globally and never close it, most ppl create it only by needing AFAIK.

Yours sincerely
Impact

[Mitchell]

Thanks for the information impact, maybe i should say as an example as like storing... lets say a time like: GameTime();, or maybe even better a player's played time and storing it, and getting it when ever a command is called? I dont know if any body would like to help me out with that would they? Learning purposes only of course.

[Impact123]

Okay, i wrote up this short example, i am 100% sure it is not the best way to do this, im not so much an user of SQL.
I've commented every second line, so you should have no problem understand it.
I have learned from: Last connect (http://forums.alliedmods.net/showthread.php?p=518734), CSS Bank (https://forums.alliedmods.net/showthread.php?p=1109391), SoD Stats (https://forums.alliedmods.net/showthread.php?p=588120), and a few more i can't remember.

I have an older example with player rounds from where i began scripting SourcePawn, i can send it to you privately, you'll see the reason ;).

Yours sincerely
Impact

[Mitchell]

Thanks man! I can't wait to look at it! :D

actually i would love to see your other round script, thanks!

[lokizito]

The only thing missing in Impact's example is that whenever you get a value from a player, (from cmds args, name, etc), you should always use SQL_EscapeString to avoid SQL injection.

[Mitchell]

The only thing missing in Impact's example is that whenever you get a value from a player, (from cmds args, name, etc), you should always use SQL_EscapeString to avoid SQL injection.

What do you mean?

[lokizito]

For example, in this query:
Format(Query, sizeof(Query), "INSERT OR IGNORE INTO players VALUES ('%s', '%s')", Id, Name);
If, the Name string was from GetClientName, or GetCmdArg, it could contain like '. It could break your query and be used for making another queries. For example if the player name was something like
sometext'); DELETE FROM players WHERE 1=1

Your query would be executed, but also, it would delete all the records from your table.
Did i made it clear enough (not very good at explanations)?

EDIT: Using SQL_EscapeString make sure that your query won't be changed by any character that would do it, by escaping then on the database, like ' becomes \' and so.

[Dr. McKay]

For example, in this query:
Format(Query, sizeof(Query), "INSERT OR IGNORE INTO players VALUES ('%s', '%s')", Id, Name);
If, the Name string was from GetClientName, or GetCmdArg, it could contain like '. It could break your query and be used for making another queries. For example if the player name was something like
sometext'); DELETE FROM players WHERE 1=1

Your query would be executed, but also, it would delete all the records from your table.
Did i made it clear enough (not very good at explanations)?

^^ He's talking about SQL injection. You always need to protect against injection. More info: http://en.wikipedia.org/wiki/SQL_injection

[flamingkirby]

Okay, i wrote up this short example, i am 100% sure it is not the best way to do this, im not so much an user of SQL.
I've commented every second line, so you should have no problem understand it.
I have learned from: Last connect (http://forums.alliedmods.net/showthread.php?p=518734), CSS Bank (https://forums.alliedmods.net/showthread.php?p=1109391), SoD Stats (https://forums.alliedmods.net/showthread.php?p=588120), and a few more i can't remember.

I have an older example with player rounds from where i began scripting SourcePawn, i can send it to you privately, you'll see the reason ;).

Yours sincerely
Impact

Wow. Thanks for this simple example even though it was not meant for me. Was trying to find out how to do this too as i find the wiki page useless to some extent =/.

[Impact123]

Please note that this code is rather old and not the best way, it was only meant as an quick example.
There are security issues in it and the connection isn't threaded, which means it could/will pause the server until a connection is made.
For a more intermediate example i currently can only link you to this (https://github.com/Impact123/CallAdmin/blob/development/gameserver/calladmin_mysql.sp), it's not optimal or trivial, but it's commented and uses all common tasks you might need to know.