DBI: CREATE USER statement fails

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I want to add new users to MySQL with DBI. I've been using DBI and DBD::mysql for years and  
adding the user directly to mysql.user and mysql.db. I want to change this method now to  
the "official" CREATE USER query syntax.

e.g. CREATE USER '$user'@'localhost' IDENTIFIED BY '$password'

Works fine via mysql in the shell, but fails when using perl.

/testmysqluser.pl testuser4 "testuser4-test"
DBD::mysql::db do failed: String 'testuser4' IDENTIFIED BY ' is too long for user name  
(should be no longer than 16) at ./testmysqluser.pl line 19.

This makes no sense as 'testuser4' isn't longer than 16 characters. It looks like DBI tries  
to create a user "'testuser4' IDENTIFIED BY '" which would indeed be longer than 16  

This happens on CentOS 5 with MySQL and DBI/DBD provided by the base installation. I tried  
several other MySQL versions on other CentOS installations, and a newer DBI version. Same  
problem. Also tried with and without the single quotes around the strings, error message  
changes only slightly. I was wondering whether the error message was misleading and  
actually talks about the password hash length (16->41), but I also get it on systems with  
old MySQL passwords.
Am I doing something wrong or is this a DBI or DBD driver problem?

simplified code without variable/parameter fetching:

use DBI;

use DBD::mysql;

$dsn = "dbi:mysql:$mysqldb:localhost:3306";

$dbh = DBI->connect($dsn, $mysqluser, $mysqlpw) or die "Unable to connect: $DBI::errstr\n";

$sql = "CREATE USER '$user'@'localhost' IDENTIFIED BY '$password'";

$sth = $dbh->do($sql);


(As I understand I don't have to open the mysql database for "CREATE USER", but I have to  
specify a database for the dsn.)

Thanks for hints!

Re: DBI: CREATE USER statement fails

Quoted text here. Click to load it
Quoted text here. Click to load it

Notice how the localhost part didn't show up in the error message? That's a
clue. A bigger clue can be seen if you'd just enable warnings:

  Possible unintended interpolation of @::localhost in string at - line 4.
  Name "main::localhost" used only once: possible typo at - line 4.

To put an @ character in a qq string you need to use \@ or you'll be
interpolating an array.

@'localhost is equivalent to @::localhost because of compatibility with an
ancient perl version that wasn't trying to look like C++.

Alan Curry

Re: DBI: CREATE USER statement fails

Alan Curry schrieb am Sat, 21 Dec 2013 14:19:47 +0000 (UTC):

Quoted text here. Click to load it

Should have known that, thanks for spotting. That happens if you program  
just once a year a bit with perl by adjusting your year's old perl scripts  
;-) I've also put a "-w" now in my shebang line. Used to do that year's  
ago, but not for these scripts.

Have nice holidays!


Re: DBI: CREATE USER statement fails

Quoted text here. Click to load it

If the OP had used placeholders and a single-quoted string this problem
would have never existed:

    my $sql = 'CREATE USER ?@? IDENTIFIED BY ?';
    $dbh->do($sql, undef, $user, "localhost", $password);

This will also avoid potential SQL-injection problems if $password
contains a single-quote character.

Quoted text here. Click to load it

More specifically, it was trying to look like Ada. It was a bad plan,
most importantly because syntax-highlighters hated it.


Re: DBI: CREATE USER statement fails

Quoted text here. Click to load it

This deserves some emphasis: The sole reason why 'SQL injection' exists
at all is because people construct complex SQL-statements via string
interpolation and get the quoting wrong because this is much more
complicated than simply passing a template statement with placeholders
to 'the DB engine' (however implemented) and a list of
parameters. Programming is not a sport abnd there are no consolation
prizes for "trying hard to accomplish something stupid and failing at
that". Use the simple way.

Re: DBI: CREATE USER statement fails

El 21/12/13 13:37, Kai Schaetzl escribió:
Quoted text here. Click to load it

Are you sure that command is as you think it is?

try inserting a

print "$sql\n";

Site Timeline