Processing MySQL result in bash

Question

Hi,

I'm currently having a already a bash script with a few thousand lines which sends various queries MySQL to generate applicable output for munin.

Up until now the results were simply numbers which weren't a problem, but now I'm facing a challenge to work with a more complex query in the form of:

$ echo "SELECT id, name FROM type ORDER BY sort" | mysql test
id      name
2       Name1
1       Name2
3       Name3

From this result I need to store the id and name (and their respective association) and based on the IDs need to perform further queries, e.g. SELECT COUNT(*) FROM somedata WHERE type = 2 and later output that result paired with the associated name column from the first result.

I'd know easily how to do it in PHP/Ruby , but I'd like to spare to fork another process especially since it's polled regularly, but I'm complete lost where to start with bash.

Maybe using bash is the wrong approach anyway and I should just fork out?

I'm using GNU bash, version 3.2.39(1)-release (i486-pc-linux-gnu).

Answer 1

You're going to "fork out," as you put it, to the mysql command line client program anyhow. So either way you're going to have process-creation overhead. With your approach of using a new invocation of mysql for each query you're also going to incur the cost of connecting to and authenticating to the mysqld server multiple times. That's expensive, but the expense may not matter if this app doesn't scale up.

Making it secure against sql injection is another matter. If you prompt a user for her name and she answers "sally;drop table type;" she's laughing and you're screwed.

You might be wise to use a language that's more expressive in the areas that are important for data-base access for some of your logic. Ruby, PHP, PERL are all good choices. PERL happens to be tuned and designed to run snappily under shell script control.

Answer 2

You would use a while read loop to process the output of that command.

echo "SELECT id, name FROM type ORDER BY sort" | mysql test | while read -r line
do
    # you could use an if statement to skip the header line
    do_something "$line"
done

or store it in an array:

echo "SELECT id, name FROM type ORDER BY sort" | mysql test | while read -r line
do
    array+=("$line")
done

That's a general overview of the technique. If you have more specific questions post them separately or if they're very simple post them in a comment or as an edit to your original question.