ERROR 1064 (42000) at line 1 – Passing MySQL Create User Arguments via SSH

I was recently building a script to help move things from one server to another and in the process stumbled into a very annoying problem when passing MySQL arguments to a remote server via SSH.

Desired outcome: Create a new user in a remote DB using SSH.

In practice, this should be extremely simple. If you do this on the server, you could execute a command like this:

mysql --user="root" --password="[pass]" --execute="create user 'testing'@'localhost' identified by '123';"

You would now have a new user in your database called testing and it’s password would be 123.

I would logically assume I could pass this to the same server remotely from another server. I would assume you could do something like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="create user 'testing'@'localhost' identified by '123';"'

Especially because commands like this will work no problem:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="show databases;"'

Instead, you will probably get something like this:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123' at line 1

Turns out, if you need to escape the double-quotes when using the execute, but it seems only when you’re passing other arguments across.

Mind you, I actually tried escaping the characters as well, but I was doing them all, not just the doublequotes. I had done:

Example 1:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="CREATE USER 'foo2'@'localhost' IDENTIFIED BY 'mariadb';"'

Example 2:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute=&#34CREATE USER 'foo2'@'localhost' IDENTIFIED BY 'mariadb';&#34'

I’m not 100% on this one, but it’s the only thing that makes sense.

So to get it working, it actually has to be like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute=\"create user 'testing'@'localhost' identified by '123';\"'

You will notice I added the \ at the opening double-quotes and the end. And make note that it doesn’t work with single quotes, must be the double-quotes escaped. I am guessing it has to do with how the other parameters are being passed.

Now if you run something like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="SELECT * FROM mysql.user;"'

You should see testing is now created.

FYI – notice how I didn’t have to escape the double-quotes? Yes, this is my life… Shoutout to Shelby for helping me get here, he answered my please of despair.. hahaha