Rails - Mar 2006 - Rails SQL generation (Firebird adapter) - can someone explain ...

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

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