ID:1931443
 
Applies to:DM Language
Status: Open

Issue hasn't been assigned a status value.
Parameterized statements aren't the perfect solution for everything, mainly if you build a query based off of multiple function calls that are expected to return parts of a sql query to be combined.

In my case, I want to make a way to fall back to sqlite from mysql (for server hosts who can't be bothered to setup mysql). I was gonna do this by making a middle tier that sends queries to either mysql or datum/database, based on a property of the config datum.

Can't do that if i can't ensure a proper escape to replace dbcon.Quote(). Anything i build in game code risks being bypassable.


in C/C++ code, this is as easy as:

char *escapedtext = sqlite3_mprintf("%q", text);

https://www.sqlite.org/capi3ref.html#sqlite3_mprintf

There's an ancient proc called text() that works similarly to parameterized strings in C:

Rather than %q in your example, you'd use [].

For instance:

text("[]",query)

text("[] derp []",herp1,herp2)

(I'm not sure I'm getting what you are getting at, though.)
What I want is a way to escape text for use in database datum sql queries (sqlite).

Right now the only way to do such things is with Parameterized queries, but if you pass an object to a function, and expect it to return sql, you can't easily combine parameter arrays for use in ? with that.

And since byonds implementation of the mysql engine doesn't support Parameterized queries, you also run in to an issue where if you want to abstract away the database engine so your code works with either/or; mysql for multiple servers wanting shared data, or sqlite database datums for servers who can't be assed to setup mysql, you can't.

The only way to escape user input for database datums is to pass it along separately, in a list, and use ? place holders in a query.

But the only way to escape input for mysql is to pass it to the database connection object's Quote() proc, (then remove the quotes it also (annoyingly) adds.) then use the escaped text in the query itself.

The sqlite3_mprintf example i gave would be how byond would implement an escape function for databases. I included it because it's not obvious how you do escaping in sqlite, unlike mysql's API, where it has a function called mysql_escape_string().
Actually you can combine arrays for parameter lists, using arglist. Nadrew worked that out a while ago.
Its *doable* but it's clunky as fuck. (and it still doesn't solve my current dilemma of wanting to make the same query code work for both mysql and database datum depending on what engine is configured.)

Not to mention that I've always been an oldschool sql programmer, I'll give up my escaped input style queries when the query police pry them out of my cold dead hands =P.



I'm gonna +1 this one, using Query()'s arguments to escape your string with the "?" format is clunky, and a big pain in the ass to read/modify later. Counting arguments to figure out which variable is being used where is not fun.
I guess I'm fuzzy on what exactly you're asking for here. Something isn't clicking for me.
MSO refuses to use parameterised queries because he enjoys having his software owned via sql injection.

He's really hoping you would give him an insecure way to do queries so he can shoot himself in the foot.

edit: this is tongue in cheek :P
I want a sqlescape proc for datum/database like mysql already has.

In the back end, It would just be as simple as:

char* sqliteEscape (char* text) {
return sqlite3_mprintf("%q", text);
}
The %q thingy returns the text with any characters that sqlite treats specially when in '' quotes escaped.

The analog to this for dantom.db is dbcon.Quote();
Also, Ignore oranges, He is trolling.

Both escaping and parameterised queries can be bypassed if you don't use them right, its just slightly harder to use parameterised queries wrong.

irc.rizon.net #coderbus wrote:
[15:39:06] [oranges] hey guys I shitposted on the byond forums
[15:39:12] [oranges] http://www.byond.com/forum/?post=1931443 check it
IIRC there already is a Quote proc, although it doesn't use that method. Is Quote inadequate in its current form?
My apologies; there isn't in fact a Quote proc. However, you can add one.

database/query/proc/Quote(v)
return "[new/database/query("?", v)]"

It doesn't use the %q format, but that probably makes more sense to use.

I do see however that sqlite3_mprintf() requires a call to sqlite3_free() on the result, or else there's a memory leak.
database/query/proc/Quote(v)
return "[new/database/query("?", v)]"

That won't escape it, because of how parameterized queries work, they never get escaped.

This would return v as is, with all backslashes and quotes intact, with no escaping.

I'm gonna back up a moment, just to make sure everybody is on the same page, (I hope this doesn't come out condescending)

In sql, there are two different ways to securely send user input in queries.

There is escaping, Some database engine api call designed to take a string in, and return it after escaping any special characters that would be treated specially in the context of a quoted string in a query. (' replaced with '', \ replaced with \\, and others) then you can just use that string inside the query as is. (dantom.db has this, datum/database doesn't)

And finally:

There is parametrized queries. in parameterized queries, the user provided string is never escaped, it never changes. Because it doesn't need to. It never gets sent with the query. The engine receives the query with ? and all, then it receives the data for the query placeholders separately, so it has no need to prevent sql inject, it just can't happen because the engine knows the data isn't suppose to contain sql, so it doesn't attempt to parse it. (datum/database has this, dantom.db doesn't)

Lummox JR
I do see however that sqlite3_mprintf() requires a call to sqlite3_free() on the result, or else there's a memory leak.
Well ya, you would absorb the string into the string tree (after converting it from char* to string) then free the char*.

What I need is basically the sqlite version of html_encode(). internally it would use that sqlite function i showed you, externally it would be datum/database/proc/escape(var/text) and return the escaped version of text.
In response to MrStonedOne
Actually you're working from an incorrect assumption about /database. Internally, the code handling the Sqlite /database datum is parsing, not sending the data as a true parameterized query. It's simply using the "?" for convenience to the end user.

The reason for this is that the data values that can be used as parameters can be strings, numbers, or cache values like icons that are turned into blobs.

Therefore the code I suggested above should properly return an escaped version of the text you put in--although granted, I haven't tested it.
hmmm. I'll take a look at playing with that then.
Works as described. Thanks.

(close this: redundant)
Quote might be worth adding to the default datum.