Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

What use DBI?

by monk2b (Pilgrim)
on Mar 12, 2013 at 20:19 UTC ( #1023042=perlquestion: print w/replies, xml ) Need Help??

monk2b has asked for the wisdom of the Perl Monks concerning the following question:

What are the benefits of using one of the DB modules like DBI as opposed to just making a mysql connection like this.
my $conn = qq|mysql -u ro_user -ppassword -e|; my $query = qq|show tables LIKE '\%jobs_finished\%'|; my $cmd = qq|$pre "$query" table_name|; open(TABLES, "$cmd|");
I know there must be real good reasons not to do this but the only thing that I can come up with is "it is bad practice to place the user name and password in the script". There has to many more drawbacks than that right?

Replies are listed 'Best First'.
Re: What use DBI?
by tobyink (Canon) on Mar 12, 2013 at 20:40 UTC

    Many, many good reasons to use DBI over that.

    Firstly, think of all the escaping necessary in defining $cmd. You need to think about not just escaping string literals at the SQL level, but also shell escaping because the command will be passed to your shell, and lastly Perl literal escaping. Ouch.

    With DBI you eliminate the need to think about shell escaping; and if you use prepared queries in DBI (which you should) you also eliminate the need to think about the escaping of SQL string literals.

    Secondly, accessing a database via pipes means the data you get back needs to be parsed. This can be non-trivial, especially if some of the results will include multi-line strings.

    Thirdly, using DBI, if you avoid using vendor-specific SQL extensions, it's pretty easy to write code that will run on many different databases - even databases you've never heard of. You'd never get that parsing the output of command-line SQL clients because each client has its own output formatting, its own command-line argument parsing, etc.


    package Cow { use Moo; has name => (is => 'lazy', default => sub { 'Mooington' }) } say Cow->new->name
Re: What use DBI?
by moritz (Cardinal) on Mar 12, 2013 at 21:07 UTC

    Some random thoughts:

    • Security: passing passwords on the command line is insecure, because other users can see them with ps and related tools
    • Security: getting the escaping right is non-trivial
    • Correctness: getting the parsing of the results right is non-trivial. As far as I can tell there's no reliable way to parse the output from the mysql command line tool, because it doesn't escape "special" characters
    • Performance: spawning a new process for each query is costly
    • Correctness: You don't check for errors at all.
Re: What use DBI?
by blue_cowdawg (Monsignor) on Mar 12, 2013 at 20:33 UTC
        What are the benefits of using one of the DB modules like DBI as opposed to just making a mysql connection like this.

    Are you kidding me? There's a whole ton of work you save yourself using DBI and the associated driver modules in the DBD:: family. I'm not even sure how your code shown in your post would even accomplish anything.

    One typical example of code:

    #!/usr/bin/perl -w use strict; use DBI; # connect to the database and create a handle to the connection. my $dbh=DBI->connect('DBI:mysql:host=myhost.domain.tld;database=mydata +base', 'user','password') or die $DBI::errstr; #create a statement handle and prep our SQL my $sth=$dbh->prepare(qq( select user_id,username,password,given,surname from users_table order +by user_id )) or die $dbh->errstr; $sth->execute(); while (my $row=$sth->fetchrow_arrayref){ # do something with it } | rest of code
    In my mind and I'm sure I'm not alone using DBI is just basic to using databases with Perl. I am positive that without using DBI and writing code to scrape the output from a database the way you seem to describe would require a lot more code that my example above. You'd also run into lots of issues with syntax, quoting and other issues that might be difficult to track down and diagnose.

    That reminds me: there's also the issue of portability. In 99% of the cases where I've written code that performs database operations I've been able to port it between database engines merely by switching which DBD driver I use. On rare occasion (using stored procedures for instance) I've had to change something up but that's rare.

    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1023042]
Approved by moritz
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2022-05-24 20:04 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (84 votes). Check out past polls.