SQL Help

[Ghost_Sailor]

I am hopeless when it comes to databases, especially when I can't get feedback in the form of errors.

[lua]
function StoreData(object, ply, value)
local r = sql.Query("SELECT "..object.." FROM Players WHERE steam = '" .. sql.SQLStr(ply:SteamID()) .. "';")
if r then
sql.Query("UPDATE Players SET "..object.." = " .. sql.SQLStr(value) .. " WHERE steam = '" .. sql.SQLStr(ply:SteamID()) .. "';")
ply:ChatPrint(sql.Query("SELECT "..object.." FROM Players WHERE steam = '" .. sql.SQLStr(ply:SteamID()) .. "';"))
else
sql.Query("INSERT INTO Players('steamid', '"..object.."') VALUES('"..sql.SQLStr(ply:SteamID()).."', '"..sql.SQLStr(value).."');")
ply:ChatPrint(sql.Query("SELECT "..object.." FROM Players WHERE steam = '" .. sql.SQLStr(ply:SteamID()) .. "';"))
end
end
[/lua]

I have this, so I can modularly save data. The only thing is, it doesn't save. I added the ChatPrint stuff to see if it was even saving the data, and it says
[ERROR] gamemodes/testing/gamemode/saves.lua:76: attempt to concatenate a nil value

I call it as [lua]StoreData("kills", attacker, attacker:Frags())[/lua]

What am I doing wrong? Also, that code is probably really inefficient, so how would I make it better?

[G4MB!T]

[lua]
function StoreData(object, ply, value)
local r = sql.Query("SELECT `"..object.."` FROM `Players` WHERE `steam` = '" .. ply:SteamID() .. "';")
if r then
sql.Query("UPDATE `Players` SET `"..object.."` = " .. sql.SQLStr(value) .. " WHERE steam = '" .. ply:SteamID() .. "';")
else
sql.Query("INSERT INTO `Players`(steamid, "..object..") VALUES('"..ply:SteamID().."', '"..sql.SQLStr(value).."');")
end
end
[/lua]

steamids are already sql safe

[editline]29th September 2013[/editline]

also it might help to look into this but its more or less optional

[URL="http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html"]http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html[/URL]

[Ghost_Sailor]

[QUOTE=G4MB!T;42343047][lua]
function StoreData(object, ply, value)
local r = sql.Query("SELECT `"..object.."` FROM `Players` WHERE `steam` = '" .. ply:SteamID() .. "';")
if r then
sql.Query("UPDATE `Players` SET `"..object.."` = " .. sql.SQLStr(value) .. " WHERE steam = '" .. ply:SteamID() .. "';")
else
sql.Query("INSERT INTO `Players`(steamid, "..object..") VALUES('"..ply:SteamID().."', '"..sql.SQLStr(value).."');")
end
end
[/lua]

steamids are already sql safe

[editline]29th September 2013[/editline]

also it might help to look into this but its more or less optional

[URL="http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html"]http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html[/URL][/QUOTE]

Are they supposed to be the little ` gravemarks, or the ' single quotes?

[EvacX]

[QUOTE=Ghost_Sailor;42343209]Are they supposed to be the little ` gravemarks, or the ' single quotes?[/QUOTE]

Tables and columns use ` and values use '

[Acecool]

Escape everything, including SteamIDs. There was a time when SteamIDs could be spoofed, and there's an age-old saying in the computer science world to never trust user-input/data. Because, if something does happen where spoofed SteamIDs happen again, and that segment of your DB is not-protected, and vulnerable to injection exploits, goodbye database! Escape it!

You really don't need the `'s for tables / columns. I do use " or 's for data though.

[EvacX]

[QUOTE=Acecool;42344772]Escape everything, including SteamIDs. There was a time when SteamIDs could be spoofed, and there's an age-old saying in the computer science world to never trust user-input/data. Because, if something does happen where spoofed SteamIDs happen again, and that segment of your DB is not-protected, and vulnerable to injection exploits, goodbye database! Escape it[/QUOTE]

Definitely this.

[QUOTE=Acecool;42344772]You really don't need the `'s for tables / columns. I do use " or 's for data though.[/QUOTE]

You should, for example in a case where the name of your column conflicts with an SQL keyword.

[Luni]

[QUOTE=EvacX;42345540]Definitely this.

You should, for example in a case where the name of your column conflicts with an SQL keyword.[/QUOTE]

As long you use table/column prefixes you should be fine.