sanitize_sql_for_assignment rails 5

DEV Community

Posted on May 21, 2021

Sanitizing SQL in Rails/ActiveRecord

ActiveRecord methods are great for easily querying your database. However, at some point you'll probably have to implement custom sql into your application if the traditional ActiveRecord methods won't get you what you need. When writing custom SQL in Rails, it is important to sanitize it, making sure there's no chance for an injection attack. Sometimes, however, you will have to interpolate Ruby variables into your SQL queries, but regular interpolation can be insecure. So what's the solution?

I have recently been working on an application with a Rails backend, that essentially matches musicians with each other based on similar interests, locational range, etc. When I was working on my Rails application recently, I had to write a long custom SQL statement. I wanted to make a method that could find users based on range, similar tags, and any filters like genres or instruments (in relation to the current user).

The query needed to be dynamic - the method containing the query took in arguments and those arguments would affect what the query would be looking for. Here is some pseudo code of the method I wanted to create.

Ok so we have our pseudo code. We know that we will have an array of ids of users to exclude in the query, an array of users that fit the range parameters, as well as an array of ids of users with instruments and genres that match the query. Here's where the sanitization comes in.

ActiveRecord has a sanitization method called sanitize_sql_array , that lets you pass an array into a SQL statement. It looks like: sanitize_sql_array(["name=? and group_id=?", "foo'bar", 4]) . You pass an array into the method where the first index is the SQL query you want to write with the question mark placeholder, and the following indexes are variables you can pass in. With this method, any sql injections will be escaped, so you can safely pass variables to your query.

So for example, my users in range looks like this:

I am checking if there is range in the parameters of the method, and if there is, returning the sanitized sql that has executed the method that returns an array of user ids that are in range of a given radius. So that variable, range_query, can be accessed and put into the custom SQL query I would write later. One thing I should note is that you have to establish a connection with the ActiveRecord base to execute these methods. You may notice before the method is 'conn'. Before I use these methods I write: conn = ActiveRecord::Base so I can use conn as the base for the sanitization methods.

In the end, my sql query looked like this:

You can see how I interpolated variables that have already been sanitized (range_query and genre_instrument_query). I also interpolated the sanitize_sql_array method in a couple areas. So this is how you can safely interpolate in a custom SQL queries.

Top comments (0)

Templates let you quickly answer FAQs or store snippets for re-use.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink .

Hide child comments as well

For further actions, you may consider blocking this person and/or reporting abuse

How to open a web page by URL directly in Telegram.

Anton MegaPort - Apr 7

Eventual consistência em sistemas de banco de dados distribuídos

Anderson Silva - Apr 6

Websites for Free Gatsby Templates

Devluc - Apr 7

RESTful API vs GraphQL

Nay Lin Aung - Apr 7

We're a place where coders share, stay up-to-date and grow their careers.

Rails 5 ActiveRecord Sanitized Order By

in Software on November 15, 2018 | Ruby Rails ActiveRecord

It may surprise you to find out that the default rails order function does not sanitize input. This can lead to potentially dangerous sql injections. Additionally, Rails 5 is starting to throw warnings when using order or sanitize_sql_for_order :

I don’t like users to be able to cause sql errors even with the worst input. I like clean logs. I went looking for someone who has solved this problem but didn’t find much. There was a decent sanitation function in a gist on github but it doesn’t work with joins. Here is a version of that function that operates cleanly when used on a joined query.

Add this file to config/initializers/sanitized_order.rb :

Use it like this and feel free to pass in user supplied data:

Beware though, a malicious user could use this to order by any column available in the model so you may need to add more protections to this depending on your use case.

Comments (3)

mcfoton Wednesday, May 1, 2019

Hi James, thanks for sharing :) One question though — what if I have the model name stated in the order clause directly, is there a way to check no only joins_values , but kind-of from-values ? Couldn’t find a method for that :( scope :ordered_by_city, ->(order) {sanitized_order("addresses.city", order)} Address.ordered_by_city('ASC') ->> RuntimeError (addresses unavailable in query)

James Kiefer In reply to mcfoton Wednesday, May 1, 2019

Oh good catch! Your example shouldn’t fail. I’ve revised the article and the function.

mcfoton In reply to mcfoton Sunday, May 12, 2019

Thanks! I’ve found some more edge cases. I’ll try to fix them and will update you a bit later. For now these are: When model is joined using a single form: joins(:state) it will not match against klass.pluralize.to_sym When join is done using syntax of joins(model1: :model2) it should unwrap the hash from joins_values P.S. I believe this is a valuable snippet we should polish so you can post it all over stackoverflow :)

Your comment has been submitted and will be published once it has been approved.

Your comment has not been submitted. Please go back and try again. Thank You!

If this error persists, please open an issue by clicking here .

Say something

Module: ActiveRecord::Sanitization::ClassMethods

Instance method summary.

Alias for #sanitize_sql_for_conditions .

Accepts an array of conditions.

Accepts an array, hash, or string of SQL conditions and sanitizes them into a valid SQL fragment for a SET clause.

Accepts an array or string of SQL conditions and sanitizes them into a valid SQL fragment for a WHERE clause.

Accepts an array, or string of SQL conditions and sanitizes them into a valid SQL fragment for an ORDER clause.

Sanitizes a hash of attribute/value pairs into SQL conditions for a SET clause.

Sanitizes a string so that it is safe to use within an SQL LIKE statement.

Instance Method Details

# sanitize_sql (condition), # sanitize_sql_array (ary).

Accepts an array of conditions. The array has each value sanitized and interpolated into the SQL statement.

# sanitize_sql_for_assignment (assignments, default_table_name = table_name)

# sanitize_sql_for_conditions (condition) also known as: #sanitize_sql, # sanitize_sql_for_order (condition), # sanitize_sql_hash_for_assignment (attrs, table), # sanitize_sql_like (string, escape_character = "\\").

Sanitizes a string so that it is safe to use within an SQL LIKE statement. This method uses escape_character to escape all occurrences of “", ”_“ and ”%“.

Search code, repositories, users, issues, pull requests...

Provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ActiveRecord::Base.sanitize removed in 5.1 #28947

niedfelj commented May 1, 2017

rafaelfranca commented May 1, 2017

Sorry, something went wrong.

kaspth commented May 1, 2017

• 👍 1 reaction
• 🎉 1 reaction
• 😕 11 reactions

9mm commented Jun 23, 2017

• 👍 3 reactions

m1neral commented Jun 23, 2017

• 👍 23 reactions
• 👎 2 reactions

thomasbalsloev commented Nov 22, 2018

• 👍 2 reactions

9mm commented Nov 22, 2018

Thomasbalsloev commented nov 23, 2018.

ykessler commented Dec 27, 2018 • edited

Thomasbalsloev commented jan 7, 2019.

RowiDont commented Apr 4, 2019

No branches or pull requests

sanitize_sql_for_order

• 5.0.0.1 (0)
• 6.1.3.1 (0)
• 6.1.7.7 (0)
• 7.1.3.2 (0)
• What's this?

Related methods

• Instance methods (20)

Accepts an array, or string of SQL conditions and sanitizes them into a valid SQL fragment for an ORDER clause.

Flowdock - Team Inbox With Chat

Flowdock is a collaboration tool for technical teams. Version control, project management, deployments and your group chat in one place.