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

Hi Monks,

I am running a database utility and I am creating a database through a menu driven script. I am getting the following error

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 '-secure-test' at line 1 at C:/Users/Sennovate/workspace/Perl/Automation/Sennovate/MySQL DB Creation/ssg-db-utility.pl line 79, <STDIN> line 5.

I am using Strawberry Perl 5.20.1

Here is my code snippet

sub create_database {
    print "Please enter the hostname:";
    chomp($hostname = <STDIN>);
    print "Please enter the name of the new MySQL database:";
    chomp($database = <STDIN>);
    $dsn = "dbi:$driver::$hostname:$port";
    
    print "Please type the MySQL root username:";
    chomp($username = <STDIN>);
    print "Please type the MySQL root password:";
    chomp($password = <STDIN>);

    $dbh = DBI->connect($dsn, $username, $password);
    $result = $dbh->do("create database $database");
    $dbh->do("use $database");

    if($result) {
        say "Database '$database' created successfully";
    } else {
        say "Database '$database' creation failed";
        exit;
    }    
}

[download]

Here is the input

----------------------------------------
S E N N O V A T E   I N C
SSG Database Utility - Version 1.1
Copyright Sennovate Inc, 2014
----------------------------------------
    
M E N U
-------
1. Create new database
2. Connect to existing database
3. Create tables
4. Create user
5. Drop existing database
6. Quit    
        
Please enter your choice:1
Please enter the hostname:db0002.stage-us.sennovate.com
Please enter the name of the new MySQL database:ssg-secure-test
Please type the MySQL root username:ssg-stage
Please type the MySQL root password:p7ZUrf59zJHYMMuLKfWTxC
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 righ
+t syntax to use near '-secure-test' at line 1 at C:/Users/Sennovate/w
+orkspace/Perl/Automation/Sennovate/MySQL DB Creation/ssg-db-utility.p
+l line 79, <STDIN> line 5.
Database 'ssg-secure-test' creation failed
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 righ
+t syntax to use near '-secure-test' at line 1 at C:/Users/Sennovate/w
+orkspace/Perl/Automation/Sennovate/MySQL DB Creation/ssg-db-utility.p
+l line 80, <STDIN> line 5.

[download]

Please help

Replies are listed 'Best First'.
Re: DBD::mysql::db do failed: You have an error in your SQL syntax;
by choroba (Cardinal) on Jan 05, 2015 at 11:23 UTC
    Can a database name contain a dash? If so, can you use such a name unquoted in a command?
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
Re: DBD::mysql::db do failed: You have an error in your SQL syntax;
by KurtSchwind (Chaplain) on Jan 05, 2015 at 12:36 UTC 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.56 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights res
+erved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software
+,
and you are welcome to modify and redistribute it under the GPL v2 lic
+ense

Type 'help;' or '\h' for help. Type '\c' to clear the current input st
+atement.

mysql> create database test-dash;
ERROR 1064 (42000): You have an error in your SQL syntax; check the ma
+nual that corresponds to your MySQL server version for the right synt
+ax to use near '-dash' at line 1
mysql>

    [download]

    You cannot put dashes in database names. 

    mysql> create database 'test-dash';
ERROR 1064 (42000): You have an error in your SQL syntax; check the ma
+nual that corresponds to your MySQL server version for the right synt
+ax to use near ''test-dash'' at line 1
mysql> create database "test-dash";
ERROR 1064 (42000): You have an error in your SQL syntax; check the ma
+nual that corresponds to your MySQL server version for the right synt
+ax to use near '"test-dash"' at line 1
mysql> create database test\-dash;
ERROR:
Unknown command '\-'.
ERROR 1064 (42000): You have an error in your SQL syntax; check the ma
+nual that corresponds to your MySQL server version for the right synt
+ax to use near '\-dash' at line 1

    [download]
    --
    I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.
[reply]
[d/l]
[select]

      You can have a dash in a database name if it is done inside backticks

      mysql> create database `test-dash`;
Query OK, 1 row affected (0.05 sec)

      [download]
[reply]
[d/l]
        Thanks rnewsham. It worked like a champ Modified code 
        sub create_database {
    print "Please enter the hostname:";
    chomp($hostname = <STDIN>);
    print "Please enter the name of the new MySQL database:";
    chomp($database = <STDIN>);
    $dsn = "dbi:$driver::$hostname:$port";
    
    print "Please type the MySQL root username:";
    chomp($username = <STDIN>);
    print "Please type the MySQL root password:";
    chomp($password = <STDIN>);

    $dbh = DBI->connect($dsn, $username, $password);
    $result = $dbh->do("create database `$database`");
    $dbh->do("use `$database`");

    if($result) {
        say "Database '$database' created successfully";
    } else {
        say "Database '$database' creation failed";
        exit;
    }    
}

        [download]
[reply]
[d/l]

        Forgot about back-tick quoting in MYSQL. Yep. That'll work.

        --
        I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.
[reply]
Re: DBD::mysql::db do failed: You have an error in your SQL syntax;
by locked_user sundialsvc4 (Abbot) on Jan 05, 2015 at 13:39 UTC

    And of course we may presume that this is an internal application that can be trusted not to be in the hands of any internal employees named Bobby Tables, right?

Back to Seekers of Perl Wisdom