[derefr]

Do prepared statements count as emulated if the DB doesn't support prepared statements, but the DB adapter is doing replacement during the encoding-to-typed-binary-wire-protocol step (i.e. replacement of typed tokens with other typed tokens) rather than by just concatenating strings?

[sarciszewski]

By prepared statements, I mean your application actually sends the query string in a separate packet from the data, and thereby gives the data no opportunity to corrupt the query string.

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.

[derefr]

That doesn't really address my question. There are real prepared statements like you're talking about; there's the crap PHP does; and then there's what you get if you use e.g. Erlang's Postgres library, which is that you pass it this:

    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.

[sarciszewski]

> That doesn't really address my question.

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. :)