1
= Security Considerations with Sequel
3
When using Sequel, there are some security areas you should be aware of:
9
* General Parameter Handling
13
The most serious security vulnerability you can have in any library is
14
a code execution vulnerability. Sequel should not be vulnerable to this,
15
as it never calls eval on a string that is derived from user input.
16
However, some Sequel methods used for creating methods via metaprogramming
17
could conceivably be abused to do so:
19
* Sequel::Schema::CreateTableGenerator.add_type_method
20
* Sequel::Dataset.def_mutation_method
21
* Sequel::Model::Plugins.def_dataset_methods
22
* Sequel.def_adapter_method (private)
23
* Sequel::SQL::Expression.to_s_method (private)
24
* Sequel::Plugins::HookClassMethods::ClassMethods#add_hook_type
26
As long as you don't call those with user input, you should not be
27
vulnerable to code execution.
31
The primary security concern in SQL database libraries is SQL injection.
32
Because Sequel promotes using ruby objects for SQL concepts instead
33
of raw SQL, it is less likely to be vulnerable to SQL injection.
34
However, because Sequel still makes it easy to use raw SQL, misuse of the
35
library can result in SQL injection in your application.
37
There are basically two kinds of possible SQL injections in Sequel:
40
* SQL identifier injections
42
=== SQL Code Injections
46
Some Sequel methods are designed to execute raw SQL, including:
48
* Sequel::Database#execute
49
* Sequel::Database#run
52
* Sequel::Database#fetch
53
* Sequel::Dataset#with_sql
55
Here are some examples of use:
62
DB.dataset.with_sql('SQL').all
64
If you pass a string to these methods that is derived from user input, you open
65
yourself up to SQL injection. The Sequel::Database#run, Sequel::Database#<<, and
66
Sequel::Database#execute methods are not designed to work at all with user input.
67
If you must use them with user input, you should escape the user input manually
68
via Sequel::Database#literal. Example:
70
DB.run "SOME SQL #{DB.literal(params[:user].to_s)}"
72
With Sequel::Database#[], Sequel::Database#fetch and Sequel::Dataset#with_sql, you should use placeholders,
73
in which case Sequel automatically literalizes the input:
75
DB['SELECT * FROM foo WHERE bar = ?', params[:user].to_s]
77
==== Manually Created Literal Strings
79
Sequel generally treats ruby strings as SQL strings (escaping them correctly), and
80
not as raw SQL. However, you can convert a ruby string to a literal string, and
81
Sequel will then treat it as raw SQL. This is typically done through String#lit
82
if the {core_extensions}[link:files/doc/core_extensions_rdoc.html] are in use,
83
or Sequel.lit[rdoc-ref:Sequel::SQL::Builders#lit] if they are not in use.
88
Using String#lit or Sequel.lit[rdoc-ref:Sequel::SQL::Builders#lit] to turn a ruby string into a literal string results
89
in SQL injection if the string is derived from user input. With both of these
90
methods, the strings can contain placeholders, which you can use to safely include
91
user input inside a literal string:
93
'a = ?'.lit(params[:user_id].to_s)
94
Sequel.lit('a = ?', params[:user_id].to_s)
96
Even though they have similar names, note that Sequel::Database#literal operates very differently from
97
String#lit or Sequel.lit[rdoc-ref:Sequel::SQL::Builders#lit].
98
Sequel::Database#literal is for taking any supported object,
99
and getting an SQL representation of that object, while
100
String#lit or Sequel.lit[rdoc-ref:Sequel::SQL::Builders#lit] are for treating
101
a ruby string as raw SQL. For example:
103
DB.literal(Date.today) # "'2013-03-22'"
104
DB.literal('a') # "'a'"
105
DB.literal(Sequel.lit('a')) # "a"
106
DB.literal(:a => 'a') # "(\"a\" = 'a')"
107
DB.literal(:a => Sequel.lit('a')) # "(\"a\" = a)"
109
==== SQL Filter Fragments
111
The most common way to use raw SQL with Sequel is in filters:
113
DB[:table].where("name > 'M'")
115
If a filter method is passed a string as the first argument, it treats the rest of
116
the arguments (if any) as placeholders to the string. So you should never do:
118
DB[:table].where("name > #{params[:id].to_s}") # SQL Injection!
120
Instead, you should use a placeholder:
122
DB[:table].where("name > ?", params[:id].to_s) # Safe
124
Note that for that type of query, Sequel generally encourages the following form:
126
DB[:table].where{|o| o.name > params[:id].to_s} # Safe
128
Sequel's DSL supports a wide variety of SQL concepts, so it's possible to
129
code most applications without every using raw SQL.
131
A large number of dataset methods ultimately pass down their arguments to a filter
132
method, even some you may not expect, so you should be careful. At least the
133
following methods pass their arguments to a filter method:
135
* Sequel::Dataset#where
136
* Sequel::Dataset#having
137
* Sequel::Dataset#filter
138
* Sequel::Dataset#exclude
139
* Sequel::Dataset#exclude_where
140
* Sequel::Dataset#exclude_having
141
* Sequel::Dataset#and
143
* Sequel::Dataset#first
144
* Sequel::Dataset#last
146
* Sequel::Dataset#[]=
148
The Model.find[rdoc-ref:Sequel::Model::ClassMethods#find] and Model.find_or_create[rdoc-ref:Sequel::Model::ClassMethods#find_or_create]
149
class methods also call down to the filter methods.
151
==== SQL Fragment passed to Dataset#update
153
Similar to the filter methods, Sequel::Dataset#update also treats a
154
string argument as raw SQL:
156
DB[:table].update("column = 1")
158
So you should not do:
160
DB[:table].update("column = #{params[:value].to_s}") # SQL Injection!
162
Instead, you should do:
164
DB[:table].update(:column => params[:value].to_s) # Safe
166
==== SQL Fragment passed to Dataset#lock_style
168
The Sequel::Dataset#lock_style method also treats an input string
169
as SQL code. This method should not be called with user input.
171
=== SQL Identifier Injections
173
Usually, Sequel treats ruby symbols as SQL identifiers, and ruby
174
strings as SQL strings. However, there are some parts of Sequel
175
that treat ruby strings as SQL identifiers if an SQL string would
176
not make sense in the same context.
178
For example, Sequel::Database#from and Sequel::Dataset#from will treat a string as
181
DB.from('t') # SELECT * FROM "t"
183
Another place where Sequel treats ruby strings as identifiers are
184
the Sequel::Dataset#insert and Sequel::Dataset#update methods:
186
DB[:t].update('b'=>1) # UPDATE "t" SET "b" = 1
187
DB[:t].insert('b'=>1) # INSERT INTO "t" ("b") VALUES (1)
189
Note how the identifier is still quoted in these cases. Sequel quotes identifiers by default
190
on most databases. However, it does not quote identifiers by default on DB2 and Informix.
191
On those databases using an identifier derived from user input can lead to SQL injection.
192
Similarly, if you turn off identifier quoting manually on other databases, you open yourself
193
up to SQL injection if you use identifiers derived from user input.
195
When Sequel quotes identifiers, using an identifier derived from user input does not lead to
196
SQL injection, since the identifiers are also escaped when quoting.
197
Exceptions to this are Oracle (can't escape <tt>"</tt>) and Microsoft Access
198
(can't escape <tt>]</tt>).
200
In general, even if doesn't lead to SQL Injection, you should avoid using identifiers
201
derived from user input unless absolutely necessary.
203
Sequel also allows you to create identifiers using
204
Sequel.identifier[rdoc-ref:Sequel::SQL::Builders#identifier] for plain identifiers,
205
Sequel.qualify[rdoc-ref:Sequel::SQL::Builders#qualify] for qualified identifiers, and
206
Sequel.as[rdoc-ref:Sequel::SQL::Builders#as] for aliased expressions. So if you
207
pass any of those values derived from user input, you are dealing with the same scenario.
209
Note that the issues with SQL identifiers do not just apply to places where
210
strings are used as identifiers, they also apply to all places where Sequel
211
uses symbols as identifiers. However, if you are creating symbols from user input,
212
you at least have a denial of service vulnerability, and possibly a more serious
217
Sequel converts some strings to symbols. Because symbols in ruby are not
218
garbage collected, if the strings that are converted to symbols are
219
derived from user input, you have a denial of service vulnerability due to
222
The strings that Sequel converts to symbols are generally not derived
223
from user input, so Sequel in general is not vulnerable to this. However,
224
users should be aware of the cases in which Sequel creates symbols, so
225
they do not introduce a vulnerability into their application.
227
=== Column Names/Aliases
229
Sequel returns SQL result sets as an array of hashes with symbol keys. The
230
keys are derived from the name that the database server gives the column. These
231
names are generally static. For example:
233
SELECT column FROM table
235
The database will generally use "column" as the name in the result set.
239
SELECT column AS alias FROM table
241
The database will generally use "alias" as the name in the result set. So
242
if you allow the user to control the alias name:
244
DB[:table].select(:column.as(params[:alias]))
246
Then you have a denial of service vulnerability. In general, such a vulnerability
247
is unlikely, because you are probably indexing into the returned hash(es) by name,
248
and if an alias was used and you didn't expect it, your application wouldn't work.
250
=== Database Connection Options
252
All database connection options are converted to symbols. For a
253
connection URL, the keys are generally fixed, but the scheme is turned
254
into a symbol and the query option keys are used as connection option
255
keys, so they are converted to symbols as well. For example:
257
postgres://host/database?option1=foo&option2=bar
259
Will result in :postgres, :option1, and :option2 symbols being created.
261
Certain option values are also converted to symbols. In the general case,
262
the sql_log_level option value is, but some adapters treat additional
265
This is not generally a risk unless you are allowing the user to control
266
the connection URLs or are connecting to arbitrary databases at runtime.
270
Mass assignment is the practice of passing a hash of columns and values
271
to a single method, and having multiple column values for a given object set
272
based on the content of the hash.
273
The security issue here is that mass assignment may allow the user to
274
set columns that you didn't intend to allow.
276
The Model#set[rdoc-ref:Sequel::Model::InstanceMethods#set] and Model#update[rdoc-ref:Sequel::Model::InstanceMethods#update] methods do mass
277
assignment. The default configuration of Sequel::Model allows all model
278
columns except for the primary key column(s) to be set via mass assignment.
283
album.set(params[:album]) # Mass Assignment
285
Both Model.new[rdoc-ref:Sequel::Model::InstanceMethods::new] and Model.create[rdoc-ref:Sequel::Model::ClassMethods#create]
286
call Model#set[rdoc-ref:Sequel::Model::InstanceMethods#set] internally, so
287
they also allow mass assignment:
289
Album.new(params[:album]) # Mass Assignment
290
Album.create(params[:album]) # Mass Assignment
292
Instead of these methods, it is encouraged to either use the
293
Model#set_only[rdoc-ref:Sequel::Model::InstanceMethods#set_only],
294
Model#update_only[rdoc-ref:Sequel::Model::InstanceMethods#update_only],
295
Model#set_fields[rdoc-ref:Sequel::Model::InstanceMethods#set_fields], or
296
Model#update_fields[rdoc-ref:Sequel::Model::InstanceMethods#update_fields]
297
methods, which allow you to specify which fields
298
to allow on a per-call basis. This pretty much eliminates the chance that the
299
user will be able to set a column you did not intend to allow:
301
album.set_only(params[:album], [:name, :copies_sold])
302
album.set_fields(params[:album], [:name, :copies_sold])
304
You can override the columns to allow by default during mass assignment via
305
the Model.set_allowed_columns[rdoc-ref:Sequel::Model::ClassMethods#set_allowed_columns] class method. This is a good
306
practice, though being explicit on a per-call basis is still recommended:
308
Album.set_allowed_columns(:name, :copies_sold)
309
Album.create(params[:album]) # Only name and copies_sold set
311
For more details on the mass assignment methods, see the {Mass Assignment Guide}[link:files/doc/mass_assignment_rdoc.html].
313
== General Parameter Handling
315
This issue isn't necessarily specific to Sequel, but it is a good general practice.
316
If you are using values derived from user input, it is best to be explicit about
317
their type. For example:
319
Album.where(:id=>params[:id])
321
is probably a bad idea. Assuming you are using a web framework, params\[:id\] could
322
be a string, an array, a hash, or nil.
324
Assuming that +id+ is an integer field, you probably want to do:
326
Album.where(:id=>params[:id].to_i)
328
If you are looking something up by name, you should try to enforce the value to be
331
Album.where(:name=>params[:name].to_s)
333
If you are trying to use an IN clause with a list of id values based on input provided
336
Album.where(:id=>params[:ids].to_a.map{|i| i.to_i})
338
Basically, be as explicit as possible. While there aren't any known security issues
339
in Sequel when you do:
341
Album.where(:id=>params[:id])
343
It allows the attacker to choose to do any of the following queries:
347
id IN ('1', '2', '3') # ['1', '2', '3']
348
id = ('a' = 'b') # {'a'=>'b'}
349
id = ('a' IN ('a', 'b') AND 'c' = '') # {'a'=>['a', 'b'], 'c'=>''}
351
While none of those allow for SQL injection, it's possible that they
352
might have an issue in your application. For example, a long array
353
or deeply nested hash might cause the database to have to do a lot of
354
work that could be avoided.
356
In general, it's best to let the attacker control as little as possible,
357
and explicitly specifying types helps a great deal there.
added
removed
doc/security.rdoc
