current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

I used to name my parameters in my SQL query when preparing it for practical reasons like in php with PDO.

So can I use named parameters with node-postgres module?

For now, I saw many examples and docs on internet showing queries like so:

client.query("SELECT * FROM foo WHERE id = $1 AND color = $2", [22, 'blue']);

But is this also correct?

client.query("SELECT * FROM foo WHERE id = :id AND color = :color", {id: 22, color: 'blue'});

or this

client.query("SELECT * FROM foo WHERE id = ? AND color = ?", [22, 'blue']);

I'm asking this because of the numbered parameter $n that doesn't help me in the case of queries built dynamically.

share|improve this question
    
In pg-promise there is very flexible Named Parameter formatting, even with Raw-Text formatting, plus Custom-Type formatting. – vitaly-t Sep 16 at 18:26
    
Thanks for the link, I heard about this module but didn't go further. It's what I'm looking for! – AnomalySmith Sep 16 at 21:07
up vote 0 down vote

I have been working with nodejs and postgres. I usually execute queries like this:

client.query("DELETE FROM vehiculo WHERE vehiculo_id= $1", [id], function (err, result){ //Delete a record in de db
    if(err){
        client.end();//Close de data base conection
      //Error code here
    }
    else{
      client.end();
      //Some code here
    }
  });
share|improve this answer
    
Yeah, I omit callback but there is still the problem. – AnomalySmith Sep 16 at 13:54
up vote 0 down vote

One solution is mount the SQL with parameter:

var id = 1, color = 'blue';
var sql = "SELECT * FROM foo WHERE id = " + id + " AND color = '" + color + "'";    
client.query(sql);

Or, try this please:

var data = {id: req.body.id, color: req.body.color};

client.query("SELECT * FROM foo WHERE id=($1) AND color=($2)", [data.id, data.color]);
share|improve this answer
    
Well, so I have to code myself sql injection countermeasure in that case. Pity there is no PDO equivalent : / – AnomalySmith Sep 16 at 16:40
    
I updated the answer. – Lucas Costa Sep 16 at 16:50
    
It's pretty the same as in my first example, it just take parameters from the request (here in body). – AnomalySmith Sep 16 at 20:56
    
Then sorry, it's the only solution I did consulting.. – Lucas Costa Sep 16 at 20:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.