LinuxQuestions.org - Bash script - how to add slashes to quotations for sending to MySQL?

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Bash script - how to add slashes to quotations for sending to MySQL? (https://www.linuxquestions.org/questions/programming-9/bash-script-how-to-add-slashes-to-quotations-for-sending-to-mysql-464822/)

Bash script - how to add slashes to quotations for sending to MySQL?

Hi all,

I have the following script:

Code:

thedate=$(date +"%Y-%m-%d")

thetime=$(date +"%H:%M:%S")



if [ "$(GET domain | grep 'Welcome')" ]

then echo "INSERT INTO hc_checks ( id_check ,date , time , status)

VALUES (

'','$thedate', '$thetime', '1'

);" | mysql --user --pass=*** host_check

else

page_text=$(GET domain)

echo "INSERT INTO hc_checks ( id_check ,date , time , status, page_text)

VALUES (

'','$thedate', '$thetime', '0', '$page_text'

);" | mysql --user --pass=*** host_check

fi

A simple script that checks the homepage for the text string "Welcome". If it doesn't find the string it inserts the page it does find into a MySQl database.

The problem is that the string may contain " and ' characters which MySQL doesn't like. In PHP I would solve this problem by:

$page_text= addslashes($page_text); but I have no idea how to do this in Bash. I can't even work out how to find/replace!

Any help greatly appreciated.

Leon

The best place to look for these answers is in Advanced Bash Scripting Guide

Find and replace is

Code:

${string/substring/replacement}                Replace first match of $substring with $replacement

${string//substring/replacement}        Replace all matches of $substring with $replacement

I think you can just escape quotes with a backslash (i.e. \" ) but you'd have to double check as I stuck on windows at work at the moment.

Is it the $page_text where you want to escape quotes?

Because there are two types of quotes to escape, you could do it like this:
page_text="${page_text//\'/\'}"
page_text="${page_text//\"/\\\"}"

or page_text=$(GET domain | sed 's/['"'"'"]/\\&/g')
Notice the sed command. MySQL isn't alone in not liking embedded quotes. I had to break up the sed command so that the shell wouldn't balk.

Here I'll add some extra spaces so that it is clearer:

Code:

sed 's/[' "'" '"]/\\/&/g'

Embedding single quotes in a bash string is the tricky part.

example demo:

Code:

jschiwal@hpamd64:~> GET domain

Welcome. This is a "sample" test.  Let's see another sentence.

jschiwal@hpamd64:~> GET | sed 's/['"'"'"]/\\&/g'

Welcome. This is a \"sample\" test.  Let\'s see another sentence.

Code:

jschiwal@hpamd64:~> page_text="$(GET domain)"

jschiwal@hpamd64:~> page_text="${page_text//\'/\'}"

jschiwal@hpamd64:~> page_text="${page_text//\"/\\\"}"

jschiwal@hpamd64:~> echo $page_text

Welcome. This is a \"sample\" test. Let\'s see another sentence.

Guys - thanks very much for the help there. Some very tricky syntax!

Heh, you can actually just use php if you'd like to,

Code:

#!/usr/bin/php

<?php

        while($str = fgets(STDIN)) {

                echo(addslashes($str));

        }

?>

Save it to whatever name you like, then either make it executable (chmod 755 xxx.php for instance), or run it via php -f xxx.php, where xxx.php is the filename. Pipe it some MySQL command (generate-sql | xxx.php), and get back the slashified version.