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

willgarcia · April 25, 2015, 4:48pm

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.

elberet · April 25, 2015, 5:20pm

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

https://github.com/discourse/discourse/blob/master/db/migrate/20140227201005_add_staff_category.rb#L11

riking · April 25, 2015, 5:57pm

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

saper · May 16, 2015, 7:10pm

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

michaeld · May 16, 2015, 7:31pm

Regression?

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

saper · May 16, 2015, 7:37pm

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

sam · May 28, 2015, 6:55am

Fixed per:

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

Topic		Replies	Views
Error upgrading, failed to bootstrap installation	21	1487	April 25, 2022
Recently added post migration causes bootstrap to fail bug	14	877	November 19, 2018
Upgrade failed with "duplicate key value violates unique constraint" error installation	18	2184	March 30, 2020
Unable to boostrap Discourse since last upgrade installation	5	522	January 22, 2023
Issues with Discourse Migration and App Startup installation	2	311	January 26, 2024

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

Related Topics