http://qs321.pair.com?node_id=67188

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

I have a MySQL database that has a password that is "some@thing". If I connect to the DB using using an escaped string or collect the password as a CGI parameter it connects without a problem. I've found that it chokes on the password when I read it from a file. As a psuedo-code snippet: 

$_ = "host|db_name|user|some@thing";
($host, $database, $username, $password) = split /\|/;
$dbh = DBI->connect("DBI:mysql:$database:$host", $username, $password)
+ || error('login');

[download]


This causes the DB connection to fail because of the password. If I do this, it then works: 

$_ = "host|db_name|user|some@thing";
($host, $database, $username, $password) = split /\|/;
$password = "some\@thing";
$dbh = DBI->connect("DBI:mysql:$database:$host", $username, $password)
+ || error('login');

[download]


I've tried $password = quotemeta($password) but that didn't work either.

I'm stumped and this is holding up my progress. How do I go about escaping the "@" character so MySQL is happy?

Thanks - THRAK

Replies are listed 'Best First'.
Re: Escaping MySQL password
by sutch (Curate) on Mar 26, 2001 at 18:13 UTC
    Since your password is the last element on the line read from the file, perhaps you need a chomp to remove a newline?
[reply]
Re: Escaping MySQL password
by ChOas (Curate) on Mar 26, 2001 at 18:15 UTC
    Hey

    Try:
    $_ = "host|db_name|user|some\@thing";

    or:

    $_ = 'host|db_name|user|some@thing';

    In you example Perl is looking for the @thing list...
    (Unless declared by you) it will be 'undef', and your
    password (after the split) will look like "some"

    something you won`t notice when you don't use strict and -w

    So use strict, and -w

    GreetZ!,
      ChOas

    print "profeth still\n" if /bird|devil/;
[reply]
Re: Escaping MySQL password
by kilinrax (Deacon) on Mar 26, 2001 at 18:17 UTC
    The problem with the specific snippet you quote here is that double-quoting the string leads it being interpolated, thus; 
    $_ = "host|db_name|user|some@thing";

    [download]
    will result in $_ containing `host|db_name|user|some`, at the array `@thing` is undefined at this point in the code.
    Try using single quotes instead.
[reply]
[d/l]
Re: Escaping MySQL password
by davorg (Chancellor) on Mar 26, 2001 at 18:19 UTC

    I think you'll find that if you develop scripts with -w and use strict then perl will do its best to tell you what you're doing wrong.

    In this case, I'm guessing that it'll complain about an unescaped '@' in a double-quoted string.

    --
    <http://www.dave.org.uk>

    "Perl makes the fun jobs fun
    and the boring jobs bearable" - me

[reply]
      I am using strict and -w, I alway do. Perl doesn't complain because it's just reading what it found in the file. This isn't the exact code, but just a brief pseudo-code to illustrate the problem. $_ is actually a line as within a while loop from a file read. I haven't tried some of the suggestions above, but I will give it a go and see what happens.

      Thanks - THRAK
[reply]
Re: Escaping MySQL password
by THRAK (Monk) on Mar 26, 2001 at 18:49 UTC
    Duh! Chomp. Somethings are so obvious you look right through them. Generally when I read from a file I always chomp it immediately to avoid these types of things. Thanks a lot for helping me see the (fairly) obvious.

    THRAK
[reply]
Re: Escaping MySQL password
by Masem (Monsignor) on Mar 26, 2001 at 19:24 UTC
    Another option is to move the user name and password out from the perl file into a configuration file, which would not need to be quoted there, and you gain the bonus of having your script a bit more secure, particularly from other users on the same system. For MySQL, you simply need a .mycfg in the home dir of the script's owner.

    With mod_perl, you can also stick the username/password details into the http configuration file, but I would suspect that you'd need to quote them there as well.

    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
[reply]

Back to Seekers of Perl Wisdom