can one create a "session" of Perl & Mysql?

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

Hi Monks!
I have made a script with Perl and Mysql, executing queries. The script works fine, the thing I would like to change -if possible- is to not need to write the credentials all the time, but only when I first execute it, and then somehow they remain stored until I logout or something.
My script looks as follows:

use strict;
use warnings;
use Term::ReadKey;
use DBI;
use List::Util qw( min max );
use Data::Types qw/:all/;
use DateTime;
use DateTime::Format::Strptime;
use Date::Calc qw(:all);

my $dates_file = $ARGV[0];

my $dsn = "DBI:mysql:DiabetesDB";
print "Please give your username:";
my $username =  <STDIN>;
chomp $username;

ReadMode(0);
print "Password for user \"$username:\"";        
ReadMode('noecho');
my $password = ReadLine(0);
ReadMode 'normal';
chomp $password;

# connect to MySQL database
my %attr =  (   PrintError=>0,      # turn off error reporting via war
+n()
                RaiseError=>1       # turn on error reporting via die(
+)           
            );

my $dbh = DBI->connect($dsn,$username,$password, \%attr);
print "\nUser \"".$username."\" is connected to the \"DiabetesDB\" dat
+abase.\n";

my ($patient_id_date, $specific_date, $wanted_date, $type_of_search, $
+max_window, $sql_query)='';

open DATES, $dates_file;
while(<DATES>)
{
    chomp;
    if($_=~/^(.*?)\t(.*?)\t(\d+)\t(.*)/)
    {
        $patient_id_date=$1;
        $specific_date=$2;
        $max_window=$3;
        $type_of_search=$4;

        my %p; 
        @p{qw(year month day)} = split /\//, $specific_date;
        my $dt = DateTime->new(%p);

        my $wanted_date = $dt->clone->add(months => $max_window)->strf
+time('%Y/%m/%d'); #this is the date I am interested in, +X months

        if($type_of_search eq 'past')
        {
            $sql_query = "SELECT * FROM MEASUREMENTS WHERE MEASUREMENT
+S.patient_id='".$patient_id_date.
                         "' AND MEASUREMENTS.measure_date<'".$wanted_d
+ate."' ORDER BY MEASUREMENTS.measure_date DESC LIMIT 1";
        }

        elsif($type_of_search eq 'future')
        {
            $sql_query = "SELECT * FROM MEASUREMENTS WHERE MEASUREMENT
+S.patient_id='".$patient_id_date. 
                         "' AND MEASUREMENTS.measure_date>'".$wanted_d
+ate."' ORDER BY MEASUREMENTS.measure_date ASC LIMIT 1";
        }

        elsif($type_of_search eq 'both')
        {
            $sql_query = "(SELECT * FROM MEASUREMENTS WHERE MEASUREMEN
+TS.patient_id='".$patient_id_date. 
                         "' AND MEASUREMENTS.measure_date<'".$wanted_d
+ate."' ORDER BY MEASUREMENTS.measure_date DESC LIMIT 1)".
                         "  UNION (SELECT * FROM MEASUREMENTS WHERE ME
+ASUREMENTS.patient_id='".$patient_id_date.
                         "' AND MEASUREMENTS.measure_date>'".$wanted_d
+ate."' ORDER BY MEASUREMENTS.measure_date ASC LIMIT 1)";
        
        }

        my $sth = $dbh->prepare($sql_query);
        $sth->execute();
        $sth->dump_results( );
        $sth->finish();
        print "\n";
    }
}
close DATES;
[download]

So, as you can see, the user is asked to provide his/her credentials when calling the script. I was wondering if there is a way so that the user does it once, but if they re-run the script 2 mins later, they can still be "logged in" on the Mysql server. Is that possible, and, if yes, how does one go about it?

Thanks!

Comment on can one create a "session" of Perl & Mysql? Download Code

Replies are listed 'Best First'.
Re: can one create a "session" of Perl & Mysql? by Marshall (Canon) on Sep 25, 2018 at 15:55 UTC
First as a suggestion, you might want to look at this node: What are placeholders in DBI, and why would I want to use them?. While your code as written does work, the prepare statements can be somewhat "expensive". By using place holders, you can prepare the statement(s) once and then execute them with different values. The table name can't be a variable because a prepare is a table specific execution strategy. If you want to save the credentials for a future run of your script, you will have to have some mechanism for persistence of this data, perhaps in some table in different SQL master account? There are various security issues with this and of course you would have to handle the case where the user changes their credentials unbeknownst to your script. Perhaps other Monks have ideas on this. You could also leave your script running so the user could just keep it open in a window with a prompt for the next set of user values (without having to enter the login stuff again). I wanted to mention the place holder idea so that you can move some code out of the loop. Update: still working on my morning coffee... Now that I re-consider this, I would re-organize the loop such that you can keep the script running in a separate window. User would exit by typing "quit", closing that window or whatever. This is an extra step for a single query, but easier for multiple queries and avoids complications with persistent storage of important security information.	[reply]
Re: can one create a "session" of Perl & Mysql? by NetWallah (Canon) on Sep 25, 2018 at 17:42 UTC
Persistent info usually means a "service". This code could easily be adapted to being a simple web server, using something like HTTP::Server::Simple. (Dancer2 and the like would be overkill) As a web Service, it stays in the background until the web page is visited again, or some programmed timeout. Memory fault -- brain fried	[reply]
Re: can one create a "session" of Perl & Mysql? by bliako (Abbot) on Sep 25, 2018 at 23:05 UTC
When I last looked DB connections could not be serialised for saving them to a file for later use. So, saving your connection for later, to a file and exiting your program will not work unless some sort of serialisation of the connection becomes possible. Then you have the "Service" model (Re: can one create a "session" of Perl & Mysql?) or the "daemon" model, I add. In either of these, you could use connection pooling. The first time a user appears (or re-appears after a long time interval) they give credentials and a connection is created for them waiting in the pool, possibly associated with a cookie the user saves on their side in order to get exact same connection the next time. But it means that you have to mediate all your DB queries through the service/daemon which will slow things down. But it has a benefit: you can restrict DB access to only a few formalised queries. For example: query1: it only accepts an AGE field and gets back 20 results max. query2: ... etc. So, doing it this way you will need to write 2 programs: the service/daemon and the client. Quite insecure if external access is allowed because it relies on user presenting a cookie for access to DB. But then isn't everything cookie-driven nowaday? It is possible to connect to mysql without providing credentials by creating a profile (see `mysql_config_editor`, search for passwordless mysql) and pointing mysql every time you need access, to the profile data file. Can this be achieved via Perl DBI? I don't know. You could save the credentials to a file (VERY INSECURE) to be read next time the same user requests DB access. I will not describe this any further because it's twice as wrong as it's practical. I am just thinking out loud, bw bliako	[reply] [d/l]
Re: can one create a "session" of Perl & Mysql? by Anonymous Monk on Sep 26, 2018 at 01:18 UTC
Um, wow, the responses are staggering You do not need a daemon, connection pooling, a way to serialize DB connections, a master mysql account.. You needs a special text file called a configuration file, ex ~/.myopsprograminfochmod600, where your program writes the credentials you prompt the user for, thats it	[reply]
Re^2: can one create a "session" of Perl & Mysql? by Anonymous Monk on Sep 26, 2018 at 14:54 UTC
If you decide on a configuration file, you may want it encrypted. If you do, Config::Identity may be of help to you. I qualify the recommendation because: You need a working GPG installation It actually spawns a command to do the encryption, so your script needs to run in an environment where this is possible It does not create the file, only read and decrypt it once created Should you decided to pursue this module, I found that I had to set environment variable `CI_GPG` to the path to gpg2, because by default the module prefers gpg.	[reply] [d/l]
Re^2: can one create a "session" of Perl & Mysql? by Anonymous Monk on Sep 26, 2018 at 10:05 UTC
Yeah... Thanks everyone for the suggestions, but I actually went for the "easy" fix here, to have a standard file that I can read the credentials from :)	[reply]
Re^2: can one create a "session" of Perl & Mysql? by bliako (Abbot) on Sep 26, 2018 at 12:57 UTC
sure, we are all adults here so i wont spam-warn about the obvious.	[reply]
Re^3: can one create a "session" of Perl & Mysql? by Anonymous Monk on Sep 27, 2018 at 00:41 UTC
bliako : sure, we are all adults here so i wont spam-warn about the obvious. =~ s/adult/baby/	[reply]
Re^4: can one create a "session" of Perl & Mysql? by bliako (Abbot) on Sep 27, 2018 at 09:39 UTC