How to solve SQL query quotes mirroring problem?

Question

MySQL

There is a script:

#!/bin/bash
ixdate=$(date +%s)
CPUCurFreq=$(xenpm get-cpufreq-states | grep "current frequency")
XENLIST=$(xl list)

mysql -h 172.16.0.3 -u ServerInfo -psosiskasardelka << EOF
INSERT INTO serverInfo.serverBoard ('id', 'name', 'update', 'data') VALUES (NULL, 'cpucurfreq', "$ixdate", "$CPUCurFreq");
INSERT INTO serverInfo.serverBoard ('id', 'name', 'update', 'data') VALUES (NULL, 'xenlist', "$ixdate", "$XENLIST");
EOF

echo $CPUCurFreq
echo $XENLIST

It is clear that it does not work because the $XENLIST and $CPUCurFreq variables break the SQL query. For example, in PHP this question MAY be escape_string method to prepare the query. And how to make a similar feint in SH?

Answer 1

In my opinion, it would be more logical to use such a construction to execute a request:

#!/bin/bash
ixdate=$(date +%s)
CPUCurFreq=$(xenpm get-cpufreq-states | grep "current frequency")
XENLIST=$(xl list)

MyUSER="ServerInfo"
MyPASS="sosiskasardelka"
MyHOST="172.16.0.3"
MYSQL="$(which mysql)"

$MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -e "INSERT INTO serverInfo.serverBoard ('id', 'name', 'update', 'data') VALUES (NULL, 'cpucurfreq', "$ixdate", "$CPUCurFreq");"
$MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -e "INSERT INTO serverInfo.serverBoard ('id', 'name', 'update', 'data') VALUES (NULL, 'xenlist', $ixdate, $XENLIST);"

echo $CPUCurFreq
echo $XENLIST

It should be correct both with double quotes and without them, so I write both options.

Answer 2

The general principle is to make a function to prepare the desired output

#!/bin/bash

func()
{
    local tab date freq
    local text

    tab=$1
    date=$2
    freq=$3

    text="INSERT INTO $tab ('id', 'name', 'update', 'data')"
    text="$text VALUES (NULL, 'cpucurfreq', '$date', '$freq');"

    echo "$text"
}

cat -n <<EOF
`func "a" "b  b" "c  c"`
`func "a" "e  e" "f  f"`
EOF

exit 0

Conclusion

[[email protected] sh]$ ./t.sh 
     1  INSERT INTO a ('id', 'name', 'update', 'data') VALUES (NULL, 'cpucurfreq', 'b  b', 'c  c');
     2  INSERT INTO a ('id', 'name', 'update', 'data') VALUES (NULL, 'cpucurfreq', 'e  e', 'f  f');
[[email protected] sh]$