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="CREATE USER 'foo2'@'localhost' IDENTIFIED BY 'mariadb';"'
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.
Related Articles
- PHP-FPM Pool Setup: WordPress Requires FTP/SFTP User After Successful Configuration
- PHP-FPM: ERROR: [pool [name1]] unable to set listen address as it’s already used in another pool ‘[name2]’
- NET::ERR_CERT_COMMON_NAME_INVALID When Moving SSL Certs Between Servers
- MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- Troubleshooting Random Connection Errors with a WAF/CDN