3 ms·

Hey yes, sorry if not clear, but I definitely output a prepared statement and do not homebrew any escaping! I'll take a look at adding a "TL;DR" section at the

by qooleot 12y ago

Hey yes, sorry if not clear, but I definitely output a prepared statement and do not homebrew any escaping! I'll take a look at adding a "TL;DR" section at the top to point that out before the second page where I actually go through how it works: http://ivc.com/blog/better-sql-strings-in-io-js-nodejs-part-2/ http://ivc.com/blog/better-sql-strings-in-io-js-nodejs-part-...

pfooti 12y ago
Huh, wow. I hadn't realized template strings can be used that way in ES6. Here I was ready to snark about sql injection. Instead I ended up learning that you can really tweak how these template strings get compiled into code, in such a way that you totally end up actually emitting $1 $2 stuff. That's pretty awesome - I am using a SQL generation library (knex.js), which I'm admittedly happy with, but primarily because I don't like dealing with $1 $2 $3 in my own code. Now if you had a way to programmatically expand an array (for an IN ($1 $2 $3) type of query where the array length isn't known ahead of time), I really wouldn't need to use knex.
- qooleot 12y ago
  RE: expand array for in-clause, its definitely not supported automatically by node-postgres: http://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query http://stackoverflow.com/questions/10720420/node-postgres-ho... https://github.com/brianc/node-postgres/issues/431 https://github.com/brianc/node-postgres/issues/431 which I confirmed: promise catch err: { [error: invalid input syntax for integer: "{"45","56","33"}"] but I can make this work by altering my 'sql' tagged template string function, and inspect for typeof <var> array and automatically expand. I wonder if there are there other purposes for arrays other than the in-clause syntax that I should check for.
  - qooleot 12y ago
    Followup on this. To avoid the in/not-in <array> expansion, the trick is actually to use: foo=any($1) where $1 is an array instead of in ($1, $2, etc.). != any($1) is the same as 'not in'.
- Arnavion 12y ago
  >Huh, wow. I hadn't realized template strings can be used that way in ES6. That feature ("tagged" template strings) is designed primarily for "escape text for X" usage. Usually X is HTML, but it can also be SQL of course.

by qooleot 12y ago

pfooti 12y ago

Huh, wow. I hadn't realized template strings can be used that way in ES6. Here I was ready to snark about sql injection. Instead I ended up learning that you can really tweak how these template strings get compiled into code, in such a way that you totally end up actually emitting $1 $2 stuff. That's pretty awesome - I am using a SQL generation library (knex.js), which I'm admittedly happy with, but primarily because I don't like dealing with $1 $2 $3 in my own code. Now if you had a way to programmatically expand an array (for an IN ($1 $2 $3) type of query where the array length isn't known ahead of time), I really wouldn't need to use knex.

qooleot 12y ago
RE: expand array for in-clause, its definitely not supported automatically by node-postgres: http://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query http://stackoverflow.com/questions/10720420/node-postgres-ho... https://github.com/brianc/node-postgres/issues/431 https://github.com/brianc/node-postgres/issues/431 which I confirmed: promise catch err: { [error: invalid input syntax for integer: "{"45","56","33"}"] but I can make this work by altering my 'sql' tagged template string function, and inspect for typeof <var> array and automatically expand. I wonder if there are there other purposes for arrays other than the in-clause syntax that I should check for.
- qooleot 12y ago
  Followup on this. To avoid the in/not-in <array> expansion, the trick is actually to use: foo=any($1) where $1 is an array instead of in ($1, $2, etc.). != any($1) is the same as 'not in'.
Arnavion 12y ago
>Huh, wow. I hadn't realized template strings can be used that way in ES6. That feature ("tagged" template strings) is designed primarily for "escape text for X" usage. Usually X is HTML, but it can also be SQL of course.