How to insert a complex linux terminal command's output to mysql database table

Question

I am trying to convert the following terminal command:

date +"%m/%d/%Y %H:%M:%S"

whose output is:

12/07/2017 19:49:48

into a terminal command that will INSERT the terminal output from the date +"%m/%d/%Y %H:%M:%S" as values in a mysql database table:

The full terminal command I am trying to use is:

echo "INSERT INTO block (date, data, sha256) VALUES ($(date +"%m/%d/%Y %H:%M:%S"), "dogs are loyal", "6324542a2ce0b80ad32e45b5ca8e8b9dd02da8802c6fc9939637dabda113e35e");"| mysql blockchain -u root;

And I get the following error:

ERROR 1064 (42000) at line 1: 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 '19:49:48, dogs are loyal, 6324542a2ce0b80ad32e45b5ca8e8b9dd02da8802c6fc9939637da' at line 1

What is odd is I notice from the error that the "date" command runs but it is only capturing the %H:%M:%S since it mentions 19:49:48.

I tried running the command directly in the mysql command line interface and got the following error:

ERROR 1305 (42000): FUNCTION blockchain.$ does not exist

My database details are as follows:

My database name is "blockchain", table is called "block", and table descriptions are listed below.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blockchain         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql> show tables;
+----------------------+
| Tables_in_blockchain |
+----------------------+
| block                |
+----------------------+

mysql> desc block;

+--------+-------------------+------+-----+---------+----------------+
| Field  | Type              | Null | Key | Default | Extra          |
+--------+-------------------+------+-----+---------+----------------+
| id     | int(255) unsigned | NO   | PRI | NULL    | auto_increment |
| date   | varchar(20)       | NO   |     | NULL    |                |
| data   | varchar(250)      | NO   |     | NULL    |                |
| sha256 | varchar(64)       | NO   |     | NULL    |                |
+--------+-------------------+------+-----+---------+----------------+

Based on these errors, I am assuming it has something to do with special character, primarily the "$", but it may also be the "+" or the " " blank space in the command.

I did some research and I tried playing around with escape charactesr '' vs `` vs "$" vs "\" to have outputs of commands be INSERT values to mysql tables but I am still running into some issues and would like to reach out to this community as a last resort.

Thank you for your patience and assistance in advance.

Sincerely,

A humbly committed student

Answer 1

I think your generated sql do not have the right quotations around the data. Try the following:

echo "INSERT INTO block (date, data, sha256) VALUES ('$(date +"%m/%d/%Y %H:%M:%S")', "\'dogs are loyal\'", "\'6324542a2ce0b80ad32e45b5ca8e8b9dd02da8802c6fc9939637dabda113e35e\'");"| mysql blockchain -u root;

For my ubuntu vm it worked fine.