Rails core - Feb 2009 - Executing Multiple SQL Statements for MySQL Adapter

Hey guys,

Is this the recommended procedure for executing multiple SQL
statements in a single call?  http://www.seanr.ca/tech/?p=75

In other words, do we have a config option to trun on
CLIENT_MULTI_STATEMENTS for MySQL?  Or are we still being advised to
go directly into the rails source to achieve this?  Seems like it''d be
dead simple to add this as a configuration option (for MySQL
specifically) either in database.yml, or in the environment files.

Thanks.

-John
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

I believe that you can override this with a client setting in my.cnf
or the user-specific mysql settings.  By not providing a value in
rails we just fall back to the user / system wide settings. Max packet
size isn''t really something I think we need in the database.yml.

On Thu, Feb 19, 2009 at 4:32 AM, John Trupiano <jtrupiano@gmail.com>
wrote:
>
> Hey guys,
>
> Is this the recommended procedure for executing multiple SQL
> statements in a single call?  http://www.seanr.ca/tech/?p=75
>
> In other words, do we have a config option to trun on
> CLIENT_MULTI_STATEMENTS for MySQL?  Or are we still being advised to
> go directly into the rails source to achieve this?  Seems like
it''d be
> dead simple to add this as a configuration option (for MySQL
> specifically) either in database.yml, or in the environment files.
>
> Thanks.
>
> -John
> >
>


-- 
Cheers

Koz

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Hrmm....this is not for max packet size, this is to allow the MySQL
Adapter to execute multiple statements in a single call to execute(),
e.g.

CREATE TEMPORARY TABLE temp_pets (
  `id` int(11) NOT NULL auto_increment,
  `child_id` int(11) NOT NULL default ''0'',
  `pets_name` int(11) NOT NULL default ''0''
);
INSERT INTO temp_pets
  SELECT id, child_id, pets_name FROM pets;

SELECT
<some_fancy_reporting_metrics_that_the_temp_table_is_indexed_more_appropriately_to_handle_efficiently>
FROM temp_pets;

DROP TABLE temp_pets;

I know my example is very contrived, but imagine any scenario where it
would be faster to create and operate from/within a temp table.
Often, you simply have several SQL commands that can/should be run all
at once (because the round trip to/from the db server is entirely
unnecessary, or the return values of intermediate commands are
throwaways).

---------------

That said, the way this is described in the blog post I referenced is
that it is configured as a parameter to the Adapter constructor:

ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username,
password, database, port, socket, 65536], config)

If we''ve already got a hook at the Adapter level, why not bubble it up
to the app configuration level?  Furthermore, this would seem to be
something that you''d want to define on a per-app level, not the DMBS
level or even the level of the database itself.  It''s asking quite a
bit (in my opinion) to have to modify the DBMS configuration for every
box that would ever want to run a database for my app.

I''m not so much concerned with whether or not this works right now.  I
was looking for the "best practice" for achieving this within rails.
If it''s just not available yet, I''d be happy to dive in and
create a
patch....just wanted to see if there was already a solution for this.

FWIW, here''s how I''m working around it:

sql = <<-SQL
  <statement_1>;
  <statement_2>;
  <statement_3>;
SQL

sql.strip.split('';'').each do |s|
  ActiveRecord::Base.connection.execute(s)
end

-John

On Feb 18, 3:46 pm, Michael Koziarski <mich...@koziarski.com>
wrote:
> I believe that you can override this with a client setting in my.cnf
> or the user-specific mysql settings.  By not providing a value in
> rails we just fall back to the user / system wide settings. Max packet
> size isn''t really something I think we need in the database.yml.
>
>
>
> On Thu, Feb 19, 2009 at 4:32 AM, John Trupiano <jtrupi...@gmail.com>
wrote:
>
> > Hey guys,
>
> > Is this the recommended procedure for executing multiple SQL
> > statements in a single call?  http://www.seanr.ca/tech/?p=75
>
> > In other words, do we have a config option to trun on
> > CLIENT_MULTI_STATEMENTS for MySQL?  Or are we still being advised to
> > go directly into the rails source to achieve this?  Seems like
it''d be
> > dead simple to add this as a configuration option (for MySQL
> > specifically) either in database.yml, or in the environment files.
>
> > Thanks.
>
> > -John
>
> --
> Cheers
>
> Koz
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Cheers,

John Trupiano wrote:
> Hey guys,
> 
> Is this the recommended procedure for executing multiple SQL
> statements in a single call?  http://www.seanr.ca/tech/?p=75
> 
> In other words, do we have a config option to trun on
> CLIENT_MULTI_STATEMENTS for MySQL?  
I''m very happy that this is not possible as it is a good security 
precaution. Multiple statements per call make you vulnerable to SQL 
injection if you ever make a mistake with escaping. See for example this 
case:

http://blog.innerewut.de/2008/6/16/why-you-should-upgrade-to-rails-2-1

This is one of the few cases where MySQL actually behaves more secure 
than PostgreSQL&co.

Jonathan


-- 
Jonathan Weiss
http://blog.innerewut.de
http://twitter.com/jweiss

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Good point Jonathon.  I''d probably want to avoid that vulnerability in
most cases.  Since this appears to be a per-connection option for
MySQL, perhaps I''m better off just opening an entirely separate
connection to the DB for this report, so that CLIENT_MULTI_STATEMENTS
is only turned on for the few reports that would benefit?

This said, I''d argue that avoiding this option to prevent SQL
Injection is a "paranoid configuration."  In other words, the DB admin
doesn''t necessarily trust his application devs.  If we just sanitize
all input (which is what we''re supposed to do anyway when receiving
data from the client), you''d avoid this, no?

-John

On Feb 19, 1:44 am, Jonathan Weiss <j...@innerewut.de>
wrote:
> Cheers,
>
> John Trupiano wrote:
> > Hey guys,
>
> > Is this the recommended procedure for executing multiple SQL
> > statements in a single call?  http://www.seanr.ca/tech/?p=75
>
> > In other words, do we have a config option to trun on
> > CLIENT_MULTI_STATEMENTS for MySQL?  
>
> I''m very happy that this is not possible as it is a good security
> precaution. Multiple statements per call make you vulnerable to SQL
> injection if you ever make a mistake with escaping. See for example this
> case:
>
> http://blog.innerewut.de/2008/6/16/why-you-should-upgrade-to-rails-2-1
>
> This is one of the few cases where MySQL actually behaves more secure
> than PostgreSQL&co.
>
> Jonathan
>
> --
> Jonathan Weisshttp://blog.innerewut.dehttp://twitter.com/jweiss
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

> This said, I''d argue that avoiding this option to prevent SQL
> Injection is a "paranoid configuration."  In other words, the DB
admin
> doesn''t necessarily trust his application devs.  If we just
sanitize
> all input (which is what we''re supposed to do anyway when
receiving
> data from the client), you''d avoid this, no?
Paranoid configurations are exactly the thing you want from a security 
standpoint. Defense in depth is very important. Is it not that the DB 
admin does not trust his devs but that they are not perfect and make 
mistakes.

Jonathan

-- 
Jonathan Weiss
http://blog.innerewut.de
http://twitter.com/jweiss

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Sure, I wasn''t trying to argue your point.  I was just suggesting that
there does exist legitimate circumstances where you''d want to
circumvent this "protection."  Jonathon, are you suggesting that due
to the risks, we shouldn''t allow any way to (even in a very localized
fashion) allow us to bypass that?

I can phrase the question another way.  Would you write the following
chunk of code in a different way?

sql = <<-SQL
  <statement_1>;
  <statement_2>;
  <statement_3>;
SQL

sql.strip.split('';'').each do |s|
  ActiveRecord::Base.connection.execute(s)
end

(btw, I realize there''s wasted cycles here in splitting what amounts
to a static string....my code looks like this so that I can debug
easier directly in the mysql shell by copying/pasting that chunk.
Furthermore, it just reads a lot nicer to any dev coming behind me)

Perhaps the best answer is simply going with a stored procedure?

-John

On Feb 19, 8:01 am, Jonathan Weiss <j...@innerewut.de>
wrote:
> > This said, I''d argue that avoiding this option to prevent SQL
> > Injection is a "paranoid configuration."  In other words,
the DB admin
> > doesn''t necessarily trust his application devs.  If we just
sanitize
> > all input (which is what we''re supposed to do anyway when
receiving
> > data from the client), you''d avoid this, no?
>
> Paranoid configurations are exactly the thing you want from a security
> standpoint. Defense in depth is very important. Is it not that the DB
> admin does not trust his devs but that they are not perfect and make
> mistakes.
>
> Jonathan
>
> --
> Jonathan Weisshttp://blog.innerewut.dehttp://twitter.com/jweiss
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---