Hacker News Reader

Comment by albertzeyer | Hacker News Reader

I wonder a bit that there isn't a real binary protocol for SQL.

Edit: It seems there are ways to work around server-side SQL parsing: http://www.xarg.org/2011/01/is-it-possible-to-avoid-query-pa...

I was thinking more about why it is allowed at all to send text-like SQL queries to a server. A binary protocol would both be simpler to handle and would have saved us from a lot of trouble.

Edit: If all client-side libs (for PHP, Python, etc.) would just use those [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statem...), it would be like what I mean.

Edit: Ah, I was wrong (as I hoped): For Python: https://launchpad.net/oursql

View on HN

At least in PostgreSQL you can use binary parameters: http://www.postgresql.org/docs/9.0/static/libpq-exec.html

Of course that does not help if you construct the entire query string as text before sending it to the server, which is how SQL injection most commonly happens.

Reply to your further edit (let't hope this is the last one ;)

Prepared statements should afford you a clean conscience because the values never make up part of the SQL query .. unless you are using a library that emulates it, and there are libraries out there that do, so don't assume anything.

Prepared statements do not afford you a "clean conscience". You still need to be careful with your inputs in parameterized queries, because not every input to every query can be bound as a variable. Some operations do require dynamic query construction.

"Prepared statements do not afford you a "clean conscience"" erm, that was the point I was trying to make.

"because not every input to every query can be bound as a variable" Can you give me an example?

"Some operations do require dynamic query construction." what does that have to do with prepared queries?

Because when you try to use a prepared statement to populate a paginated table, you still force the database to construct a dynamic query based in part on user inputs when you pass the OFFSET and LIMIT to it.

Recommended exercise: implement the MySQL wire protocol. It took me a couple hours a few years ago. It's not hard. Do it, come back, and then see if you still think the same things about SQLI defenses.

"because not every input to every query can be bound as a variable" Can you give me an example?

var sql = "select * from User where UserType=@ut order by ModificationDate " + sort_order;

In this case, if sort_order directly comes from a 'asc'/'desc' radio button then you have an injection attack.

The correct way to do it would be:

var sql = "select * from User where UserType=@ut order by ModificationDate " + (sort_order=="asc"?"asc":"desc");

The point was that there are some parts of sql that can't be parameterized like sort order or limits on the resultant recordset. Although, ideally support for those things should be coming from your database vendor (if it isn't already there).

For example, SqlServer supports using variables in top expressions: select top(@max) * from ....

SQL is a human readable language .. what do you mean by "real binary protocol" ?

The language itself doesn't matter at all. Everybody would be fine to use some construct like:

    Select(args=["name", "id"], from="students")

And maybe "name", "id" and "students" also could be replaced my some reference pointers.

But in case someone really wants to keep the language, you could still do the parsing inside of the lib and thus for example disallow multiple commands in one statement and then only send some binary representation to the server (mostly like Prepared Statements in MySQL). Whereby I think that this step is obsolete and would in any way produce further problems.

Btw., this is not exactly like NoSQL what I am talking about here.

How can you say everyone should be fine using a simple construct like that, have you ever had to write a complex query on a non-trivial database?

You will be able in a straight-forward way to extend this to allow any possible query.

Thus arriving, via the most circuitous and expensive plausible path, right back at the problem we have today.

What do you mean?

1. It would be much cheaper and faster. Right now, you are: constructing a string first, doing some escaping, then sending this more bloated pure-text query to the server, then parse the SQL language, unescape, convert back into some machine representation. I.e.

a) Most of this extra handling (escaping, string-conversions, parsing) would go away. b) Less data need to be send around.

2. It would actually make SQL injections impossible and thus solve all these problems.

You've made "SQL injection" impossible by inventing "albertzeyerql", which will inevitably be vulnerable to "AZQL injection". Many of the NoSQL's have similar problems; expect to hear more about them in Blackhat presentations yet to come.

It's not pointless to discuss how query languages can be made simpler to parse and thus less susceptible to injection, but the technique isn't foolproof and is (obviously) expensive; meanwhile, the proper defense against SQLI (being mindful about query inputs, and using the framework to abstract and normalize dynamic queries) is available today and works well.

I am not really talking about a language at all (at least not on a byte-alphabet).

Maybe we misunderstood a bit. For example, take a look at the [new Redis 2.0 protocol](http://redis.io/topics/protocol). In this protocol, injections are simply impossible. This may not be the best example because you don't really have more complicated queries but you could just do the same for tree-like structures.

I think you're wrong, both in your general point and about the possibility about Redis query injection; unfortunately, you're wrong in ways I'm not at liberty to correct. I can only ask that you take my word for it that you need to be as mindful with the code that constructs Redis keys as you do with code that concatenates SQL queries. And, as with SQL, much (perhaps most) production-quality code isn't susceptible to attack; but the lines that are tend to blindside you.

Now that made me curious...

Can you say when/where I could read about that?

tptacek, are you claiming you are aware of critical vulnerabilities in Redis?

I don't see why you couldn't inject a CR LF? Sure it might not be that dangerous (although it could be depending on what validation the server does on # of args), but you're still modifying the query.

Because you are sending the byte-size of the key/value. So whatever there comes, it will be covered, you can just pass any possible binary string.

That looks like perl's DBIx::Class library. http://search.cpan.org/dist/DBIx-Class-0.08127/lib/DBIx/Clas... has a lot of example queries.

Yes, this looks much like what I want from the outside/usage.

But the implementation of it is actually again the error-prone and slow way to generate a SQL-statement from that, then send that to MySQL and MySQL parses it back to get the original structure.

I actually want to complete avoid that step to generate an SQL statement.

You might be interested in Handler Socket.

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

Reply to your edit.

SQL is text. Also no one is sending a query to the server. It's a value that is embedded into the SQL query that circumvents it.

I don't understand this (or, to be honest, the parent). MySQL has a binary protocol. The MySQL binary protocol does, in fact, allow you to parse a query template and use it in subsequent calls with bound variables; that's how "parameterized queries" work.

Binary protocols aren't magically immune to injection-style attacks, just look at the decades of buffer overflow vulnerabilities.

Reply on news.ycombinator.com