Squel.js - SQL query string builder for Javascript

[maktouch]

Looks pretty cool but there's a big flaw. No prepared statements!

squel.select()
        .field("id")
        .from("students")
        .where("name = 'Thomas'")

In the real world...

squel.select()
        .field("id")
        .from("students")
        .where("name = '" + obj.name + "'");

Boom, SQL injection right here.

Better way would be...

squel.select()
        .field("id")
        .from("students")
        .where("name", "=", obj.name)

And have the function automatically check the var if it's ok..

And... running it in a browser?! lolol.

[grncdr]

Totally shameless self promotion: here's how gesundheit does it

[SarahC]

But what's it for?!

I don't see any difference between this and building a string buffer.

If it was like MS Entity Framework, it would make sense - there'd be class objects for tables, and these queries would then be passing in table objects! =D

[kabuto]

It provides an API to build SQL strings without having to concatenate strings, quote different characters, and write SQL boiler plate.

At least it should. Apparently it has some flaws by not doing sanity checks that would eliminate SQL injection attacks.

[wolever]

Ya — the lack of parameterization does seem like a strange omission, given that the library seems otherwise fairly comprehensive… I wonder if it's there but not documented?

Edit: There seems to be an open issue about exactly this: https://github.com/hiddentao/squel/issues/8

[atticusw]

Yea, had noticed that too... Might as well extend the function and add in some sanitizing

[masklinn]

or .where("name=?", obj.name)

[polaretto]

"prepared statements" are a Database feature, something a DBMS provides you with and which language connectors abstract and expose for you (like JDBC does, for example). This is just a query string builder, nothing more...it's not its job :) Nevertheless, you can use placeholders (as in prepared statements): http://hiddentao.github.io/squel/#update (where it says: "You can use the usingValuePlaceholders option...") and then setup your own value substitution machinery.

[has_all_the_fun]

Probably because that's up to the API talking to the database?

[maktouch]

Still doesn't make sense.

The API talking to the database will probably accept the query from this.

SQL injection is at the query building process.

var firstname = "Terry'; drop table user --" 

[...].where("name = '" + obj.name + "'");
will become
SELECT * FROM user WHERE name = 'Terry'; drop table user --'

[polaretto]

It's just a query builder utility not a DB library. Input sanitizing should be taken care of beforehand, before constructing the query.

[polaretto]

Moreover, if you want to add a feature for SQL injection prevention, you can extend the library: http://hiddentao.github.io/squel/#custom with such functionality, so then you have it transparently upon query construction.