Docker boostrap fails due to single quotes escaping in SQL INSERT commands

Contribute Bug

AI-generated summary

A Docker bootstrap failure due to unescaped single quotes in SQL INSERT commands has been reported by willgarcia. The issue occurs when setting the default language to French in the app.yml file, causing a Rails db migration error. elberet suggests that the issue is due to unsanitized data values.

riking notes that multiple migrations need to be checked for this issue, listing several affected files. saper points out that PostgreSQL supports bound parameters, which would avoid the need for quoting, but ActiveRecord::Migration does not support this.

saper later provides an example of a properly fixed migration file (20141014191645_fix_tos_name.rb) and michaeld links to a related meta discussion. The issue has since been fixed by sam, referencing a specific GitHub commit.

1

When bootstrapping a Discourse environment with Docker (samsaffron/discourse image, version 1.0.11), Rails db migration fails because of unescaped single quotes in translations’ files.

Steps to reproduce:

$ git clone https://github.com/discourse/discourse_docker.git /var/discourse

Set the default lang to French in /var/discourse/containers/app.yml
,

...
env:
  LANG: fr_FR.UTF-8
  DISCOURSE_DEFAULT_LOCALE: fr
...

DB migration error:

$ cd /var/discourse && ./launcher boostrap app
...
I, [2015-04-25T07:53:17.634912 #39]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2015-04-25 07:53:34 UTC [315-1] discourse@discourse ERROR:  syntax error at or near "équipe" at character 284
2015-04-25 07:53:34 UTC [315-2] discourse@discourse STATEMENT:  INSERT INTO categories
	                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)
	                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)
	                   RETURNING id
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR:  syntax error at or near "équipe"
LINE 3: ...f', 'Catégorie privée pour les discussions de l'équipe du fo...
                                                             ^
: INSERT INTO categories
                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)
                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)
                   RETURNING id/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/patches/db/pg.rb:90:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/patches/db/pg.rb:90:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `block in execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.9/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/postgresql/database_statements.rb:127:in `execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:656:in `block in method_missing'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:628:in `block in say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:628:in `say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:648:in `method_missing'
/var/www/discourse/db/migrate/20140227201005_add_staff_category.rb:9:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:605:in `exec_migration'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:586:in `block (2 levels) in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:585:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:584:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:759:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:1044:in `block in ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/transactions.rb:208:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:1044:in `ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:959:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:955:in `each'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:955:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:814:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:792:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
I, [2015-04-25T07:53:35.556523 #39]  INFO -- : == 20140227201005 AddStaffCategory: migrating =================================
-- execute("INSERT INTO categories\n                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)\n                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)\n                   RETURNING id")

I, [2015-04-25T07:53:35.557179 #39]  INFO -- : Terminating async processes
I, [2015-04-25T07:53:35.557276 #39]  INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main pid: 68
2015-04-25 07:53:35 UTC [68-2] LOG:  received fast shutdown request
2015-04-25 07:53:35 UTC [68-3] LOG:  aborting any active transactions
2015-04-25 07:53:35 UTC [75-2] LOG:  autovacuum launcher shutting down
2015-04-25 07:53:35 UTC [72-1] LOG:  shutting down
I, [2015-04-25T07:53:35.557509 #39]  INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 191
191:signal-handler (1429948415) Received SIGTERM scheduling shutdown...
191:M 25 Apr 07:53:35.607 # User requested shutdown...
191:M 25 Apr 07:53:35.607 * Saving the final RDB snapshot before exiting.
191:M 25 Apr 07:53:40.060 * DB saved on disk
191:M 25 Apr 07:53:40.060 # Redis is now ready to exit, bye bye...
I, [2015-04-25T07:53:45.593683 #39]  INFO -- : HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main pid:68 did not terminate cleanly, forcing termination!


FAILED
--------------------
RuntimeError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 303 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:105:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"bundle_exec", "cmd"=>["su discourse -c 'bundle install --deployment --verbose --without test --without development'", "su discourse -c 'bundle exec rake db:migrate'", "su discourse -c 'bundle exec rake assets:precompile'"]}
fd361942a3688d08c9d47a84305aabb47cc52d1216b3812c0474cc50388c9835
FAILED TO BOOTSTRAP

I guess this is not acceptable to introduce single quotes escaping in the translation files because official translators do not have to be aware of database migrations.

4 Curtiram
2

Looks like someone forgot to SQL-sanitize their data values… :smile:

2 Curtiram
3

All the migrations that call I18n.t need to be checked for this.

db/migrate/20140122043508_add_meta_category.rb
db/migrate/20140120155706_add_lounge_category.rb
db/migrate/20141014191645_fix_tos_name.rb
db/migrate/20140929204155_migrate_tos_setting.rb
db/migrate/20140227201005_add_staff_category.rb

4 Curtiram
4

PostgreSQL supports bound parameters - then there is no need to quote anything. But I think ActiveRecord::Migration does not support this?

5

Regression?

https://meta.discourse.org/t/quote-in-category-name-breaks-migrations/21084

6

The fix was for db/migrate/20140122043508_add_meta_category.rb only, there are few more files with that problem. I think 20141014191645_fix_tos_name.rb does it properly:

class FixTosName < ActiveRecord::Migration
  def up
    execute ActiveRecord::Base.sql_fragment('UPDATE user_fields SET name = ? WHERE name = ?', I18n.t('terms_of_service.title'), I18n.t("terms_of_service.signup_form_message"))

  end
end
2 Curtiram
7

Fixed per:

https://github.com/discourse/discourse/commit/cbe45975e8f0d18f1176270e2fc5435e0f4caa13

2 Curtiram