Specifically, S-SQL only escapes SQL and does parametric statements (Interpolation) only manually, while SxQL is safe by default: Every object in an SQL query is passed in an injection-safe way.
From what I've seen, Elephant gives you an interface to some persistent data structures then finds a way to back them with SQL or something else. This doesn't have that kind of backend hiding, it's meant to be tightly integrated with SQL.
* CLSQL was not built for a web environment (A man can dream) and is not thread-safe. The clsql-helper[0] project was created to address this, among other things.
* CLSQL supports more databases. While CLSQL has its own bindings for every database (Like SQLAlchemy does, I think), Crane uses cl-dbi[1], which right now only supports the big three (Postgres, MySQL, SQLite3).
* Crane was originally conceived as a set of macros to reduce the verbosity of SQL, but I later decided it would be wiser to make it stand on its own.
* CLSQL's manual is what Crane's manual aspires to be. It's probably the most well-documented Common Lisp project.
* CLSQL only escapes strings, it doesn't do prepared statements, which is a huge security flaw.
CLSQL-Fluid [0][1] makes CLSQL thread-safe. (It also makes interesting reading as a clever use of CLOS.)
How is string escaping a huge security flaw? Certainly, you rely on the escaping being done correctly, which may be harder to get right than prepared statements, but assuming it's correct, it's just as secure, no?
I don't think most people have a solid sense of what is acceptable safety for dynamic SQL. The goal is to avoid injections, so you have to ensure that can't happen. If you have a reliable way to quote/escape literals (values and identifiers might have different syntax for this) you're good. It is risky, like all programming, to take matters into your own hands, but so is using a library you can't understand. Good luck.
CLSQL does the escaping itself. The question is whether a library that does prepared statements is reasonably expected to be safer than one that does escaping.
I haven't audited CLSQL's escaping myself, however. And I concede that using prepared statements takes one possible vulnerability off the table, which is nice. I was just wondering whether 'eudox had any specific reason to think that CLSQL's escaping was flawed.
Even assuming it's correct, I don't think that's a good enough guarantee. Prepared statements are as close as it gets to 'demonstrably correct', really smart escaping might be 'probably correct', but I will pick demonstrably over probably any day, especially in a web environment.
The project page makes claims that it doesn't fall into common ORM traps such as recreating SQL syntax, locking you into a limited subset of it, yet... just a few sections below it shows exactly that. I'm sorry, but building SQL out of s-expressions doesn't count as "functional SQL". It's just crippled declarative normal SQL, made from s-expressions.
There's also the usual for ORMs display of superficial understanding of transactions with a "with-transactions" global switch, as if transactions are something you can just sprinkle on top of your finished product like an exotic condiment, and get greater goodness. This is not how SQL works.
Even the "fine-grained" transactions interface doesn't expose common-place and crucial features such as transaction isolation levels, store points, selecting for update and lock in share mode. If you're reading this and thinking "this guy's just listing arbitrary niche features no one uses", then you have no business using or, god forbid, writing ORMs, because the first time you get serious traffic hitting your database you'll end up with inconsistent data state, and have no clue how it happened (blame MySQL? Sure, blame MySQL).
SQL ORMs feel oddly anachronistic in 2014. People have largely moved to persisting objects with databases built for persisting objects, leaving SQL databases to be used where they work best - for relational data queries, without quirky layers in between caller, query and results.
Plus... and I realize maybe it's just my bias against ORMs, but "an ORM for Common Lisp" reads a bit like "we ported AIDS to heaven". Just because you could do it, didn't mean you should've.
The project page makes claims that it doesn't fall into common ORM traps such as recreating SQL syntax
I don't see anywhere on the page where it is critical of recreating SQL syntax. I think the trap the SQL stuff is trying to avoid is doing everything through OOP method calls — something like Django's filter method, which the page explicitly calls out.
Even if it was critical of recreating SQL, the point of an s-expression syntax is that it is still SQL, rather than a naïve reinvention that ends up missing important features.
SQLAlchemy has a core SQL layer that lets you compose SQL better than by bashing strings together and it handles the transaction lifecycle correctly for you through its unit of work pattern (the session).
While all I know of Crane is this page, it would appear to follow the same design, there its "filter" shortcut is built on top of the lower layer, which is also available to use directly.
>While all I know of Crane is this page, it would appear to follow the same design, there its "filter" shortcut is built on top of the lower layer, which is also available to use directly.
Essentially this. To be perfectly clear: Crane doesn't share SQLAlchemy's data mapper/unit of work pattern, it is much closer to regular active record. However, it still provides good flexibility by using SxQL and not pretending SQL doesn't exist.
The problem is that people who end up writing most of those libs are:
1. People who have very superficial knowledge about range of features, semantics, and algorithms behind SQL.
2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
3. People who are typically very averse towards learning new technologies. They are blissfully unaware of all the ways they can persist their objects without "mapping" them. They know in depth and favor one application language, and they've stored data in SQL a few times, so let's do everything in SQL, because learning is hard, but writing abstractions is cool. It feels like an achievement.
> 2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
I won't claim anything about people who write ORMs, but wanting to avoid a mess of concatenated strings in your code, which will blow up unexpectedly because your forgot a ',' in an if somewhere is a sane attitude to have. SQL strings are unsafe and compose very badly.
As for claiming that "all code is string anyway", this is a strawman. Sure, "all code is string", except it can be statically checked for basic syntax errors.
If we'll be talking about strawmen, the idea every SQL query is composed of many concatenated string pieces is the biggest of them all.
You both talk about the perils of dynamically building queries, yet proclaim the benefits of static syntax checks. You can't "statically syntax check" a dynamically built query. Even with ORMs. So pick one. Either you're building it dynamically, or if it's static there won't be "string pieces" to worry about.
Most of queries are defined statically: a query with several anonymous (?) or named (:foobar) parameters to insert literals. So none of those scary string concatenation problems even occur.
Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
It's just ugly that we have a complex piece of machinery like a modern SQL database, with all those advanced querying and MVCC features packed in for people to use them in order to build fast and safe applications, and watch them use it like a little more than a file system.
It's like buying yourself a million dollar supercomputer so you can play Solitaire on it.
> You can't "statically syntax check" a dynamically built query.
In a language with any sort of decent static type system, you most assuredly can. There should be no way to build and use an invalid query, the same as you can use the type system to enforce any other constraints.
> It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
Almost any SQL I've written has needed to be composable, for the same reasons that any other code should be composable. I need to be able to get <X> complex set of data from the database, given an identifier which could be an ID, or an URL slug, or any number of other things. I may also need to get that either for one row or for many.
It's nice to be able to split that up into the bit of the query that selects the data, and the bit of the query that chooses which row(s) to select it from, without writing out the SQL twice or developing "stringly-typed" code. It's a standard across most of the software development world that when dealing with source code (which SQL is), you almost always work with ASTs.
The frameworks I use for this do allow me complete access to the database, including all the things you've mentioned, and the ability to extend my queries with arbitrary syntax.
> "There should be no way to build and use an invalid query"
Well the SQL server won't actually run an invalid query, so what's your point?
I'm not afraid of invalid queries. I'm rather afraid of valid queries like these:
"DELETE FROM Table"
Because depending on how abstract your ORM is, it's far easier to run something you really don't want to run on your SQL server. But at least it'll be valid, hey!
> Well the SQL server won't actually run an invalid query, so what's your point?
To avoid runtime errors, I suppose.
As for "DELETE FROM table", I would say it's not any likelier to happen with an ORM than without. I haven't experienced issues of this sort, at least. The problem is more to build efficient, complex SELECT queries without getting a bad surprise performance-wise once in production.
> If we'll be talking about strawmen, the idea every SQL query is composed of many concatenated string pieces is the biggest of them all.
I'm usually careful about using such words as "never" and "always". However, I have yet to encounter a non-trivial application built on top of raw SQL where this doesn't come and bite you. Of course you can't statically check your ORM-built query, but just like with static typing, you can still avoid many problems this way.
> Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
That's just one example. I've done complex dynamic queries with joins and aliases, and frankly, that's ugly with an ORM. Without it, it's an abomination. And I'll point that most (all?) ORMs let you use raw SQL.
> If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
I'm not the biggest ORM advocate there is, but even I have to admit that persisting a complex object graph using an ORM is way easier than without it. The problems arise (usually) when you combine ORMs with naive developers attempting to get a lot of data out of the database.
I think you dismiss too easily the advantages you get from a good ORM (not that there are many of them), but I agree they're full of performance pitfalls as well (lazy collections being the most prominent one).
I've noticed that as my SQL skills evolved, I became more and more repulsed by ORMs. Now when I write a schema, I design a remote facade and map that 1:1 to a service object in client code. It is the best way I have contrived in a commercial setting (e.g. constrained in every way) to get testing isolation between database and client code to be useful.
This is something I've also noticed, and ironically it took writing an ORM to get it. I still think however that using Crane is worthwhile, even if I only use the migrations and connection management and write all my SQL by hand.
I recommend to anyone who thinks they want to use an ORM to start out by writing one, so they'll understand the product space and use cases better. It's my own little subversion of a crappy paradigm.
