SS-week9

Database Hardening

Mass Assignment Vulnerabilities

What

要解決問題就要先了解問題

• Mass assignment is a computer vulnerability where an active record pattern in a web application is abused to modify data.

• ∵ In serialization formats is automatically converted on input into internal object and, in turn, into database record fields. It’s possible to overwrite fields that were never intended to be modified from outside.

• 最常見的在insert 或 update時 塞入id欄位的值

Mass assignment example

# Project has many Configurations
# when add_configuration, the project_id should be given by db
new_data = { name: 'Safe Project', repo_url: 'http://github.com/safeproject' }
new_proj = Project.create(new_data)
new_proj.add_configuration(filename: 'app.yml')
# however sb set project_id to what doesn't own this configuration
# procject_id 3 would be other account' project
new_proj.add_configuration(filename: 'app.yml', project_id: 3)

Solutions

• Sequel has Default Mass Assignment Restrictions: Primary keys, Foreign keys🙏

• 除此之外，也可以有Custom Mass Attack Restrictions

class Configuration < Sequel::Model
  many_to_one :project
  # Specified columns are unrestricted
  # Unspecified columns are restricted
  # 預設是restricted，有特別指定才是unrestricted
  # so sweet 💋
  set_allowed_columns :filename, :relative_path, :description
end

SQL Injection Vulnerability

What

Any input from users should be considered dangerous and ‘dirty’ to your system

• SQL statements are inserted into an entry field for execution
• Passing web route parameters directly into the database permits SQL injection

SQLInjection example

s = 'http://localhost:9292/api/v1/projects/2%20or%20id%3D1'

Solutions

• Validation : Essential, but has limited effect on SQL injection

• Literalization : simplest way to prevent sql injection

project = Project.where(id: 1)

• Query Parameterization : One of the best ways to prevent SQL injection

  • Application side : Bound Statements

projects = Project.where(id: :$find_id)
#<Sequel::SQLite::Dataset: "SELECT * FROM `projects` WHERE (`id` = `$find_id`)">
projects.call(:select, :find_id => 1)

• Database side : Prepared Statements (wiki)

  • a feature used to execute the same or similar database statements repeatedly with high efficiency.

  • The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times.

  • because parameter values need not be correctly escaped

ds = DB[:items].where(:name=>:$n)
ps = ds.prepare(:select, :select_by_name)
ps.call(:n=>'Jim')

UUID

Pros

• Unique across every table, every database, every server
• Allows easy merging of records from different databases
• Allows easy distribution of databases across multiple servers
• Can generate IDs without having to roundtrip to the database
• DB replication usually requires UUIDs

Cons

• Performance costs on inserts and joins
• Ugly URLs (if uuid is in URL)
• Cumbersome to debug: long, unpredictable

SecureRandom.uuid
# => "eac3d7a1-a4c3-49c0-8892-f2b329e600ea"

use uuid in sequel

# migrations
Sequel.migration do
  change do
    create_table(:configurations) do
    String :id, type: :uuid, primary_key: true

# sequel model
class Configuration < Sequel::Model
  plugin :uuid, field: :id