6 ms·

His proposal is not string concatenation, and it is not vulnerable to injection.

djur 12y ago
"WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?
- nknighthb 12y ago
  It's pseudocode for an implementation that doesn't exist. There is nothing in his proposal that requires this hypothetical implementation to copy the value of the price_range variable into the string at all, much less unescaped. The strings don't even have to be sent to the DB at all. You've utterly missed his point. He wants the language to be intelligent about what the SQL means and do the right thing. It's not raw SQL, it's an abstraction.
  - djur 12y ago
    The psuedocode in question happens to be syntactically valid Ruby. If the goal was to demonstrate how simple query generation could be without abstraction layers like Sequel, it is a valid criticism to note that the example given is eliding a necessary feature. Especially since helping avoid injections is one of the primary advantages of such an abstraction layer. The original argument was that if one could "just write SQL" one wouldn't need to refer to documentation to become productive, "not once". I don't personally think it's practical to use any database library without reading the documentation, but certainly the moment you get away from "the SQL I type in goes straight to the database" you're going to need documentation to tell you what's going to change. What's being described here sounds like a library that parses SQL fragments, combines them into whole queries, and sends them off to the database. To implement this this without resorting to simple string concatenation needs an intermediate representation of SQL language components. That intermediate representation is going to look a lot like SQLAlchemy, or Sequel, or Arel. That means that instead of "just writing SQL" you're actually adding an extra layer of abstraction and another chunk of documentation to read. All that said, the "just use SQL" approach is already fairly well served. Most languages have mature bindings to specific databases as well as at least one ODBC-style API to simplify access to those bindings. Nearly all of these have support for client-prepared statements, which handles the interpolation problem. If you want to build more complex queries on the fly, you can use string concatenation or CTEs or decide to use a SQLAlchemy/Sequel/Arel-type library for just that one case. And, honestly, you don't need to give up SQL to make use of Sequel. I personally really dislike the query composition API demonstrated at the top of this thread, but you can do useful things with Sequel without it: db = Sequel.sqlite db.execute "create table foos (id int not null, name text)" db.execute "insert into foos (id, name) values (1, 'bar')" # or db[:foos].insert(id: 2, name: "baz") db["select * from foos where id = ?", 1].first # ==> {:id=>1, :name=>"bar"} # or db[:foos]["id = ?", 2] # ==> {:id=>2, :name=>"baz"} No need to go any further than raw SQL, but increasing levels of abstraction available if you feel like it.
    - nknighthb 12y ago
      > The psuedocode in question happens to be syntactically valid Ruby. So is everything else anyone types, code-like or not. He even said "Note how I deliberately shuffled the order and didn't bother with escaping.". The response was flippant, intelligence-insulting, and obviously the result of failing to read thoroughly. And speaking of intelligence-insulting, we all know you can run raw SQL through Sequel. You're not having a useful dialogue, you're being combative, like the person I initially replied to.
      - emodendroket 12y ago
        As everyone has repeatedly pointed out, once you deal with the obvious issues you quickly end up with something that looks like the gazillions of other ORMs on the market; sorry if I hurt anyone's feelings.
      - djur 12y ago
        I said the code snippet looked like raw interpolation to me, and I asked how it could be made not vulnerable to injection. It was an honest observation and a genuine question. No flippancy was involved. You're free to think I'm an idiot, but you are the one being insulting and combative here.
- moe 12y ago
  How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query? It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping): s.add "WHERE foo > $(bar)" Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed. I believe both of these problems should be solvable without compromising the simplicity of the approach.
  - vinceguidry 12y ago
    Or you could just use Sequel and not have to build up your own library.
  - grey-area 12y ago
    You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar Or even this: s.Where "foo > ?", bar At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember the ordering rules of sql as to which part of the query must be built first. There's a reason these query builders have converged on similar syntax. I see where you're coming from - you don't want to learn two sets of syntax, but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL while avoiding sqli and statement order issues, they also usually have an sql() option which lets you just send SQL if you wish for a complex query. There are very good reasons people keep inventing layers over SQL: They help avoid sqli They centralise db access so you can optimise in one place by say adding caching layers to queries They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to
    - qooleot 12y ago
      "You still need to supply the local variables to your ORM" That can be done automatically in many languages. At least in the sense you can do: "where value=$(bar)" and not: "where value = ?", bar OpenACS was an old web framework for the Tcl language, but it even had that 20 years ago!. The company backing it failed in the .com era, and the lack of types was a turnoff for many - but it made SQL much much more readable. And of course the queries were turned into prepared statements. A query looked like this: select foo from table where age>:min_age and name != :user_name Things like this can also be done in node.js (disclaimer: I wrote the blog post below): http://ivc.com/blog/better-sql-strings-in-node-js/ http://ivc.com/blog/better-sql-strings-in-node-js/

djur 12y ago

"WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?

nknighthb 12y ago
It's pseudocode for an implementation that doesn't exist. There is nothing in his proposal that requires this hypothetical implementation to copy the value of the price_range variable into the string at all, much less unescaped. The strings don't even have to be sent to the DB at all. You've utterly missed his point. He wants the language to be intelligent about what the SQL means and do the right thing. It's not raw SQL, it's an abstraction.
- djur 12y ago
  The psuedocode in question happens to be syntactically valid Ruby. If the goal was to demonstrate how simple query generation could be without abstraction layers like Sequel, it is a valid criticism to note that the example given is eliding a necessary feature. Especially since helping avoid injections is one of the primary advantages of such an abstraction layer. The original argument was that if one could "just write SQL" one wouldn't need to refer to documentation to become productive, "not once". I don't personally think it's practical to use any database library without reading the documentation, but certainly the moment you get away from "the SQL I type in goes straight to the database" you're going to need documentation to tell you what's going to change. What's being described here sounds like a library that parses SQL fragments, combines them into whole queries, and sends them off to the database. To implement this this without resorting to simple string concatenation needs an intermediate representation of SQL language components. That intermediate representation is going to look a lot like SQLAlchemy, or Sequel, or Arel. That means that instead of "just writing SQL" you're actually adding an extra layer of abstraction and another chunk of documentation to read. All that said, the "just use SQL" approach is already fairly well served. Most languages have mature bindings to specific databases as well as at least one ODBC-style API to simplify access to those bindings. Nearly all of these have support for client-prepared statements, which handles the interpolation problem. If you want to build more complex queries on the fly, you can use string concatenation or CTEs or decide to use a SQLAlchemy/Sequel/Arel-type library for just that one case. And, honestly, you don't need to give up SQL to make use of Sequel. I personally really dislike the query composition API demonstrated at the top of this thread, but you can do useful things with Sequel without it: db = Sequel.sqlite db.execute "create table foos (id int not null, name text)" db.execute "insert into foos (id, name) values (1, 'bar')" # or db[:foos].insert(id: 2, name: "baz") db["select * from foos where id = ?", 1].first # ==> {:id=>1, :name=>"bar"} # or db[:foos]["id = ?", 2] # ==> {:id=>2, :name=>"baz"} No need to go any further than raw SQL, but increasing levels of abstraction available if you feel like it.
  - nknighthb 12y ago
    > The psuedocode in question happens to be syntactically valid Ruby. So is everything else anyone types, code-like or not. He even said "Note how I deliberately shuffled the order and didn't bother with escaping.". The response was flippant, intelligence-insulting, and obviously the result of failing to read thoroughly. And speaking of intelligence-insulting, we all know you can run raw SQL through Sequel. You're not having a useful dialogue, you're being combative, like the person I initially replied to.
    - emodendroket 12y ago
      As everyone has repeatedly pointed out, once you deal with the obvious issues you quickly end up with something that looks like the gazillions of other ORMs on the market; sorry if I hurt anyone's feelings.
    - djur 12y ago
      I said the code snippet looked like raw interpolation to me, and I asked how it could be made not vulnerable to injection. It was an honest observation and a genuine question. No flippancy was involved. You're free to think I'm an idiot, but you are the one being insulting and combative here.
moe 12y ago
How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query? It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping): s.add "WHERE foo > $(bar)" Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed. I believe both of these problems should be solvable without compromising the simplicity of the approach.
- vinceguidry 12y ago
  Or you could just use Sequel and not have to build up your own library.
- grey-area 12y ago
  You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar Or even this: s.Where "foo > ?", bar At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember the ordering rules of sql as to which part of the query must be built first. There's a reason these query builders have converged on similar syntax. I see where you're coming from - you don't want to learn two sets of syntax, but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL while avoiding sqli and statement order issues, they also usually have an sql() option which lets you just send SQL if you wish for a complex query. There are very good reasons people keep inventing layers over SQL: They help avoid sqli They centralise db access so you can optimise in one place by say adding caching layers to queries They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to
  - qooleot 12y ago
    "You still need to supply the local variables to your ORM" That can be done automatically in many languages. At least in the sense you can do: "where value=$(bar)" and not: "where value = ?", bar OpenACS was an old web framework for the Tcl language, but it even had that 20 years ago!. The company backing it failed in the .com era, and the lack of types was a turnoff for many - but it made SQL much much more readable. And of course the queries were turned into prepared statements. A query looked like this: select foo from table where age>:min_age and name != :user_name Things like this can also be done in node.js (disclaimer: I wrote the blog post below): http://ivc.com/blog/better-sql-strings-in-node-js/ http://ivc.com/blog/better-sql-strings-in-node-js/