Simple SQLite Interface - Garry's Mod

Howdy there! This post is for a small interface I made for SQLite that gives you the benefits of SQLite without having to deal with error handling and commands. Hope this helps some developers out there! I am well aware that there is a built in library for this kind of thing but this gives an even more simplified interface, which in my case, helped speed up my development time greatly on a lot of projects. [B]Download:[/B] Snippet: [URL]https://gist.github.com/Cornfinder/d32ed180cf5309df61517a9179603ddf[/URL] Snippet (Module Version): [url]https://gist.github.com/Cornfinder/09ecdbe2dfb89af133d748671abd8d67[/url] [I]or[/I] Addon: [url]https://drive.google.com/file/d/0B544dfxEUEUxUWpVdHFqdVhYRk0/view[/url] [B]Installation:[/B] Snippet: Insert the file somewhere in the lua realm and call it with the include function or just throw it in the server side autorun folder. Module Version (Thanks Apickx): Put the module version in your project and include it like this [CODE] local ezdb = include("sv_sqlModule.lua") // You could also use the require function [/CODE] [B]Functions and their descriptions:[/B] [U]Some Notes[/U]: The parameter "obj" for all functions can be a player or a string, else, it will throw an error in the gists of ".. is not a valid identifier". All functions will return true on success and false on failure. An object or obj in this context means the player/string you inputted for the parameter "obj" The functions not included below are internal helper functions and can be added to a separate list if someone wants it! This library has the same limitations as the file functions [B]Using a player as the object will run the function "ply:SteamID()" and it might return nil if called too early[/B] [CODE]ezdb.Alive(db)[/CODE] Checks if a database exists and creates it if it does not. [CODE]ezdb.Purge(db)[/CODE] Deletes the database inputted [CODE]ezdb.Exists(db, obj)[/CODE] Checks if the object is in the database. [CODE]ezdb.Create(db, obj)[/CODE] Adds an object to the database so it can be written to and read from. [CODE]ezdb.Delete(db, obj)[/CODE] Deletes an object from the database [CODE]ezdb.Read(db, obj)[/CODE] Reads the table assigned to the object. [CODE]ezdb.Write(db, obj, data)[/CODE] Writes over the table assigned to object. [B]Examples (Put in snippets to save space):[/B] This is an example of a basic wallet system [url]https://gist.github.com/Cornfinder/f6a00d58364ac2d1496249749e9a15a9[/url] Other examples canceled due to low popularity. [B]Changelog:[/B] - Added Table Sanitation - Now uses sql.SQLStr on strings

Very nice, but why does everyone insist on polluting the global namespace?

[QUOTE=Apickx;50710821]Very nice, but why does everyone insist on polluting the global namespace?[/QUOTE] Thank you. What should I have done instead of making it global?

[QUOTE=Cornfinder;50710835]Thank you. What should I have done instead of making it global?[/QUOTE] Don't listen to him, you did nothing wrong.

[QUOTE=man with hat;50710860]Don't listen to him, you did nothing wrong.[/QUOTE] :S [QUOTE=Cornfinder;50710835]Thank you. What should I have done instead of making it global?[/QUOTE] Well, the usual way to do modules like this is [CODE] local module = {} module.myfunction = ... : : : return module [/CODE] Then in another file, you would do this [CODE] local hismodule = require("hismodule") --Or, as is more common in glua local hismodule = include("hismodule.lua") hismodule.myfunction() [/CODE] I've noticed a lot of people like throwing their stuff in the global namespace, even big projects that should know better like mysqloo, what's up with that?

Thanks, Apickx. Added module version encase anyone else wanted it.

[QUOTE=Apickx;50710981] I've noticed a lot of people like throwing their stuff in the global namespace, even big projects that should know better like mysqloo, what's up with that?[/QUOTE] GMod didn't support file level returning until recently.

Sick! Now people can create database tables that allow for easy injection without their knowledge! Please escape the text so people can't accidentally get their servers hacked for not knowing how to properly secure their SQLite syntax.

[QUOTE=xbeastguyx;50714541]Sick! Now people can create database tables that allow for easy injection without their knowledge! Please escape the text so people can't accidentally get their servers hacked for not knowing how to properly secure their SQLite syntax.[/QUOTE] Could you please give a little more detail?

[QUOTE=xbeastguyx;50714541]Sick! Now people can create database tables that allow for easy injection without their knowledge! Please escape the text so people can't accidentally get their servers hacked for not knowing how to properly secure their SQLite syntax.[/QUOTE] Sanitation doesn't belong here, you should be escaping user-enetered strings before giving it to ezdb. There's no reason to spend time escaping steamid's, scores, or most other data you would want to save. [QUOTE=Cornfinder;50714541]Could you please give a little more detail? [/QUOTE] [URL="http://www.w3schools.com/sql/sql_injection.asp"]SQL is a very common vulnerability, and you should know about it and how to prevent it before working with SQL.[/URL] For example, if you were to save player names and scores at the end of each round, with this statement: [code] ezdb.Write("scoredb",ply,{ply:Nick(),ply:Frags()}) [/code] and I had the name [B]'; DROP TABLE scoredb --[/B], then the sql string would become [B]"UPDATE scoredb SET dat = '["'; DROP TABLE scoredb --",5]' WHERE id = '0:0:0:0'[/B]" To prevent it, you need to make the code: [code] ezdb.Write("scoredb",ply,{sql.SQLStr(ply:Nick()),ply:Frags()}) [/code]

All data that is going into a database should be sanitised , I can't think of a situation where you wouldn't unless you needed to append SQL to a query. Asking the caller to sanitise the strings themselves doesn't make sense. In the case of a steamid you wouldn't need to sanitise it, but considering there is little overhead and libraries like these are supposed to abstract that sort of behaviour away. Better safe than sorry.

Good information Apickx, updated it. Example was very helpful. Also, nice catch KillerLUA. Fixed the sanitation and SQL injection in the latest update.