Rails应用连接外部MySQL数据库的安全与优化方案咨询

Hey there! Let's walk through how to make your cross-database insertion safer, cleaner, and more maintainable. Your current approach works, but there are a few risks and optimizations we can address:

First, let's spot the issues with your current code #

• Hardcoded credentials: Putting usernames/passwords directly in your code is a big security no-no—they can leak into version control or logs.
• SQL injection risk: Using raw, unparameterized SQL means if any of those values ever become dynamic (e.g., user input), you're opening the door to injection attacks.
• Manual connection cleanup: If an error happens mid-execution, your code might fail to restore the original connection, causing weird issues later.

Here's the optimized, safer approach #

1. Move the second database config to database.yml

First, add your php_db settings to your Rails config file (this keeps credentials organized and out of your application code):

# config/database.yml
production:
  adapter: mysql2
  host: localhost
  username: ror_db_user
  password: <%= ENV['ROR_DB_PASSWORD'] %>
  database: ror_db

php_db_production:
  adapter: mysql2
  host: localhost
  username: <%= ENV['PHP_DB_USERNAME'] %>
  password: <%= ENV['PHP_DB_PASSWORD'] %>
  database: php_db

Pro tip: Use environment variables (via gems like dotenv) for passwords instead of hardcoding them—this keeps sensitive data out of your repo.

2. Create an abstract model for the PHP database

Instead of manually switching connections every time, create an abstract base model that connects to php_db once. Then you can create a model for your cxv_journal table that inherits from it:

# app/models/php_db_base.rb
class PhpDbBase < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "php_db_#{Rails.env}"
end

# app/models/cxv_journal.rb
class CxvJournal < PhpDbBase
  self.table_name = 'cxv_journal' # Only needed if your model name doesn't match the table name
end

This way, you never have to manually manage connections—Rails handles it for you.

3. Insert records safely (no SQL injection!)

Use ActiveRecord's built-in methods to create records—they automatically parameterize queries to prevent injection:

# Super clean and safe
CxvJournal.create!(
  trans_no: 2,
  reference: 2,
  tran_date: 'xxxx-xx-xx',
  event_date: 'xxxx-xx-xx',
  doc_date: 'xxxx-xx-xx'
)

If you absolutely need to use raw SQL (for complex queries), always use parameterized statements:

sql = "INSERT INTO cxv_journal(trans_no, reference, tran_date, event_date, doc_date) VALUES (?, ?, ?, ?, ?)"
PhpDbBase.connection.execute(sql, 2, 2, 'xxxx-xx-xx', 'xxxx-xx-xx', 'xxxx-xx-xx')

The ? placeholders tell Rails to properly escape each value, eliminating injection risks.

4. If you must manually switch connections (use blocks!)

If you don't want to create a model, use with_connection to handle connection switching safely. This block automatically restores the original connection when it finishes—even if an error occurs:

ActiveRecord::Base.with_connection(ActiveRecord::Base.configurations["php_db_#{Rails.env}"]) do
  sql = "INSERT INTO cxv_journal(trans_no, reference, tran_date, event_date, doc_date) VALUES (?, ?, ?, ?, ?)"
  ActiveRecord::Base.connection.execute(sql, 2, 2, 'xxxx-xx-xx', 'xxxx-xx-xx', 'xxxx-xx-xx')
end

No more manual remove_connection or re-establishing the production connection—Rails takes care of cleanup.

Extra security tip #

Make sure the database user you're using for php_db has minimal permissions. Only grant INSERT access to the cxv_journal table—no DROP, ALTER, or other dangerous privileges. This limits the damage if credentials ever leak.

内容的提问来源于stack exchange，提问作者Qmr