Perl script executing mysql commands?

I'm new to Perl and was attempting to write a script on a RHEL box that will auto-configure a vanilla DB for new sites we create on our host. I already have the connect statement working and I'm able to connect and create a DB from the script (Used $dbh->do( qq(CREATE DATABASE $dbcreate) ); Is this the best way???), but I have a couple that I haven't been able to figure out how to make them work.

These are the mysql commands which I'm having issues with. Any suggestions? Thanks!

$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
$dbh->do( qq(CREATE DATA开发者_JAVA百科BASE $dbcreate) );
$dbh->do(qq(GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `$dbcreate` . * TO 'moodle'@'%'`) );
$dbh->do( qq(FLUSH PRIVILEGES) );
$dbh->do( qq($dbcreate < MySQL_pristine.sql) );
$dbh->do( qq(USE $dbcreate) );
$dbh->do( qq(UPDATE md1_label SET content = REPLACE( content, "pristine", "$dbcreate")) );
$dbh->do( qq(UPDATE md1_label SET contents = REPLACE( contents, "pristine", "$dbcreate")) );
$dbh->do( qq(UPDATE md1_label SET questiontext = REPLACE( questiontext, "pristine", "$dbcreate")) );

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 'GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX ,�' at line 1 at create-auto-db.pl line 52.
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 'test3 < MySQL_pristine.sql' at line 1 at create-auto-db.pl line 54.
DBD::mysql::db do failed: Table 'test3.md1_label' doesn't exist at create-auto-db.pl line 56.
DBD::mysql::db do failed: Table 'test3.md1_label' doesn't exist at create-auto-db.pl line 57.
DBD::mysql::db do failed: Table 'test3.md1_label' doesn't exist at create-auto-db.pl line 58.

The main issue you've encountered is interpolation of strings with containing an at-sign. If you have something like qq{Here is my email: test@hotmail.com} - this fails because it tries to put a list variable @hotmail, which doesn't exist. Either use a backslash to escape it (i.e., qq{Here is my email: test\@hotmail.com}), or if you don't need to interpolate any variables, use the non-interpolating quote q{...}.

Having said that, you need to be a little careful with some of these statements. You're putting variable values into these SQL statements, and that is a risk for SQL injection attacks. I'd use $dbh->quote($dbcreate) to get a string version, and $dbh->quote_identifier($dbcreate) to get an identifier version of the value of $dbcreate, and embed those values in. This is much safer, as it will avoid somebody doing a Bobby Tables on you and giving you a database name like: db'; DROP TABLE mysql.user; '; or similar. DBI provides both string and identifier quoting, so you can get the right kinds of quoting as needed. For example:

my $quoted_id_dbcreate = $dbh->quote_identifier($dbcreate);
$dbh->do( qq(USE $quoted_id_dbcreate) );

Placeholders are usually better, but some of these admin statements probably won't support them, so using proper quoting to inject the values is likely to be necessary.

Two things stand out to me.

$dbh->do(qq(GRANT SELECT , INSERT , (snip), EXECUTE ON '$dbcreate' . * TO 'moodle'@'%'`) );

...you've got a trailing backtick that you probably don't intend.

$dbcreate < MySQL_pristine.sql

...isn't quite what you want. I think you're trying to do is to read that file in Perl, and iterate over each contained SQL statement, calling "$dbh->do()" against it. If you're very lucky, you have one line per statement in that .sql file.