Using shell script to insert data into remote MYSQL database

Question

I've been trying to get a shell(bash) script to insert a row into a REMOTE database, but I've been having some trouble :(

The script is meant to upload a file to a server, get a URL, HASH, and a file size, connect to a remote mysql database, and insert the data into an existing table. I've gotten it working until the remote MYSQL database bit.

It looks like this:

#!/bin/bash

zxw=randomtext
description=randomtext2

for file in "$@"
 do
 echo -n *****
 ident= *****
 data= ****
 size=` ****
 hash=`****
mysql --host=randomhost --user=randomuser --password=randompass randomdb
insert into table (field1,field2,field3) values('http://www.example.com/$hash','$file','$size');
echo "done"
done

I'm a total noob at programming so yeah :P

Anyway, I added the \ to escape the brackets as I was getting errors. As it is right now, the script is works fine until connects to the mysql database. It just connects to the mysql database and doesn't do the insert command (and I don't even know if the insert command would work in bash).

PS: I've tried both the mysql commands from the command line one by one, and they worked, though I defined the hash/file/size and didn't have the escaping "".

Anyway, what do you guys think? Is what I'm trying to do even possible? If so how?

Any help would be appreciated :)

Solution 1

The insert statement has to be sent to mysql, not another line in the shell script, so you need to make it a "here document".

mysql --host=randomhost --user=randomuser --password=randompass randomdb << EOF
insert into table (field1,field2,field3) values('http://www.site.com/$hash','$file','$size');
EOF

The << EOF means take everything before the next line that contains nothing but EOF (no whitespace at the beginning) as standard input to the program.

Solution 2

This might not be exactly what you are looking for but it is an option.

If you want to bypass the annoyance of actually including your query in the sh script, you can save the query as .sql file (useful sometimes when the query is REALLY big and complicated). This can be done with simple file IO in whatever language you are using.

Then you can simply include in your sh scrip something like:

mysql -u youruser -p yourpass -h remoteHost < query.sql &

This is called batch mode execution. Optionally, you can include the ampersand at the end to ensure that that line of the sh script does not block.

Also if you are concerned about the same data getting entered multiple times and your rdbms getting inconsistent, you should explore MySql transactions (commit, rollback, etc).

Solution 3

Don't use raw SQL from bash; bash has no sane facility for sanitizing the data beforehand. Generate a CSV file and upload that instead.