Skip to content

Use escape function with custom query #304

New issue
New issue
Closed
Closed
Use escape function with custom query#304
Assignees
dxg
Labels
enhancement
@SamuelBolduc

Description

@SamuelBolduc
SamuelBolduc
opened on Aug 19, 2013

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?

Activity

dxg

dxg commented on Aug 19, 2013

@dxg
dxg
on Aug 19, 2013
Collaborator

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

dresende commented on Aug 20, 2013

@dresende
dresende
on Aug 20, 2013
Owner

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

SamuelBolduc

SamuelBolduc commented on Aug 20, 2013

@SamuelBolduc
SamuelBolduc
on Aug 20, 2013
Author

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.

ghost assigned
dxg
on Aug 20, 2013
dxg

dxg commented on Aug 20, 2013

@dxg
dxg
on Aug 20, 2013
Collaborator

I'll work on this

dxg
mentioned this on Aug 21, 2013
dxg
added a commit that references this issue on Aug 21, 2013

Enable auto-escaping raw queries #304

d5af517
dxg
mentioned this on Aug 21, 2013
SamuelBolduc

SamuelBolduc commented on Aug 21, 2013

@SamuelBolduc
SamuelBolduc
on Aug 21, 2013
Author

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

SamuelBolduc
closed this as completedon Aug 21, 2013
SamuelBolduc

SamuelBolduc commented on Aug 22, 2013

@SamuelBolduc
SamuelBolduc
on Aug 22, 2013
Author

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...

SamuelBolduc
reopened this on Aug 22, 2013
dxg

dxg commented on Aug 22, 2013

@dxg
dxg
on Aug 22, 2013
Collaborator

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

ghazel
mentioned this on Aug 22, 2013
SamuelBolduc

SamuelBolduc commented on Aug 23, 2013

@SamuelBolduc
SamuelBolduc
on Aug 23, 2013
Author

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

5 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

enhancement

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions
    Use escape function with custom query · Issue #304 · dresende/node-orm2