# HG changeset patch # User Nattee Niparnan # Date 2015-08-11 11:28:41 # Node ID f7b4a30e2f5d6bfa2e03cdbac83a6a854913fcf3 # Parent f58411dd54f5654064522ad1bcaece93e0ab44af # Parent 89e2deff986b53a01f891cf7aae4b951da3eed31 merge with algo-bm, take cheat report diff --git a/app/controllers/application_controller.rb b/app/controllers/application_controller.rb --- a/app/controllers/application_controller.rb +++ b/app/controllers/application_controller.rb @@ -2,6 +2,7 @@ protect_from_forgery SINGLE_USER_MODE_CONF_KEY = 'system.single_user_mode' + MULTIPLE_IP_LOGIN_CONF_KEY = 'right.multiple_ip_login' def admin_authorization return false unless authenticate @@ -61,6 +62,23 @@ return true end + def authenticate_by_ip_address + #this assume that we have already authenticate normally + unless GraderConfiguration[MULTIPLE_IP_LOGIN_CONF_KEY] + user = User.find(session[:user_id]) + if (not user.admin? and user.last_ip and user.last_ip != request.remote_ip) + flash[:notice] = "You cannot use the system from #{request.remote_ip}. Your last ip is #{user.last_ip}" + redirect_to :controller => 'main', :action => 'login' + return false + end + unless user.last_ip + user.last_ip = request.remote_ip + user.save + end + end + return true + end + def authorization return false unless authenticate user = User.find(session[:user_id]) diff --git a/app/controllers/configurations_controller.rb b/app/controllers/configurations_controller.rb --- a/app/controllers/configurations_controller.rb +++ b/app/controllers/configurations_controller.rb @@ -16,6 +16,7 @@ def update @config = GraderConfiguration.find(params[:id]) + User.clear_last_login if @config.key = 'multiple_ip_login' and @config.value == 'true' and params[:grader_configuration][:value] == 'false' respond_to do |format| if @config.update_attributes(params[:grader_configuration]) format.json { head :ok } diff --git a/app/controllers/main_controller.rb b/app/controllers/main_controller.rb --- a/app/controllers/main_controller.rb +++ b/app/controllers/main_controller.rb @@ -13,6 +13,8 @@ prepend_before_filter :reject_announcement_refresh_when_logged_out, :only => [:announcements] + before_filter :authenticate_by_ip_address, :only => [:list] + # COMMENTED OUT: filter in each action instead # before_filter :verify_time_limit, :only => [:submit] diff --git a/app/controllers/report_controller.rb b/app/controllers/report_controller.rb --- a/app/controllers/report_controller.rb +++ b/app/controllers/report_controller.rb @@ -1,6 +1,7 @@ class ReportController < ApplicationController - before_filter :admin_authorization, only: [:login_stat,:submission_stat, :stuck] + before_filter :admin_authorization, only: [:login_stat,:submission_stat, :stuck, :cheat_report, :cheat_scruntinize] + before_filter(only: [:problem_hof]) { |c| return false unless authenticate @@ -252,4 +253,127 @@ end end + def cheat_report + date_and_time = '%Y-%m-%d %H:%M' + begin + md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/) + @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i) + rescue + @since_time = Time.zone.now.ago( 90.minutes) + end + begin + md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/) + @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i) + rescue + @until_time = Time.zone.now + end + + #multi login + @ml = Login.joins(:user).where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).select('users.login,count(distinct ip_address) as count,users.full_name').group("users.id").having("count > 1") + + st = <<-SQL + SELECT l2.* + FROM logins l2 INNER JOIN + (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name + FROM logins l + INNER JOIN users u ON l.user_id = u.id + WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}' + GROUP BY u.id + HAVING count > 1 + ) ml ON l2.user_id = ml.id + WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}' +UNION + SELECT l2.* + FROM logins l2 INNER JOIN + (SELECT l.ip_address,COUNT(DISTINCT u.id) as count + FROM logins l + INNER JOIN users u ON l.user_id = u.id + WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}' + GROUP BY l.ip_address + HAVING count > 1 + ) ml on ml.ip_address = l2.ip_address + INNER JOIN users u ON l2.user_id = u.id + WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}' +ORDER BY ip_address,created_at + SQL + @mld = Login.find_by_sql(st) + + st = <<-SQL + SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id + FROM submissions s INNER JOIN + (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name + FROM logins l + INNER JOIN users u ON l.user_id = u.id + WHERE l.created_at >= ? and l.created_at <= ? + GROUP BY u.id + HAVING count > 1 + ) ml ON s.user_id = ml.id + WHERE s.submitted_at >= ? and s.submitted_at <= ? +UNION + SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id + FROM submissions s INNER JOIN + (SELECT l.ip_address,COUNT(DISTINCT u.id) as count + FROM logins l + INNER JOIN users u ON l.user_id = u.id + WHERE l.created_at >= ? and l.created_at <= ? + GROUP BY l.ip_address + HAVING count > 1 + ) ml on ml.ip_address = s.ip_address + WHERE s.submitted_at >= ? and s.submitted_at <= ? +ORDER BY ip_address,submitted_at + SQL + @subs = Submission.joins(:problem).find_by_sql([st,@since_time,@until_time, + @since_time,@until_time, + @since_time,@until_time, + @since_time,@until_time]) + + end + + def cheat_scruntinize + #convert date & time + date_and_time = '%Y-%m-%d %H:%M' + begin + md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/) + @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i) + rescue + @since_time = Time.zone.now.ago( 90.minutes) + end + begin + md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/) + @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i) + rescue + @until_time = Time.zone.now + end + + #convert sid + @sid = params[:SID].split(/[,\s]/) if params[:SID] + unless @sid and @sid.size > 0 + return + redirect_to actoin: :cheat_scruntinize + flash[:notice] = 'Please enter at least 1 student id' + end + mark = Array.new(@sid.size,'?') + condition = "(u.login = " + mark.join(' OR u.login = ') + ')' + + @st = <<-SQL + SELECT l.created_at as submitted_at ,-1 as id,u.login,u.full_name,l.ip_address,"" as problem_id,"" as points,l.user_id + FROM logins l INNER JOIN users u on l.user_id = u.id + WHERE l.created_at >= ? AND l.created_at <= ? AND #{condition} +UNION + SELECT s.submitted_at,s.id,u.login,u.full_name,s.ip_address,s.problem_id,s.points,s.user_id + FROM submissions s INNER JOIN users u ON s.user_id = u.id + WHERE s.submitted_at >= ? AND s.submitted_at <= ? AND #{condition} +ORDER BY submitted_at + SQL + + p = [@st,@since_time,@until_time] + @sid + [@since_time,@until_time] + @sid + @logs = Submission.joins(:problem).find_by_sql(p) + + + + + + end + + end diff --git a/app/models/login.rb b/app/models/login.rb --- a/app/models/login.rb +++ b/app/models/login.rb @@ -1,3 +1,5 @@ class Login < ActiveRecord::Base + belongs_to :user + attr_accessible :ip_address, :logged_in_at, :user_id end diff --git a/app/models/user.rb b/app/models/user.rb --- a/app/models/user.rb +++ b/app/models/user.rb @@ -307,6 +307,10 @@ end end + def self.clear_last_login + User.update_all(:last_ip => nil) + end + protected def encrypt_new_password return if password.blank? diff --git a/app/views/report/_date_range.html.haml b/app/views/report/_date_range.html.haml --- a/app/views/report/_date_range.html.haml +++ b/app/views/report/_date_range.html.haml @@ -7,11 +7,11 @@ %tr %td{style: 'width: 120px; font-weight: bold'}= param_text %td{align: 'right'} since: - %td= text_field_tag 'since_datetime' + %td= text_field_tag 'since_datetime', @since_time %tr %td %td{align: 'right'} until: - %td= text_field_tag 'until_datetime' + %td= text_field_tag 'until_datetime', @until_time %tr %td %td diff --git a/app/views/report/_report_menu.html.haml b/app/views/report/_report_menu.html.haml --- a/app/views/report/_report_menu.html.haml +++ b/app/views/report/_report_menu.html.haml @@ -4,5 +4,6 @@ %br/ = link_to '[Hall of Fame]', :action => 'problem_hof' = link_to '[Struggle]', :action => 'stuck' - = link_to '[Login]', :action => 'login_stat' + = link_to '[Cheat Detection]', :action => 'cheat_report' + = link_to '[Cheat Detail]', :action => 'cheat_scruntinize' = link_to '[Multiple Login]', :action => 'multiple_login' diff --git a/app/views/report/cheat_report.html.haml b/app/views/report/cheat_report.html.haml new file mode 100644 --- /dev/null +++ b/app/views/report/cheat_report.html.haml @@ -0,0 +1,77 @@ +- content_for :header do + = stylesheet_link_tag 'tablesorter-theme.cafe' + = javascript_include_tag 'local_jquery' + +%script{:type=>"text/javascript"} + $(function () { + $('#since_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} ); + $('#until_datetime').datetimepicker({ showButtonPanel: true, dateFormat: "yy-mm-dd", controlType: "slider"} ); + $('#my_table').tablesorter({widthFixed: true, widgets: ['zebra']}); + $('#my_table2').tablesorter({widthFixed: true, widgets: ['zebra']}); + $('#sub_table').tablesorter({widthFixed: true, widgets: ['zebra']}); + }); + +%h1 Login status + +=render partial: 'report_menu' +=render partial: 'date_range', locals: {param_text: 'Login date range:', title: 'Query login stat in the range' } + +%h2 Suspect + +%table.tablesorter-cafe#my_table + %thead + %tr + %th login + %th full name + %th login count + %tbody + - @ml.each do |l| + %tr{class: cycle('info-even','info-odd')} + %td= link_to l[:login], controller: 'users', action: 'profile', id: l[:id] + %td= l[:full_name] + %td= l[:count] + + +%h2 Multiple Logins Report +This section reports all logins record that have either multiple ip per login or multiple login per ip. + +%table.tablesorter-cafe#my_table2 + %thead + %tr + %th login + %th full name + %th IP + %th time + %tbody + - @mld.each do |l| + %tr{class: cycle('info-even','info-odd')} + %td= link_to l.user[:login], controller: 'users', action: 'profile', id: l[:user_id] + %td= l.user[:full_name] + %td= l[:ip_address] + %td= l[:created_at] + +%h2 Multiple IP Submissions Report +This section reports all submission records that have USER_ID matchs ID that logins on multiple IP +and that have IP_ADDRESS that has multiple ID logins + +Be noted that when submission IP address is not available, this might exclude +submissions that come from ID that login on multiple-login IP + +%table.tablesorter-cafe#sub_table + %thead + %tr + %th login + %th full name + %th IP + %th problem + %th Submission + %th time + %tbody + - @subs.each do |s| + %tr{class: cycle('info-even','info-odd')} + %td= link_to s.user[:login], controller: 'users', action: 'profile', id: s[:user_id] + %td= s.user[:full_name] + %td= s[:ip_address] + %td= s.problem.name + %td= link_to(s.id, controller: 'graders' , action: 'submission', id: s.id) + %td= s[:submitted_at] diff --git a/db/migrate/20150503164846_change_userid_on_login.rb b/db/migrate/20150503164846_change_userid_on_login.rb new file mode 100644 --- /dev/null +++ b/db/migrate/20150503164846_change_userid_on_login.rb @@ -0,0 +1,9 @@ +class ChangeUseridOnLogin < ActiveRecord::Migration + def up + change_column :logins, :user_id, :integer + end + + def down + change_column :logins, :user_id, :string + end +end diff --git a/db/schema.rb b/db/schema.rb --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended to check this file into your version control system. -ActiveRecord::Schema.define(:version => 20150203153534) do +ActiveRecord::Schema.define(:version => 20150618085823) do create_table "announcements", :force => true do |t| t.string "author" @@ -87,7 +87,7 @@ end create_table "logins", :force => true do |t| - t.string "user_id" + t.integer "user_id" t.string "ip_address" t.datetime "created_at", :null => false t.datetime "updated_at", :null => false @@ -242,6 +242,7 @@ t.string "section" t.boolean "enabled", :default => true t.string "remark" + t.string "last_ip" end add_index "users", ["login"], :name => "index_users_on_login", :unique => true diff --git a/db/seeds.rb b/db/seeds.rb --- a/db/seeds.rb +++ b/db/seeds.rb @@ -61,6 +61,13 @@ }, { + :key => 'right.multiple_ip_login', + :value_type => 'boolean', + :default_value => 'true', + :description => 'When change from true to false, a user can login from the first IP they logged into afterward.' + }, + + { :key => 'right.user_view_submission', :value_type => 'boolean', :default_value => 'false',