Skip to content

Query parameters without escaping #114

Closed
tpetry opened this Issue Sep 15, 2011 · 2 comments

2 participants

@tpetry
tpetry commented Sep 15, 2011

Sometimes you want to insert data for a column into a table which have values and sometimes these values should be null.
With the actual implementation it's relatively complex:

if(someValue === null)
    con.query('UPDATE .... SET ...., someValue = NULL WHERE ....', callback);
else
    con.query('UPDATE .... SET ...., someValue = ? WHERE ....', [someValue], callback);

You have to use two different queries. It's not very intuitive and disables control flow functions like async.series()

It would be nice to have some method for indicating values are expressions and should not be escaped, a function like con.expr(someValue). This function could wrap the value into a special object which then will be checked when escaping the values. This would make the code above smaller and allows the use of control flow functions as long as the value will be marked as expression before:

someValue = (someValue === null) ? con.expr(null) : someValue;
con.query('UPDATE .... SET ...., someValue = ? WHERE ....', [someValue], callback);

A complete async example:

someValue = (someValue === null) ? con.expr(null) : someValue;
async.series([
     async.apply(con.query.bind(con), 'UPDATE .... SET ...., someValue = ? WHERE ....', [someValue]),
     async.apply(con.query.bind(con), 'INSERT ... ? ...', [someValue]),
     async.apply(con.query.bind(con), 'SELECT.....'),
], function(err, results) {
     // ...
});
@felixge
Owner
felixge commented Sep 15, 2011

I might be missing something, but the internal escape mechanism will does cast undefined / null into NULL, see:

https://github.com/felixge/node-mysql/blob/master/lib/client.js#L164

@felixge felixge closed this Sep 15, 2011
@tpetry
tpetry commented Sep 15, 2011

Sorry, your're completly right: A third-party module changed the null to a string containing 'null' without documenting this type change - oops.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.