On Oct 30, 5:51 am, ast <[email protected]> wrote:
> Hi folks,
>
> I've been playing with sequel now for a few months, and overall, I
> think it's great. I've seen a couple of minor issues that I've worked
> around (might post those separately), but my biggest concern relates
> to the way the parameter substitution is handled in the dataset class.
>
> I've looked at this in the code, and I must say I'm nervous about what
> sequel is doing when it is creating the queries to send to the
> database. Normally, the best defense for SQL injection is to use
> prepared statements, but one of the areas I've found the most "messy"
> is the sequel support for prepared statements.
I agree completely that prepared statement support is messy. Sequel
was not designed to handle prepared statements when I took over
maintenance, so the support was basically hacked in by extending
dataset instances with modules that change the behavior. The code can
be difficult to follow and isn't very fun to work with. This is one
of the reasons that I only recommend using it if you have profiled
your code and found a bottleneck, and you've benchmarked the prepared
statement/bound variable approach to make sure it is faster.
> My application is such that I'm not really interested in the Model
> capabilities, so I'm using the dataset directly. However, because of
> my database schema, there are a number of multi-table queries that
> I've written using standard SQL92 syntax. It seems that it is
> impossible to use these as prepared statements in sequel. Is this
> really true?
No, it's not true. You can use static SQL with placeholders for
prepared statements. It's a bit unintuitive, though:
DB['SELECT * FROM table WHERE column = ?', :$n].prepare
(:select, :blah).call(:n=>1)
If you were doing this a lot, I'd use the master branch for named
placeholder support (or wait until 3.6.0 is released next week), then
have a method that automatically scans the string for placeholders.
Basically, an API like:
DB.prepare_query(:blah, :select, "SELECT * FROM table WHERE column
= :value")
That does something like:
DB.dataset.with_sql("SELECT * FROM table WHERE column
= :value", :value=>:$value).prepare(:select, :blah)
Then you could call it easily:
DB.call(:blah, :value=>1)
> I'm using both SQLite3 and PostgreSQL for the database back-ends, and
> I believe that I'm using the ruby-pg implementation.
>
> I generally really like the syntax provided by the library, but the
> only way that I can directly execute my multi-table queries is to use
> the "raw SQL" mode of the dataset (which, thankfully, uses SQL92
> standard prepared statement placeholders). However, since the library
> does string substitutions on the parameter values, I have the concern
> that my application is not nearly as robust & secure as it would be if
> I were using the "real" prepared statement support of the underlying
> database engine.
If you are really concerned, you can certainly adopt the approach I've
laid out above.
I'm comfortable with the approach Sequel uses. It generally uses the
native adapter quoting methods where available, so as long as the
database adapters are quoting things correctly, I don't think there
are any security issues. Now, if there are problems with the native
adapter quoting methods, Sequel may be vulnerable, but that's a bug in
the database adapter, not in Sequel itself. Of course, using prepared
statements/bound variables can eliminate that as a method of attack.
> Fundamentally, I guess my question boils down to: how extensively has
> the library been tested against all known SQL injection techniques?
>
> Currently, I've about 1,325 lines of a ~15K LOC application which
> depend on sequel. Because I like the majority of the syntax, I'm
> hesitant to change to a different database library, but my security
> concerns currently outweigh my affinity for the library.
When in doubt, test. The integration test suite does a simple test to
make sure that strings with potential SQL injection points (i.e. ')
are quoted correctly. If you think more extensive tests would be
beneficial, I'd be happy to review patches.
I don't know of any SQL injection techniques that work on Sequel,
assuming you don't allow the users to create arbitrary symbols (which
is a DoS vulnerability in ruby anyway). Obviously, if you do
something like:
DB["SELECT * FROM table WHERE column = '#{params[:value]}'"]
then you are vulnerable, but that's not a correct use of the library.
> Before this application gets much further, I'm going to need to
> finalize on the database back-end API. Currently, I'd prefer to use
> sequel vs. going back to DBI, but DBI has a much more "natural" API
> for working with prepared statements, so I'm tempted to revert back to
> it in the near future.
Usage of prepared statements is definitely more natural with DBI, and
I used DBI, I'd probably use prepared statements. Since you are
writing all the queries by hand anyway, it's easy to do. Sequel was
not built with prepared statements/bound variables in mind, and one of
the benefits of using it is that you don't need to write all of SQL by
hand.
Honestly, I'm proud that I was able to get to the current level of
support for prepared statements, given the constraints I had. I'm not
aware of another database library that abstracts as much as Sequel
does with integrated support for prepared statements/bound variables
in the abstraction layer (there may be one, I just don't know about
it). Such as:
DB[:table].filter(:column1=>:$v1, :column2=>:$v2).call
(:select, :v1=>1, :v2=>2)
> Apologies for the slightly stream-of-consciousness post, but I'd
> appreciate any comments or thoughts on the above to help me decide how
> to proceed with the application.
If your primary concern is security, and you don't trust how Sequel
literalizes values, hopefully either using the prepared statement
approach so you don't have to trust Sequel's literalization, or
writing tests to increase your trust level will make you comfortable
using Sequel in production.
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
