execute("SELECT foo FROM bar WHERE baz = ?", [5])
and it becomes something like this: db_socket ! encode_to_wire_format(
{'SELECT', "foo", "bar", [{'baz', 5}]}
))
Postrges's prepared statements aren't being used, but the distinction between "tainted" user-generated data and the "trusted" statement is maintained, because the 5 in the above is typed data being sent over the wire in a length-prefixed binary encoding, rather than string data being serialized+escaped into another string.Which is to say, if you (or your users) tried to put a fragment of SQL in place of the 5 above, it'd just get treated as string-typed data, rather than SQL. You don't need packet-level separation to achieve that.
But is this approach still bad for "emulating" prepared statements, somehow? I don't see how.
Sorry.
The answer to your question is: I don't know, that's a new solution to me.
It looks like it could be safe, but I'd have to dig into its internals to know for sure. My gut instinct is that it's probably safer than escape-and-concatenate.
If any Erlang experts want to chime in with their insight, please do.
EDIT:
> Which is to say, if you (or your users) tried to put a fragment of SQL in place of the 5 above, it'd just get treated as string-typed data, rather than SQL. You don't need packet-level separation to achieve that. > > But is this approach still bad for "emulating" prepared statements, somehow? I don't see how.
Above you said:
> the distinction between "tainted" user-generated data and the "trusted" statement is maintained
If this holds true, then you've still solved the data-instructions separation issue and what Erlang does is secure against SQL injection. So, yes, you don't need to send separate packets to ensure query string integrity in that instance.
The shit PHP does is what I meant to decry when I was talking about emulated prepared statements.
Thanks for broadening my horizons a bit. I've edited my earlier post. :)
You can stop all known attacks with escaping, but then you run into fun corner cases like http://stackoverflow.com/a/12118602/2224584
What PHP does is silently perform string escaping for you instead of doing a prepared statement. This is stupid, but PHP Internals discussions are painful (so changing it is unlikely to happen any time soon) and the userland fix is easy:
https://github.com/paragonie/easydb/blob/f90fbca34ac7b7b96f7...
If you're sending a 1+N packets (for N >= 1) to your RDBMS for each new query, then you're probably using prepared statements.