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

Have you tried running the command in the terminal with single quotes around the value i.e. 'dogs are loyal'? — Pankaj Gadge
– Pankaj Gadge, Commented Dec 12, 2017 at 2:36

Md Monjur Ul Hasan · Accepted Answer · 2017-12-12 03:03:06Z

2

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.

answered Dec 12, 2017 at 3:03

Md Monjur Ul Hasan

1,8011 gold badge16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

unitelife Over a year ago

Thank you M H Rasel, It was a simple fix, I was on the right track but was trying the \ and ' in the wrong locations. I was able to use your recommendation: I apologize for the novice mistake but have learned a great deal. Thanks for your time and patience.

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

and it wrote to the table

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related