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 filesize, 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.site.com/$hash','$file','$size');
echo "done"
done

I'm a total noob at programming so yeh :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 dont 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 im trying to do even possible? If so how?

Any help would be appreciated :)

Answer 1

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.

Answer 2

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 EOF as standard input to the program.

Answer 3

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).