jeremyevans/sequel — pull request #695 by trydionel (2013-08-21 13:37:45+00:00)
Title: Do not allow SQL placeholders to override ::cast syntax

This PR resolves an issue in which invalid SQL would be generated when:

* The supplied SQL included PostgreSQL-style typecasting syntax (`column::type`); and
* An empty parameter hash was supplied.

Previously, the placeholder replacement method could generate a regex that matched only `/:\b/`. This would match the last colon of the typecast syntax and replace it with `NULL`, generating PG exceptions like the following:

08:24:04 web.1 | PG::Error: ERROR: syntax error at or near ":"
08:24:04 web.1 | LINE 4: WHERE timestamp > (now() - '7 days':NULLinterval):NULLdate
08:24:04 web.1 | ^: SELECT
08:24:04 web.1 | MAX(value) AS deployment_size
08:24:04 web.1 | FROM fse_time_series
08:24:04 web.1 | WHERE timestamp > (now() - '7 days':NULLinterval):NULLdate
08:24:04 web.1 | AND stat_type='deployment_size';

(where the unadulterated clause was `WHERE timestamp > (now() - '7 days'::interval)::date`). With this patch, the replacement routine is skipped entirely when an empty parameter hash is supplied.

This doesn't solve the general case unfortunately, so situations like `created_at::date = :date` will still be mangled. Resolving this requires either a non-ruby1.8-compatible change to the placeholder regex or some slightly gnarly string checking in the replacement loop. I have a patch available for that as well, but I don't know what versions of ruby `sequel` is supporting. Would you like that added to this PR?