Parameterized Queries about pgmoon HOT 11 CLOSED

Prepared queries isn't just for escaping, they are actually handled differently in the database, making it so you can make queries more efficient by just having it compile once. This would be one feature that would be nice to get at least basic low-level support for.

from pgmoon.

correct, there's a manual escape interface

lapis supports that syntax: http://leafo.net/lapis/reference/database.html#query-interface

from pgmoon.

correct, prepared statements are a different feature than parameterized queries. You can use prepared statements by providing the appropriate SQL to the query method: https://www.postgresql.org/docs/9.6/static/sql-prepare.html

from pgmoon.

I was much more concerned about the security implications than the performance. Typically query planning takes far less time than the actual query for any sizable dataset. Prepared queries don't protect against SQL injection attacks. This is huge.

Everyone makes mistakes. Everyone gets tired, pressured, or distracted. Manual escaping relies on faulty humans. It is far too easy to forget (or have a new team member not yet understand the implications) and—this is the important part—still have the code work when doing any simple checks for functionality.

Whether the implementation uses prepared queries or not is just that: an implementation detail. It's usually a minor performance tweak. Parameterized queries on the other hand are a very different animal. They are not a database feature; they are a client library feature. For the time being, I've simply taken a slightly modified function from lapis to escape my params automatically. It's 2016. You don't just concatenate strings using user-supplied data. You just don't. Because humans make mistakes, and some mistakes are just too subtle.

from pgmoon.

As an aside, I've found "?" as a placeholder to be problematic. It's used for geometric and JSON comparisons—two features I'm personally rather fond of. It looks like every punctuation character is taken. It appears the only safe bet is the comment prefix: "--"

 db:query("INSERT INTO cats (age, name, alive) VALUES (--, --, --)", 7, "Spot", true)

This should be safe since it's unlikely the client needs to send comments and they are categorically forbidden from use in raw queries without substantial breakage.

from pgmoon.

For my current application I do $1,$2,$3 which is pretty easy to parse and replace, and don't think they will be in typical SQL usage. The bonus is you can refer to them multiple times:

INSERT INTO users
  (username, password)
SELECT $1, $2
WHERE
  NOT EXISTS (
      SELECT username FROM users WHERE username = $1
  );

from pgmoon.

encode_array, encode_json, and encode_hstore all return a string value, losing your type context and potentially triggering a second literal escape. For things like arrays, JSON, and hstore, perhaps sending a closure would be more appropriate? Using @WetDesertRock's syntax…

  db:query("INSERT INTO cats (age, name, alive, profile, kittens) VALUES ($1, $2, $3, $4, $5)",
                  7, "Spot", true, as_json(profile_table), as_array(kitten_table))

Where as_json/as_array/as_hstore return a closure

  function as_json(obj)
    return function() return encode_json(obj) end
  end

so that the replacement logic can just check for

  if type(param) == "function" then
    return param()
  else
    return encode_literal(param)
  end

from pgmoon.

@WetDesertRock, I hope your example was off the top of your head, and you're not storing passwords as plain text in your database…

from pgmoon.

@ttfkam the closure idea makes sense. I'm not entirely sure how gsub works but I'm not sure that would solve any issues. I'll encounter that when I get there.

Password is bcrypt'ed before being passed into the database, and when this project reaches any sort of real status I'll look into encrypting the column in the database. No worries, I'm not Yahoo.

from pgmoon.

@leafo Any reason to do not implement parameterized queries support?

I used https://github.com/SnarkyClark/luapgsql before, it uses nice syntax:

con:exec("select * from test where val1 >= $1 and val2 is null", {0})
con:exec("select * from test1 where f1=$1 and date1 between $2 and $2 + '1 hour'::interval", {1, '2020-01-01 18:00:00'})

For now I try to use openresty + pgmoon, but I lack parameters support.

from pgmoon.

I'm closing old issues. Query interpolation syntax is opinionated so it won't be added to pgmoon for now. Escape functions for literals and values will always be provided.

from pgmoon.