Bash variables

Springs · 08-30-2018, 04:29 PM

i'm having issues with a script that i'm working on.

Within the script i have 2 variables which are pulling txt from a file and writing it into a database.

my problem is that some of the words have a single apostrophe in them which causes my script to fail or write the literal variable name to the database. eg isn't / i'm / i've etc..

Any ideas on how i can work around it? I've tried single and double quotes and also searching google but a lot of results don't talk about actual words with an apostrophe.

lougavulin · 08-30-2018, 04:34 PM

For the databases I know, I think it is SQL ANSI, you can protect a single quote by adding another one : ' become '' (2 singles quotes)

hydrurga · 08-30-2018, 04:49 PM

Can you paste the relevant portion of the script here?

lougavulin · 08-30-2018, 05:04 PM

Sorry it seems I read your post to quickly for my first answer.

So I go with hydrurga.

Springs · 08-30-2018, 05:06 PM

var1=$(cat "$file" | cut -d "-" -f1)

echo "INSERT INTO database ("id", "var1", "var2") VALUES ("$id", "$var1","$var2");" | mysql -h 192.168.1.8 -P 3307 -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE"

this is the basic part of the script thats not working. both $var1 and $var2 get entered into the db literally as $var1 etc. as mentioned i've tried different quotes but can't get anything to take

lougavulin · 08-30-2018, 06:43 PM

Something like that ?

Code:

var1=$(cat "$file"| cut -d "-" -f1 | sed "s/'/''/g")

echo "INSERT INTO database (id, var1, var2) VALUES ('$id', '$var1','$var2');" | mysql -h 192.168.1.8 -P 3307 -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE"

ondoho · 08-31-2018, 01:19 AM

since this is a shell script, shouldn't the ' be escaped like this: \' ? or maybe even \'\' ?

NevemTeve · 08-31-2018, 02:30 AM

Maybe it could be solved with little steps like creating a mysql-escaping function:

Code:

#!/usr/local/bin/bash

escape_for_mysql () {
    sed "s_[\\\\'\"]_\\\\&_g" <<<"$1"
}

test1 () {
    local V=$(escape_for_mysql "$1")
    printf 'escape[%s]=[%s]\n' "$1" "$V"
}

test1 'back\slash'
test1 'quote: "text"'
test1 "'s1'='s2'"

result:

Code:

escape[back\slash]=[back\\slash]
escape[quote: "text"]=[quote: \"text\"]
escape['s1'='s2']=[\'s1\'=\'s2\']

Having this you can write something like this:

Code:

cmd="$(printf "INSERT INTO table (\`id\`, \`var1\`, \`var2\`) VALUES ('%s', '%s', '%s');" \
 "$(escape_for_mysql "$id")" \
 "$(escape_for_mysql "$var1")" \
 "$(escape_for_mysql "$var2")")"

Springs · 09-01-2018, 05:46 AM

Quote:

Originally Posted by lougavulin

Something like that ?

Code:

var1=$(cat "$file"| cut -d "-" -f1 | sed "s/'/''/g")

echo "INSERT INTO database (id, var1, var2) VALUES ('$id', '$var1','$var2');" | mysql -h 192.168.1.8 -P 3307 -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE"

tried this and it seems to have worked.

Going to run some more test data through to make sure but it looks good so far!