CODEWITHSUNDEEP
mysqldatabaseperl
Andrew
Andrew

Reputation: 241

perl DBI grant privileges

Please help understand what I'm doing wrong here. I guess escaping is wrong, but I can't google a workable example.

my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pass = "111";
my $db_name = "test";
my $db_user = "test";
my $db_pass = "test";

my $dsn = "dbi:mysql::$host:$port";
my $dbh = DBI->connect($dsn, $user, $pass) or die "Unable to connect: $DBI::errstr\n";

$dbh->do("CREATE DATABASE $db_name");
$dbh->do("CREATE USER $db_user\@$host");
$dbh->do("GRANT ALL ON $db_name.* TO $db_user\@$host IDENTIFIED BY $db_pass");

$dbh->disconnect();

Error:

DBD::mysql::db do failed: Operation CREATE USER failed for 'test'@'localhost' at /home/andrew/sandbox/script.pl line 42.
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1 at /home/andrew/sandbox/script.pl line 43.

Thank you for answers.

Upvotes: 0

Views: 1568

Answers (2)

derobert
derobert

Reputation: 51197

You need to quote the password.

$dbh->do("GRANT ALL ON $db_name.* TO $db_user\@$host IDENTIFIED BY '$db_pass'");
                                                                   ^        ^

Though of course prepared statements/parameters would be much better (if MySQL allows them here, which I'm not 100% sure of).

You should quote the $db_user and $host as well (again, assuming parameters don't work).

If parameters work:

$dbh->do(q{GRANT ALL ON ?.* TO ?@? IDENTIFIED BY ?}, {}, $db_name, $db_user, $host, $db_pass);

I'm fairly confident parameters will work on the password, and probably also on user and host. The database name, I'm not so sure of. You may just have to inline it with quote_identifier.

edit: You should just strike the CREATE USER line entirely. Granting permissions will create the user (and with a password).

Upvotes: 1

Matt Fenwick
Matt Fenwick

Reputation: 49105

It looks like you're trying to create the user every time you run the script -- I get that exact error if I try to create a user more than once.

Upvotes: 0

Related Questions