2

I have a weird problem that I don't have a good way around.

My company, because reasons, stores scripting code in a database table. Don't ask. Whenever I have to insert a new version of the code, I have to go through the file and manually backslash all the strings to be able to run it in my insert statement.

Is there a tool which will escape all of the strings in the file for me so that I can just copy and paste without having to do this manual work?

The file will likely contain single and double quotes as well as quotes within quotes, ie "this is \"weird\"".

Here's an example of the query I'm running:

delimiter $$
insert into table (code) values ("package a.b.c; 
class SomeClass {
    var thing = "so \"weird\" and thing's stuff"

}");

I don't need spaces escaped, nor semicolons, but anything that would break my quotes should be escaped. The contents of the thing variable would break my quoting in the example given above.

  • Please clarify what you need escaped. You said that spaces should not be escaped for example. OK, what else should be? Only quotes? How about semicolons? In the example you show, you want one set of double quotes escaped but not the other. How would that work? – terdon Feb 6 '15 at 18:45
  • I've updated the question. – Naftuli Kay Feb 6 '15 at 18:53
  • 1
    And desired output of that example is...? – jimmij Feb 6 '15 at 18:58
  • 1
    So, should the $$ become \$\$? Should \"weird\" become \\\"weird\\\"? – terdon Feb 6 '15 at 19:01
  • Normally, every database (driver, …) already provides boilerplate (prepared statements, escape functions, …) to handle arbitrary data properly. Why not just use those? – frostschutz Feb 7 '15 at 15:33
2

If you only want to escape every double quotation mark and backslash you could use

perl -wpe 's/([\\"])/\\$1/g'

You could also use this with xclip:

cat myfile | perl -wpe 's/([\\"])/\\$1/g' | xclip -selection clipboard
  • This adds escapes backslashes to single-quoted strings. – mikeserv Feb 7 '15 at 15:08
2

POSIXly you can safely escape any string into one concatenated string for reinput to the shell like:

alias "string=$(cat file)"; alias string

alias will hard-quote its output and prepend (at least) string= to head of the string. bash (in a break with the standard) also adds the string alias to head of the output. Still, you can get an eval-friendly quoted version of any string like:

string=$(alias "string=$(cat file)"; alias string)
string=${string#*=}

Or, to get it into your clipboard:

<infile sh -c 'alias "c=$(cat)" c' | sed 1s/..// | xsel -bi 
  • bash does not add alias when in POSIX mode (invoked as sh or POSIXLY_CORRECT in the environment or --posix, -o posix...) – Stéphane Chazelas Feb 7 '15 at 20:47
  • @StéphaneChazelas - yeah, that's why I used sh at the bottom - it will work even when sh is bash. – mikeserv Feb 7 '15 at 20:49
  • See also: a=$'\n' ksh -c 'alias foo="$a"; alias foo', or single quotes, the OP wanted the text escaped for a SQL interpreter, not a shell. – Stéphane Chazelas Feb 7 '15 at 20:51
  • @StéphaneChazelas - I thought it was for pasting into a command-line SQL query? But, yes, ksh derivatives do the ANSI quote strings. – mikeserv Feb 7 '15 at 20:52
0

bash:

> printf %q "this is \"weird\""
this\ is\ \"weird\"

> printf -v var %q "this is \"weird\""
> echo $var
this\ is\ \"weird\"
  • Can I pipe stdin? ie cat myfile | printf %q | xclip -selection clipboard – Naftuli Kay Feb 6 '15 at 18:36
  • I also don't need spaces escaped and other characters, I'm pasting this into a MySQL query. – Naftuli Kay Feb 6 '15 at 18:37
0

Oracle, mysql and sqlite all have quote function. var thing = quote("so \"weird\" and thing's stuff") may work.

It seems code is transformed one way. It's very difficult to reverse if only single and double quotes are escaped by backslash because we wouldn't know which one to remove.
A better way that can perform bidirectional transformation of plain as well as binary in safe way is base64 encoding.

echo \'\"\' \"\'\" \\
'"' "'" \
echo \'\"\' \"\'\" \\ | base64
JyInICInIiBcCg==
echo JyInICInIiBcCg== | base64 -d
'"' "'" \  

can be compressed to save space.

wc -c  /usr/bin/bashbug
6957
gzip < /usr/bin/bashbug | base64 |wc -c
4094

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.