RE: (Fwd) Perl DBI question

[Vancura, Mark D (Mark)]

Bruce,
Thanks very much, I do see it in the several comments, and it appears to work 
as well!

Thanks to Tim and then particularly Bruce for showing me the way, I do 
appreciate it!
So in a nutshell:

When a mysql command executed in a perl DBI do or execute method:
load data local infile 'TEMP_LOAD_DATA_7318' into table ports ;

Gives the error message:
DBD::mysql::db do failed: The used command is not allowed with this 
MySQL version

It is because of the special treatment of load data, explained in 6.1.6. 
Security Issues with LOAD DATA LOCAL

*If you use LOAD DATA LOCAL in Perl scripts or other programs that read the 
[client] group from option files, you can add the local-infile=1 option to that 
group. However, to keep this from causing problems for programs that do not 
understand local-infile, specify it using the loose- prefix: 
[client]
loose-local-infile=1
*If LOAD DATA LOCAL is disabled, either in the server or the client, a client 
that attempts to issue such a statement receives the following error message: 
ERROR 1148: The used command is not allowed with this MySQL version

And the "option files" can be the dsn setup line in my perl program:
my $dsn = "DBI:mysql:database=test;host=myhost;mysql_local_infile=1;"

And then the load data local infile command will work without this error, and 
load the data!

I hope this helps.

Mark Vancura
LSI Corporation

-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu] 
Sent: Thursday, May 09, 2013 12:10 PM
Cc: dbi-users@perl.org (dbi-users@perl.org)
Subject: Re: (Fwd) Perl DBI question


On May 9, 2013, at 10:47 AM, "Vancura, Mark D (Mark)"  
wrote:

> Bruce,
> Thanks, the explanation makes sense, so I need to figure out how to 
> follow through on this documented suggestion from: 6.1.6. Security 
> Issues with LOAD DATA LOCAL
> 
> *If you use LOAD DATA LOCAL in Perl scripts or other programs that read the 
> [client] group from option files, you can add the local-infile=1 option to 
> that group. However, to keep this from causing problems for programs that do 
> not understand local-infile, specify it using the loose- prefix:
> [client]
> loose-local-infile=1
> 
> *If LOAD DATA LOCAL is disabled, either in the server or the client, a client 
> that attempts to issue such a statement receives the following error message: 
> ERROR 1148: The used command is not allowed with this MySQL version
> 
> Thus this fits my situation exactly, I just have to learn where the 
> "option files" are and how to put the [client] loose-local-infile=1 into it.
> 
> Any suggestions where to look this up?

right there in theat section you looked at, down in the comments is


"For use in perl DBI scripts, adding an option at the end of the data source 
definition for DBI->connect fixes the LOAD DATA LOCAL problem in some 
situations...

use strict;
use DBI;
my $dsn = "DBI:mysql:mydb;mysql_local_infile=1";
my $user = "me";
my $password = "secret";
my $dbh = DBI->connect($dsn,$user,$password);"

the $dsn creation statement is where you can put a ton of options; mysql-only 
ones would be documented in the DBD:mysql documentation, I'll bet.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: (Fwd) Perl DBI question

[Bruce Johnson]

On May 9, 2013, at 10:47 AM, "Vancura, Mark D (Mark)"  
wrote:

> Bruce,
> Thanks, the explanation makes sense, so I need to figure out how to follow 
> through
> on this documented suggestion from: 6.1.6. Security Issues with LOAD DATA 
> LOCAL
> 
> *If you use LOAD DATA LOCAL in Perl scripts or other programs that read the 
> [client] group from option files, you can add the local-infile=1 option to 
> that group. However, to keep this from causing problems for programs that do 
> not understand local-infile, specify it using the loose- prefix:
> [client]
> loose-local-infile=1
> 
> *If LOAD DATA LOCAL is disabled, either in the server or the client, a client 
> that attempts to issue such a statement receives the following error message: 
> ERROR 1148: The used command is not allowed with this MySQL version
> 
> Thus this fits my situation exactly, I just have to learn where the "option 
> files" are
> and how to put the [client] loose-local-infile=1 into it.
> 
> Any suggestions where to look this up?

right there in theat section you looked at, down in the comments is


"For use in perl DBI scripts, adding an option at the end of the data source 
definition for DBI->connect fixes the LOAD DATA LOCAL problem in some 
situations...

use strict;
use DBI;
my $dsn = "DBI:mysql:mydb;mysql_local_infile=1"; 
my $user = "me";
my $password = "secret";
my $dbh = DBI->connect($dsn,$user,$password);"

the $dsn creation statement is where you can put a ton of options; mysql-only 
ones would be documented in the DBD:mysql documentation, I'll bet.


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

RE: (Fwd) Perl DBI question

[Vancura, Mark D (Mark)]

Bruce,
Thanks, the explanation makes sense, so I need to figure out how to follow 
through
on this documented suggestion from: 6.1.6. Security Issues with LOAD DATA LOCAL

*If you use LOAD DATA LOCAL in Perl scripts or other programs that read the 
[client] group from option files, you can add the local-infile=1 option to that 
group. However, to keep this from causing problems for programs that do not 
understand local-infile, specify it using the loose- prefix:
[client]
loose-local-infile=1

*If LOAD DATA LOCAL is disabled, either in the server or the client, a client 
that attempts to issue such a statement receives the following error message: 
ERROR 1148: The used command is not allowed with this MySQL version

Thus this fits my situation exactly, I just have to learn where the "option 
files" are
and how to put the [client] loose-local-infile=1 into it.

Any suggestions where to look this up?

Thanks again for your help with this!

Mark Vancura

-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu] 
Sent: Thursday, May 09, 2013 11:26 AM
To: dbi-users@perl.org (dbi-users@perl.org)
Subject: Re: (Fwd) Perl DBI question


On May 9, 2013, at 9:37 AM, tim.bu...@pobox.com wrote:

> 
>   However, then I want to put data into it, with a command like the following:
> 
> 
> 
>   load data local infile 'TEMP_LOAD_DATA_26021' into table ports ;
> 
> 
> 
>   And I get:
> 
>   DBD::mysql::db do failed: The used command is not allowed with this MySQL 
> version at
>   read_excel_write_mysql.pl line 140.

I find this in the MySQL docs:

"If the statement fails, it is likely that your MySQL installation does not 
have local file capability enabled by default. See Section 6.1.6, "Security 
Issues with LOAD DATA LOCAL", for information on how to change this."



-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: (Fwd) Perl DBI question

[Bruce Johnson]

On May 9, 2013, at 9:37 AM, tim.bu...@pobox.com wrote:

> 
>   However, then I want to put data into it, with a command like the following:
> 
> 
> 
>   load data local infile 'TEMP_LOAD_DATA_26021' into table ports ;
> 
> 
> 
>   And I get:
> 
>   DBD::mysql::db do failed: The used command is not allowed with this MySQL 
> version at
>   read_excel_write_mysql.pl line 140.

I find this in the MySQL docs:

"If the statement fails, it is likely that your MySQL installation does not 
have local file capability enabled by default. See Section 6.1.6, “Security 
Issues with LOAD DATA LOCAL”, for information on how to change this."



-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

(Fwd) Perl DBI question

[tim.bu...@pobox.com]

- Forwarded message from "Vancura, Mark D (Mark)"  
-

Date: Thu, 9 May 2013 11:05:37 -0400
From: "Vancura, Mark D (Mark)" 
To: "tim.bu...@pobox.com" 
Subject: Perl DBI question

   Tim,

   I am newly trying to make Perl and DBI work with mysql, with some success, 
e.g. I have been

   able to get connected, accessing a database, and create a table in it, with 
this command:

   MySQL_command = create table ports (

PortName varchar(40),

direction enum('input','output','inout'),

digital_analog enum('digital','analog'),

port_pad enum('port','pad'),

powerSupplies varchar(20) ) ;

   executed create table command



   However, then I want to put data into it, with a command like the following:



   load data local infile 'TEMP_LOAD_DATA_26021' into table ports ;



   And I get:

   DBD::mysql::db do failed: The used command is not allowed with this MySQL 
version at
   read_excel_write_mysql.pl line 140.

   DBD::mysql::db do failed: The used command is not allowed with this MySQL 
version at
   read_excel_write_mysql.pl line 140.



   Whether I "do" it or "prepare" and then "execute" it.



   If I run /usr/bin/mysql interactively, these commands are completely 
successful, so it hardly seems like
   a "MySQL version" issue.



   But I also have not been able to find that message in any of the DBI/DBD 
code, thus I suspect part of it
   is coming from MySQL.



   Is there some permission issue related to "Load data local infile" that I 
can/must manage.  I am
   currently running as the "root" of

   mysql, and thus I expect I have, or can get any permission necessary.



   Thanks for any help you can provide.



   Mark Vancura

   LSI Corporation.





- End forwarded message -

Re: Huh? 4=3?

[Tim Bunce]

On Wed, May 08, 2013 at 10:58:02AM -0700, fe...@crowfix.com wrote:
> On Wed, May 08, 2013 at 09:43:27AM -0700, Bill Ward wrote:
> > Cool. That whole scalar vs list context thing is one of Perl's biggest
> > strengths, but also one of its biggest weaknesses (in that it is a common
> > source of bugs like this). When you see head-scratching problems, it's one
> > of the first things to look for.
> 
> Just guessing here, not familiar with the particular code in question.
> But I have been bitten a few times by code which returns false in the
> 'proper' manner
> 
> return;
> 
> instead of forcing a scalar return
> 
> return undef;
> 
> or list return
> 
> return ();

There is no difference between "return;" and "return ();".
Both return an empty list when called in list context
and an undef when called in scalar context.

The "return ();" style does serve as a reminder to the reader.

> Is that what's going on here -- the original code imparted a list
> context, which triggered another perl gotcha, whereby missing list
> values simply disappear:
> 
> scalar(1,2,,4,,6) ---> 4, not 6

That returns 6, or rather, it returns whatever happens to be the last value.

Tim.