BASH how to simplify query to SQLITE-CodePudding

i have a simple SQLITE database and working query from BASH like this:

/usr/local/bin/sqlite3 $database "INSERT INTO ac VALUES (NULL, datetime('now', 'localtime'),'`echo $QUERY_STRING|cut -f2 -d'&'`','`echo $QUERY_STRING|cut -f3 -d'&'`','`echo $QUERY_STRING|cut -f4 -d'&'`','`echo $QUERY_STRING|cut -f5 -d'&'`','`echo $QUERY_STRING|cut -f6 -d'&'`','`echo $QUERY_STRING|cut -f7 -d'&'`');"

My QUERY_STRING is:

QUERY_STRING="u=bbaacc&1&233.90&0.11&0.00&8.35&0.00"

How to simplify this query? I think there is a simple solution to this, but I can't find it. In the future my QUERY_STRING will get bigger and it will look like this:

QUERY_STRING="u=bbaacc&1&233.90&0.11&0.00&8.35&0.00&2&233.80&0.07&0.80&0.00&0.05&3&233.70&0.07&0.90&0.00&0.05"

CodePudding user response：

You want the second through seventh "fields" (so to speak) from the query string? I'd use awk to do the split-select-merge operation:

params="'$(echo "$QUERY_STRING" | awk -F '&' -v OFS="','" '{print $2, $3, $4, $5, $6, $7}')'"

The awk program splits fields on &, and prints two through seven with ',' (the output field separator) between them. There are also single-quotes at the beginning and end, outside the whole $( ).

And then just use that in the actual query:

/usr/local/bin/sqlite3 "$database" "INSERT INTO ac VALUES (NULL, datetime('now', 'localtime'),$params);"

Note that I would recommend using lower- or mixed-case variable names (i.e. query_string instead of QUERY_STRING) to avoid conflicts with the many all-caps names with special meanings. Also, you should almost always double-quote variable references (e.g. echo "$QUERY_STRING" instead of echo $QUERY_STRING) to avoid weird parsing. I always recommend running scripts through shellcheck.net to check for mistakes like missing double-quotes.

BTW, since that QUERY_STRING looks a lot like it came from a URL, and therefore from some web-ish client, it's probably from an untrustworthy source. Putting untrusted data directly into an SQL query is the classic way to get an SQL injection vulnerability (obligatory relevant xkcd). You should be sure to validate the data before letting it near your database.

If I understand the format of the string, something like this should work:

valid_query='^u=[.&[:alnum:]] $'
if [[ ! "$QUERY_STRING" =~ $valid_query ]]; then
    echo "Bad query, cancelling." >&2
    exit or return or something
fi
[do the actual query here]

That regex pattern looks for something that starts with "u=", then has some mix of ".", "&", letters, and digits. All those characters should be safe to allow in queries without allowing an injection.