Executing Multiple SQL Statements for MySQL Adapter

rubyonrails-core
1

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

2

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.

3

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

4

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

5

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

6

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

7

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