SQL injectioning

rubyonrails-talk
1

Hi All,

What is the best way for the sql injectioning.

I have problem with field named "name" that if we enter improper value like salil's system get crashed. it gives error Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's' and parent_id= 21) LIMIT 1 at line 1: SELECT * FROM `categories` WHERE (name='salil's' and parent_id= 21) LIMIT 1

how to avoid that i wwant either of this two 1] user cannot create category with special characters like ' , < > 2] if user enter name with special characteres system shouldn't get crashed for any situation.

Thanks & Regards,

Salil Gaikwad

2

Please see documentation for “h” (html escape) and “sanitize” in rails documentation - might be of some help. From Rails 3, I hear, html will be escaped automatically. Also see this: http://railspikes.com/2008/1/28/auto-escaping-html-with-rails

Thanks, Abhinav

3

Thanks Abhinav for your quick reply. i use following link to install plugin but nothing happens. script/plugin install http://xssterminate.googlecode.com/svn/trunk/xss_terminate do you have any other link to install it.

Thanks & Regards,

Salil Gaikwad

4

I think project was moved to Github: http://github.com/jasherai/xss_terminate/tree/master BTW, I haven’t used it, and project has not been updated for a while, so do check it and test it before using it.

Thanks, Abhinav

5

Hi All,

What is the best way for the sql injectioning.

I have problem with field named "name" that if we enter improper value like salil's system get crashed. it gives error Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's' and parent_id= 21) LIMIT 1 at line 1: SELECT * FROM `categories` WHERE (name='salil's' and parent_id= 21) LIMIT 1

What does the code that generated this sql look like?

Colin

6

You'll want to look up the documentation for :conditions in ActiveRecord::Base. My guess is that the code you're using inserts parameters directly into a SQL fragment, which is bad bad bad.

--Matt Jones

7

Colin Law wrote:

8

You could also write it like this:

Category.find(:first, :conditions=> ["name LIKE :name AND parent_id = :parent_id", {:name => self.name, :parent_id => self.parent_id}]

That should properly quote the SQL to avoid injections.

9

Salil Gaikwad wrote:

Category.find(:first, :conditions=>["name= ? and "+query, self.name ])

Maybe you typed this wrong, but using the string "name =? and" + query still looks BAD to me. If "query" could possible contain any user input then it is still not sanitized against SQL Injection.

When the following form is used: :conditions => ["name = ? and parent_id = ?", a, b]

Rails will sanitize a and b while substituting them for the ? placeholders.

Rails also properly sanitizes when using hashes for the :conditions: :conditions => { :name => a, :parent_id => b }

Rule of thumb: Never directly concatenate to a SQL fragment when there is any possibility that user provided input might be involved.