SQL injectioning

Rails
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 G.

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:

Thanks,
Abhinav

–
अभिनव
http://twitter.com/abhinav

On Mon, Sep 7, 2009 at 11:23 AM, Salil G. <

3

I think project was moved to Github:
GitHub - jasherai/xss_terminate: git svn clone of xss_terminate for you git-rails-ers out there... unlikely there will be any enhancements by me 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

–
अभिनव
http://twitter.com/abhinav

On Mon, Sep 7, 2009 at 12:01 PM, Salil G. <

4

2009/9/7 Salil G. [email protected]:

(name=‘salil’s’ Â and parent_id= 21) Â LIMIT 1
What does the code that generated this sql look like?

Colin

5

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 G.

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 J.

On Sep 7, 1:53 am, Salil G. [email protected]

7

Colin L. wrote:

2009/9/7 Salil G. [email protected]:

(name=‘salil’s’ Â and parent_id= 21) Â LIMIT 1
What does the code that generated this sql look like?

Colin
Actuallt i used following code in my Model
Category.find(:first, :conditions=>[“name= #{self.name} and parent_id=
21”])
it gives error Then i change it as follows

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

and it works like a magic.

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.

On Sep 7, 8:12 am, Salil G. [email protected]

9

Salil G. 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.