SQL injectioning

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

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

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

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

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

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

Colin Law wrote:

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.

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.