Is GLON SQL-safe?

[lua]
local newother = glon.encode(info)
tmysql.query(“INSERT INTO players (other) VALUES (’” … newother “’)”, iPly.cb)
[/lua]

After some testing, glon seems to return a string with symbols and whatnot, and I’m not sure if my MySQL database is setup to accept those kinds of characters. Also, the resulting string could also have quotes or other special SQL characters.

Basically I have a column called “other” that I want to store a table that has a mess of player variables that aren’t used often enough to deserve a column.

Does anyone have any suggestions?

Yes, GLON encodes into a string, the only way GLON wouldn’t be safe would be if it possibly had NULLs. Since GLON is entirely in Lua there are no NULLs in the string.

It won’t have quotes or anything that would break the SQL interpretation of the string literal?

That’s why you tmysql.escape.

If I tmysql.escape it then the encoded glon won’t be functional anymore .-.

Edit: Actually what I thought escaping was was actually filtering. How does tmysql.escape work? I don’t really get the documentation

tmysql.escape and THEN encode.

Don’t do that, you escape to make the input safe to INSERT, it doesn’t come back escaped.

Based on my experience, no it’s not safe. At least my version of GLON uses these weird box characters to indicate the level of the table, and most data savers (NWStrings, SQLite, whatever) don’t store it.

Works fine in MySQL.

GLON works fine with MySQL.

Azu is correct.

Encode the data and then escape it for MySQL.

It’s a bit like how you escape in Lua strings.
You may put “John said “Hello!””, but it doesn’t come out being:



John said \"Hello!\"


It comes out being:



John said "Hello!"


Those ‘weird box characters’ are simply characters with low numeric values. They are fine.
From memory, I made GLON escape null characters within strings.
I’m not entirely sure, and I cannot check at the moment.
Would someone please run this code for me:
[lua]
print(string.match(glon.encode({“Oh shit \0null characters!\0”}), “%z”) and “OH SHIT IT DOESN’T ESCAPE THEM!” or “Phew, they’re escaped.”)
[/lua]

I’m pretty sure tmysql.escape should remove the null characters. However, if the queries don’t work, try removing handling them with your own system.

[lua]
] lua_run print(string.match(glon.encode({“Oh shit \0null characters!\0”}), “%z”) and “OH SHIT IT DOESN’T ESCAPE THEM!” or “Phew, they’re escaped.”)
> print(string.match(glon.encode({“Oh shit \0null characters!\0”}), “%z”) and “OH SHIT IT DOESN’T ESCAPE THEM!” or “Phew, they’re escaped.”)…
Phew, they’re escaped.
[/lua]

Works just fine.

There ya go. Thanks. You still need to check for quotes, new lines and other stuff. So you still need to use the SQL escaping functions, or your own implementation.

[lua]
local sqlEscapeMap = {
{’%z’, ‘+null_’},
{"’", ‘+apos_’},
{[["]], ‘+quo_’},
}

– when encoding
function iPly.sqlEscape(str)
local str2 = str
for _,w in pairs(sqlEscapeMap) do
string.gsub(str2, w[1], w[2])
end
return str2
end

– when decoding
function iPly.sqlUnEscape(str)
local str2 = str
for _,w in pairs(sqlEscapeMap) do
string.gsub(str2, w[2], w[1])
end
return str2
end [/lua]

Is this how I should do it? One to use when decoding, one to use when encoding.

I haven’t come across a case yet where the glon’d string needs to be escaped, but I’d still like to know. Am I missing any characters? Will “%z” work in the way I’m using it?

why don’t you just use tmysql.escape?

Umm… there’s no tmysql.unescape? I don’t even know what tmysql.escape does specifically. Does it just remove the bad characters? Because that’s not what I’m looking for.

tmysql.escape makes any string SQL safe. It’s then automatically unescaped when you retrieve it.

-snip- read it wrong.