A couple of points to note:
- You can omit the name of the database from the data source name (dsn) but you need the trailing colon.
- In the connect call the two undefs are needed (on the remote server at least, it worked ok locally without them).
my $my_cnf = 'path_to_somewhere_safe/my_cnf.cnf';
my $dsn =
"DBI:mysql:;" .
"mysql_read_default_file=$my_cnf";
my $dbh = DBI->connect(
$dsn,
undef,
undef,
{RaiseError => 1}
) or die "DBI::errstr: $DBI::errstr";
Note there is no connection info in the script at all. It's tucked away nicely in a cnf file. For example:
# my_cnf.cnf [client] host = hostname database = my_db user = my_usr password = my_pwd
The [cpan://DBD::mysql|DBD::mysql docs] give an indication on how to use option files but more can be found on the MySQL site:
- [http://dev.mysql.com/doc/refman/4.1/en/option-files.html|Using Option Files]
- [http://dev.mysql.com/doc/refman/4.1/en/mysql-options.html| mysql_options] (half way down the page)
- [http://www.stardata.it/articoli_en/hidden_param_articoli_en.html| Hiding connection parameters with Perl/DBI]
The last link points out that you need the two undef parameters in the connect statement.
Version info:
MySQL -> 4.0.17 (remote)
MySQL -> 4.1.15 (local)
DBI 1.32 (remote)
DBI 1.49 (local)
DBD::MySQL 2.0419 (remote)
DBD::MySQL 3.0002 (local)
Hope this is of some use and many thanks to [Your Mother] for the pointer.
John
update:
The 'interesting article' linked to above should be credited to [gmax] (see below).
++ to [gmax]
One more trick usable with DBD::mysql is mysql_read_default_group ,an option to have a configuration file with credentials for different applications.
First, you need to create a configuration file, with different [label]s.
# $HOME/.my.cnf # Here are some general options [client] socket=/tmp/mysql.sock port=3306 # the following are specific to each application [mysqldump] user=dumpuser password=not_my_pwd [backup] user=bkpuser password=not_my_real_one [usage] user=simpleguy password=not_this_one [readonly] user=poorguy password=don_t_try_it [myapp] user=specialguy password=something_different
Then, in your code you can refer to such labels this way:
my $dbh = DBI->connect("DBI:mysql:test"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
.';mysql_read_default_group=myapp',
undef,
undef
) or die "something went wrong ($DBI::errstr)";
This code will use the label [myapp] from the file $ENV{HOME}/.my.cnf.
To use a backup application, replace myapp with backup in the above code and your application will use that username and password under [backup].
You can also use this trick to test the same application with different users having different access profiles. (Update. - I mean database users, not O.S. users)
my $profile = shift || 'usage';
my $dbh = DBI->connect("DBI:mysql:test"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
.";mysql_read_default_group=$profile",
undef,
undef
) or die "something went wrong ($DBI::errstr)";
BTW, the article you were referring to is mine, also published in my blog.
Update - While mysql_read_default_file adds to security, because you won't leave your password hardcoded in your script and you can store it outside the document tree in web applications, using mysql_read_default_group is only a matter of convenience. Using it does not add to security, but just to tidiness.
_ _ _ _ (_|| | |(_|>< _|
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Ok this is pretty cool.. I think I need to using something like this. My question.. this hits the disk, so.. performance - If I need to query a table every time a user logs in, etc- this creates an expensive disk operation, right?
If 20 people log in per minute, or if every time a user does something I need to perform some kind of query to the db, then.. won't reading from disk for a db connection sort of slow things?
Maybe I'm missing something here- maybe I should be looking into opening a db connection for a user's session and not closing it somehow ?
Where do you think your database retrieves its data from, thin air? ;-) No, the db reads its data from disk as well, and that's a much more complicated operation than a simple text file read. Luckily (for both cases) the OS will cache often-accessed data in RAM, and therefore the expensive disk operation turns into a simple memory lookup. Which is why database servers can have too little RAM even if only a small percentage of it is being used by the DB process.
A computer is a state machine. Threads are for people who can't program state machines. -- Alan Cox
Now, if I have a text file with the word 'soup' in it. And I have an infinite loop that makes a system call to a script that reads the content into memory and then exits.. . Are you saying at some point the text file will be coming from memory instead of disk ?!
What if it's an infinite loop and it's spaced at a random 1 to 10 seconds appart?
Not only that, but if you have a process that opens the file, performs tr/ua/au/ on the content and writes it back, exits and starts again, then chances are most of these actions will never touch the disk but rather be performed entirely in RAM. Changed content will be written out to disk eventually of course, but that happens largely asynchronously to your activities and it's likely to be at a time when your OS isn't busy with the disk anyway and the disk is in a good state to perform the write. Assuming you're on a decent OS.
There are a number of parameters which influence how and when this will happen, such as frequency of the read/writes, general memory pressure on the system, disk activity, the filesystem used and also mount options (on *NIX you can mount disks in sync mode which ensures that data is written out to disk almost immediately, this can obviously slow down the system a lot).
See this text for some more details on how this is done on Linux.
A computer is a state machine. Threads are for people who can't program state machines. -- Alan Cox