Rails SQL generation (Firebird adapter) - can someone explai

Rails
1

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 J.

2

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