5 ms·

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/

• xd 16y ago
  SQL is a human readable language .. what do you mean by "real binary protocol" ?
  • albertzeyer 16y ago
    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.
    • xd 16y ago
      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?
      • albertzeyer 16y ago
        You will be able in a straight-forward way to extend this to allow any possible query.
        • tptacek 16y ago
          Thus arriving, via the most circuitous and expensive plausible path, right back at the problem we have today.
          • albertzeyer 16y ago
            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.
            • tptacek 16y ago
              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.
              • albertzeyer 16y ago
                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 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.
    • __david__ 16y ago
      That looks like perl's DBIx::Class library. http://search.cpan.org/dist/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Cookbook.pod http://search.cpan.org/dist/DBIx-Class-0.08127/lib/DBIx/Clas... has a lot of example queries.
      • albertzeyer 16y ago
        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.
• xd 16y ago
  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.
  • tptacek 16y ago
    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.
• wulczer 16y ago
  At least in PostgreSQL you can use binary parameters: http://www.postgresql.org/docs/9.0/static/libpq-exec.html 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.
• xd 16y ago
  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.
  • tptacek 16y ago
    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.
    • xd 16y ago
      "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?
      • tptacek 16y ago
        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.
      • statictype 16y ago
        "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 ....
• jonursenbach 16y ago
  You might be interested in Handler Socket. https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
• kalleboo 16y ago
  Binary protocols aren't magically immune to injection-style attacks, just look at the decades of buffer overflow vulnerabilities.