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 double quotes. 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

Website Security Platform

A full-stack performance, monitoring and security solution designed to simplify administrative tasks and remove the stress of online threats.

Learn More
Was this article helpful?
Yes No

Related Articles

Need Support?

Can't find the answer you're looking for?
Contact Support