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

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.

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

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

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

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

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