SQL Wrapper

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*, true) .. ")"
		else
			query = query .. sql.SQLStr(data*, 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*, true) .. " ) VALUES ("
		else
			query = query .. sql.SQLStr(Colum*, true) .. ", "
		end
	end
	
	for i = 1, table.Count(Value) do
		if i == table.Count(Value) then
			query = query .. "'" .. sql.SQLStr(Value*, true) .. "')"
		else
			query = query .. "'" .. sql.SQLStr(Value*, 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*
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.

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

sql keywords aren’t case sensitive.

my life has been based on a lie

Prevent SQL injection and it will be perfect!

Use

sql/SQLStr

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

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]

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

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.

yea i seen that to going to fix it.

I always thought SQL keywords had to always be uppercase. :hammered:

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.

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

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)