CODEWITHSUNDEEP
ruby-on-railspostgresqlsql-injection
sites
sites

Reputation: 21785

Ensure my SQL is not injected receiving array of values

I need to receive an array of values of like:

['restaurant']
['restaurant', 'pharmacy']

I would like which approach to take to ensure that when I use this:

SELECT * FROM places WHERE type IN (array_joined_with_commas_and_quotes)

I don't get injection attacks.

I am writing the sentence without any library and I am working in Rails.

I don't have Active Record, I am doing a postgis query to an external server.

Upvotes: 1

Views: 83

Answers (2)

Chris Travers
Chris Travers

Reputation: 26454

You have two basic approaches (using ? for parameterization in these examples).

If this is an array, then your ['restaurant', 'pharmacy'] becomes '{"restaurant","pharmacy"}' and then you can:

SELECT * FROM places WHERE type = ANY (?);

You could also

SELECT * FROM places WHERE type IN (? ....);

Dynamically generating the number of parameters as you go. As scones mentioned ActiveRecord may be able to automate this for you. The point though is that these methods send data separately from the query, which means that the SQL cannot be injected into the query.

Upvotes: 2

scones
scones

Reputation: 3345

How about using the ActiveRecord functions for query building? If you use rails, the gem should still be there.

Place.where(:type => ['foo', 'bar']).to_sql

Upvotes: 2

Related Questions