SQL injection prevention with unsafe and safe query patterns

14647 views

                  # Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

                  # Safe: ActiveRecord parameterization prevents SQL injection.
user = User.find_by(email: params[:email])

if user&.authenticate(params[:password])
  session[:user_id] = user.id
else
  head :unauthorized
end

                  CREATE ROLE app_readonly LOGIN PASSWORD 'replace-me';
GRANT CONNECT ON DATABASE app_production TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

I teach SQL injection by showing the vulnerable pattern first and then replacing it with parameterized queries. The important point is that escaping is not a strategy and string interpolation is not acceptable anywhere user input reaches SQL. I also prefer narrow database roles so a missed injection path cannot become a full database compromise.

Kai Nakamura

More from Kai Nakamura

SQL injection prevention with unsafe and safe query patterns

0 Comments

More from Kai Nakamura