Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
875 views
in Technique[技术] by (71.8m points)

rails 3 activerecord order - what is the proper sql injection work around?

let us say I have a list page of users and you can sort by the different columns, when clicking 'email' it will pass sort_by=email sort_direction=asc or desc

sort_by = "email" # really params[:sort_by]
sort_direction = "asc" # really params[:sort_direction]
User.order("#{sort_by} #{sort_direction}")
# SELECT "users".* FROM "users" ORDER BY email asc

so that works as expected, however if we change the sort_by

sort_by = "email; DELETE from users; --"
User.order("#{sort_by} #{sort_direction}")
# SELECT "users".* FROM "users" ORDER BY email; DELETE from users; -- asc

now we have no more users :(

I can manually build a whitelist of valid sort_by and compare params[:sort_by] to that, but was hoping there is some built in way to handle this kind of thing

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Ryan Bates' method:

in your controller:

def index
  @users = User.order(sort_by + " " + direction)
end

private
  def sort_by
    %w{email name}.include?(params[:sort_by]) ? params[:sort_by] : 'name'
  end

  def direction
    %w{asc desc}.include?(params[:direction]) ? params[:direction] : 'asc'
  end

Essentially you're making a whitelist, but it's easy to do and insusceptible to injection.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...