Forum: Ruby on Rails Rails SQL generation (Firebird adapter) - can someone explai

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Be09addcbb47f2a684fa5c48bac94149?d=identicon&s=25 David Johnson (Guest)
on 2006-03-16 04:29
(Received via mailing list)
I see some possible issues with the SQL that is being generated by the
firebird adapter.

Every SQL is being generated on the fly by concatenating strings.

Can someone please explain why the SQL is being generated the way it is?
I cannot use this in real life for a couple of reasons:

1. This introduces a security hole because a user, accidentally or
deliberately, could potentially submit a string that alters the intent
of the query.

2. Given that the most expensive part of executing a query is often the
prepare phase, wouldn't prepared statements make more sense, wherever
they are possible?

Currently, the framework is generating and running the string ...

    SELECT a.field
    from table
    WHERE (table."ID" = '--- :id
    ' )

Is something like this example mechanism feasible?  (I would be prepared
to assist in the design and construction):

a. lookaside to see if we have already prepared a statement returning
these columns with this input parameter

b. if we have no prepared statement, generate similar parameterized SQL:

    SELECT a.field
    from table
    WHERE (table."ID" = ? )

   prepare the statement and keep the statement handle, with metadata so
we can apply parameters on demand

c. Apply parameters to the prepared statement and execute it

On 2nd and subsequent invocations, the prepare phase within
the DBMS (determining access paths) is sidestepped, the
bandwidth usage is reduced to only parameters, scalability is
improved enormously,and the security hole is plugged
so special characters cannot cause undesired behavior.

Thanks,
David Johnson
Be09addcbb47f2a684fa5c48bac94149?d=identicon&s=25 David Johnson (Guest)
on 2006-03-16 04:53
(Received via mailing list)
In the ActiveRecord, I see an obvious place where rails could allow the
connection the option of taking over the responsibility for generating
SQL.  Of course, the same modifications would need to be applied to the
other SQL generators.

In theory support parameterization and dialectic optimizations does not
appear to me to be too far away.

Example of proposed modifications to ActiveRecord

        def construct_finder_sql_with_included_associations(options,
schema_abbreviations, reflections)

# ---- example proposal code
          if connection.has_own_sql_generator?
              # sql may be a string or an instance of a connection
              # defined class that provides DBMS specific functionality
              # such as prepared statements or implementation specific
              # optimizations.  If the connection takes
              # responsibility for the SQL, then the ActiveRecord can
              # wash its hands of responsibility for the sanitization
              # since the DBMS driver is supposed to be intelligent
              # about its own DBMS' requirements.

              sql =
connection.construct_finder_sql_with_included_associations(options,
schema_abbreviations, reflections)
              return sql
          else
# ---- end example proposal code

              sql = "SELECT #{column_aliases(schema_abbreviations)} FROM
#{table_name} "
              sql << reflections.collect { |reflection| association_join
(reflection) }.to_s
              sql << "#{options[:joins]} " if options[:joins]

              add_conditions!(sql, options[:conditions])
              add_sti_conditions!(sql, reflections)
              add_limited_ids_condition!(sql, options) if !
using_limitable_reflections?(reflections) && options[:limit]

              sql << "ORDER BY #{options[:order]} " if options[:order]

              add_limit!(sql, options) if using_limitable_reflections?
(reflections)

              return sanitize_sql(sql)
# ---- end else for example proposal code
          end
        end
This topic is locked and can not be replied to.