SQL Wrapper

[Vissaben]

I'm currently working on a project that will use sql in gmod.
So i made a little wrapper to help streamline it a little and keep things neat.
Not sure if any one else has done it as i have not looked and mine by no means has everything.
But as i work more on my project ill be adding more into this.

Have not tested it yet but should work as is, there is no error handling yet but i do plan to add that in.

--Example usage: SQL_Create("My_Table_Name", "User string,Pass string,Member boolean")
--The second var should not have any spaces other then in your colums and there type.
function SQL_Create(Table, String)
	local data = string.Explode(",", String)
	local query = "CREATE TABLE " .. sql.SQLStr(Table, true) .. " ("
	
	for i = 1, table.Count(data) do
		if i == table.Count(data) then
			query = query .. sql.SQLStr(data[i], true) .. ")"
		else
			query = query .. sql.SQLStr(data[i], true) .. ", "
		end
	end
	
	local result = sql.Query(query)
	return result
end

--Example usage: SQL_Insert(My_Table_Name, "User,Pass,Member", "Tom,My_Password,true")
--This will add Tom to the database with User: Tom Password: My_Password Member: true.
function SQL_Insert(Table, colum, value)
	local Colum = string.Explode(",", colum)
	local Value = string.Explode(",", value)
	local query = "INSERT INTO " .. sql.SQLStr(Table, true) .. " ("
	
	for i = 1, table.Count(Colum) do
		if i == table.Count(Colum) then
			query = query .. sql.SQLStr(Colum[i], true) .. " ) VALUES ("
		else
			query = query .. sql.SQLStr(Colum[i], true) .. ", "
		end
	end
	
	for i = 1, table.Count(Value) do
		if i == table.Count(Value) then
			query = query .. "'" .. sql.SQLStr(Value[i], true) .. "')"
		else
			query = query .. "'" .. sql.SQLStr(Value[i], true) .. "'"
		end
	end
	
	local result = sql.Query(query)
	return result
end

--Example usage: SQL_SafeInsert("INSERT INTO My_Table_Name (User, Pass) VALUES (?, ?)", func:GetUser(), func:GetPass())
function SQL_SafeInsert(String, ...)
	local arg = {...}
	local s, e, colum, value = string.find(String, "%b()")
	local repString = "("
	local argCount = table.Count(arg)
	
	for i,v in ipairs(arg) do
		if i == argCount then
			repString = sql.SQLStr(repString, true) .. ")"
		else
			repString = sql.SQLStr(repString, true) .. ", "
		end
	end
	
	local query = string.Replace(String, value, repString)
	local result = sql.Query(query)
	return result
end

--Example usage one: SQL_Select("My_Table_Name", "*", "User", "Tom") --This will get all of Tom's information from the database.
--Example usage two: SQL_Select("My_Table_Name", "User", "Member", "true") --This will get any users that are members.
function SQL_Select(Table, colum, col, value)
	local query = "Select " .. sql.SQLStr(colum, true) .. " FROM " .. sql.SQLStr(Table, true) .. " WHERE " .. sql.SQLStr(col, true) .. " = " .. sql.SQLStr(value, true)
	local result = sql.Query(query)
	return result
end

--This ones simple, all we do is pass it the table we want all the rows from.
function SQL_Select_All(Table)
	local query = ("SELECT * FROM " .. sql.SQLStr(Table, true))
	local result = sql.Query(query)
	return result
end

--This ones simple, all we do is pass it the table we want to count.
function SQL_Select_Count(Table)
	local query = "SELECT COUNT(*) FROM " .. sql.SQLStr(Table, true)
	local result = sql.QueryValue(query)
	return result
end

--Example usage: SQL_Update("My_Table_Name", "Pass", "My_Password", "User", "Tom") -- This will set Tom's password to My_Password.
function SQL_Update(Table, colum, value, col, val)
	local query = "UPDATE " .. sql.SQLStr(Table, true) .. " SET " .. sql.SQLStr(colum, true) .. " = '" .. sql.SQLStr(value, true) .. "' WHERE " .. sql.SQLStr(col, true) .. " = '" .. sql.SQLStr(val, true) .. "'"
	local result = sql.Query(query)
	return result
end

Fix: Value should have been Value[i]
Added: Prevent SQL Injection.
Fix: made result and query local
Added: SafeInsert with injection protection and value detection from args.
Note: Must add args in same order as your colums in SafeInsert.

[Klaes4Zaugen]

In SQL.Select you dont do full caps SELECT in the query

[bigdogmat]

[QUOTE=Klaes4Zaugen;51082343]In SQL.Select you dont do full caps SELECT in the query[/QUOTE]

sql keywords aren't case sensitive.

[Klaes4Zaugen]

[QUOTE=bigdogmat;51082383]sql keywords aren't case sensitive.[/QUOTE]
my life has been based on a lie

[SFAriel]

Prevent SQL injection and it will be perfect!

[Liquidsocks]

Use [img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/sql/SQLStr]sql/SQLStr[/url]

[Vissaben]

will work on that now idk why that skip'd my mind lol

[Luni]

if you want something to improve next, consider a "safe insert" function that automatically escapes and substitutes its arguments
[lua]
sql.SafeInsert("INSERT INTO foobar (name, class) VALUES (?, ?)", foo:GetName(), foo:GetClass())
[/lua]

[Vissaben]

nice idea i like it.
ill see what i can do already have an idea on how to do it to.

[SFAriel]

I just noticed, in your last 4 functions, "result" and "query" aren't local variables. It would be a shame if they were somehow used by other scripts, and you accidently overriden them.

[Vissaben]

yea i seen that to going to fix it.

[zeaga]

[QUOTE=Klaes4Zaugen;51082468]my life has been based on a lie[/QUOTE]

I always thought SQL keywords had to always be [i]upper[/i]case. :hammered:

[Vissaben]

i use to think that, but now i do it just to make it easier to tell the SQL keywords from the rest of the code.

[aftokinito]

[QUOTE=zeaga;51086174]I always thought SQL keywords had to always be [i]upper[/i]case. :hammered:[/QUOTE]

It kinda helps to use uppercase letters with SQL keywords so that you can differentiate the keywords from table names, columns, values, etc.

[zeaga]

[QUOTE=aftokinito;51093265]It kinda helps to use uppercase letters with SQL keywords so that you can differentiate the keywords from table names, columns, values, etc.[/QUOTE]

That's probably why I was taught that way. The thought it wasn't 100% necessary never crossed my mind.

[editline]23rd September 2016[/editline]

Missing 'end' statement for the 'if' block in the second 'for' loop of the function 'SQL_Insert' (Line 38/39)