# pg_auto_parameterize.rb

Path: | lib/sequel/extensions/pg_auto_parameterize.rb
Last Update: | Sun May 19 07:59:58 +0000 2013

This extension allows Sequel‘s postgres adapter to automatically parameterize all common queries. Sequel‘s default behavior has always been to literalize all arguments unless specifically using parameters (via :$arg placeholders and the prepare/call methods). This extension makes Sequel take all string, numeric, date, and time types and automatically turn them into parameters. Example:

      # Default
      DB[:test].where(:a=>1)
      # SQL: SELECT * FROM test WHERE a = 1

      DB.extension :pg_auto_parameterize
      DB[:test].where(:a=>1)
      # SQL: SELECT * FROM test WHERE a = $1 (args: [1])

This extension is not necessarily faster or more safe than the default behavior. In some cases it is faster, such as when using large strings. However, there are also some known issues with this approach:

1. Because of the way it operates, it has no context to make a determination about whether to literalize an object or not. For example, if it comes across an integer, it will turn it into a parameter. That breaks code such as:

         DB[:table].select(:a, :b).order(2, 1)

   Since it will use the following SQL (which isn‘t valid):

         SELECT a, b FROM table ORDER BY $1, $2

   To work around this, you can either specify the columns manually or use a literal string:

         DB[:table].select(:a, :b).order(:b, :a)
         DB[:table].select(:a, :b).order(Sequel.lit('2, 1'))

2. In order to avoid many type errors, it attempts to guess the appropriate type and automatically casts all placeholders. Unfortunately, if the type guess is incorrect, the query will be rejected. For example, the following works without automatic parameterization, but fails with it:

         DB[:table].insert(:interval=>'1 day')

   To work around this, you can just add the necessary casts manually:

         DB[:table].insert(:interval=>'1 day'.cast(:interval))

You can also work around any issues that come up by disabling automatic parameterization by calling the no_auto_parameterize method on the dataset (which returns a clone of the dataset).

It is likely there are other corner cases I am not yet aware of when using this extension, so use this extension with caution.

This extension is only compatible when using the pg driver, not when using the old postgres driver or the postgres-pr driver.

[Validate]