Escaping Characters in Queries

I have the following in my controller:

@company = Company.find(@params[:id]) @other_locations = Company.find(:all, :conditions => "name = '#{@company.name}' and id != #{@company.id}", :order => "total_annual_service_charge DESC")

It works perfectly except when @company.name returns something like "O'Keefe". Now when O'Keefe is passed to the Company.find as a condition the SQL craps out. Is their a way that I am suppose to escape those characters? (I thought the escaping was done for me)

Thanks :slight_smile:

John Kopanas wrote:

I have the following in my controller:

@company = Company.find(@params[:id]) @other_locations = Company.find(:all, :conditions => "name = '#{@company.name}' and id != #{@company.id}", :order => "total_annual_service_charge DESC")

It works perfectly except when @company.name returns something like "O'Keefe".

It works perfectly except when your loving customers enter an SQL insertion attack. They could enter " '; delete from company; -- ", just for cheap thrills.

This is why PHP has a reputation for insecurity - specifically its weak support for replacement arguments.

ActiveRecord's support is exemplary; read the tutorials, then do things just a little harder:

:conditions => [         'name = ? and id = ?',         @company.name, @company.id ]

You can even get more literate (and place-insensitive):

:conditions => [         'name = :name and id = :id',         :name => @company.name, :id => @company.id ]

Rewrite _all_ your SQL-facing statements like that. Never pass a naked string, even if you think you know where it came from.

(I thought the escaping was done for me)

That's because ActiveRecord, in its cheapest mode, wisely lets you insert anything you like into a string, if you don't ask for escaping. You could use #{} to stitch together an arbitrarily complex string that deliberately passes quote marks.

And I know that Ruby can override anything, but the #{} marks are generally handled at the "" string level, before ActiveRecord sees them...

Ahhh... yes... that is right... I remember reading about this! Perfect.