As far as I can make out, what you are trying to do is generate, in Lisp, some string which contains valid SQL, which itself contains an SQL literal string.
First of all this is an instance of an antipattern I call 'language in a string'. I keep thinking I have a big diatribe about this to point people at but it seems I haven't. Suffice it to say it's kind of the antithesis of what Lisp people have tried to achieve for more than 60 years and it's why we have SQL injection attacks and a lot of the other crap that afflicts us. But that battle is long lost and all we can do is try to avoid actually drowning in the sea of mud and rotting bits of people that now litters the battlefield.
So, to do this you need to be able to do two things.
- You need to be able to generate an SQL literal string from a sequence of characters (or from a string). This means you need to know the syntax of literal SQL strings and in particular what characters are legal in them and how you express characters which are not.
- You need to be able to interpolate this string into a CL string.
The second of these is trivial: this is what format's ~A directive does. Or if you want to get fancy you could use cl-interpol.
For the first, I don't know the syntax of SQL literal strings, but I will give an example which assumes the following simple rules:
- literal strings are delimited by
" characters;
- the character
\ escapes the following character to remove it of any special significance;
- all other characters are allowed (this is almost certainly wrong).
Well, there are lots of ways of doing this, all of which involve walking along the sequence of characters looking for the ones that need to be escaped. Here is something reasonably horrible and quick which I wrote. It needs a macro called nlet which is Scheme's 'named let' construct, and it assumes TRO in the implementation (if your implementation does not do this, get one that does).
(defmacro nlet (name bindings &body forms)
"named let"
(multiple-value-bind (vars vals) (values (mapcar (lambda (b)
(etypecase b
(symbol b)
(cons (first b))))
bindings)
(mapcar (lambda (b)
(etypecase b
(symbol 'nil)
(cons
(unless (null (cddr b))
(error "bad binding ~A" b))
(second b))))
bindings))
`(labels ((,name ,vars ,@forms))
(,name ,@vals))))
(defun ->sql-string (seq)
;; turn SEQ (a sequence of characters) into a string representing an
;; SQL literal string (perhaps)
(nlet loupe ((tail (coerce seq 'list))
(accum '()))
(if (null tail)
(coerce (cons #\" (nreverse (cons #\" accum))) 'string)
(destructuring-bind (first . rest) tail
(loupe rest
(case first
((#\\ #\")
(append (list first #\\) accum))
(otherwise
(cons first accum))))))))
So now:
> (->sql-string "foo")
"\"foo\""
> (->sql-string '(#\f #\\ #\o #\" #\o))
"\"f\\\\o\\\"o\""
This is made ugly by the Lisp printer, but (see above) we can see what the strings actually are:
> (format t "~&select x from y where x.y = ~A;~%"
(->sql-string '(#\f #\\ #\o #\" #\o)))
select x from y where x.y = "f\\o\"o";
nil
And you can see that the SQL literal string obeys the rules I set out above.
Before using anything like this check what the rules are, because if you get them wrong you are possibly open to SQL injection attacks.
