How to use user defined variable of ruby in mysql?

Question

I'm working on Rails 3.2.9 app with ruby 1.9.3 and mysql 5.5. I'm required to write a query where in i'm supposed to use a user defined variable in the where clause in my controller file. Here's the code.. Please let me know how can i do it! and if not how can i convert the object(i guess so) i get from code line no 4 so that i can compare it with a fixnum later

def is_user_allowed?
    @company_id = params[:user][:company_id]

    #THIS LINES GIVES A SYNTAX ERROR AT '?'
    @no_of_licenses = Company.find_by_sql("SELECT NO_OF_LICENSES FROM COMPANIES WHERE ID=?",@company_id)

    #THIS LINE RETURNS AN OBJECT I GUESS N HENCE CANNOT COMPARE WITH FIXNUM
    @no_of_licenses = Company.find(:first,:conditions => ["id = ?",@company_id] , :select => 'no_of_licenses')   

    @present_users = User.where("company_id = ?", @company_id).count

    if @present_users < @no_of_licenses
      return true
    else
      return false
    end
  end

Amit Thawait · Accepted Answer · 2013-05-30 08:38:10Z

1

You just have to call the field name(column name) on the returned object. For ex:

@no_of_licenses = Company.find(:first,:conditions => ["id = ?",@company_id] , :select => 'no_of_licenses').no_of_licenses

The above query can be simplified as

@no_of_licenses = Company.where(:id => @company_id).pluck(:no_of_licenses).first

answered May 30, 2013 at 8:38

Amit Thawait

5,3422 gold badges35 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sonal S. · Accepted Answer · 2013-05-30 09:09:44Z

1

@no_of_licenses = Company.find_by_sql("SELECT NO_OF_LICENSES FROM COMPANIES WHERE ID= #{@company_id}")

I think this is.. what u want.

answered May 30, 2013 at 9:09

Sonal S.

1,4421 gold badge15 silver badges31 bronze badges

2 Comments

Amit Thawait Over a year ago

Well, I will not recommend this solution because @company_id is directly been taken from params as @company_id = params[:user][:company_id] . So, there is a possiblity of SQL Injection.

Sonal S. Over a year ago

Company.select(:no_of_lincenses).where(:id => @company_id) U can use this also. It will work in rails 3.2.12

Collectives™ on Stack Overflow

How to use user defined variable of ruby in mysql?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related