Bash – Need to insert single quotes in text file for use as SQL query using sed

bashsedtext processing

I have created a line of text that looks like this:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);

I want to use sed to make it look like this:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This makes way more sense in context and I have gotten a little farther with it over the last (apparently) 17 hours:

#!/bin/bash
ssh ubnt@xxx.xxx.xxx.xxx brmacs >>MACS.txt mv MACS.txt /etc/persistent scp mhalls@xxx.xxx.xxx.xxx:/etc/persistent/MACS.txt MACS.txt
sed -i "1d" MACS.txt
head -c 58 MACS.txt >>shortmacs.txt
tail -c 18 shortmacs.txt >>usermac.txt
sed 's/"//g' usermac.txt >>usermacrdy.txt
sed -i 's/^/INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,'',/' usermacrdy.txt
sed "s/$/','Auth-Type',':=','Accept');/" usermacrdy.txt > sqlquery.txt
sed -i "s/,,/\,\'\',\'/" sqlquery.txt
rm -f MACS.txt
rm -f shortmacs.txt
rm -f usermac.txt
rm -f usermacrdy.txt

WORKS!!!

The head and tail cut the MAC address out of the original text file xfer'd over from the UBNT CPE device and then I pass it through sed to build the SQL syntax around the MAC address.

After all that I found out the id portion of the query is not needed for success so now I am in slightly the same boat with:

sed -i 's/^/INSERT INTO `radcheck`(`username`, `attribute`, `op`, `value`) VALUES (/' usermacrdy.txt
sed "s/$/','Auth-Type',':=','Accept');/" usermacrdy.txt > sqlquery.txt
sed -i "s/\,\'\',\'//" sqlquery.txt

Best Answer

There are four ways to include the single quote that you need.

One cannot escape a single-quotes string within a single-quoted string. However, one can end the quoted string, insert an escaped single-quote, and then start a new single-quoted string. Thus, to put a single quote in the middle of 'ab', use: 'a'\''b'. Or, using the sed command that you need:

$ sed -r 's/,([^ ),]+)/,'\''\1'\''/g; s/,,/,'\'\'',/g' file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

The second way is to use a double-quoted string, in which case the single-quote can be inserted easily:

$ sed -r "s/,([^ ),]+)/,'\1'/g; s/,,/,'',/g" file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This issue with double-quoted strings is that the shell does processing on them. Here, though, there are no shell-active characters, so it is easy.

The third method is to use a hex escape as PM2Ring demonstrates.

The fourth way, suggested in the comments by Jonathan Leffler, is to place the sed commands in a separate file:

$ cat script.sed 
s/,([^ ),]+)/,'\1'/g
s/,,/,'',/g
$ sed -rf script.sed file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This way has the strong advantage that sed reads the commands directly without any interference from the shell. Consequently, this completely avoids the need to escape shell-active characters and allows the commands to be entered in pure sed syntax.

How the sed solution works

The trick is to put single quotes around the comma-separated strings that you want but not around the others. Based on the single example that you gave, here is one approach:

  • s/,([^ ),]+)/,'\1'/g

    This looks for one or more non-space, non-comma, and non-close-parens characters which follow a comma. These characters are placed inside single quotes.

  • s/,,/,'',/g

    This looks for consecutive commas and places a two single-quotes between them.

OSX and other BSD platforms

To avoid extra backslashes, the above sed expressions use extended regular expressions. With GNU, these are invoked as -r but, with BSD, they are invoked with -E. Also, some non-GNU sed do not accept multiple commands separated with semicolons. Thus, on OSX, try:

sed -E -e "s/,([^ ),]+)/,'\1'/g" -e "s/,,/,'',/g" file

Addendum: Matching a MAC address

From the comments, we have the following input;

$ cat file3
 INSERT INTO radcheck(username, attribute, op, value) VALUES (00:23:32:c2:a9:e8,'Auth-Type',':=','Accept');

And, we want to put single-quotes around the MAC address that follows the open-parens. To do that:

$ sed -r "s/\(([[:xdigit:]:]+)/('\1'/" file3
 INSERT INTO radcheck(username, attribute, op, value) VALUES ('00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

In any locale, [:xdigit:] will match any hexadecimal digit. Thus, ([[:xdigit:]:]+) will match a MAC address (hex digit or colon).

Related Question