Use escape function with custom query

[SamuelBolduc]

I have a few custom queries in my models and I would like to use the function you use for escaping strings to me inserted. Is there a way to call it to sanitize my inputs?

[dxg]

For partial queries:

Person.find().where("name LIKE ?", ["john"]).run(...)

For complete queries it's less nice:

var val = db.driver.query.escapeVal("john o'connor");
var sql = "SELECT * FROM person WHERE name LIKE "+val;

We could provide something nicer like:

var sql = db.driver.execQuery("SELECT * FROM person WHERE name LIKE ?", [val])

Thoughts?
I'm also wondering if there are implications of overloading execQuery but I think it should be fine.

[dresende]

Yes, it should be fine, if backwards compliant this could be nice. Maybe then Model.find().where() could use it directly.

[SamuelBolduc]

This would be a really nice feature! My app is pretty complex and there are quite a few queries I can't really do with the ORM directly, so I create Model and Instance methods and put my queries there. This keeps the app structure intact (MVC).

Being able to escape my queries directly there would be very nice and more in line with my app structure.

[dxg]

I'll work on this

[SamuelBolduc]

Many thanks for this!! It will save me a lot of time and uselessly long code!

[SamuelBolduc]

I didn't try it until today, and here is what I get with the latest git version :

var sql = "INSERT INTO object (id, name, object_subtype_id, object_type_id, client_id) VALUES (DEFAULT, '?', ?, ?, ?) RETURNING id;";
    db.driver.execQuery(sql, [
        data.name,
        data.object_subtype_id,
        data.object_type_id,
        data.client_id
      ], function(err, returning) {...}

And here is the SQL I see in the debug console :

[SQL/postgres] INSERT INTO object (id, name, object_subtype_id, object_type_id, client_id) VALUES (DEFAULT, '?', ?, ?, ?) RETURNING id;

Followed by this error:

/var/www/project/node_modules/orm/lib/Drivers/DML/postgres.js:86
                                        cb(err);
                                        ^
TypeError: object is not a function
    at null.callback (/var/www/project/node_modules/orm/lib/Drivers/DML/postgres.js:86:6)
    at Query.handleError (/var/www/project/node_modules/pg/lib/query.js:93:10)
    at null.<anonymous> (/var/www/project/node_modules/pg/lib/client.js:178:19)
    at EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (/var/www/project/node_modules/pg/lib/connection.js:89:12)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:736:14)
    at Socket.EventEmitter.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:408:10)
    at emitReadable (_stream_readable.js:404:5)

Is there something I didn't do correctly? I followed the docs but I might still have done some error...

[dxg]

Did you run npm install ? You need sql-query version 0.1.11

[SamuelBolduc]

Here is the result of npm install in the orm directory (since npm-install in my project directory did nothing) :

async@0.2.9 node_modules/async

should@1.2.2 node_modules/should

sql-query@0.1.11 node_modules/sql-query

mocha@1.12.0 node_modules/mocha
├── growl@1.7.0
├── debug@0.7.2
├── commander@0.6.1
├── mkdirp@0.3.5
├── diff@1.0.2
├── ms@0.3.0
├── jade@0.26.3 (mkdirp@0.3.0)
└── glob@3.2.1 (inherits@1.0.0, graceful-fs@1.2.3, minimatch@0.2.12)

pg@1.0.0 node_modules/pg
├── generic-pool@2.0.2
└── buffer-writer@1.0.0 (cloned@0.0.1)

mysql@2.0.0-alpha7 node_modules/mysql
├── require-all@0.0.3
└── bignumber.js@1.0.1

mongodb@1.3.15 node_modules/mongodb
├── kerberos@0.0.3
└── bson@0.2.2

sqlite3@2.1.7 node_modules/sqlite3

But I still have the same issue as in my last post

[dxg]

Hey, you have this:

var sql = "INSERT INTO object (id, name, object_subtype_id, object_type_id, client_id) VALUES (DEFAULT, '?', ?, ?, ?) RETURNING id;";

Can you provide a more complete example? Something like the test case

I'm curious about how you're calling execQuery.

[SamuelBolduc]

Well the code where I call execQuery is in the example I posted... Like this :

var sql = "INSERT INTO object (id, name, object_subtype_id, object_type_id, client_id) VALUES (DEFAULT, '?', ?, ?, ?) RETURNING id;";
    db.driver.execQuery(sql, [
        data.name,
        data.object_subtype_id,
        data.object_type_id,
        data.client_id
      ], function(err, returning) {...}

It's pretty much exactly like in the test case. I don't think anything more than this could really help. I write my SQL query, I call execQuery, I pass an array of values to the query (and the values are OK when console.logged before this) and I handle the callback in a third argument. I really don't see where it fails

[SamuelBolduc]

I'm sorry, I searched some more today to find what was wrong (cause your tests showed that the escaping indeed worked) and I found that when I updated to last version it wasn't properly done. Now it works... Sorry guys! All's good now!