Perl one-liner recipe: Convert lists into 'comma-separated strings in single quotes' OR 'multiple lines' on the fly

Question

Q: How to convert lists in a swift and convenient way on the fly for further processing?

The focus is on a general approach to achive a fully fluent workflow (without the hassle of handling files, pipes, cut & past and so on) for further processing.

Q1) Convert lists into comma-separated strings in single quotes

Mostly needed to INSERT values in databases like MySQL, MariaDB, Oracle and so on.

Convert A B C into 'A','B','C'

Q2: How to convert list of ids into a list of commands using the ids

Mostly needed to repeat a single command with, lets say, multiple process numbers in order to terminate numerous isakmp sessions (VPN) on a Cisco router due to a lack of shell-capabilities (like for-loops or xargs) on the IOS command line (despite the fact it could be achived using the Cisco IOS tclsh).

Convert 23828 11281 22873 3765 1234 into

clear crypto isakmp 23828
clear crypto isakmp 22873
clear crypto isakmp 11281
clear crypto isakmp 22873
clear crypto isakmp  3765
clear crypto isakmp  1234

Addtion to the main focus about this question

I am fully aware about the risk of SQL injection attacks. But security is not the point here, as I know what kind of data my lists carry. The main focus is to have a more generic approach to convert lists on the fly, while being as much flexible as possible. Certainly, some tasks can be better achieved using the appropriate tool like sed, awk, tr, cut or whatever tool buzz around. Unfortunately, everytime choosing the best tool for a specific task you have to fiddle around with the syntax, switches and aside how the tool work. This is exactly the hassle I want to avoid having a more generic approach at hand.

Hence, bear the topic in mind: Calling a Perl one-liner just by pressing a keystroke which can be easily adjusted/edited in the shell before executing it. My approach - see the answer by myself and this as a side node - fulfills exactly this requirement. So, it could be a good idea to read my answer before just posting a solution or suggestion to question. ;-)

Answer 1

Both can be done quickly and easily using a short perl one-liner.

A1: Build one string (i. e. one single line) with comma-separated strings in single quotes

First create a list to be used in the following (SQL) examples:

cat > list.txt <<EOF
A
B
C
D
EOF

Or in Perl style:

perl -le 'print foreach (A..D)' > list.txt

Create the comma separated list with single quoted strings:

In order to avoid struggling with bash's interpreation of single quotes use the two-digit hex ASCII value \x27 for the single quote < ' >.

perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' list.txt     # 'A','B','C','D'

Same principle, just using the octal ASCII value \047:

 perl -e 'print join ",", map { chomp; qq(\047$_\047) } <>' list.txt    # 'A','B','C','D'

For other purposes (non SQL) where double quotes are needed:

 perl -e 'print join ",", map { chomp; qq("$_") } <>' list.txt          # "A","B","C","D"

In case #, ; or : is needed as separator, just replace the , within the double-quotes of join ",", into what is required.

For example:
join "#",
join ";",
join ":",

As in most cases lists are taken by cut & paste from somewhere else. So, the usage of pbpaste on Mac OS X | macos can substantially shorten the workflow. btw: Any hint for an equivalent Linux command would be highly appreciated. To test the following example first fill the clipboard with the above created list: cat list.txt | pbcopy

 pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>'

Even shorter; replace the clipboard's content with the transformed list on the fly using pbcopy:

 pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' | pbcopy

In order to having it at hand when needed (e. g. by pressing ctrl + x + c ~ c to convert), just write this line to $HOME/.inputrc

"\C-xc": "pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' | pbcopy"

Activate the key-binding (see readline or the many questions tagged readline to learn more):

 bind -f $HOME/.inputrc     # read/activate settings
 bind -s                    # show key-bindings

Now just hit ctrl + x + c for a fully automated conversion of your SQL INSERT Values.

For the sake of completeness some annotations about SQL-quoting

Excerpt taken from this very good clarification about quotes and backticks: Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double quotes.

A2: Create a command list (i. e. many lines) from a list (e. g. process numbers)

This approach builds - in contrast to A1 - multiple lines (a repetition of commands with process ids).

Again: First create a list to be used for the following (Cisco) example:

perl -le 'print foreach (1000..1010)' | pbcopy

In reality copy the real process ids to the clipboard; after that transform the plain list of numbers into the appropriate command list:

pbpaste | perl -wle 'while (<>) {chomp; print "clear crypto isakmp $_"}'

This gives:

clear crypto isakmp 1000
clear crypto isakmp 1001
clear crypto isakmp 1002
clear crypto isakmp 1003
clear crypto isakmp 1004
clear crypto isakmp 1005
clear crypto isakmp 1006
clear crypto isakmp 1007
clear crypto isakmp 1008
clear crypto isakmp 1009
clear crypto isakmp 1010

To have it also at hand when needed (e. g. by pressing ctrl + x + p ~ p for process list), just write this line to $HOME/.inputrc

"\C-xc": "pbpaste | perl -wle 'while (<>) {chomp; print "clear crypto isakmp $_"}' | pbcopy"

Answer 2

The question states that we are not using cut&paste, etc, so I assume we are taking the list items and processing them inside the Perl script.

Q1 posits a very dangerous way to insert into SQL. If the individual list items happen to contain characters that Perl doesn't consider special but SQL does, then you have opened up your database to an injection attack.

Rather than combine the list in Perl, you should create a prepared statement for your DBI layer and then pass the list items directly as arguments.

While it is possible to correctly sanitise elements of a list to avoid misinterpretation when treated as SQL, using the prepared statement approach is much less likely to result in a security hole.

The same is true of Q2. While process id numbers can be easily checked, in the general case, it is better to pass arguments directly, for example using Perl's multi-argument form of exec() or system().

Answer 3

Q1) There are 2 ways.

$ echo "A B C" | perl -lpe ' s/(\S+)/\x27$1\x27/g ; s/\s+/,/g '
'A','B','C'

$ echo "A B C" | perl -ne ' @x=split(/\s+/); $_="\x27$_\x27" for(@x); print join(",",@x) '
'A','B','C'

Q2)

$ echo "23828 11281 22873 3765 1234" | perl -lane ' print "clear crypto isakmp $_" for(@F) '
clear crypto isakmp 23828
clear crypto isakmp 11281
clear crypto isakmp 22873
clear crypto isakmp 3765
clear crypto isakmp 1234

$

Answer 4

You say that your first requirement is "Mostly needed to INSERT values in databases". The solution you have come up with is open to SQL injection attacks and should be avoided.

To avoid this problem, you should always use bind points when inserting external data into a database. Something like this:

open my $fh, '<', 'somefile.txt' or die $!;

chomp(my @data = <$fh>);

my $sql = 'INSERT INTO some_table VALUES (';
$sql .= join ',', ('?') x @data;
$sql .= ')';

# Assume you already have a $dbh
my $sth = $dbh->prepare($sql);
$sth->execute(@data);

(Of course, it's always good practice to explicitly list the names of the columns that you're inserting data into - I've omitted that here for simplicity.)