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

Question

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 [email protected] brmacs >>MACS.txt mv MACS.txt /etc/persistent scp [email protected]:/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

Answer 1

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).

Answer 2

When you work globally you've got to consider how sed reads. A global substitution is going to divide up a pattern space into individual fields delimited per your specifications and operate on each. The delimiters are recognized from left to right - in the order they are read - and sed will apply each action as soon as it might. Here is a single s///ubstitution statement that can do what you're asking:

sed "s/\(([^)]*)[^,]*\)\{0,1\},\([^,)]*\)/\1,'\2'/g
" <<\IN
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);

When run, that prints:

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

Here's how:

• \(([^)]*)[^,]*\)\{0,1\} - Most important are the distribution of *zero+ matches and the other kind. This entire block is optional - sed will happily match a null string in its place given the opportunity - and it actually does a lot of the time, else this wouldn't work at all. But because \{0,1\} is used sed will not match more than one entire sequence like this. And that's why the distribution is important. Within this block we have:
  • ( - at least one left paren.
  • [^)]* - zero+ not-right parens.
  • ) - one right paren
  • [^,]* - zero+ not-commas.

• sed will match each of these in order - and as a block because they are \(grouped\). The *zero+ match is very important globally - if you use + at least one in that setting you drastically reduce effectiveness of your applied edits. For just the input the block above, sed delimits your string thus:

• INSERT INTO radcheck - This string comes before any of our matches and contains none of them. It is ignored.

• (id, username, attribute, op, value) VALUES ( - This string makes up our entire * optional match block. First sed matches the ( then the whole string of [^)]* then the ) and last - and importantly - the whole string of [^,]* which carries us beyond another match case for (. It cannot now match - and so skip - the next ( for this optional match group. sed will try the next instead.

And while sed applies the replacements for \2 on the second (.,.,.) sequence it will still apply replacements for \1 as well because it still matches \1. I first matched \1 as the entire (.,.,.)..( block, saved it in \1 and replaced it with \1 - with itself. From the beginning of the second sequence up to the next occurring ( sed will replace \1 with null string every time it matches one - which is every time it matches a comma.

Given many sequences like this on a line - and given that commas only occur within the ( ) delimiters, sed will alternate quoting for each sequence. Here's your sample several times:

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

• ,\([^,)]*\) - notice that the only not optional match for the entire regex is the ,comma. The other block occurs before it in the expression and in input and so sed allows it to apply - but the comma takes precedence when all else is equal, and it is the only reason sed does not insert ''s everywhere as it matches all of the null-strings between every byte on the other two blocks do not match a null-string while applying - sed delimits on it.
• sed will continue to match \2 - and save to it - each sequence of chars that are not ) and not ,. It will replace each comma with it encounters with ,'\2'. It will continue in this fashion until it encounters ( at which point it will be able to fill \1 and so will do so.

Answer 3

Try this:

sed 's/,,/,\x27\x27,/'

\x27 is the hex escape code for '

demo

$ echo 'INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);' |
> sed 's/,,/,\x27\x27,/'
INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,'',00:23:32:c2:a9:e8,Auth-Type,:=,Accept);